July « 2009 « Oracle DBA – Tips and Techniques

RMAN Recovery using the SWITCH DATABASE TO COPY command

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 related to restoring large datafiles from tape in the event of a media failure.

The downside of this is that we need to consider the disk space (and cost) of additional disks which will be required to hold the recovered incremental copies of backups on disk. Also as we will see, the SWITCH DATABASE TO COPY command will alter the names of all the datafiles and there is manual work involved in renaming them back to their original datafile names.

The following RMAN command can be used to take an optimized incremental backup to disk. Note in the

Continue reading RMAN Recovery using the SWITCH DATABASE TO COPY command

11g Optimizer Plan Stabilty using SQL Plan Baselines

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 there is proven performance benefits of adopting a new plan. Plans can also be manually adopted or “evolved”.

One of the dangers of upgrading from 10g to 11g (or between any release) is that execution plans may change in the new version which could affect application performance.

But with this new 11g feature, if we have tried and trusted 10g execution plans in place, we can ensure that the same plans will be used by the optimizer even after the migration unless new 11g plans are found to offer better performance. This can be achieved by capturing 10g

Continue reading 11g Optimizer Plan Stabilty using SQL Plan Baselines

Drop and Recreate Online Redolog Files

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 database drop logfile group 1;

alter database drop logfile group 1
*
ERROR at line 1:
ORA-01623: log 1 is current log for instance test (thread 1) – cannot drop
ORA-00312: online log 1

Continue reading Drop and Recreate Online Redolog Files

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

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 encounter the RMAN-06563 error even if we set the DBID or explicitly alllocate a channel for a tape device.

set dbid=693232013;

executing command: SET DBID

RMAN> run {
2> restore controlfile;
3> recover database;
4> }

Starting restore at 28-JUL-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/28/2009 12:17:19
RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP

RMAN> run {
2> allocate channel c1 device type sbt_tape;
3> restore controlfile;
4> alter database mount;
5> recover database;
6> }

released channel: ORA_DISK_1
allocated channel: c1
channel c1:

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

Script – Check RMAN Backup Status

Scripts to check backup status and timings of database backups -

This script will be run in the database, not the catalog.

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,
to_char(END_TIME,’mm/dd/yy hh24:mi’) end_time,
elapsed_seconds/3600 hrs
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;

This script will report all on full and incremental backups, not archivelog backups -

Troubleshooting Grid Control Agent issues with startup

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. All rights reserved.
—————————————————————
Agent is Not Running

START AGENT -

oracle(DATABASE)@fhostname:./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 …… failed.
Failed to start HTTP listener.
Consult the log files in: /u01/oracle/agent10g/sysman/log

To solve the problem.

CHECK IF PORT 3872 – PORT USED BY AGENT IS IN USE.

oracle(DATABASE)@hostname:netstat -an | grep 3872
tcp4 0 0 *.3872 *.*

Continue reading Troubleshooting Grid Control Agent issues with startup

Alert Log Test Message

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.

exec dbms_system.ksdwrt(2,’ORA-00600: This is a test error message for monitoring and can be ignored.’);

To check -

cd $BDUMP

view alert_SID.log and you will find the alert written to the log.

Tue Jul 28 10:06:28 2009 ORA-00600: ” This is a test error message for monitoring and can be ignored “

Exporting and Importing AWR snapshot data

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 grow too large in size or if the retention was increased beyond the default of 7 days.

A good solution is to have a central repository and move statistical AWR data periodically to this central repository database using the Oracle supplied awrextr.sql and awrload.sql scripts which can be found in the $ORACLE_HOME/rdbms/admin directory.

The AWR History is by default maintained for 7 days and the data is gathered in the AWR repository tables every hour by default.

The current snapshot retention settings and data gathering frequency can be determined by the query shown below. Note in this case the

Continue reading Exporting and Importing AWR snapshot data

RMAN Validate Backup

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 to test the validity of a backup.

RMAN> run {
2> set until time “to_date(’23-JUL-2009 17:00:00′,’DD-MON-YYYY HH24:MI:SS’)”;
3> restore database validate;4> }

executing command: SET until clause
using target database control file instead of recovery catalog

Starting restore at 23-JUL-09
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=159 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.5.1.0
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=143 devtype=DISK

channel ORA_SBT_TAPE_1: starting validation of datafile backupset
channel ORA_SBT_TAPE_1: reading from backup piece 1pkkbkop_1_1
channel ORA_SBT_TAPE_1: restored backup piece 1
piece handle=1pkkbkop_1_1 tag=TAG20090717T085801
channel ORA_SBT_TAPE_1: validation complete, elapsed time: 00:04:27
Finished restore at 23-JUL-09

We can validate spfile and controlfile backups as well as shown below

RMAN> restore validate spfile;
Starting restore at 23-JUL-09
using channel

Continue reading RMAN Validate Backup

Unix – tar and gzip commands

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 dba 105952962 Feb 26 11:31 test.tar.gz

Now to untar and uncompress the files back to the original area -

cd /u02/oradata/test

gzip -dc < /u02/oradata/test_bkup/test.tar.gz | tar xvf -

Oracle DBA – Tips and Techniques

That’s Me

Feedback

Categories

Archives

Recent Posts

Administration