Script to check integrity of a 9i or 10g database before upgrade to 11g. Run this script connected as sysdba in the 9i or 10g database. NAME: DBUPGDIAG.SQL This script can be downloaded from Oracle Metalink note 556610.1 — PURPOSE: — This script is intended to provide a user friendly output to diagonise — the […]
FLASHBACK DATABASE – IMPACT OF RESETLOGS- A flashback database can be completed and the database can either be opened for READ ONLY or opened with a RESETLOGS option. As long as the RESETLOGS option is not used, a flashback can be rolled back . Example – Create table test – insert some records – select […]
CREATE OR REPLACE procedure utl_file_test_write1 ( path in varchar2, filename in varchar2, firstline in varchar2, secondline in varchar2) is output_file utl_file.file_type; begin output_file := utl_file.fopen (path,filename, ‘W’); utl_file.put_line (output_file, firstline); utl_file.put_line (output_file, secondline); utl_file.fclose(output_file); –exception — when others then null; end; / begin utl_file_test_write1 ( ‘/u03/users/ofsa/ofsatest/oracle_utl_data’, ‘utl_file_test’, ‘first line’, ‘second line’ ); end; / This […]
If the agent in GRID control in not uploading to the OMS – cd $AGENT_HOME/bin oracle(DATABASE)@prdu030:./emctl start agent Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0. Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved. Starting agent ………. started. oracle(DATABASE)@prdu030:./emctl status agent Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0. Copyright (c) 1996, […]
FLASHBACK DATABASE TO SCN Time to FLASHBACK DATABASE TO – 06:30 a.m on 31 July. Get oldest flashback time – SELECT OLDEST_FLASHBACK_SCN,to_char(OLDEST_FLASHBACK_TIME,’DD-MON-YYYY HH24:MI:SS’) FROM V$FLASHBACK_DATABASE_LOG; If there are logs up until 06:30 on 31 Jul, you can flashback. Get the SCN number to flashback to – col first_change# format 99999999999 select first_change# ,to_char(first_time,’DD-MON-YYYY HH24:MI:SS’),sequence# from […]
Method to drop and recreate online redolog files with 2 members to each group. Firstly ORACLE will never allow you to drop the current ONLINE redolog file – Ex : SQL> select GROUP#,THREAD#,STATUS from v$log; GROUP# THREAD# STATUS ———- ———- —————- 1 1 CURRENT 2 1 UNUSED 3 1 INACTIVE 4 1 INACTIVE SQL> alter […]
Scripts to check backup status and timings of database backups – This script will be run in the database, not the catalog. Login as sysdba – This script will report on all backups – full, incremental and archivelog backups – col STATUS format a9 col hrs format 999.99 select SESSION_KEY, INPUT_TYPE, STATUS, to_char(START_TIME,’mm/dd/yy hh24:mi’) start_time, […]
Sometimes the GRID CONTROL agent will not start because an old HTTP process is still running on the host – this usually happens if the agent has crashed for any reason. Example – Check status of agent. oracle(DATABASE)@hostname:./emctl status agent Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0. Copyright (c) 1996, 2007 Oracle Corporation. […]
FORCE MESSAGE TO BE WRITTEN TO THE ALERT LOG If you wish to force an alert to be written to the alert log- for example ORA-600 to test the functioning of a monitoring system you can use this command to force an ORA-600 alert to be written to the alert log. Login as sysdba exec […]
The validate command for backup and restore is useful to confirm that backup and restoration is posssible and valid without actually backing up or restoring datafiles. We can validate the restore of a spfile,controlfile,archivelog or even the whole database backup. The following example enables us to specify a point in time upto which we need […]
tar and compress a bunch of datafiles and then untar and uncompress them cd /u02/oradata/test– (area of database files ) Now tar and compress and copy the files to backup area – /u02/oradata/test_bkup and name the file as test.tar.gz tar cvf – * |gzip -c >/u02/oradata/test_bkup/test.tar.gz – cd /u02/oradata/test_bkup > ls -lrt -rw-r–r– 1 ofsad1 […]
If a database is cloned or recreated on another machine with the same name, the new database will not be discovered on the GRID. There is a reason behind this . Example Database called test already discovered on GRID – database on ABC machine. Create or clone a database with the same name test on […]
Sometimes , you may install the 10g OEM GRID agent before you actually create a new database or you may add a new database to an existing box. This new database will have to be discovered by GRID CONTROL. I created a database called test on a box which already had a Grid agent running […]
Many DBA’s have to be familiar with TSM ( Tivoli Storage Manager) commands to backup files to tape or restore backups from tape. To query backups – for example all compressed files with ‘.Z’ extension. oracle:/u05/backup/test > dsmc q b -inactive ‘*.Z’ File /u05/backup/TEST/20090702_0841.test_tables.dmp.Z To Restore the file above – dsmc restore -inactive /u05/backup/TEST/20090702_0841.ofsap_tables.dmp.Z To […]
After a clone of a database to another machine with the same database name or if a new database name is created by recreating the controlfile, there is a need to recreate the dbid especially if the new database is to be registered in the same RMAN catalog. This ensures especially if an RMAN catalog […]
While restoring and recovering a database UNTIL TIME which uses the block change tracking file, the recovery may be interrupted. This was the error – released channel: ch1 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 02/18/2009 09:33:29 ORA-00283: recovery session canceled due to errors RMAN-11003: […]
MANUAL DELETION OF GRID CONTROL JOBS. Sometimes GRID CONTROL jobs may show as RUNNING although they have completed. This will restrain the job from returning to the ‘SCHEDULED’ state and thereby will not run. To manually remove a job in the job queue from the backend – Login to the repository owner in the Grid […]
To tar and compress files, backup the files and also untar and uncompress the same files. cd /u01/oradata/test – area of files . Now tar and compress and copy the files to backup area – /u02/oradata/test_bkup named as test.tar.gz tar cvf – * |gzip -c >/u02/oradata/test_bkup/test.tar.gz – Check for compressed files – cd /u02/oradata/test_bkup test:/u02/oradata/test_bkup> […]
CREATING NEW RMAN CATALOG Database name to store catalog – RMAN10CAT 1. Create Tablespace for catalog owner in catalog database Login to RMAN10CAT database as sysdba SQL> create tablespace rman10 datafile ‘/u03/oradata/rman10cat/rman10.dbf’ size 200m autoextend on; Create catalog owner in Catalog database create user rman10 identified by rman default tablespace rman10 temporary tablespace temp; Issue […]
Steps to take to use logminer to check for changes made to a certain table on a certain date. 1. Restore the archive log files from backup pertaining to archivelogs generated on the day and approximate time if you are not certain of the exact time – $ rman target / catalog rman/rmann@rcat Recovery Manager: […]
