RMAN Recovery using the SWITCH DATABASE TO COPY command

Posted by Gavin Soorma
On July 31, 2009
3 Comments

By using Optimized Incremental Backup to disk backup strategy, an up to date copy of the database is always available on disk. In the event of a failure, the SWITCH command will point the controlfile to the backup of the datafiles that are present on disk. In this way, we can significantly reduce the downtime […]

11g Optimizer Plan Stabilty using SQL Plan Baselines

Posted by Gavin Soorma
On July 30, 2009
0 Comments

In Oracle 11g, we can ensure that the same proven execution plans are used by the Optimizer regardless of any change which can effect the optimizer like a version change, index drop or recreate, init.ora parameter change etc. Once plans are marked as enabled and accepted, the optimizer will not use any new plans unless […]

Posted by Arjun Raja
On July 29, 2009
0 Comments

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 […]

RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP

Posted by Gavin Soorma
On July 28, 2009
0 Comments

Backup and Recovery best practices dictate that we must use a RMAN recovery catalog and also have the controlfile AUTOBACKUP enabled. If we do not do either and we lose all the controlfiles, we cannot restore the controlfiles even if we have taken a backup to tape as shown in the case below. We will […]

Posted by Arjun Raja
On July 28, 2009
2 Comments

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, […]

Troubleshooting Grid Control Agent issues with startup

Posted by Arjun Raja
On July 28, 2009
0 Comments

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. […]

Posted by Arjun Raja
On July 28, 2009
0 Comments

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 […]

Exporting and Importing AWR snapshot data

Posted by Gavin Soorma
On July 25, 2009
0 Comments

The AWR tables contains a wealth of important performance data which can be very useful in performance tuning trend analysis and also when comparing performance between two seperate periods of time. AWR data is stored in the WRH$ and DBA_HIST tables in the SYSAUX tablespace. There could be performance implications if these tables were to […]

Posted by Arjun Raja
On July 23, 2009
0 Comments

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 […]

Posted by Arjun Raja
On July 23, 2009
0 Comments

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 […]

Posted by Gavin Soorma
On July 22, 2009
1 Comments

After a data guard failover operation has been performed, instead of recreating the original primary database as the new standby database, we can use the FLASHBACK DATABASE to SCN command instead. This is particulary important in case the size of the primary database would mean a long backup and restore RMAN operation to recreate the […]

Posted by Arjun Raja
On July 22, 2009
1 Comments

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 […]

Posted by Arjun Raja
On July 22, 2009
0 Comments

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 […]

Posted by Gavin Soorma
On July 19, 2009
0 Comments

In this example we will perform an online definition of the EMP table in the SCOTT schema by performing the following tasks online: 1) Range partition the EMP table based on empno 2) Change the column name of SALARY to SAL Verify that the table is a candidate for online redefinition SQL> conn / as […]

Posted by Gavin Soorma
On July 17, 2009
0 Comments

Locating Files under a particular directory find . -print |grep -i test.sql Using AWK in UNIX To remove a specific column of output from a UNIX command – for example to determine the UNIX process Ids for all Oracle processes on server (second column) ps -ef |grep -i oracle |awk ‘{ print $2 }’ Changing […]

Posted by Gavin Soorma
On July 17, 2009
0 Comments

Checks to be performed at the machine level (note the example is Red Hat Linux specific) run queue should be ideally not more than the number of CPU’s on the machine At the maximum it should never be more than twice the number of CPU’s. This is denoted by the column ‘r’ in the vmstat […]

Posted by Gavin Soorma
On July 16, 2009
1 Comments

If we have a number of Data Guard physical standby database environments to manage, the following report can help us quickly identify the log transported and log applied status of the Primary and Standby databases in our environment and if any standby database is lagging behind the primary as well. The script can also be […]

Posted by Arjun Raja
On July 15, 2009
0 Comments

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 […]

Posted by Arjun Raja
On July 14, 2009
0 Comments

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 […]

Posted by Gavin Soorma
On July 14, 2009
0 Comments

If we are using Guaranteed Restore Points with Flashback logging turned on, we need to exercise care that we drop restore points that are not in use or that we allocate sufficient space for the flashback logs as specified by the init.ora parameter db_recovery_file_dest_size. This is because the flashback logs will be retained and not […]

Page 1 of 412 3 4

News

RMAN Recovery using the SWITCH DATABASE TO COPY command

11g Optimizer Plan Stabilty using SQL Plan Baselines

Drop and Recreate Online Redolog Files

RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP

Script – Check RMAN Backup Status

Troubleshooting Grid Control Agent issues with startup

Alert Log Test Message

Exporting and Importing AWR snapshot data

RMAN Validate Backup

Unix – tar and gzip commands

Flashback Database after a Data Guard Failover

EM Grid Control target discovery after a clone

EM Grid Control Agent Target Discovery

10g Online Table Redefinition using DBMS_REDEFINTION

Unix for the Oracle DBA – Part 1

Performance Tuning Tips and Techniques

Data Guard Log Shipping Report

TSM (Tivoli Storage Manager) – Using the dsmc command

Change DBID of database

Guaranteed Restore Points can cause a database hang