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 first run, since no copy of any of the datafiles are found since this is the first backup, no recovery is performed.

But subsequent backups will find that the backup copies of the datafiles will undergo recovery as changes which have occurred since the last incremental backup are applied to them making them ‘current’.

 run {
allocate channel c1 device type disk format '/u02/oradata/backup/bkp.%u';
recover copy of database with tag 'LEVEL0_BKP';
backup incremental level 1 for recover of copy with tag 'LEVEL0_BKP' database;
}

RMAN>  run {
allocate channel c1 device type disk format '/u02/oradata/backup/bkp.%u';
recover copy of database with tag 'LEVEL0_BKP';
backup incremental level 1 for recover of copy with tag 'LEVEL0_BKP' database;
}
2> 3> 4> 5>
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=143 devtype=DISK

Starting recover at 31-JUL-09
no copy of datafile 1 found to recover
no copy of datafile 2 found to recover
no copy of datafile 3 found to recover
no copy of datafile 4 found to recover
no copy of datafile 5 found to recover
no copy of datafile 6 found to recover
no copy of datafile 7 found to recover
no copy of datafile 8 found to recover
no copy of datafile 9 found to recover
Finished recover at 31-JUL-09

Starting backup at 31-JUL-09
no parent backup or copy of datafile 2 found
no parent backup or copy of datafile 1 found
no parent backup or copy of datafile 3 found
no parent backup or copy of datafile 4 found
no parent backup or copy of datafile 8 found
no parent backup or copy of datafile 6 found
.......
.......

RMAN>  run {
allocate channel c1 device type disk format '/u02/oradata/backup/bkp.%u';
recover copy of database with tag 'LEVEL0_BKP';
backup incremental level 1 for recover of copy with tag 'LEVEL0_BKP' database;
}2> 3> 4> 5>

allocated channel: c1
channel c1: sid=140 devtype=DISK

Starting recover at 31-JUL-09
channel c1: starting incremental datafile backupset restore
channel c1: specifying datafile copies to recover
recovering datafile copy fno=00001 name=/u02/oradata/backup/bkp.2qklgmt6
recovering datafile copy fno=00002 name=/u02/oradata/backup/bkp.2pklgmpt
recovering datafile copy fno=00003 name=/u02/oradata/backup/bkp.2rklgmvr
recovering datafile copy fno=00004 name=/u02/oradata/backup/bkp.2sklgn0u
recovering datafile copy fno=00005 name=/u02/oradata/backup/bkp.2vklgn30
recovering datafile copy fno=00006 name=/u02/oradata/backup/bkp.2uklgn2h
recovering datafile copy fno=00007 name=/u02/oradata/backup/bkp.31klgn3f
recovering datafile copy fno=00008 name=/u02/oradata/backup/bkp.2tklgn1o
recovering datafile copy fno=00009 name=/u02/oradata/backup/bkp.30klgn37
channel c1: reading from backup piece /u02/oradata/backup/bkp.33klgnda
channel c1: restored backup piece 1
piece handle=/u02/oradata/backup/bkp.33klgnda tag=TAG20090731T103002
channel c1: restore complete, elapsed time: 00:00:08
Finished recover at 31-JUL-09

Starting backup at 31-JUL-09
channel c1: starting incremental level 1 datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00002 name=/u02/oradata/testdb/undotbs01.dbf
input datafile fno=00001 name=/u02/oradata/testdb/system01.dbf
input datafile fno=00003 name=/u02/oradata/testdb/sysaux01.dbf
input datafile fno=00004 name=/u02/oradata/testdb/users01.dbf
input datafile fno=00008 name=/u02/oradata/testdb/rman10.dbf
input datafile fno=00006 name=/u02/oradata/testdb/users02.dbf
input datafile fno=00005 name=/u02/oradata/testdb/example01.dbf
input datafile fno=00009 name=/u02/oradata/testdb/users03.dbf
......
......

Let us simulate a database failure by removing all the datafiles at the OS level.

We will then use the SWITCH DATABASE TO COPY command to point the database to the backups which are available on disk and note how the recovery is performed in a few seconds without any any backups having to be restored from tape and no archive log files also being applied.

testdb:/u02/oradata> cd /u02/oradata/testdb/
testdb:/u02/oradata/testdb> rm users*.dbf

We then shutdown and mount the database

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 264241152 bytes
Fixed Size 2083304 bytes
Variable Size 146802200 bytes
Database Buffers 109051904 bytes
Redo Buffers 6303744 bytes
Database mounted.

RMAN> switch database to copy;

using target database control file instead of recovery catalog
datafile 1 switched to datafile copy "/u02/oradata/backup/bkp.2qklgmt6"
datafile 2 switched to datafile copy "/u02/oradata/backup/bkp.2pklgmpt"
datafile 3 switched to datafile copy "/u02/oradata/backup/bkp.2rklgmvr"
datafile 4 switched to datafile copy "/u02/oradata/backup/bkp.2sklgn0u"
datafile 5 switched to datafile copy "/u02/oradata/backup/bkp.2vklgn30"
datafile 6 switched to datafile copy "/u02/oradata/backup/bkp.2uklgn2h"
datafile 7 switched to datafile copy "/u02/oradata/backup/bkp.31klgn3f"
datafile 8 switched to datafile copy "/u02/oradata/backup/bkp.2tklgn1o"
datafile 9 switched to datafile copy "/u02/oradata/backup/bkp.30klgn37"

RMAN> recover database;

Starting recover at 31-JUL-09
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=156 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=155 devtype=DISK
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u02/oradata/backup/bkp.2qklgmt6
destination for restore of datafile 00002: /u02/oradata/backup/bkp.2pklgmpt
destination for restore of datafile 00003: /u02/oradata/backup/bkp.2rklgmvr
destination for restore of datafile 00004: /u02/oradata/backup/bkp.2sklgn0u
destination for restore of datafile 00005: /u02/oradata/backup/bkp.2vklgn30
destination for restore of datafile 00006: /u02/oradata/backup/bkp.2uklgn2h
destination for restore of datafile 00007: /u02/oradata/backup/bkp.31klgn3f
destination for restore of datafile 00008: /u02/oradata/backup/bkp.2tklgn1o
destination for restore of datafile 00009: /u02/oradata/backup/bkp.30klgn37
channel ORA_DISK_1: reading from backup piece /u02/oradata/backup/bkp.35klgnj6
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/oradata/backup/bkp.35klgnj6 tag=TAG20090731T103309
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 31-JUL-09

RMAN> alter database open;

database opened

After the SWITCH command has completed, we can now see that the datafile names have changed.

I have not been able to identify an easy way to rename them back to their original names other than recreating the controlfile and renaming all the datafiles at the OS level as well. This could be an issue when we have hundreds of datafiles to rename.

 1* select name,status from v$datafile
SQL> /

NAME                                     STATUS
---------------------------------------- -------
/u02/oradata/backup/bkp.2qklgmt6         SYSTEM
/u02/oradata/backup/bkp.2pklgmpt         ONLINE
/u02/oradata/backup/bkp.2rklgmvr         ONLINE
/u02/oradata/backup/bkp.2sklgn0u         ONLINE
/u02/oradata/backup/bkp.2vklgn30         ONLINE
/u02/oradata/backup/bkp.2uklgn2h         ONLINE
/u02/oradata/backup/bkp.31klgn3f         ONLINE
/u02/oradata/backup/bkp.2tklgn1o         ONLINE
/u02/oradata/backup/bkp.30klgn37         ONLINE

Similarly, we can also use the SWITCH TABLEPACE TO COPY command to quickly recover from the loss of all datafiless at the tablespace level.

We simulate this by removing all the datafiles for the USERS tablespace at the OS level.

testdb:/u02/oradata/testdb> rm users*.dbf

SQL> conn scott/tiger
Connected.
SQL> create table emp2
2 tablespace users
3 as select * from emp;
as select * from emp
*
ERROR at line 3:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: ‘/u02/oradata/testdb/users01.dbf’
ORA-27041: unable to open file
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3

RMAN>  run {
2> sql 'alter tablespace users offline immediate';3>
}

sql statement: alter tablespace users offline immediate

RMAN> switch tablespace users to copy;

datafile 4 switched to datafile copy "/u02/oradata/backup/bkp.04klgv2b"
datafile 6 switched to datafile copy "/u02/oradata/backup/bkp.06klgv3k"
datafile 9 switched to datafile copy "/u02/oradata/backup/bkp.08klgv4i"

RMAN> recover tablespace users;

Starting recover at 31-JUL-09
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=143 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=141 devtype=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 31-JUL-09

RMAN> sql 'alter tablespace users online';

sql statement: alter tablespace users online

SQL> select file_name from dba_data_files where tablespace_name='USERS';

FILE_NAME
--------------------------------------------------------------------------------
/u02/oradata/backup/bkp.08klgv4i
/u02/oradata/backup/bkp.06klgv3k
/u02/oradata/backup/bkp.04klgv2b

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 plans in SQL tuning sets (STS) and exporting those to the 11g database.

Let us demonstrate the same with a small example using the SALES table in the SH schema.

We need to enable the use of SQL Plan management by setting the parameter optimizer_capture_sql_plan_baselines to TRUE. The default value is FALSE.

Initially the OPTIMIZER_MODE is set to FIRST_ROWS which should optimize the statement for best response time. So in this case the index on the SALES table is used.

SQL> ALTER SESSION SET optimizer_mode='FIRST_ROWS';

Session altered.

SQL> EXPLAIN PLAN FOR
  2  SELECT * FROM SALES
  3  WHERE QUANTITY_SOLD > 40 ORDER BY PROD_ID;

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

| Id  | Operation                           | Name            | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                 |    29 |  5063   (2)| 00:01:01 |       |       |
|   1 |  SORT ORDER BY                      |                 |    29 |  5063   (2)| 00:01:01 |       |       |
|   2 |   PARTITION RANGE ALL               |                 |    29 |  5062   (2)| 00:01:01 |     1 |    28 |
|*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID| SALES           |    29 |  5062   (2)| 00:01:01 |     1 |    28 |
|   4 |     BITMAP CONVERSION TO ROWIDS     |                 |            |          |       |       |
|   5 |      BITMAP INDEX FULL SCAN         | SALES_PROMO_BIX |       |            |          |     1 |    28 |
---------------------------------------------------------------------------------------------------------------

if we change the OPTIMIZER_MODE to ALL_ROWS, we now see from the explain plan that a FULL TABLE SCAN is being used as the statement is optimized for best throughput.

SQL> ALTER SESSION SET optimizer_mode='ALL_ROWS';

Session altered.

SQL> EXPLAIN PLAN FOR
  2  SELECT * FROM SALES
  3  WHERE QUANTITY_SOLD > 40 ORDER BY PROD_ID;

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 3147563666

----------------------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |     1 |    29 |  1196   (3)| 00:00:15 |       |       |
|   1 |  SORT ORDER BY       |       |     1 |    29 |  1196   (3)| 00:00:15 |       |       |
|   2 |   PARTITION RANGE ALL|       |     1 |    29 |  1195   (3)| 00:00:15 |     1 |    28 |
|*  3 |    TABLE ACCESS FULL | SALES |     1 |    29 |  1195   (3)| 00:00:15 |     1 |    28 |
----------------------------------------------------------------------------------------------

We will run the query – initially with OPTIMIZER_MODE set to FIRST_ROWS and then the same query with OPTIMIZER_MODE set to ALL_ROWS. We will use a label ‘GAVIN’ in the SQL statement to help us easily identify it from the other SQL statements in the shared pool.

We explicitly flush the shared pool to force a hard parse the next time the same query is run but with the ALL_ROWS optimizer session setting.

SQL> ALTER SESSION SET optimizer_mode='FIRST_ROWS';

Session altered.

SQL>  SELECT /* GAVIN */
  2   *
  3   FROM SALES WHERE QUANTITY_SOLD > 40
  4  ORDER BY PROD_ID;

no rows selected

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

SQL> ALTER SESSION SET optimizer_mode='ALL_ROWS';

Session altered.

SQL>  SELECT /* GAVIN */
  2   *
  3   FROM SALES WHERE QUANTITY_SOLD > 40
  4  ORDER BY PROD_ID;

no rows selected

We will notice that the second execution of the query has encountered a change in the execution plan. But it has not been ‘accepted as yet even though it has been ‘enabled’.

SQL> SELECT sql_handle, plan_name, enabled, accepted FROM dba_sql_plan_baselines
WHERE sql_text like '%GAVIN%';  2

                                                              Ena-
SQL_HANDLE                     PLAN_NAME                      bled Acpt
------------------------------ ------------------------------ ---- ----
SYS_SQL_6f3dcd88c7488035       SYS_SQL_PLAN_c748803511df68d0  YES  YES
SYS_SQL_6f3dcd88c7488035       SYS_SQL_PLAN_c748803554bc8843  YES  NO

We can use the EVOLVE_SQL_PLAN_BASELINE function to compare the performance between the two plans. We note that the second plan has not been automatically evolved or accepted because it does not pass the performance improvement criteria which has been laid down

SQL> SET LONG 10000
SQL> SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SYS_SQL_6f3dcd88c7488035') FROM dual;

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_6F3DCD88C7488035')
--------------------------------------------------------------------------------

-------------------------------------------------------------------------------
                        Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------

Inputs:
-------
  SQL_HANDLE = SYS_SQL_6f3dcd88c7488035
  PLAN_NAME  =
  TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
  VERIFY     = YES
  COMMIT     = YES

Plan: SYS_SQL_PLAN_c748803554bc8843
-----------------------------------
  Plan was verified: Time used 1.457 seconds.
  Failed performance criterion: Compound improvement ratio <= 1.16.

                      Baseline Plan      Test Plan     Improv. Ratio
                      -------------      ---------     -------------
  Execution Status:        COMPLETE       COMPLETE
  Rows Processed:                 0              0
  Elapsed Time(ms):            1036            304              3.41
  CPU Time(ms):                1020            300               3.4
  Buffer Gets:                 1929           1727              1.12
  Disk Reads:                     0              0
  Direct Writes:                  0              0
  Fetches:                        0              0
  Executions:                     1              1

-------------------------------------------------------------------------------
                                 Report Summary
-------------------------------------------------------------------------------
Number of SQL plan baselines verified: 1.
Number of SQL plan baselines evolved: 0.

We can also manually evolve the plan if we ourselves feel that we know that the plan is a better one

SQL> var spm number;

SQL> exec :spm := dbms_spm.alter_sql_plan_baseline(sql_handle =>'SYS_SQL_6f3dcd88c7488035',
- plan_name =>'SYS_SQL_PLAN_c748803554bc8843', attribute_name => 'ACCEPTED',attribute_value => 'YES');

PL/SQL procedure successfully completed.

SQL> SELECT sql_handle, plan_name, enabled, accepted FROM dba_sql_plan_baselines
  2  WHERE sql_text like '%GAVIN%';

                                                              Ena-
SQL_HANDLE                     PLAN_NAME                      bled Acpt
------------------------------ ------------------------------ ---- ----
SYS_SQL_6f3dcd88c7488035       SYS_SQL_PLAN_c748803511df68d0  YES  YES
SYS_SQL_6f3dcd88c7488035       SYS_SQL_PLAN_c748803554bc8843  YES  YES

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 thread 1: ‘/u03/oradata/testdb/redo01.log’

Now to drop and recreate online redolog files in the same directory -

SQL> select  member from v$logfile;

MEMBER
——————————————————————————–
/u50/oradata/test/redo4a.log
/u51/oradata/test/redo4b.log
/u50/oradata/test/redo3a.log
/u51/oradata/test/redo3b.log
/u50/oradata/test/redo2a.log
/u51/oradata/test/redo2b.log
/u50/oradata/test/redo1a.log
/u51/oradata/test/redo1b.log

8 rows selected.

Intention is to drop and recreate these online logs with a different size.

SQL> select GROUP#,THREAD#,STATUS from v$log;

GROUP# THREAD# STATUS
———- ———- —————-
1 1 INACTIVE
2 1 INACTIVE
3 1 CURRENT
4 1 INACTIVE

Begin dropping the redolog groups with STATUS=’INACTIVE’ – As mentioned earlier Oracle will not allow you to drop a current online redolog file.

sql > alter database drop logfile group 1;

cd /u50/oradata/test

rm redo1a.log

cd /u51/oradata/test

rm redo1b.log

Recreate group with both members -

sql > alter database add logfile group 1('/u50/oradata/test/log1a.ora','/u51/oradata/test/log1b.ora')size 2048m;

Do the same for the other three groups -

To change the status of a log group from CURRENT to INACTIVE, simply switch a logfile with this command-

sql> alter system switch logfile;

Run the command 2 or 3 times if the group status does not change.

To check status of group remember the command is

sql > select GROUP#,THREAD#,STATUS from v$log;

Also make sure the logfile is physically removed from the disk with the rm command – if you don’t do this and try to recreate a logfile an error will appear similar to this -

Ex: You dropped log group 3 but did not remove the physical file redo03.log from disk /u03/oradata/test and tried to recreate the group.

SQL> alter database add logfile group3 '/u03/oradata/test/redo03.log' size 50m;

alter database add logfile ‘/u03/oradata/test/redo03.log’ size 50m
*
ERROR at line 1:
ORA-01577: cannot add log file ‘/u03/oradata/test/redo03.log’ – file already
part of database

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: sid=159 devtype=SBT_TAPE
channel c1: Data Protection for Oracle: version 5.5.1.0

Starting restore at 28-JUL-09

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

However, if we connect to a RMAN recovery catalog, we can restore a controlfile without using the AUTOBACKUP keyword.

$ rman target / catalog rman9p/xxx@rcatp

Recovery Manager: Release 9.2.0.8.0 - 64bit Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

connected to target database: ofsap (not mounted)
connected to recovery catalog database

RMAN> run {
2> allocate channel ch1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
3> restore controlfile;
4> release channel ch1;
5> }

allocated channel: ch1
channel ch1: sid=8 devtype=SBT_TAPE
channel ch1: Data Protection for Oracle: version 5.4.1.0

Starting restore at 27-JUN-08

channel ch1: starting datafile backupset restore
channel ch1: restoring controlfile
output filename=/u04/oradata/ofsap/control01.ctl
channel ch1: restored backup piece 1
piece handle=c-2764499561-20080627-08 tag=null params=NULL
channel ch1: restore complete
replicating controlfile
input filename=/u04/oradata/ofsap/control01.ctl
output filename=/u04/oradata/ofsap/control02.ctl
Finished restore at 27-JUN-08

released channel: ch1

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.

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,
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 -

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
where input_type='DB INCR'
order by session_key;

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 *.* LISTEN

oracle(DATABASE)@hostname:ps -ef | grep emagent
oracle 864486 1 0 Jan 27 – 5:36 /u01/oracle/agent10g/perl/bin/perl /u01/oracle/agent10g/bin/emwd.pl agent /u01/oracle/agent10g/sysman/log/emagent.nohup
oracle 1593344 864486 0 Jan 27 – 97:38 /u01/oracle/agent10g/bin/emagent

KILL ALL EMAGENT PROCESSES STILL RUNNING -

oracle(DATABASE)@hostname:ps -ef | grep emagent | awk ‘ {print $2}’ | xargs kill -9

oracle(DATABASE)@hostname:ps -ef | grep emagent| grep -v grep

No emagent process running now.

cd $AGENT_HOME/bin

oracle(DATABASE)@hostname:pwd
/u01/oracle/agent10g/bin

START AGENT

oracle(DATABASE)@hostname:./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.

AGENT WILL START.

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.

Login as sysdba

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 default settings of 7 days and 1 hour is displayed.

SQL> select to_char(snap_interval,'DD'),to_char(retention,'DD') FROM dba_hist_wr_control;

TO_CHAR(SNAP_INTER TO_CHAR(RETENTION,
------------------ ------------------
+00000 01:00:00.0  +00007 00:00:00.0;

The AWR default settings can be modified using the DBMS_WORKLOAD_REPOSITORY package as shown below. In this case the retention is being increased to 30 days and the interval to every 30 minutes.

BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
retention => 43200,
interval => 30);
END;
/
Extracting AWR data

Create a directory

SQL> CREATE DIRECTORY AWR_DATA AS
2 ‘/u01/oracle/’;

Directory created.

SQL> @?/rdbms/admin/awrextr.sql

The script will list the information we need to provide to it

AWR EXTRACT
~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~  This script will extract the AWR data for a range of snapshots  ~
~  into a dump file.  The script will prompt users for the         ~
~  following information:                                          ~
~     (1) database id                                              ~
~     (2) snapshot range to extract                                ~
~     (3) name of directory object                                 ~
~     (4) name of dump file                                        ~

After entering the range of snapshot ids, we will need to provide the directory location where the data pump export file will be located. We need to also enter the dumpfile name as well.

Note: the script will autiomatically append a ‘.dmp’ to the data punp export file name. So we need to just enter the dumpfile name without any extension.

Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~

Directory Name                 Directory Path
------------------------------ -------------------------------------------------
ADMIN_DIR                      /u01/oracle/product/10.2.0/rmand/md/admin
AWR_DATA                       /u01/oracle/
DATA_PUMP_DIR                  /u01/oracle/product/10.2.0/rmand/admin/rmand/dpdu
                               mp/

ORACLE_OCM_CONFIG_DIR          /u01/oracle/product/10.2.0.4/rmand/ccr/state
WORK_DIR                       /u01/oracle/product/10.2.0/rmand/work

Choose a Directory Name from the above list (case-sensitive).

Enter value for directory_name: AWR_DATA

Using the dump directory: AWR_DATA

Specify the Name of the Extract Dump File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The prefix for the default dump file name is awrdat_11369_11383.
To use this name, press  to continue, otherwise enter
an alternative.

Enter value for file_name: awrexp

After the export is complete, we will need to ftp the data pump dump file awrexp.dmp to the target server where our central repository database is located.

Loading AWR Data

On the repository database, we will create a directiory AWR_DATA as well and the ensure that the DIRECTORY_PATH corresponds to the directory where the awrexport.dmp file is located.

Also, a temporary staging schema AWR_STAGE is created. Objects are first imported into this staging schema and then inserted into the WR$ and DBA_HIST AWR historical tables.

We will need to provide information about the directory location, dump file and the staging schema name

@?/rdbms/admin/awrload.sql

AWR LOAD
~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~  This script will load the AWR data from a dump file. The   ~
~  script will prompt users for the following information:    ~
~     (1) name of directory object                            ~
~     (2) name of dump file                                   ~
~     (3) staging schema name to load AWR data into           ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

While specifying the dumpfile name we need to only provide the file name without the .dmp extension

Specify the Name of the Dump File to Load
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Please specify the prefix of the dump file (.dmp) to load:

We need to specify the default tablespace and temporary tablespace for this staging user. Note – this user will be dropped once the load is completed.

Choose the AWR_STAGE users's default tablespace.  This is the
tablespace in which the AWR data will be staged.

TABLESPACE_NAME                CONTENTS  DEFAULT TABLESPACE
------------------------------ --------- ------------------
MGMT_ECM_DEPOT_TS              PERMANENT
MGMT_TABLESPACE                PERMANENT
PATROL                         PERMANENT
SYSAUX                         PERMANENT *
USERS                          PERMANENT

Pressing  will result in the recommended default
tablespace (identified by *) being used.

Enter value for default_tablespace:

Using tablespace SYSAUX as the default tablespace for the AWR_STAGE

Choose the Temporary tablespace for the AWR_STAGE user
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Choose the AWR_STAGE user's temporary tablespace.

TABLESPACE_NAME                CONTENTS  DEFAULT TEMP TABLESPACE
------------------------------ --------- -----------------------
TEMP                           TEMPORARY *

Pressing  will result in the database's default temporary
tablespace (identified by *) being used.

Enter value for temporary_tablespace:

Using tablespace TEMP as the temporary tablespace for AWR_STAGE

... Creating AWR_STAGE user

if we look at the import log file, we will see that data is first imported into the AWR_STAGE schema and then from here it is inserted into the WRH$ and other DBA_HIST tables.

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "AWR_STAGE"."WRH$_SQL_PLAN"                 3.845 MB   11494 rows
. . imported "AWR_STAGE"."WRH$_SQLTEXT"                  1.012 MB     569 rows
. . imported "AWR_STAGE"."WRH$_SYSMETRIC_SUMMARY"        156.8 KB    2025 rows
.......
........
	Append Data for "AWR_STAGE".WRH$_SGASTAT.
INSERT /*+ APPEND */ INTO SYS.WRH$_SGASTAT (SNAP_ID, DBID, INSTANCE_NUMBER, NAME, POOL, BYTES) SELECT SNAP_ID,
3228342000, INSTANCE_NUMBER, NAME, POOL, BYTES FROM "AWR_STAGE".WRH$_SGASTAT WHERE DBID = :c_dbid
... appended 388 rows

If we now query the DBA_HIST_SNAPSHOT table, we see that it contains the data for two DBID’s – this shows that AWR history data is available in the repository database for two databases.

SQL> select distinct dbid from dba_hist_snapshot;

      DBID
----------
3228342000
3892233981

We can query the repository AWR tables based on DBID values to obtain data for a specific database.

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 ORA_SBT_TAPE_1
using channel ORA_DISK_1

channel ORA_DISK_1: starting validation of datafile backupset
channel ORA_DISK_1: reading from backup piece /u02/oradata/testdb/TESTDB/autobackup/2009_07_22/o1_mf_s_692887841_56f6o2jr_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/oradata/testdb/TESTDB/autobackup/2009_07_22/o1_mf_s_692887841_56f6o2jr_.bkp tag=TAG20090722T125041
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
Finished restore at 23-JUL-09

RMAN> restore validate controlfile;

Starting restore at 23-JUL-09
using channel ORA_SBT_TAPE_1
using channel ORA_DISK_1

channel ORA_DISK_1: starting validation of datafile backupset
channel ORA_DISK_1: reading from backup piece /u02/oradata/testdb/TESTDB/autobackup/2009_07_22/o1_mf_s_692887841_56f6o2jr_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/oradata/testdb/TESTDB/autobackup/2009_07_22/o1_mf_s_692887841_56f6o2jr_.bkp tag=TAG20090722T125041
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
Finished restore at 23-JUL-09

Let us now simulate a case where the restore validate will fail because either a backup or backupset is not available or because a file is not available on the disk itself.

We will rename one of the most recent archivelog files and then attempt a restore validate of archivellogs for the past day.

$ mv arch.355.1.692887607.log arch.355.1.692887607.log.old

RMAN> restore validate archivelog from time ’sysdate -1′;

Starting restore at 23-JUL-09
using target database control file instead of recovery catalog
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=140 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=141 devtype=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/23/2009 21:22:21
RMAN-06026: some targets not found – aborting restore
RMAN-06025: no backup of log thread 1 seq 355 lowscn 5192098 found to

This command can be safely run at anytime .

Also worth noting that even if you simply forget to enter validate and type for example -

RMAN > restore database ;

RMAN will never restore on top of an ‘OPEN’ database -

An error similar to the one below will be received if you try to overwrite files of an open database.

ORA-19870: error while restoring backup piece 16kermqo_1_1
ORA-19573: cannot obtain exclusive enqueue for datafile 2

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 -

Flashback Database after a Data Guard Failover

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 standby during which we have no DR in place.

In this test case we will simulate a failure on the Primary node by shutting down the current Primary database.

We will then perform the standard Failover procedure. Before performing a Failover, we first try and ensure that there is no archive log gap between the Primary and Standby databases. If there is one (and if the Primary server is still accessible), we can register any ‘missing’ archivelog files using the ALTER DATABASE REGISTER PHYSICAL LOGFILE command.

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

no rows selected

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;

Database altered.

These are the relevant lines taken from the alert log ….

Identified End-Of-Redo for thread 1 sequence 19
Terminal Recovery: Updated next available block for thread 1 sequence 19 lno 4 to value 92
Wed Jul 22 12:43:53 2009
Incomplete recovery applied all redo ever generated.
Recovery completed through change 5003771
Wed Jul 22 12:43:53 2009
MRP0: Media Recovery Complete (testdb)

 ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Database altered.

The alert log will confirm the switchover operation ….

Standby terminal recovery start SCN: 5003412
RESETLOGS after complete recovery through change 5003771
Standby became primary SCN: 5003411

Complete the switchover operation by shutting down and starting the database

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area  264241152 bytes
Fixed Size                  2083304 bytes
Variable Size             142607896 bytes
Database Buffers          113246208 bytes
Redo Buffers                6303744 bytes
Database mounted.
Database opened.

Note the SCN at which the former standby has now become the primary

SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;

TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)
----------------------------------------
5003411

On the original primary, we will now flashback the database using the SCN obtained above.

SQL> flashback database to scn 5003411;

Flashback complete.

from the alert log ….

Incomplete Recovery applied until change 5003412
Flashback Media Recovery Complete
Completed: flashback database to scn 5003411

We will now turn off the flashback logging to delete existing falshback log files. Since we will be restoring a standby control file as well in the next step, these flashback logs will become obsolete.

SQL> ALTER DATABASE FLASHBACK OFF;

Database altered.

From the alert log we can see that the flashback logs are being deleted.

Stopping background process RVWR
Deleted Oracle managed file /u02/oradata/testdb/TESTDB_DG/flashback/o1_mf_56dydp02_.flb
.......
Deleted Oracle managed file /u02/oradata/testdb/TESTDB_DG/flashback/o1_mf_56f0pz1n_.flb
Deleted Oracle managed file /u02/oradata/testdb/TESTDB_DG/flashback/o1_mf_56f0q51v_.flb
Deleted Oracle managed file /u02/oradata/testdb/TESTDB_DG/flashback/o1_mf_56f0qmw4_.flb
Deleted Oracle managed file /u02/oradata/testdb/TESTDB_DG/flashback/o1_mf_56f0qsk9_.flb
Deleted Oracle managed file /u02/oradata/testdb/TESTDB_DG/flashback/o1_mf_56f0r312_.flb
Deleted Oracle managed file /u02/oradata/testdb/TESTDB_DG/flashback/o1_mf_56f0rkro_.flb
Deleted Oracle managed file /u02/oradata/testdb/TESTDB_DG/flashback/o1_mf_56f0rtqq_.flb
Flashback Database Disabled

We will now create a standby control file and overwrite the existing control files with the standby control file.

SQL>  ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/control01.ctl';

Database altered.

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

[DEV] testdb:/tmp > cp control01.ctl /u02/oradata/testdb/control01.ctl
[DEV] testdb:/tmp > cp control01.ctl /u02/oradata/testdb/control02.ctl
[DEV] testdb:/tmp > cp control01.ctl /u02/oradata/testdb/control03.ctl

We then mount the standby database, turn on flashback logging and the managed recovery process on the standby database

SQL> startup mount;
ORACLE instance started.

Total System Global Area  390070272 bytes
Fixed Size                  2084272 bytes
Variable Size             364905040 bytes
Database Buffers           16777216 bytes
Redo Buffers                6303744 bytes
Database mounted.
SQL> alter database flashback on;

Database altered.

SQL> recover managed standby database disconnect;
Media recovery complete.

EM Grid Control target discovery after a clone

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 another machine XYZ.

Try to discover this new database on the GRID using the manual option or the option to monitor via grid control while creating the database.

The new database test will not be discovered on the GRID and cannot be seen in the list of targets on the GRID .

That is because a databse with the same name the test database has also has been discovered and runs from another box.

Only when a manual install was attempted and the name of the instance was issued as test.domain instead of just test that it worked .

As you can see below – the repository ( sysman in emrep database ) has a table called mgmt_targets which maintains these details and does not allow duplicates( so a little tweaking is necessary )

The agent tried to upload test automatically when installed on the new box and start it up- and it failed to do so as this entry exists in the table.

Currently both databases are disovered – run this command in the emrep ( repository database) as sysman.

SQL> select target_name, from mgmt_targets where target_name like ‘%TEST%’;

TARGET_NAME
test
test.domain

EM Grid Control Agent Target Discovery

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 on it.

This database has to be discovered by the agent and it’s details uploaded to the GRID.

Make sure the ORAINVENTORY location in /etc/oraInst.loc matches the location when you installed the AGENT.

Go to the new AGENT_HOME/bin and issue command agentca- d

test:/u01/ofsap/agent10g/bin> ./agentca -d
Stopping the agent using /u01/ofsap/agent10g/bin/emctl stop agent
Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
Stopping agent … stopped.
Running agentca using /u01/ofsap/agent10g/oui/bin/runConfig.sh
ORACLE_HOME=/u01/ofsap/agent10g ACTION=Configure MODE=Perform
RESPONSE_FILE=/u01/ofsap/agent10g/response_file RERUN=TRUE
INV_PTR_LOC=/etc/oraInst.loc
COMPONENT_XML={oracle.sysman.top.agent.10_2_0_1_0.xml}
Perform – mode finished for action: Configure
Perform – mode finished for action: Configure

You can see the log file:
/u01/ofsap/agent10g/cfgtoollogs/oui/configActions2009-06-18_01-13-37-PM.log
test:/u01/ofsap/agent10g/bin>

Output of log -
/u01/ofsap/agent10g/cfgtoollogs/oui/configActions2009-06-18_01-13-37-PM.log

The action configuration is performing
——————————————————
The plug-in Agent Configuration Assistant is running

Performing free port detection on host=prdu024.bankwest.com
Performing targets discovery and agent configuration
Starting the agent
AgentPlugIn:agent configuration finished with status = true

The plug-in Agent Configuration Assistant has successfully been performed
——————————————————
The action configuration has successfully completed
###################################################

Now login to the database as sys, unlock the dbsnmp user and also change
the password of dbsnmp user – for example to temp.

Login to the GRID , and click on targets – choose the new box and then the
new database and configure the dbsmp user .

Once this is complete, the GRID screen will show both the agent and
database on the targets page.

10g Online Table Redefinition using DBMS_REDEFINTION

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 sysdba
Connected.
SQL> BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE(‘SCOTT’,'EMP’);
END;
/

PL/SQL procedure successfully completed.

SQL> conn scott/tiger
Connected.

SQL> desc emp
Name Null? Type
—————————————– ——– —————————-
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
DOJ DATE
SALARY NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

Create an Interim Table

SQL> CREATE TABLE ONLINE_EMP
2 (empno NUMBER(5) NOT NULL,
3 ename VARCHAR2(15) NOT NULL,
4 job VARCHAR2(10),
5 mgr NUMBER(5),
6 doj DATE DEFAULT (sysdate),
7 sal NUMBER(7,2),
8 comm NUMBER(7,2),
9 deptno NUMBER(3) NOT NULL)
10 PARTITION BY RANGE(empno)
11 (PARTITION emp1 VALUES LESS THAN (7800) ,
12 PARTITION emp2 VALUES LESS THAN (8000) );

Table created.

Start the table Redefinition process

SQL> conn / as sysdba
Connected.

SQL> BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(‘SCOTT’, ‘EMP’,'ONLINE_EMP’,
‘empno empno, ename ename, job job, mgr mgr, doj doj, salary sal, comm comm, deptno deptno’,
dbms_redefinition.cons_use_pk);
END;
/

PL/SQL procedure successfully completed.

While the redefinition process in progress, dependant objects are still VALID and are in use

SQL> conn scott/tiger
Connected.
SQL> select get_sal(7934) from dual;

GET_SAL(7934)
————-
1950

Automatically create any triggers, indexes and constraints that were originally defined on EMP on interim table ONLINE_EMP

SQL> declare x binary_Integer;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(‘SCOTT’,'EMP’,'ONLINE_EMP’,
1, TRUE, TRUE, TRUE,FALSE,x);
END;
/

PL/SQL procedure successfully completed.

Perform some DML on the table while redefinition is in progress

SQL> update emp set comm =comm +salary;

14 rows updated.

SQL> commit;

Commit complete.

Synchronize the Interim and Original Tables

SQL> conn / as sysdba
Connected.
SQL> BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE(‘SCOTT’, ‘EMP’, ‘ONLINE_EMP’);
END;
/

PL/SQL procedure successfully completed.

Complete the redefinition

SQL> BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE(‘SCOTT’, ‘EMP’, ‘ONLINE_EMP’);
END;
/

PL/SQL procedure successfully completed.

The table ONLINE_EMP is locked in the exclusive mode only for a small window toward the end of this step. After this call the table EMP is redefined such that it has all the attributes of the ONLINE_EMP table

Drop the Interim Table

SQL> conn scott/tiger
Connected.
SQL> drop table online_emp;

Table dropped.

Check the Online Redefinition process

Check the table column names

SQL> desc emp
Name Null? Type
—————————————– ——– —————————-
EMPNO NOT NULL NUMBER(5)
ENAME NOT NULL VARCHAR2(15)
JOB VARCHAR2(10)
MGR NUMBER(5)
DOJ DATE
SAL (Originally SALARY) NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NOT NULL NUMBER(3)

Confirm the table is now partitioned

SQL> select count(*) from emp partition (emp1);

COUNT(*)
———-
8

SQL> select count(*) from emp partition (emp2);

COUNT(*)
———-
6

Check all constraints originally defined on EMP are still present

SQL> select constraint_name from user_constraints
2 where table_name=’EMP’;

CONSTRAINT_NAME
——————————
SYS_C0017251
SYS_C0017252
SYS_C0017253
PK_EMP
FK_DEPTNO

The function however will have to be recreated as the code still contained the original column name “SALARY”

SQL> alter function get_sal compile;

Warning: Function altered with compilation errors.

SQL> sho err
Errors for FUNCTION GET_SAL:

LINE/COL ERROR
——– —————————————————————–
6/8 PL/SQL: SQL Statement ignored
6/15 PL/SQL: ORA-00904: “SALARY”: invalid identifier
SQL>

Unix for the Oracle DBA – Part 1

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 the standard prompt for Oracle Users

Edit the .profile for the oracle user

PS1=”`hostname`*$ORACLE_SID:$PWD>”

Display top 10 CPU consumers using the ps command

/usr/ucb/ps auxgw | head –11

Show number of active Oracle dedicated connection users for a particular ORACLE_SID

ps -ef | grep $ORACLE_SID|grep -v grep|grep -v ora_|wc –l

Display the number of CPU’s in Solaris

psrinfo -v | grep “Status of processor”|wc –l

Display the number of CPU’s in AIX

lsdev –C | grep Process|wc –l

Display RAM Memory size on Solaris

prtconf |grep -i mem

Display RAM memory size on AIX

First determine name of memory device

lsdev -C |grep mem

then assuming the name of the memory device is ‘mem0’

lsattr -El mem0

Swap space allocation and usage

Solaris : swap -s or swap -l

Aix : lsps -a

Total number of semaphores held by all instances on server

ipcs -as | awk ‘{sum += $9} END {print sum}’

View allocated RAM memory segments

ipcs -pmb

Manually deallocate shared memeory segments

ipcrm -m ‘ID’

Show mount points for a disk in AIX

lspv -l hdisk13

Display amount of occupied space (in KB) for a file or collection of files in a directory or sub-directory

du -ks * | sort -n| tail

Display total file space in a directory

du -ks .

Cleanup any unwanted trace files more than seven days old

find . *.trc -mtime +7 -exec rm {} \;

Locate Oracle files that contain certain strings

find . -print | xargs grep rollback

Locate recently created UNIX files (in the past one day)

find . -mtime -1 –print

Finding large files on the server (more than 100MB in size)

find . -size +102400 -print

Crontab :

To submit a task every Tuesday (day 2) at 2:45PM

45 14 2 * * /opt/oracle/scripts/tr_listener.sh > /dev/null 2>&1

To submit a task to run every 15 minutes on weekdays (days 1-5)

15,30,45 * 1-5 * * /opt/oracle/scripts/tr_listener.sh > /dev/null 2>&1

To submit a task to run every hour at 15 minutes past the hour on weekends (days 6 and 0)

15 * 0,6 * * opt/oracle/scripts/tr_listener.sh > /dev/null 2>&1

Performance Tuning Tips and Techniques

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 output shown below

vmstat – 5

face:/opt/oracle>vmstat 5
procs                      memory      swap          io     system         cpu
 r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
 4  1 488700 245704 178276 12513572    0    1    10    17   48  1365 40 12 43  5
 2  0 488700 302568 178312 12514904    0    0   157   686 3354  4342 55 22 22  1
 3  1 488700 257500 178508 12517896    0    0   616   221 1352  2132 21  7 64  9
 2  2 488700 232348 178820 12525392    0    0  1550   274 3632  6091 29  9 42 20
 3  0 488700 225040 178880 12527336    0    0   346   452 2494  3300 45 13 38  4
 5  0 488700 225680 178916 12527884    0    0    79   269 2005  3847 29 11 58  2

CPU idle% < 10 ( id column) could indicate a machine that is having CPU resource issues

Note: How to find number of CPU’s on a LINUX machine?

cat /proc/cpuinfo |grep -w “processor” |wc –l

Swap columns si and so should ideally be 0 to indicate no swapping activity

face:/opt/oracle>free -m
             total       used       free     shared    buffers     cached
Mem:         15635      15476        159          0        173      12196
-/+ buffers/cache:       3106      12528
Swap:         3999        477       3522

We should be looking at the free and used values in the row denoted by
“-/+ buffers/cache”

The ‘top’ command will help us identify the load average on the machine as well as any process that is consuming excessive CPU

face:/opt/oracle>top
09:04:01  up 243 days, 13:01, 10 users,  load average: 2.80, 3.14, 3.13
813 processes: 797 sleeping, 8 running, 8 zombie, 0 stopped
CPU states:  cpu    user    nice  system    irq  softirq  iowait    idle
           total   40.7%    0.0%   12.7%   0.0%     0.3%    2.3%   43.7%
           cpu00   42.8%    0.0%   13.4%   0.3%     1.1%    1.9%   40.0%
           cpu01   37.3%    0.0%   12.6%   0.0%     0.0%    1.7%   48.2%
           cpu02   47.5%    0.0%   14.1%   0.0%     0.1%    1.1%   36.9%
           cpu03   35.1%    0.0%   10.5%   0.0%     0.0%    4.5%   49.7%

Mem:  16010560k av, 15808688k used,  201872k free,       0k shrd,  181324k buff
                   11368660k actv, 2257548k in_d,  265628k in_c
Swap: 4095984k av,  488700k used, 3607284k free                 12551192k cache

  PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME CPU COMMAND
24062 oracle    18   0 1342M 1.3G 1328M S    14.0  8.5 370:13   2 oracle
 6129 oracle    18   0  958M 953M  923M R    12.3  6.0 172:14   0 oracle
10077 oracle    25   0  149M 149M  145M R     4.5  0.9   0:00   2 oracle
 9924 oracle    16   0  156M 155M  150M S     2.6  0.9   0:00   2 oracle
10038 oracle    17   0  140M 140M  136M S     2.5  0.8   0:00   1 oracle
10055 oracle    24   0  136M 135M  132M S     2.4  0.8   0:00   0 oracle
25529 oracle    17   0  782M 782M  775M S     1.7  5.0   3:58   0 oracle
 8245 oracle    16   0  274M 274M  268M S     1.6  1.7   0:03   1 oracle
 9751 oracle    16   0 70484  67M 62952 S     1.3  0.4   0:01   2 oracle
31879 oracle    16   0  830M 828M  815M R     1.0  5.2  46:06   3 oracle
 9210 oracle    15   0  229M 229M  225M S     0.7  1.4   0:01   3 oracle

A load average greater than 5 or 10 could indicate a heavily utilized machine

CPU information for each CPU is also provided via the top command as well as information on the physical as well as virtual Memory that is available on the machine.

Information on the top CPU as well as Memory consuming processes is also provided along with the Process ID (PID). In a later section we will use this PID as a parameter for a SQL query to identify the SQL being executed by the same CPU consuming process.

Identify if any single PID is constantly appearing in the top output

The iowait column can also help us identify if there s any resource contention from the IO perspective.

A value above 40-50% would indicate I/O resource issues and require further investigation into the process that is causing this high I/O – or it could indicate a case of inefficient disk sub system or file layout in the database.

We can also view the state of the machine at a particular time of the day by running the sar command which will provide the system utilisation report since 12:00 AM on that particular day.

We can use the sar command to identify the machine state even for a particular day of the month

For example sar -f /var/log/sa/sa03 will report for the 3rd of the month

Checks to be performed at the database level

Identify with the user if the problem is a slow response or a hanging situation.

Establish a connection via SQL*NET using a non SYSDBA account to confirm that the listener is accepting client connections and the hanging is not due to the archive area getting 100% full.

Examine the alert log file for ‘Checkpoint Not Completed’ errors recorded at the time the performance problem is reported – this could indicate an I/O contention issue or inadequately sized redo log files which can also cause an application hang while the checkpoint completes.

Ensure that the mount point on which the Oracle software is not 100% full or the disks holding the controlfiles are also not 100% full.

Check the listener.log file if it exists and ensure that it is not > 2GB – on some Operating Systems like LINUX, there is a file size upper limit for the listener.log file after which client connections will not be accepted by the listener.

Check for locked sessions (see script check_lock.sql).

If a PID has been identified as a top CPU consuming process, check the SQL being executed by that particular PID (see script check_pid_sql.sql)

If the user provides a particular SID where a possible performance issue exists, check the SQL being executed by that SID ( see script check_sid_sql.sql)

If the user provides a particular Oracle username where a possible performance issue exists, check the SQL being executed by that Oracle user ( see script check_username_sql.sql)

Very Important – check the major wait events in the database (see script wait_events.sql)

Check the SID along with the events that each SID is waiting on (see script wait_events_sid.sql) – based on the SID, the SQL being executed by the waiting sessions can be obtained as well ( see script check_sid_sql.sql)

Refer document Resolving common Oracle Wait Events using the Wait Interface for details on important wait events along with their possible causes and resolution.

Check for any sessions continuously waiting for on a particular latch (see script check_latch.sql) and also refer document “Resolving common Oracle Wait Events using the Wait Interface.doc”.

What has changed?

Is there a measurable baseline regarding the “problem” query – when did it last perform well?

Has the database been upgraded recently?

Has any modifications been done to the database in terms of init.ora parameters?

Have any new indexes been added to the table or has the table structure changed?

Has the platform or database version changed?

Is this a period of unusual business activity? – like a monthly data load or one-off batch job

Appendix

check_pid_sql.sql

SET PAGESIZE 500
set long 500000
set head off
select
       s.username su,
       substr(sa.sql_text,1,540) txt
from v$process p,
     v$session s,
     v$sqlarea sa
where    p.addr=s.paddr
and      s.username is not null
and      s.sql_address=sa.address(+)
and      s.sql_hash_value=sa.hash_value(+)
and spid=;

check_sid_sql.sql

SET PAGESIZE 500
PROMPT=============================================================
PROMPT Current SQL statement this session executes
PROMPT=============================================================
col sql_text for a70 hea "Current SQL"
select q.sql_text
from v$session s
,    v$sql     q
WHERE s.sql_address = q.address
and   s.sql_hash_value + DECODE
                 (SIGN(s.sql_hash_value), -1, POWER( 2, 32), 0) = q.hash_value
AND   s.sid= ;

check_username_sql.sql

set long 500000
SET PAGESIZE 500
select
       s.username su,
       substr(sa.sql_text,1,540) txt
from v$process p,
     v$session s,
     v$sqlarea sa
where    p.addr=s.paddr
and      s.username is not null
and      s.sql_address=sa.address(+)
and      s.sql_hash_value=sa.hash_value(+)
and s.username=upper('&username');

check_lock.sql

set linesize 500
SET PAGESIZE 500
col waiting_session format 99999 heading 'Waiting|Session'
col holding_session format 99999 heading 'Holding|Session'
col mode_held format a20 heading 'Mode|Held'
col mode_requested format a20 heading 'Mode|Requested'
col lock_type format a20 heading 'Lock|Type'
prompt blocked objects from V$LOCK and SYS.OBJ$

set lines 132
col BLOCKED_OBJ format a35 trunc

select /*+ ORDERED */
    l.sid
,   l.lmode
,   TRUNC(l.ctime/60) min_blocked
,   u.name||'.'||o.NAME blocked_obj
from (select *
      from v$lock
      where type='TM'
      and sid in (select sid
                  from v$lock
                  where block!=0)) l
,     sys.obj$ o
,     sys.user$ u
where o.obj# = l.ID1
and   o.OWNER# = u.user#
;

prompt blocked sessions from V$LOCK

select /*+ ORDERED */
   blocker.sid blocker_sid
,  blocked.sid blocked_sid
,  TRUNC(blocked.ctime/60) min_blocked
,  blocked.request
from (select *
      from v$lock
      where block != 0
      and type = 'TX') blocker
,    v$lock        blocked
where blocked.type='TX'
and blocked.block = 0
and blocked.id1 = blocker.id1
;

prompt blockers session details from V$SESSION

set lines 132
col username format a10 trunc
col osuser format a12 trunc
col machine format a15 trunc
col process format a15 trunc
col action format a50 trunc

SELECT sid
,      serial#
,      username
,      osuser
,      machine
FROM v$session
WHERE sid IN (select sid
      from v$lock
      where block != 0
      and type = 'TX')
;

wait_events.sql

SELECT count(*), event FROM v$session_wait
WHERE wait_time = 0
AND event NOT IN
('smon timer','pmon timer','rdbms ipc message',
'SQL*Net message from client')
GROUP BY event ORDER BY 1 DESC
;

wait_events_sid.sql

col username format a12
col sid format 9999
col state format a15
col event format a45
col wait_time format 99999999
set pagesize 800
set linesize 800
select s.sid, s.username, se.event
from v$session s, v$session_wait se
where s.sid=se.sid
and se.event not like 'SQL*Net%'
and se.event not like '%rdbms%'
and s.username is not null
order by 3;

check_latch.sql

select count(*), name latchname from v$session_wait, v$latchname
where event='latch free' and state='WAITING' and p2=latch#
group by name order by 1 desc;

Data Guard Log Shipping Report

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 customised so as to email an alert notification if the standby and the primary go out of sync by say 5 log files. Drop me a line if you need that customisation script.

This report is based on a Unix shell script (check_logship.sh) which in turn calls a SQL script (check_logship.sql).

The script requires a user MONITOR to be created in each target database with the CONNECT and SELECT ANY DICTIONARY privileges. We also have a config file (in our case bw_dg.lst) which will contain the list of all the TNS aliases of the Primary databases which we need to monitor.

[PROD] emrep:/u01/oracle/scripts > ./check_logship.sh

#######################################################################################
	Data Guard Log Shipping Summary Report:  Thu Jul 16 14:22:02 WAUST 2009
#######################################################################################

DB_NAME  HOSTNAME     LOG_ARCHIVED LOG_APPLIED APPLIED_TIME  LOG_GAP
-------- ------------ ------------ ----------- ------------  -------

GENPRD   CBDORCA201          16742       16742 16-JUL/14:12       0

CPSPRD   PRDU009N1           11494       11494 16-JUL/14:10       0

LN1P     CBDORCA101          51173       51171 16-JUL/12:25       2

LA1P     CBDORCA105          76971       76970 16-JUL/13:10       1

#######################################################################################

check_logship.sql

SET PAGESIZE 124
SET HEAD OFF
COL DB_NAME FORMAT A8
COL HOSTNAME FORMAT A12
COL LOG_ARCHIVED FORMAT 999999
COL LOG_APPLIED FORMAT 999999
COL LOG_GAP FORMAT 9999
COL APPLIED_TIME FORMAT A12
SELECT DB_NAME, HOSTNAME, LOG_ARCHIVED, LOG_APPLIED,APPLIED_TIME,
LOG_ARCHIVED-LOG_APPLIED LOG_GAP
FROM
(
SELECT NAME DB_NAME
FROM V$DATABASE
),
(
SELECT UPPER(SUBSTR(HOST_NAME,1,(DECODE(INSTR(HOST_NAME,’.'),0,LENGTH(HOST_NAME),
(INSTR(HOST_NAME,’.')-1))))) HOSTNAME
FROM V$INSTANCE
),
(
SELECT MAX(SEQUENCE#) LOG_ARCHIVED
FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED=’YES’
),
(
SELECT MAX(SEQUENCE#) LOG_APPLIED
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED=’YES’
),
(
SELECT TO_CHAR(MAX(COMPLETION_TIME),’DD-MON/HH24:MI’) APPLIED_TIME
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED=’YES’
);

check_logship.sh

if [ -f /tmp/dataguard1.out ]
then
rm /tmp/dataguard1.out
fi

if [ -f /tmp/dataguard2.out ]
then
rm /tmp/dataguard2.out
fi

export SCRPT=/u01/app/scripts

for i in `cat $SCRPT/bw_dg.lst`
do
sqlplus -s monitor/xxx@$i <> /tmp/dataguard2.out
@/$SCRPT/check_logship.sql
EOF
echo “#######################################################################################” > /tmp/dataguard1.out
echo ” Data Guard Log Shipping Summary Report: `date ` ” >> /tmp/dataguard1.out
echo “#######################################################################################” >> /tmp/dataguard1.out
echo >> /tmp/dataguard1.out
echo “DB_NAME HOSTNAME LOG_ARCHIVED LOG_APPLIED APPLIED_TIME LOG_GAP” >> /tmp/dataguard1.out
echo “——– ———— ———— ———– ———— ——-” >> /tmp/dataguard1.out
cat /tmp/dataguard2.out >> /tmp/dataguard1.out
done
cat /tmp/dataguard1.out

TSM (Tivoli Storage Manager) – Using the dsmc command

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 backup permanently and retain for 2 years.

dsmc archive -archmc=RETAIN2YEARS -description=”test_remove” “/u05/backup/TEST/test_export.sh” -su=yes > test.log

The log for the output is written to test.log

To query all permanent archived backups under a particular directory -

dsmc query Archive -detail “/u05/backup/TEST/test_export.sh” -su=yes

To retrive the backup above -

dsmc retrieve “/u05/backup/TEST/test_export.sh”

Retrieve function invoked.

— User Action is Required —
File /u05/backup/TEST/test_export.sh ‘ exists

Select an appropriate action
1. Replace this object
2. Replace all objects that already exist
3. Skip this object
4. Skip all objects that already exist
A. Abort this operation
Action [1,2,3,4,A] : 1
Action [1,2,3,4,A] : 1
Retrieving 472 /u05/backup/TEST/test_export.sh [Done]

Retrieve processing finished.

Change DBID of database

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 is used that the database can be registered in the catalog since only one dbid is allowed to be registered in a catalog – hence 2 databases with the same dbid on different machines, still can’t be registered in the same catalog.

To change dbid follow these steps -

Shutdown immediate;

startup mount;

Make sure the sys password is correct -

sqlplus “sys/password@test as sysdba” – should connect without errors.

Then command line :

cd $ORACLE_HOME/bin

oracle(DATABASE)@tmpu020:./nid target=sys/password@test
DBNEWID: Release 9.2.0.8.0 – 64bit Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

Connected to database TEST (DBID=2764499561)

Control Files in database:
/u01/oradata/test/control01.ctl
/u02/oradata/test/control02.ctl

Change database ID of database TEST? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 2764499561 to 3112540754
Control File /u01/oradata/test/control01.ctl – modified
Control File /u02/oradata/test/control02.ctl – modified
Datafile /u02/oradata/test/system01.dbf – dbid changed
Datafile /u02/oradata/test/users01.dbf – dbid changed
Datafile /u02/oradata/test/patrol01.dbf – dbid changed
Datafile /u02/oradata/test/temp01.dbf – dbid changed
Datafile /u02/oradata/test/temp02.dbf – dbid changed
Control File /u01/oradata/test/control01.ctl – dbid changed
Control File /u02/oradata/test/control02.ctl – dbid changed

Database ID for database TEST changed to 3112540754.
All previous backups and archived redo logs for this database are unusable.
Shut down database and open with RESETLOGS option.
Succesfully changed database ID.
DBNEWID – Completed succesfully.

oracle(DATABASE)@tmpu020:

Shutdown immediate;

startup mount;

alter database open resetlogs;

To check new dbid-

oracle(DATABASE)@tmpu020:rman target /

connected to target database: TEST (DBID=3112540754)

RMAN>

Guaranteed Restore Points can cause a database hang

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 overwritten due to space constraints when we create guaranteed restore points.

To illustrate the same, we create a guaranteed restore point and with the db_recovery_file_dest_size set to a value of 100M, perform a DELETE operation on a million row table.

After some time we notice that the delete statement is hanging. We notice from the alert log that we are getting a warning that the database background process RVWR is stuck as the flashback area is full and space cannot be reused because we have set a guaranteed restore point.

The solution to this problem is to increase the space allocated for the flashback logging by changing the parameter db_recovery_file_dest_size which can be done on the fly.

Once we do that we see that the delete operation completes successfully.

The view V$RESTORE_POINT can be queried to see all the restore points which have been set and if they are normal or guaranteed. The column storage_size will give us an indicator of the required disk space to accomodate the flashback logs based on current database workload.

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u02/oradata/testdb/
db_recovery_file_dest_size           big integer 100M

SQL> CREATE RESTORE POINT test_guarantee GUARANTEE FLASHBACK DATABASE;

Restore point created.

SQL> DELETE sh.sales;

WE SEE THAT THIS DELETE STATEMENT IS HANGING …..

From the alert log we see the following warning message ….

current recovery area of size 104857600 bytes.
Recovery Writer (RVWR) is stuck until more space is
available in the recovery area.
Unable to write Flashback database log data because the
recovery area is full, presence of a guaranteed
restore point and no reusable flashback logs.
Use ALTER SYSTEM SET db_recovery_file_dest_size command
to add space. DO NOT manually remove flashback log files
to create space.

The V$RESTORE_POINT view shows us that we need about 179 MB of disk space to hold the flashback logs.

SQL> select name,guarantee_flashback_database,storage_size
  2  from v$restore_point
  3   where guarantee_flashback_database = 'YES';

NAME                 GUA STORAGE_SIZE
-------------------- --- ------------
TEST_GUARANTEE       YES    179159040

After we increase the space allocated for the flashback logs, we see that the delete statement which was originally hanging has now completed.

SQL> alter system set db_recovery_file_dest_size=200M;

System altered.

SQL> DELETE sh.sales;

918843 rows deleted

RMAN Backup and Recovery scenarios

The following notes will illustrate the steps required to recover from a number of common failure scenarios that a DBA could possibly face in his career.

RMAN Restore on another machine with different file system- same database name

RMAN duplicate database on the same host

Loss Of All Files – Disaster Recovery

Incomplete Recovery (until point in time)

Incomplete Recovery (until log sequence)

Loss of all online redo log files

Loss of all controlfiles

Recovery when no backup exists of a datafile

ORA-19755 error using block change tracking file.

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: failure during parse/execution of SQL statement: alter database recover logfile ‘/u02/oradata/flash_recovery_area/TEST/archivelog/2009_02_18/o1_mf_1_117043_4spoq75y_.arc’
ORA-00283: recovery session canceled due to errors
ORA-19755: could not open change tracking file
ORA-19750: change tracking file: ‘/u02/oradata/test/ofsap_blk_change.dbf’
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3

This file is not backed up by RMAN and not needed for recovery – it is purely used by RMAN for speeding up the incremental backups.

Therefore to workaround this problem simply disable block_change_tracking and restart the recovery .

sql > alter database disable block change tracking ;

RESTART THE RECOVERY UNTIL TIME -

RMAN> run {
SET UNTIL TIME “TO_DATE (‘09-02-09 18:00:00′, ‘DD-MM-YY HH24:MI:SS’)”;
allocate channel ch1 type ’sbt_tape’ parms ‘ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)’;
recover database;
release channel ch1 ;
}
executing command: SET until clause

allocated channel: ch1
channel ch1: sid=310 devtype=SBT_TAPE
channel ch1: Data Protection for Oracle: version 5.4.1.0

Starting recover at 18-FEB-09

starting media recovery

archive log thread 1 sequence 117043 is already on disk as file /u02/oradata/flash_recovery_area/TEST/archivelog/2009_02_18/o1_mf_1_117043_4spoq75y_.arc
archive log thread 1 sequence 117044 is already on disk as thread=1 sequence=117045
channel ch1: starting archive log restore to default destination ==

Recovery continues.

Grid control- Manual deletion of jobs

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 Control repository database -

Repository owner – SYSMAN

Repository database – EMREP

SYSMAN@emrep> select job_id, job_name, job_owner from mgmt_job where job_name like ‘%BACKUP%’;

JOB_ID
——————————–
JOB_NAME
—————————————————————-
JOB_OWNER
——————————————————————————–
5C153E5F858740BCE0430AFEC84040BC
TEST BACKUP

Firstly stop all executions of this job.

SYSMAN@emrep> exec mgmt_job_engine.stop_all_executions_with_id(‘5C153E5F858740BCE0430AFEC84040BC’,TRUE);

PL/SQL procedure successfully completed.

SYSMAN@emrep> commit;

Commit complete.

Finally remove this job from the job_management engine.

SYSMAN@emrep> exec mgmt_job_engine.delete_job(‘5C153E5F858740BCE0430AFEC84040BC’);

PL/SQL procedure successfully completed.

SYSMAN@emrep> commit;

Commit complete.

Compress and uncompress Files

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> ls -lrt
-rw-r–r– 1 oracle dba 105952962 Feb 26 11:31 test.tar.gz

test:/u02/oradata/test_bkup>

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

cd /u01/oradata/test – original area of the files.

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

Files are uncompressed and sent to original area.

RMAN – Create Catalog

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 required grants to catalog owner

grant create session,resource to rman10;

grant recovery_catalog_owner to rman10;

alter user rman10 quota unlimited on rman10;

At this point the catalog owner exists in the database but there is no catalog created -

To test this –

SQL> conn rman10/rman
Connected.
SQL> select name from rc_database;
select name from rc_database
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> exit

This is because the RECOVERY CATALOG has not yet been created in the database.

CREATE CATALOG

Login as sysdba into database RMAN10CAT and also as catalog owner.

rman10cat:/u01/oracle> rman target / catalog rman10/rman

Recovery Manager: Release 10.2.0.4.0 – Production on Mon Jul 13 12:42:15 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: TESTDB (DBID=2469552796)
connected to recovery catalog database

RMAN> create catalog;

recovery catalog created

RMAN> exit

Recovery Manager complete.

rman10cat:/u01/oracle> sqlplus rman10/rman

SQL> select name from rc_database;

no rows selected

Catalog exists and the sql statement above will show results once databases are registered in this catalog .

Customizing the dbstart and dbstop scripts to deal with non standard listener names

The Oracle supplied dbstart and dbstop scripts seem to be having a limitation when it comes to starting and stopping listeners which are not named the standard listener name which is ‘listener’.

The reason being the script has the listener name hard coded as ‘listener’ and any other listener will not be handled by the startup and shutdown scripts.

Even if we do have a listener called listener, but have multiple Oracle Home’s with different listeners, the scripts will not work as they can only handle one listener.

We have overcome this limitation by creating a configuration file similar to the oratab file which has the listener names and Oracle Home’s they operate from and then make a few changes to the dbstart and dbstop scripts as shown below.

For example on one of the AIX machines, we have three listeners that are running as shown below

[DEV] ofsad2:/u01/oracle/product/10.2/ofsad2/bin > ps -ef |grep tns

rapmd2 241824 1 0 Jul 08 – 0:08 /u01/oracle/product/10.2/rapmd2/bin/tnslsnr LISTENER -inherit
ofsad2 1601784 1 0 09:04:57 pts/1 0:00 /u01/oracle/product/10.2/ofsad2/bin/tnslsnr primary -inherit
ofsad2 1605790 1 0 09:04:57 pts/1 0:00 /u01/oracle/product/11.0/bin/tnslsnr listener_11gtest -inherit

We have created a file called lsnrtab which has the following lines

/u01/oracle/product/11.0:listener_11gtest:Y
/u01/oracle/product/10.2/ofsad2:primary:Y

The dbstart and dbstop files have been edited accordingly to read this file and obtain the listener name as well as the Oracle Home location. Note that the original lines have been commented out.

dbstop

# Stop Oracle Net Listener
#if [ -f $ORACLE_HOME_LISTNER/bin/tnslsnr ] ; then
# echo “$0: Stoping Oracle Net Listener” >> $LOG 2>&1
# $ORACLE_HOME_LISTNER/bin/lsnrctl stop >> $LOG 2>&1 &
#else
# echo “Failed to auto-stop Oracle Net Listener using $ORACLE_HOME_LISTNER/bin/tnslsnr”
#fi

LSNRTAB=/u01/local/bwdba/scripts/lsnrtab_`whoami`

cat $LSNRTAB | while read LINE
do
LSNRHOME=`echo $LINE | awk -F: ‘{print $1}’`;export LSNRHOME
LSNRNAME=`echo $LINE | awk -F: ‘{print $2}’`;export LSNRNAME
export TNS_ADMIN=$LSNRHOME/network/admin
export ORACLE_HOME=$LSNRHOME
$ORACLE_HOME/bin/lsnrctl stop $LSNRNAME >> $LOG 2>&1 &
done

dbstart

# Start Oracle Net Listener
#if [ -x $ORACLE_HOME_LISTNER/bin/tnslsnr ] ; then
# echo “$0: Starting Oracle Net Listener” >> $LOG 2>&1
# $ORACLE_HOME_LISTNER/bin/lsnrctl start >> $LOG 2>&1 &
# export VER10LIST=`$ORACLE_HOME_LISTNER/bin/lsnrctl version | grep “LSNRCTL for ” | cut -d’ ‘ -f5 | cut -d’.’ -f1`
#else
# echo “Failed to auto-start Oracle Net Listener using $ORACLE_HOME_LISTNER/bin/tnslsnr”
#fi

LSNRTAB=/u01/local/bwdba/scripts/lsnrtab_`whoami`

cat $LSNRTAB | while read LINE
do
LSNRHOME=`echo $LINE | awk -F: ‘{print $1}’`;export LSNRHOME
LSNRNAME=`echo $LINE | awk -F: ‘{print $2}’`;export LSNRNAME
export TNS_ADMIN=$LSNRHOME/network/admin
export ORACLE_HOME=$LSNRHOME
$ORACLE_HOME/bin/lsnrctl start $LSNRNAME >> $LOG 2>&1 &
done

Using Log Miner

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: Release 9.2.0.6.0 – 64bit Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database: TESTDBA (DBID=168397266)
connected to recovery catalog database

RMAN> run {
set archivelog destination to ‘/tmp/backup/arch_restore’;
restore archivelog from sequence 1000 until sequence 1005 ;
}

executing command: SET ARCHIVELOG DESTINATION
starting full resync of recovery catalog

Starting restore at 08-APR-08

allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=185 devtype=SBT_TAPE

Finished restore at 08-APR-08

Create DICTIONARY….Make sure that the path is in UTL_FILE_DIR.

If it is not in the UTL_FILE_DIR path, you can use any path in the UTL_FILE_DIR if you don’t want an outage – otherwise if an outage is not an issue you can choose a new path, update the init.ora file and restart the database.

execute DBMS_LOGMNR_D.BUILD(‘dictionary.ora’, ‘/tmp/backup’, options => dbms_logmnr_d.store_in_flat_file);

Then create script file for logs :

exec dbms_logmnr.add_logfile(LogFileName=>’/tmp/backup/1_1000.ora’,Options=>dbms_logmnr.ADDFILE);
exec dbms_logmnr.add_logfile(LogFileName=>’/tmp/backup/1_1001.ora’,Options=>dbms_logmnr.ADDFILE);
exec dbms_logmnr.add_logfile(LogFileName=>’/tmp/backup/1_1002.ora’,Options=>dbms_logmnr.ADDFILE);
exec dbms_logmnr.add_logfile(LogFileName=>’/tmp/backup/1_1003.ora’,Options=>dbms_logmnr.ADDFILE);
exec dbms_logmnr.add_logfile(LogFileName=>’/tmp/backup/1_1004.ora’,Options=>dbms_logmnr.ADDFILE);
exec dbms_logmnr.add_logfile(LogFileName=>’/tmp/backup/1_1005.ora’,Options=>dbms_logmnr.ADDFILE);

Next run statement to ‘data mine’ the logfiles for that date and period when the changes were made -

SQL> BEGIN
DBMS_LOGMNR.start_logmnr (
dictfilename => ‘/tmp/backup/dictionary.ora’,
starttime => TO_DATE(‘03-APR-2008 09:00:00′, ‘DD-MON-YYYY HH:MI:SS’),
endtime => TO_DATE(‘03-APR-2008 12:00:00′, ‘DD-MON-YYYY HH:MI:SS’));
END;
/

Once this is complete run this statement to extract actions made against table test.

select username,to_char(TIMESTAMP,’DD-MON:YYYY,HH24:MI:SS’),operation,sql_redo,session_info from v$logmnr_contents
where seg_name=’TEST’;

The command above will return rows if there were any changes made to this table.

Flashback Table, Flashback Database,Flashback Query – examples

Oracle Flashback Technology provides a set of features available on 9i or higher version that support viewing and rewinding data back and forth in time to recover from Logical Corruptions while the database is online.

Flashback Technology gives you 6 different ways to track and fix logical corruption through different approach.

Oracle Flashback Query feature lets you specify a target time and then run queries against your database, viewing results as they would have appeared at that time.
Oracle Flashback Version Query lets you view all the versions of all the rows that ever existed in one or more tables in a specified time interval.
Oracle Flashback Transaction Query lets you view changes made by a single transaction, or by all the transactions during a period of time.
Oracle Flashback Table returns a table to its state at a previous point in time.
Oracle Flashback Drop reverses the effects of a DROP TABLE statement.
Oracle Flashback Database provides a more efficient alternative to database point-in-time recovery.

This Document contain the step by step example of how to enable flash back on a DB, how to flash back some deleted rows, how to flash back dropped tables and how to flash back a database for point in time recovery.

Download …

RMAN Duplicate database on same host

This note describes in detail the procedure used to duplicate an Oracle 10g database on the same host but with a different database name using RMAN (on a Linux platform).

Download …

10g Cross Platform Transportable Tablespace

This note decribes in detail the steps used to migrate a 1.4 TB Oracle 10g database from an AIX platform to a Red Hat Linux platform with minimal downtime using the Cross Platform TTS technology introduced in Oracle 10g.

UTL_FILE_DIR path test

To test if the utl_file_dir path in the init.ora file can indeed be written to .

Login as sysdba -

1. Create a test procedure

CREATE OR REPLACE procedure utl_file_test_write (
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;
/

Now run a test -

begin
utl_file_test_write (
‘/tmp’,
‘utl_file_test’,
‘first line’,
’second line’
);
end;
/

If ‘/tmp’ is not a part of the UTL_FILE_DIR path in the init.ora, then this test will fail.

Add color to the Unix PS1 prompt

This has been tested on an AIX box -

To customize the colour of the message on login by updating the profile called on login for a user into a unix machine update the profile with the following -

Red=”33[31m"
RedBG="33[41m"
LightRed="33[1;31m"
Green="33[32m"
GreenBG="33[42m"
Yellow="33[33m"
YellowBG="33[43m"
Blue="33[34m"
BlueBG="33[44m"
Purple="33[35m"
PurpleBG="33[45m"
BoldON="33[1m"
BoldOFF="33[22m"
NC="33[0m" # No Color
MESSAGE=`echo "$Green$BoldON[DEV]$BoldOFF$NC”`
export MESSAGE
Bold=$(tput smso)
Normal=$(tput sgr0)
export PS1=”$MESSAGE \${ORACLE_SID}:\$PWD > “

This entry will show a message which points to the machine being a DEV machine and in Green -

Example – The DEV below will show up in colour GREEN –

[DEV] ofsad1:/u01/ofsad1

Script – Sessions with high physical reads

set linesize 120 col os_user format a10 col username format a15 col pid format 9999999999 PROMPT SESSIONS SORTED BY PHYSICAL READS PROMPT select OSUSER os_user,username, PROCESS pid, ses.SID sid, SERIAL#, PHYSICAL_READS, BLOCK_CHANGES from v$session ses, v$sess_io sio where ses.SID = sio.SID and username is not null and status='ACTIVE' order by PHYSICAL_READS;

Import partition data

A partition in a partitioned table was truncated by mistake and data needs to be imported from an export dump.

However instead of importing the data directly into the original table, the plan is to import the partition data into the temporary user and ratify the data before importing it to the original table using partition exchange.

Steps -

1. Create a temporary user.
2. Import partition data only into table in the temporary user
3. Ratify data.
4. Move segments in temporary table into new non-partitioned table.
5. Move this non-partitioned table into the original users tablespace.
6. Exchange partition between temporary and original partition and clean up.

NOTE -

ORIGINAL OWNER is PART_OWNER
TABLESPACE_NAME IS TEST_TBS
TABLE_NAME is TEST
PARTITION IS TEST_PART_DEC

1. Create temporary user with separate tablespace -

Temporary user called PART_RESTORE.

2. Import partition data only into table in the temporary user

imp userid/password file=test_export.dmp log=test_import.log fromuser=PART_OWNER touser=PART_RESTORE tables=TEST:TEST_PART_DEC feedback=10000 buffer = 64000 ignore=y &

3. RATIFY DATA IN PART_RESTORE SCHEMA.

4. Move segments fron temporary table into new non-partitioned table.

CREATE TABLE PART_RESTORE.TEST_TEMP
AS SELECT * FROM PART_RESTORE.TEST
WHERE ROWNUM<1;

5. Move this non-partitioned table into the original users tablespace.

ALTER TABLE PART_RESTORE.TEST_TEMP MOVE TABLESPACE TEST_TBS;

6. Exchange partition between temporary and original partition and clean up.

– set it as a nologging table

ALTER TABLE PART_RESTORE.TEST_TEMP NOLOGGING;

– move the data into the temp table.

INSERT /*+ APPEND */INTO PART_RESTORE.TEST_TEMP
SELECT *
FROM PART_RESTORE.TEST;
COMMIT;

— exchange the partition into the final PART_OWNER TABLE

ALTER TABLE PART_OWNER.TEST
EXCHANGE PARTITION TEST_PART_DEC
WITH TABLE PART_RESTORE.TEST_TEMP
UPDATE GLOBAL INDEXES;

– rebuild any unusable local indexes
ALTER TABLE PART_OWNER.TEST
MODIFY PARTITION TEST_PART_DEC
REBUILD UNUSABLE LOCAL INDEXES;

– gather stats on the new partition…
begin
DBMS_STATS.GATHER_TABLE_STATS (ownname => PART_OWNER, tabname => ‘TEST’, partname => ‘TEST_PART_DEC’, estimate_percent => 5, degree => 1, granularity => ‘ALL’, cascade => FALSE);
end;
/

– drop the ofsa_restored temp table…

DROP TABLE PART_RESTORE.TEST_TEMP ;

Delete duplicate rows in a table

In case you want to identify duplicates and remove them from a table.

1. Identify duplicates.

select count(*) from RF_BUSINESS_UNIT_HISTORY WHERE ROWID IN
(select rowid from RF_BUSINESS_UNIT_HISTORY
MINUS
select max(rowid) from RF_BUSINESS_UNIT_HISTORY
GROUP BY
CALENDAR_YEAR_MONTH,BUSINESS_UNIT_NO,BUSINESS_UNIT_NAME);

COUNT(*)
———-
2528

2. 2258 duplicates exist – these can be deleted with command below -

delete from RF_BUSINESS_UNIT_HISTORY WHERE ROWID IN
(select rowid from RF_BUSINESS_UNIT_HISTORY
MINUS
select max(rowid) from RF_BUSINESS_UNIT_HISTORY
GROUP BY
CALENDAR_YEAR_MONTH,BUSINESS_UNIT_NO,BUSINESS_UNIT_NAME);

Customizing the Unix prompt (PS1)

We can customize the .profile on Unix to make the prompt display (variable PS1) more meaningful as well as provide some useful shortcuts.

For example, this is the .profile we use on one of the production AIX machines.

This results in the prompt display as shown below where we display the type of the machine using a color combination (in this case Red is used for all Production machines) , the current ORACLE_SID and the present working directory.

[PROD] rcatp:/u01/oracle

stty erase ^?
export ORACLE_SID=rcatp
alias sql=”sqlplus sys/xxx as sysdba”
set -o vi
export EDITOR=/usr/bin/vi
LOGS=/u01/local/bwdba/backup/logs;export LOG
export SCRPT=/u01/local/bwdba/scripts
Green=”33[32m"
Red="33[31m"
Yellow="33[33m"
BoldON="33[1m"
BoldOFF="33[22m"
NC="33[0m" # No Color
MESSAGE=`echo "$Red$BoldON[PROD]$BoldOFF$NC”`
export MESSAGE
Bold=$(tput smso)
Normal=$(tput sgr0)
export PS1=”$MESSAGE \${ORACLE_SID}:\$PWD > “

Including aliases and shortcuts make navigation very easy and enables us to quickly locate files when required.

For example to check the alert log we just need to do

$ cd $BDUMP

Or, to check the days RMAN backup logs, we just need to do

$ cd $LOG

Script – Monitor Flashback Logs

PROMPT How Far Back Can We Flashback To (Time)?
PROMPT
select to_char(oldest_flashback_time,'dd-mon-yyyy hh24:mi:ss') "Oldest Flashback Time"
 from v$flashback_database_log;

PROMPT
PROMPT How Far Back Can We Flashback To (SCN)?
PROMPT
col oldest_flashback_scn format 99999999999999999999999999
select oldest_flashback_scn from v$flashback_database_log;

PROMPT
PROMPT Flashback Area Usage
SELECT * FROM   V$FLASH_RECOVERY_AREA_USAGE;

PROMPT
col ROUND(SPACE_LIMIT/1048576) heading "Space Allocated (MB)" format 999999
col round(space_used/1048576) heading "Space Used (MB)" format 99999
col name Heading "Flashback Location" format a40

select name, round(space_limit/1048576),round(space_used/1048576)
 from  v$RECOVERY_FILE_DEST;

How Far Back Can We Flashback To (Time)?

Oldest Flashback Time
-----------------------------
05-jul-2009 22:53:07

How Far Back Can We Flashback To (SCN)?

       OLDEST_FLASHBACK_SCN
---------------------------
                15321928761

Flashback Area Usage

FILE_TYPE    PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE                   0                         0               0
ONLINELOG                     0                         0               0
ARCHIVELOG                16.33                     16.33              51
BACKUPPIECE               16.34                     16.34               6
IMAGECOPY                     0                         0               0
FLASHBACKLOG              45.94                     10.61             204

6 rows selected.

Flashback Location                       Space Allocated (MB) Space Used (MB)
---------------------------------------- -------------------- ---------------
/u05/oradata/flash_recovery_area                         5120            4025

10g materialized view complete refresh using atomic_refresh

After migrating from 9i to 10g, take care in case you have materialized views and are doing a complete refresh.

The following command to refresh an MVIEW resulted in the database undo tablespace filling up and the appearance of the dreaded snapshot too old error.

execute DBMS_MVIEW.REFRESH(‘OBJECT_OWNER.TEST_MVIEW,’C');

That is because in 10g the behaviour of Oracle in case the atomic_refresh=false parameter is not included is to delete the data first before inserting or refreshing the view.

To avoid this, issue the following command

execute DBMS_MVIEW.REFRESH(‘OBJECT_OWNER.TEST_MVIEW’,'C’,ATOMIC_REFRESH=>false);

This guarantees that Oracle will first TRUNCATE the MIVEW and then refresh it thereby excluding the need of the use of the UNDO tablespace.

ORA-27123 and Operating System permissions

PERMISSION ON ORACLE FILES FOR OTHER USERS OTHER THAN SOFTWARE OWNERS TO ACCESS DATABASE.

Login as another operating system user – gentest.

Oracle software owner is oracle.

gentest@10.53.200.236’s password:
gentest@imisd(devu001/u03/users/app/gentest) % sqlplus /

SQL*Plus: Release 10.2.0.3.0 – Production on Wed Dec 3 11:11:17 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

ERROR:
ORA-01034: ORACLE not available
ORA-27123: unable to attach to shared memory segment
IBM AIX RISC System/6000 Error: 13: Permission denied

To fix this issue -

Login as owner oracle –

cd $ORACLE_HOME/bin

ls -l oracle

-rwxr-xr-x 1 oracle oinstall 131619948 May 31 2007 oracle

chmod ug+s oracle or chmod 6751 oracle

oracle@ bin> ls -l oracle
-rwsr-sr-x 1 oracle oinstall 131619948 May 31 2007 oracle
oracle@ bin>

Now as gentest user connection to database works….

gentest@imisd(devu001/u03/users/app/gentest) %
gentest@imisd(devu001/u03/users/app/gentest) % sqlplus /

SQL*Plus: Release 10.2.0.3.0 – Production on Wed Dec 3 11:19:14 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL>

11g Snapshot Standby Database

Oracle 11g introduces the Snapshot Standby database which essentially is an updateable standby database which has been created from a physical standby database.

We can convert a physical standby database to a snapshot standby database, do some kind of testing on a database which is a read write copy of the current primary or production database and then finally revert it to it’s earlier state as a physical standby database.

While the snapshot standby database is open in read-write mode, redo is being received from the primary database, but is not applied.

After converting it back to a physical standby database, it is resynchronized with the primary by applying the accumalated redo data which was earlier shipped from the primary database but not applied.

Using a snapshot standby, we are able to do real time application testing using near real time production data. Very often we are required to do production clones for the purpose of testing. But using snapshot standby databases we can meet the same requirement sparing the effort,time,resources and disk space.

Initially we have a Data Guard broker configuration in place where APEX is the Primary database
and APEXDG is the Standby database.

DGMGRL> show configuration

Configuration
  Name:                gavin
  Enabled:             YES
  Protection Mode:     MaxAvailability
  Databases:
    apex   - Primary database
    apexdg - Physical standby database

Fast-Start Failover: DISABLED

Current status for "gavin":
SUCCESS

We now convert the physical standby database to a snapshot standby

DGMGRL> convert database 'apexdg' to snapshot standby;
Converting database "apexdg" to a Snapshot Standby database, please wait...
Database "apexdg" converted successfully

DGMGRL> show configuration

Configuration
  Name:                gavin
  Enabled:             YES
  Protection Mode:     MaxAvailability
  Databases:
    apex   - Primary database
    apexdg - Snapshot standby database

Fast-Start Failover: DISABLED

Current status for "gavin":
SUCCESS

We now connect to the snapshot standby database which is open in read-write mode and we create a table 

apex:/u01/oracle/scripts> export ORACLE_SID=apexdg
apexdg:/u01/oracle/scripts> sqlplus sh/SH

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Sep 19 09:33:29 2008

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table test_snapshot
  2  as select * from all_objects;

Table created.

SQL> select count(*) from test_snapshot;

  COUNT(*)
----------
     56467

At this point in time, we revert back to the original state

DGMGRL> convert database 'apexdg' to physical standby;

Converting database "apexdg" to a Physical Standby database, please wait...
Operation requires shutdown of instance "apexdg" on database "apexdg"
Shutting down instance "apexdg"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "apexdg" on database "apexdg"
Starting instance "apexdg"...
ORACLE instance started.
Database mounted.
Continuing to convert database "apexdg" ...
Operation requires shutdown of instance "apexdg" on database "apexdg"
Shutting down instance "apexdg"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "apexdg" on database "apexdg"
Starting instance "apexdg"...
ORACLE instance started.
Database mounted.
Database "apexdg" converted successfully
DGMGRL>

We now connect to the physical standby database and see that all the changes we made while
the standby database was functioning as a snapshot standby have been rolled back. The table
which we created in the snapshot standby database is not physically present in the physical
standby database.

apexdg:/u01/oracle/scripts> sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Fri Sep 19 09:48:38 2008

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database open read only;

Database altered.

SQL> conn sh/SH@apexdg
Connected.

SQL> select count(*) from test_snapshot;
select count(*) from test_snapshot
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

RMAN unregister database from catalog

1. Go to the database you wish to unregister and get its DBID:

system@HERML> select dbid,name from v$database;

DBID NAME
—————– ——————————
2082566940 HERML

2. Connect to the recovery catalog-owning schema and determine the DB_KEY of the database in the recovery catalog:

rman10@RMAN10P> select db_key,dbid,name from rc_database
2 where dbid = 2082566940;

DB_KEY DBID NAME
—————– —————– ——————————
555419843 2082566940 HERML

3. Use the dbms_rcvcat.unregisterdatabase procedure to unregister the database from the recovery catalog (this procedure takes the DB_KEY and the DB_ID that we just found as parameters)

– The syntax for the procedure is:
– dbms_rcvcat.unregisterdatabase(db_key,db_id);
In recovery catalog schema -

execute dbms.rcvcat.unregisterdatabase(555419843,2082566940);

10g RMAN restore on another machine

TASK: Restoring a tape backup onto another machine and bringing up the database.

ORIGINAL DATABASE: RPSP ON ITLINUX25 AND TARGET IS RPSP ON ITLINUX26.

PRE-REQUISITES:

Make sure that the tdpo.opt file uses the same TDP library pointer on the new machine as the original machine – only then will RMAN restore the backup from the correct library.

In ITLINUX26 ( NEW MACHINE)

rpsp:/opt/tivoli/tsm/client/oracle/bin>ls –lrt
total 1364
tdpo.opt.smp*
tdpoconf*
libobk.so*
agent.lic*
tdposync*
dsm.opt
tdpo.rpspd.opt*….should point to TDP on ITLINUX25.

1. BACKUP DATABASE PLUS ARCHIVE LOGS….ON ITLINUX25…DATABASE RPSP.

Backup script will look like this…

run {
allocate channel ch1 type ’sbt_tape’ parms ‘ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin/tdpo.rpspd.opt)’;
Backup database plus archivelog;
release channel ch1 ;
}

Get dbid name of rpsp….select dbid from v$database;

565953070

2. Now in ITLINUX26 machine…ALL RESTORATION AND RECOVERY COMMANDS MUST BE RUN ONLY IN ITLINUX26.

A:

SCENARIO ONE: ORIGINAL MACHINE IS AVAILABLE…

Copy setrpsp, initrpsp.ora to ITLINUX26 machine in appropriate areas and make required changes especially the new path of the controlfiles….

Create spfile from pfile….

Export ORACLE_HOME
EXPORT ORACLE_SID

sql>startup nomount;

After db is in nomount stage…exit

Then move directly to the RESTORE CONTROLFILE part…ignore next section…

B:

SCENARIO TWO: ORIGINAL MACHINE COMPLETELY LOST AND NO SPFILE AVAILABLE.

In case you do not have an spfile and the database has to restored and recovered….

Firstly startup nomount force the database….it will force an instance start without using an spfile….

This command must be run in an RMAN block.

vi nospfile.rcv

set dbid= 565953070;
run {
startup nomount force ;
}

Save file…

From command line..

rman target / catalog rman10gr2/rman10gr2@rmanp cmdfile=nospfile.rcv

rpsp:/opt/oracle/scripts>rman target / catalog rman10gr2/rman10gr2@rmanp cmdfile=nospfile.rcv

Recovery Manager: Release 10.2.0.1.0 – Production on Tue Nov 22 13:37:31 2005

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database (not started)
connected to recovery catalog database

RMAN> set dbid= 565953070;
2> run {
3> startup nomount force ;
4> }
5>
6>
executing command: SET DBID
database name is “RPSP” and DBID is 565953070

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file ‘/opt/oracle/product10g/dbs/initrpsp.ora’

starting Oracle instance without parameter file for retrival of spfile
Oracle instance started

Total System Global Area 159383552 bytes

Fixed Size 1218244 bytes
Variable Size 58722620 bytes
Database Buffers 92274688 bytes
Redo Buffers 7168000 bytes

Recovery Manager complete.

Once the instance is in nomount state, restore the spfile.

vi restorespfile.rcv

set dbid= 565953070;
run {
allocate channel ch1 type ’sbt_tape’ parms ‘ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin/tdpo.rpspd.opt)’;
restore spfile ;
release channel ch1 ;

rpsp:/opt/oracle/scripts>rman target / catalog rman10gr2/rman10gr2@rmanp cmdfile=restorespfile.rcv

Recovery Manager: Release 10.2.0.1.0 – Production on Tue Nov 22 14:00:52 2005

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: RPSP (not mounted)
connected to recovery catalog database

RMAN> set dbid= 565953070;
2> run {
3> allocate channel ch1 type ’sbt_tape’ parms ‘ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin/tdpo.rpspd.opt)’;
4> restore spfile ;
5> release channel ch1 ;
6> }
7>
8>
executing command: SET DBID
database name is “RPSP” and DBID is 565953070

allocated channel: ch1
channel ch1: sid=35 devtype=SBT_TAPE
channel ch1: Tivoli Data Protection for Oracle: version 5.2.0.0

Starting restore at 22-NOV-05

channel ch1: starting datafile backupset restore
channel ch1: restoring SPFILE
output filename=/opt/oracle/product10g/dbs/spfilerpsp.ora
channel ch1: reading from backup piece 20h4bm92_1_1
channel ch1: restored backup piece 1
piece handle=20h4bm92_1_1 tag=TAG20051122T030003
channel ch1: restore complete, elapsed time: 00:03:27
Finished restore at 22-NOV-05

released channel: ch1

Recovery Manager complete.
rpsp:/opt/oracle/scripts>

Now that the spfile is restored, create pfile from spfile…..edit pfile…make changes to point to this machine and then recreate spfile from pfile.

Next restore controlfile..

Make a restore.rcv file…TO RESTORE CONTROLFILE

Include following…

set dbid= 565953070;
run {
allocate channel ch1 type ’sbt_tape’ parms ‘ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin/tdpo.rpspd.opt)’;
restore controlfile ;
release channel ch1 ;
}

Run at command prompt

rpsp:/opt/oracle/scripts>rman target / catalog rman10gr2/rman10gr2@rmanp cmdfile=restore.rcv log=restore.log

tail – f restore.log

allocated channel: ch1
channel ch1: sid=323 devtype=SBT_TAPE
channel ch1: Tivoli Data Protection for Oracle: version 5.2.0.0

Starting restore at 21-NOV-05

channel ch1: starting datafile backupset restore
channel ch1: restoring control file
channel ch1: reading from backup piece 1ph491rn_1_1
channel ch1: restored backup piece 1
piece handle=1ph491rn_1_1 tag=TAG20051121T030004
channel ch1: restore complete, elapsed time: 00:04:46
output filename=/itlinux26db06/ORACLE/rpsp/control01.ctl
output filename=/itlinux26db06/ORACLE/rpsp/control02.ctl
output filename=/itlinux26db06/ORACLE/rpsp/control03.ctl
Finished restore at 21-NOV-05

released channel: ch1

Recovery Manager complete.

Next step …since controlfile is restored…MOUNT THE DATABASE.

SQL> alter database mount;

Database altered.

SQL> select name from v$controlfile;

NAME
——————————————————————————–
/itlinux26db06/ORACLE/rpsp/control01.ctl
/itlinux26db06/ORACLE/rpsp/control02.ctl
/itlinux26db06/ORACLE/rpsp/control03.ctl

Now create new .rcv for restoration of DATAFILES……

Use this script to generate the files to be renamed…

select ’set newname for datafile ‘||file_id||’ to ”’||’/'||
decode(substr(file_name,instr(file_name,’/',1)+ 1,instr(file_name,’/',1,2) – 2),
‘patriot7′,’itsun103ekcl07′)||’/'||substr(file_name,(instr(file_name,’ORACLE’)))||”’;’
from dba_data_files;

vi restoredb.rcv

run {
allocate channel ch1 type ’sbt_tape’ parms ‘ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin/tdpo.rpspd.opt)’;
set newname for datafile 4 to ‘/itlinux26db06/ORACLE/rpsp/users01.dbf’;
set newname for datafile 3 to ‘/itlinux26db06/ORACLE/rpsp/sysaux01.dbf’;
set newname for datafile 2 to ‘/itlinux26db06/ORACLE/rpsp/undotbs01.dbf’;
set newname for datafile 1 to ‘/itlinux26db06/ORACLE/rpsp/system01.dbf’;
set newname for datafile 5 to ‘/itlinux26db06/ORACLE/rpsp/example01.dbf’;
set newname for datafile 6 to ‘/itlinux26db06/ORACLE/rpsp/perfstat01.dbf’;
set newname for datafile 7 to ‘/itlinux26db06/ORACLE/rpsp/tools01.dbf’;
set newname for datafile 8 to ‘/itlinux26db06/ORACLE/rpsp/rpsp_dat01.dbf’;
set newname for datafile 9 to ‘/itlinux26db06/ORACLE/rpsp/rpsp_dat02.dbf’;
set newname for datafile 10 to ‘/itlinux26db06/ORACLE/rpsp/rpsp_dat03.dbf’;
set newname for datafile 11 to ‘/itlinux26db06/ORACLE/rpsp/rpsp_dat04.dbf’;
set newname for datafile 12 to ‘/itlinux26db06/ORACLE/rpsp/rpsp_ind01.dbf’;
set newname for datafile 13 to ‘/itlinux26db06/ORACLE/rpsp/rpsp_ind02.dbf’;
set newname for datafile 14 to ‘/itlinux26db06/ORACLE/rpsp/rpsp_ind03.dbf’;
set newname for datafile 15 to ‘/itlinux26db06/ORACLE/rpsp/undotbs02.dbf’;
restore database ;
switch datafile all;
release channel ch1 ;
}
SAVE THE FILE.

From command prompt…

rman target / catalog rman10gr2/rman10gr2@rmanp cmdfile=restoredb.rcv log=restoredb.log

:/opt/oracle/scripts>tail -f restoredb.log

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 21-NOV-05

channel ch1: starting datafile backupset restore
channel ch1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /itlinux26db06/ORACLE/rpsp/system01.dbf
restoring datafile 00002 to /itlinux26db06/ORACLE/rpsp/undotbs01.dbf
restoring datafile 00003 to /itlinux26db06/ORACLE/rpsp/sysaux01.dbf
restoring datafile 00004 to /itlinux26db06/ORACLE/rpsp/users01.dbf
restoring datafile 00005 to /itlinux26db06/ORACLE/rpsp/example01.dbf
restoring datafile 00006 to /itlinux26db06/ORACLE/rpsp/perfstat01.dbf
restoring datafile 00007 to /itlinux26db06/ORACLE/rpsp/tools01.dbf
restoring datafile 00008 to /itlinux26db06/ORACLE/rpsp/rpsp_dat01.dbf
restoring datafile 00009 to /itlinux26db06/ORACLE/rpsp/rpsp_dat02.dbf
restoring datafile 00010 to /itlinux26db06/ORACLE/rpsp/rpsp_dat03.dbf
restoring datafile 00011 to /itlinux26db06/ORACLE/rpsp/rpsp_dat04.dbf
restoring datafile 00012 to /itlinux26db06/ORACLE/rpsp/rpsp_ind01.dbf
restoring datafile 00013 to /itlinux26db06/ORACLE/rpsp/rpsp_ind02.dbf
restoring datafile 00014 to /itlinux26db06/ORACLE/rpsp/rpsp_ind03.dbf
channel ch1: reading from backup piece 1gh47mvc_1_1

Now RECOVER DATABASE USING ANY INCREMENTAL BACKUP AND ARCHIVE LOGS…RMAN WILL DO THE NEEDFUL.

To get list of archive logs backed up by RMAN…

Sqlplus rman10rg2/rman10gr2@rmanp

select sequence# from rc_backup_redolog where db_name=’RPSP’;

Last seq is 466.

vi recoverdb.rcv

run {
allocate channel ch1 type ’sbt_tape’ parms ‘ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin/tdpo.rpspd.opt)’;
recover database until logseq=466 ;
release channel ch1 ;
}

Save the file…

Command prompt…

rman target / catalog rman10gr2/rman10gr2@rmanp cmdfile=rescover.rcv log=recover.log

RMAN does a recovery until log archive log sequence number 466…

allocated channel: ch1
channel ch1: sid=323 devtype=SBT_TAPE
channel ch1: Tivoli Data Protection for Oracle: version 5.2.0.0

Starting recover at 21-NOV-05
channel ch1: starting incremental datafile backupset restore
channel ch1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /itlinux26db06/ORACLE/rpsp/system01.dbf
destination for restore of datafile 00002: /itlinux26db06/ORACLE/rpsp/undotbs01.dbf
destination for restore of datafile 00003: /itlinux26db06/ORACLE/rpsp/sysaux01.dbf
destination for restore of datafile 00004: /itlinux26db06/ORACLE/rpsp/users01.dbf
destination for restore of datafile 00005: /itlinux26db06/ORACLE/rpsp/example01.dbf
destination for restore of datafile 00006: /itlinux26db06/ORACLE/rpsp/perfstat01.dbf
destination for restore of datafile 00007: /itlinux26db06/ORACLE/rpsp/tools01.dbf
destination for restore of datafile 00008: /itlinux26db06/ORACLE/rpsp/rpsp_dat01.dbf
destination for restore of datafile 00009: /itlinux26db06/ORACLE/rpsp/rpsp_dat02.dbf
destination for restore of datafile 00010: /itlinux26db06/ORACLE/rpsp/rpsp_dat03.dbf
destination for restore of datafile 00011: /itlinux26db06/ORACLE/rpsp/rpsp_dat04.dbf
destination for restore of datafile 00012: /itlinux26db06/ORACLE/rpsp/rpsp_ind01.dbf
destination for restore of datafile 00013: /itlinux26db06/ORACLE/rpsp/rpsp_ind02.dbf
destination for restore of datafile 00014: /itlinux26db06/ORACLE/rpsp/rpsp_ind03.dbf
channel ch1: reading from backup piece 1nh491pl_1_1
channel ch1: reading from backup piece 1nh491pl_1_1
channel ch1: restored backup piece 1
piece handle=1nh491pl_1_1 tag=TAG20051121T030004
channel ch1: restore complete, elapsed time: 00:04:26
channel ch1: starting incremental datafile backupset restore
channel ch1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00015: /itlinux26db06/ORACLE/rpsp/undotbs02.dbf
channel ch1: reading from backup piece 1oh491rm_1_1
channel ch1: restored backup piece 1
piece handle=1oh491rm_1_1 tag=TAG20051121T030004
channel ch1: restore complete, elapsed time: 00:17:57

starting media recovery

channel ch1: starting archive log restore to default destination
channel ch1: restoring archive log
archive log thread=1 sequence=456
channel ch1: reading from backup piece 1qh495a4_1_1
channel ch1: restored backup piece 1
piece handle=1qh495a4_1_1 tag=TAG20051121T040004
channel ch1: restore complete, elapsed time: 00:00:02
archive log filename=/itlinux26db06/ORACLE/rpsp/arch/arch.456.1.556053936.log thread=1 sequence=456
channel ch1: starting archive log restore to default destination
channel ch1: restoring archive log
archive log thread=1 sequence=457
channel ch1: restoring archive log
archive log thread=1 sequence=458
channel ch1: restoring archive log
archive log thread=1 sequence=459
channel ch1: restoring archive log
archive log thread=1 sequence=460
channel ch1: restoring archive log
archive log thread=1 sequence=461
channel ch1: reading from backup piece 1rh49jc6_1_1
channel ch1: restored backup piece 1
piece handle=1rh49jc6_1_1 tag=TAG20051121T080006
channel ch1: restore complete, elapsed time: 00:01:06
archive log filename=/itlinux26db06/ORACLE/rpsp/arch/arch.457.1.556053936.log thread=1 sequence=457
archive log filename=/itlinux26db06/ORACLE/rpsp/arch/arch.458.1.556053936.log thread=1 sequence=458
archive log filename=/itlinux26db06/ORACLE/rpsp/arch/arch.459.1.556053936.log thread=1 sequence=459
archive log filename=/itlinux26db06/ORACLE/rpsp/arch/arch.460.1.556053936.log thread=1 sequence=460
archive log filename=/itlinux26db06/ORACLE/rpsp/arch/arch.461.1.556053936.log thread=1 sequence=461
channel ch1: starting archive log restore to default destination
channel ch1: restoring archive log
archive log thread=1 sequence=462
channel ch1: restoring archive log
archive log thread=1 sequence=463
channel ch1: reading from backup piece 1sh49jdk_1_1
channel ch1: restored backup piece 1
piece handle=1sh49jdk_1_1 tag=TAG20051121T080006
channel ch1: restore complete, elapsed time: 00:02:56
archive log filename=/itlinux26db06/ORACLE/rpsp/arch/arch.462.1.556053936.log thread=1 sequence=462
archive log filename=/itlinux26db06/ORACLE/rpsp/arch/arch.463.1.556053936.log thread=1 sequence=463
channel ch1: starting archive log restore to default destination
channel ch1: restoring archive log
archive log thread=1 sequence=464
channel ch1: restoring archive log
archive log thread=1 sequence=465
channel ch1: reading from backup piece 1th4a1e6_1_1
channel ch1: restored backup piece 1
piece handle=1th4a1e6_1_1 tag=TAG20051121T120006
channel ch1: restore complete, elapsed time: 00:00:36
archive log filename=/itlinux26db06/ORACLE/rpsp/arch/arch.464.1.556053936.log thread=1 sequence=464
archive log filename=/itlinux26db06/ORACLE/rpsp/arch/arch.465.1.556053936.log thread=1 sequence=465
media recovery complete, elapsed time: 00:00:26
Finished recover at 21-NOV-05

released channel: ch1

Recovery Manager complete.

Now rename redolog files to point to this machine…

Now select member from v$logfile;…..rename the redo log files to area in ITLINUX26 machine

Alter database rename file ‘/….’ to ‘/itlinux26db06/ORACLE/rpsp/redo…..

Alter database open resetlogs…(IF THIS FAILS DUE TO THE PRESENCE OF A BLOCK CHANGE TRACKING FILE….

alter database disable block change tracking;

Database altered.

SQL> alter database open;

Database altered.

Script – Database structure and file location

set pagesize 500
set linesize 130
Prompt
Prompt Control Files Location >>>>
col name  format a60 heading "Control Files"

select name
from   sys.v_$controlfile
/

Prompt
Prompt Redo Log File Locations >>>>
Prompt

col Grp    format 9999
col member format a50 heading "Online REDO Logs"
col File#  format 9999
col name   format a50 heading "Online REDO Logs"
break on Grp
select group#,member
from   sys.v_$logfile
/

Prompt Data Files Locations >>>>

col Tspace    format a25
col status    format a3  heading Sta
col Id        format 9999
col Mbyte     format 999999999
col name      format a50 heading "Database Data Files"
col Reads     format 99,999,999
col Writes    format 99,999,999

break on report
compute sum label 'Total(MB)'  of Mbyte  on report 

select F.file_id Id,
       F.file_name name,
       F.bytes/(1024*1024) Mbyte,
       decode(F.status,'AVAILABLE','OK',F.status) status,
       F.tablespace_name Tspace
from   sys.dba_data_files F
order by tablespace_name;

Control Files Location >>>>

Control Files
------------------------------------------------------------
/u03/oradata/rcatp/control01.ctl
/u05/oradata/rcatp/control02.ctl

Redo Log File Locations >>>>

    GROUP# Online REDO Logs
---------- --------------------------------------------------
         1 /u03/oradata/rcatp/redo01.log
         2 /u05/oradata/rcatp/redo02.log
         3 /u03/oradata/rcatp/redo03.log
         3 /u05/oradata/rcatp/redo03b.log
         1 /u05/oradata/rcatp/redo01b.log
         2 /u03/oradata/rcatp/redo02b.log

6 rows selected.

Data Files Locations >>>>

   ID Database Data Files                                     MBYTE Sta TSPACE
----- -------------------------------------------------- ---------- --- -------------------------
    9 /u03/oradata/rcatp/patrol01.dbf                            20 OK  PATROL
    7 /u03/oradata/rcatp/rman10p01.dbf                          466 OK  RMAN10P
    5 /u03/oradata/rcatp/rman11p01.dbf                          200 OK  RMAN11P
    8 /u03/oradata/rcatp/rman9p01.dbf                           106 OK  RMAN9P
    3 /u03/oradata/rcatp/sysaux01.dbf                           540 OK  SYSAUX
    1 /u03/oradata/rcatp/system01.dbf                           700 OK  SYSTEM
    2 /u03/oradata/rcatp/undotbs01.dbf                          220 OK  UNDOTBS1
    4 /u03/oradata/rcatp/users01.dbf                              6 OK  USERS
                                                         ----------
sum                                                            2258

8 rows selected.

Script – Tablespace free space and fragmentation

      set linesize 150
        column tablespace_name format a20 heading 'Tablespace'
     column sumb format 999,999,999
     column extents format 9999
     column bytes format 999,999,999,999
     column largest format 999,999,999,999
     column Tot_Size format 999,999 Heading 'Total| Size(Mb)'
     column Tot_Free format 999,999,999 heading 'Total Free(MB)'
     column Pct_Free format 999.99 heading '% Free'
     column Chunks_Free format 9999 heading 'No Of Ext.'
     column Max_Free format 999,999,999 heading 'Max Free(Kb)'
     set echo off
     PROMPT  FREE SPACE AVAILABLE IN TABLESPACES
     select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
     sum(a.sumb/1048576) Tot_Free,
     sum(a.sumb)*100/sum(a.tots) Pct_Free,
     sum(a.largest/1024) Max_Free,sum(a.chunks) Chunks_Free
     from
     (
     select tablespace_name,0 tots,sum(bytes) sumb,
     max(bytes) largest,count(*) chunks
     from dba_free_space a
     group by tablespace_name
     union
     select tablespace_name,sum(bytes) tots,0,0,0 from
      dba_data_files
     group by tablespace_name) a
     group by a.tablespace_name
order by pct_free;

                         Total
Tablespace            Size(Mb) Total Free(MB)  % Free Max Free(Kb) No Of Ext.
-------------------- --------- -------------- ------- ------------ ----------
SYSTEM                     790              3     .38        3,008          2
SYSAUX                     752             52    6.86       32,768        132
USERS                        5              1   11.25          576          1
MGMT_ECM_DEPOT_TS          100             43   43.25       43,968          2
MGMT_TABLESPACE         13,940          8,388   60.17      155,200       1594
UNDOTBS1                   605            491   81.07      311,360         44
PATROL                       1              1   93.75          960          1

Data Guard Switchover Unix shell script

The following Unix shell scripts can be used to automate the Data Guard Switchover process of a physical standby database.

It is very important that the scripts are run in the correct order and on right machine.

These scripts are based on a few customisations. The *.sh scripts are located in a directory called “/var/opt/oracle/dataguard” and there is another file called “set$ORACLE_SID” which is located under “/var/opt/oracle/cronjobs”. This script sets the environment for the individual Oracle instance like the $ORACLE_SID and the $ORACLE_HOME.

On the machine where the Primary Database is running we need to run the following scripts

$ cd /var/opt/oracle/dataguard
$ ./pre_switchover_check.sh
$ ./make_me_standby.sh

On the machine where the Standby Database is running we need to run the following script:

$ cd /var/opt/oracle/dataguard
$ ./make_me_primary.sh

After the switchover is completed, we need to run the following script on the machine where the former Primary (now new Standby) database is running:

$ cd /var/opt/oracle/dataguard
$ ./start_recovery.sh

pre_switchover_check.sh

!/bin/ksh

if [ "$1" = "" ]
then
echo ""
echo "##################################################################"
echo "# PLEASE ENTER ORACLE_SID AS THE FIRST COMMAND LINE ARGUMENT     #"
echo "#      SCRIPT USAGE : pre_switchover_check.sh        #"
echo "##################################################################"
echo ""
exit
fi

ORACLE_SID=`echo $1 |tr '[A-Z]' '[a-z]'`; export ORACLE_SID;

ls -l /var/opt/oracle/cronjobs/set$ORACLE_SID > /dev/null
if [ $? != 0 ]
then
echo ""
echo "##################################################################"
echo "#           ENVIRONMENT SETTING SET FILE NOT FOUND...!!!         #"
echo "#           /var/opt/oracle/cronjobs/set$ORACLE_SID              #"
echo "##################################################################"
echo ""
exit
fi

. /var/opt/oracle/cronjobs/set$ORACLE_SID

DB=`echo $1 |tr '[a-z]' '[A-Z]'`; export DB;

echo ""
echo "##################################################################"
echo "#            PERFORMING PRE-SWITCHOVER CHECKS FOR $DB            #"
echo "##################################################################"
echo ""

sqlplus  -s /nolog |&
print -p  "connect / as sysdba"
print -p  "set feedback off pause off pagesize 0 heading off verify off linesize 500 term off "
print -p  "set sqlprompt ''"
read  -p   JUNK
print -p  "SELECT DATABASE_ROLE DBROLE FROM V\$DATABASE;"
read  -p   DBROLE
print -p  "SELECT COUNT(FILE#) FILESTAT FROM V\$DATAFILE WHERE STATUS IN ('RECOVER', 'OFFLINE');"
read  -p   FILESTAT
print -p  "SELECT COUNT(FILE#) BKPSTAT FROM V\$BACKUP WHERE STATUS = 'ACTIVE';"
read  -p   BKPSTAT
print -p  "SELECT LOG_ARCHIVED-LOG_APPLIED LOGGAP
FROM (SELECT MAX(SEQUENCE#) LOG_ARCHIVED
FROM V\$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES'
AND RESETLOGS_CHANGE#=(SELECT MAX(RESETLOGS_CHANGE#)
FROM V\$ARCHIVED_LOG)),
(SELECT MAX(SEQUENCE#) LOG_APPLIED
FROM V\$ARCHIVED_LOG WHERE DEST_ID=2
AND APPLIED='YES'
AND RESETLOGS_CHANGE#=(SELECT MAX(RESETLOGS_CHANGE#) FROM V\$ARCHIVED_LOG)) ;"
read  -p   LOGGAP
print -p  "EXIT"

echo "CHECKING CURRENT DATABASE ROLE..."
if [ "$DBROLE" = "PHYSICAL STANDBY"  ]
then
echo ""
echo "##################################################################"
echo "# ERROR ! ! ! NOT  A PRIMARY DATABASE, PLEASE RUN IT ON PRIMARY  #"
echo "##################################################################"
echo ""
exit
else
echo ""
echo "   OK   "
echo ""
fi

echo "CHECKING LOG GAP BETWEEN PRIMARY & STANDBY..."
if [ $LOGGAP -ne 0 ]
then
echo ""
echo "######################################################################"
echo "# ERROR ! ! !  PRIMARY & STANDBY ARE NOT IN SYNC, LOG GAP = $LOGGAP  #"
echo "######################################################################"
echo ""
exit
else
echo ""
echo "   OK   "
echo ""
fi

echo "CHECKING FILES OFFLINE OR RECOVER STATUS..."
if [ $FILESTAT -ne 0 ]
then
echo ""
echo "##################################################################"
echo "#  ERROR ! ! !  ONE OR MORE FILE/S OFFLINE OR IN RECOVER STATUS  #"
echo "##################################################################"
echo ""
exit
else
echo ""
echo "   OK   "
echo ""
fi

echo "CHECKING FILES IN BACKUP MODE..."
if [ $BKPSTAT -ne 0 ]
then
echo ""
echo "##################################################################"
echo "#      ERROR ! ! !  ONE OR MORE FILE/S ARE IN BACKUP MODE        #"
echo "##################################################################"
echo ""
exit
else
echo ""
echo "   OK   "
echo ""
fi

echo ""
echo "##################################################################"
echo "# ALL PRE-SWITCHOVER CHECKS SUCCEEDED... PROCEED WITH SWITCHOVER #"
echo "##################################################################"
echo ""

make_me_standby.sh

#!/bin/ksh

if [ "$1" = "" ]
then
echo ""
echo "##################################################################"
echo "# PLEASE ENTER ORACLE_SID AS THE FIRST COMMAND LINE ARGUMENT     #"
echo "#        SCRIPT USAGE : make_me_standby.sh           #"
echo "##################################################################"
echo ""
exit
fi

ORACLE_SID=`echo $1 |tr '[A-Z]' '[a-z]'`; export ORACLE_SID;

ls -l /var/opt/oracle/cronjobs/set$ORACLE_SID > /dev/null
if [ $? != 0 ]
then
echo ""
echo "##################################################################"
echo "#           ENVIRONMENT SETTING SET FILE NOT FOUND...!!!         #"
echo "#           /var/opt/oracle/cronjobs/set$ORACLE_SID              #"
echo "##################################################################"
echo ""
exit
fi

. /var/opt/oracle/cronjobs/set$ORACLE_SID

DB=`echo $1 |tr '[a-z]' '[A-Z]'`; export DB;

sqlplus  -s /nolog |&
print -p  "connect / as sysdba"
print -p  "set feedback off pause off pagesize 0 heading off verify off linesize 500 term off"
print -p  "set sqlprompt ''"
read  -p   JUNK
print -p  "SELECT DATABASE_ROLE DBROLE FROM V\$DATABASE;"
read  -p   DBROLE
print -p  "SELECT COUNT(FILE#) FILESTAT FROM V\$DATAFILE WHERE STATUS IN ('RECOVER', 'OFFLINE');"
read  -p   FILESTAT
print -p  "SELECT COUNT(FILE#) BKPSTAT FROM V\$BACKUP WHERE STATUS = 'ACTIVE';"
read  -p   BKPSTAT
print -p  "SELECT LOG_ARCHIVED-LOG_APPLIED LOGGAP
FROM (SELECT MAX(SEQUENCE#) LOG_ARCHIVED
FROM V\$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES'
AND RESETLOGS_CHANGE#=(SELECT MAX(RESETLOGS_CHANGE#)
FROM V\$ARCHIVED_LOG)),
(SELECT MAX(SEQUENCE#) LOG_APPLIED
FROM V\$ARCHIVED_LOG WHERE DEST_ID=2
AND APPLIED='YES'
AND RESETLOGS_CHANGE#=(SELECT MAX(RESETLOGS_CHANGE#) FROM V\$ARCHIVED_LOG)) ;"
read  -p   LOGGAP
print -p  "EXIT"

if [ "$DBROLE" = "PHYSICAL STANDBY"  ]
then
echo ""
echo "##################################################################"
echo "# ERROR ! ! ! NOT  A PRIMARY DATABASE, PLEASE RUN IT ON PRIMARY  #"
echo "##################################################################"
echo ""
exit
fi

if [ $LOGGAP -ne 0 ]
then
echo ""
echo "######################################################################"
echo "# ERROR ! ! !  PRIMARY & STANDBY ARE NOT IN SYNC, LOG GAP = $LOGGAP  #"
echo "######################################################################"
echo ""
exit
fi

echo "CHECKING FILES OFFLINE OR RECOVER STATUS..."
if [ $FILESTAT -ne 0 ]
then
echo ""
echo "##################################################################"
echo "#  ERROR ! ! !  ONE OR MORE FILE/S OFFLINE OR IN RECOVER STATUS  #"
echo "##################################################################"
echo ""
exit
fi

if [ $BKPSTAT -ne 0 ]
then
echo ""
echo "##################################################################"
echo "#      ERROR ! ! !  ONE OR MORE FILE/S ARE IN BACKUP MODE        #"
echo "##################################################################"
echo ""
exit
fi

echo ""
echo "##################################################################"
echo "#              ALL PRE-SWITCHOVER CHECKS SUCCEEDED...            #"
echo "       SWITCHING $DB TO STANDBY ROLE, PLEASE CONFIRM ...          "
echo "##################################################################"
echo ""
echo ""
echo "Continue? - Please enter 'Y' to proceed & 'N' to exit"
read ans
if [ "$ans" = 'Y' -o "$ans" = 'y' ]
then
sqlplus -s  /nolog < /tmp/make_me_standby.log
connect / as sysdba;
startup force;
alter database commit to switchover to standby with session shutdown;
shutdown immediate;
startup nomount;
alter database mount standby database;
select database_role from v\$database;
EOF
cat /tmp/make_me_standby.log
$ORACLE_HOME/bin/lsnrctl stop PRIMARY_$DB
else
echo "Quitting ....."
exit
fi

echo ""
echo "####################################################################"
echo "# PLEASE CONFIRM THAT THE STATUS OF DATABASE IS NOW "STANDBY"      #"
echo "#                                                                  #"
echo "# On OLD STANDBY Host please run the following script:         #"
echo "# /var/opt/oracle/dataguard/make_me_primary.sh                #"
echo "####################################################################"
echo ""

make_me_primary.sh

#!/bin/ksh

if [ "$1" = "" ]
then
echo ""
echo "##################################################################"
echo "#  PLEASE ENTER ORACLE_SID AS THE FIRST COMMAND LINE ARGUMENT    #"
echo "#         SCRIPT USAGE : make_me_primary.sh          #"
echo "##################################################################"
echo ""
exit
fi

ORACLE_SID=`echo $1 |tr '[A-Z]' '[a-z]'`; export ORACLE_SID;

ls -l /var/opt/oracle/cronjobs/set$ORACLE_SID > /dev/null
if [ $? != 0 ]
then
echo ""
echo "##################################################################"
echo "#           ENVIRONMENT SETTING SET FILE NOT FOUND...!!!         #"
echo "#           /var/opt/oracle/cronjobs/set$ORACLE_SID              #"
echo "##################################################################"
echo ""
exit
fi

. /var/opt/oracle/cronjobs/set$ORACLE_SID

DB=`echo $1 |tr '[a-z]' '[A-Z]'`; export DB;

sqlplus  -s /nolog |&
print -p  "connect / as sysdba"
print -p  "set feedback off pause off pagesize 0 heading off verify off linesize 500 term off"
print -p  "set sqlprompt ''"
read  -p   JUNK
print -p  "SELECT DATABASE_ROLE DBROLE FROM V\$DATABASE;"
read  -p   DBROLE
print -p  "exit"

if [ "$DBROLE" = "PRIMARY"  ]
then
echo ""
echo "##################################################################"
echo "#   ERROR ! ! ! NOT STANDBY DATABASE, PLEASE RUN IT ON STANDBY   #"
echo "##################################################################"
echo ""
exit
fi

echo ""
echo "##################################################################"
echo "#         SWITCHING $DB TO PRIMARY ROLE, PLEASE CONFIRM ...      #"
echo "##################################################################"
echo ""
echo "Continue? - Please enter 'Y' to proceed & 'N' to exit"
read ans
if [ "$ans" = 'Y' -o "$ans" = 'y' ]
then
sqlplus -s /nolog < /tmp/make_me_primary.log
connect / as sysdba;
alter database commit to switchover to primary with session shutdown;
shutdown immediate;
startup mount;
alter system set log_archive_dest_state_2=enable scope=both;
alter database set standby database to maximize performance;
alter database open;
select database_role from v\$database;
EOF
cat /tmp/make_me_primary.log
$ORACLE_HOME/bin/lsnrctl start PRIMARY_$DB
else
echo "Quitting ....."
exit
fi

echo "####################################################################"
echo "# PLEASE CONFIRM THAT THE STATUS OF DATABASE IS NOW "PRIMARY"      #"
echo "#                                                                  #"
echo "# On NEW STANDBY Host please run the following script:             #"
echo "# /var/opt/oracle/dataguard/start_recovery.sh                 #"
echo "####################################################################"

start_recovery.sh

#!/bin/ksh

if [ "$1" = "" ]
then
echo ""
echo "##################################################################"
echo "#   PLEASE ENTER ORACLE_SID AS THE FIRST COMMAND LINE ARGUMENT   #"
echo "#      SCRIPT USAGE : start_recovery.sh              #"
echo "##################################################################"
echo ""
exit
fi

ORACLE_SID=`echo $1 |tr '[A-Z]' '[a-z]'`; export ORACLE_SID;

ls -l /var/opt/oracle/cronjobs/set$ORACLE_SID > /dev/null
if [ $? != 0 ]
then
echo ""
echo "##################################################################"
echo "#           ENVIRONMENT SETTING SET FILE NOT FOUND...!!!         #"
echo "#           /var/opt/oracle/cronjobs/set$ORACLE_SID              #"
echo "##################################################################"
echo ""
exit
fi

. /var/opt/oracle/cronjobs/set$ORACLE_SID

DB=`echo $1 |tr '[a-z]' '[A-Z]'`; export DB;

sqlplus  -s /nolog |&
print -p  "connect / as sysdba"
print -p  "set feedback off pause off pagesize 0 heading off verify off linesize 500 term off"
print -p  "set sqlprompt ''"
read  -p   JUNK
print -p  "SELECT DATABASE_ROLE DBROLE FROM V\$DATABASE;"
read  -p   DBROLE
print -p  "exit"

if [ "$DBROLE" = "PRIMARY"  ]
then
echo ""
echo "##################################################################"
echo "#   ERROR ! ! ! NOT STANDBY DATABASE, PLEASE RUN IT ON STANDBY   #"
echo "##################################################################"
echo ""
exit
fi

echo ""
echo "##################################################################"
echo "#     STARTING RECOVERY FOR $DB  STANDBY, PLEASE CONFIRM ...     #"
echo "##################################################################"
echo ""
echo "Continue? - Please enter 'Y' to proceed & 'N' to exit"
read ans
if [ "$ans" = 'Y' -o "$ans" = 'y' ]
then
sqlplus -s /nolog < /tmp/start_recovery.log
connect / as sysdba;
recover managed standby database disconnect;
alter system set log_archive_dest_state_2=defer scope=both;
EOF
cat /tmp/start_recovery.log
ps -ef | grep ora_mrp0_$ORACLE_SID |grep -v grep > /dev/null
if [ $? != 0 ]
then
echo ""
echo "##################################################################"
echo "#           RECOVERY PROCESS NOT RUNNING... PLEASE CHECK         #"
echo "##################################################################"
echo ""
exit
else
echo "##################################################################"
echo "#           MRP PROCESS SUCESSFULLY STARTED                      #"
echo "##################################################################"
echo ""
echo "####################################################################"
echo "# 		SWITCHOVER COMPLETE                              #"
echo ""
echo "* Perform Database Post-Switchover Checklist!                      "
echo "####################################################################"
fi
else
echo "Quitting ....."
exit
fi

Script – Latch Contention (top 5 latches)

This script will display the top 5 latches with the most sleeps.

Script can be changed to even sort the display on misses instead.

set linesize 120
col name format a30

select * from
 (select name, gets,misses,  sleeps
 from   v$latch
 order by sleeps desc)
 where rownum < 6;

Script – Top SQL (Physical Reads)

This script will list the top 5 SQL statements sorted by the most number of physical reads

set serverout on size 1000000

set feedback off

declare

top5 number;

text1 varchar2(4000);

x number;

len1 number;

Cursor c1 is

select disk_reads,substr(sql_text,1,4000)

from v$sqlarea

order by disk_reads desc;

begin

dbms_output.put_line('Reads'||'  '||'                          Text');

dbms_output.put_line ('-----'||'  '||'----------------------------------------------------');

dbms_output.put_line('      ');

open c1;

for i in 1 .. 5 loop

fetch c1 into top5, text1;

dbms_output.put_line(rpad(to_char(top5),9)|| ' '||substr(text1,1,66));

len1 :=length(text1);

x := 66;

while len1 > x-1 loop

dbms_output.put_line('"         '||substr(text1,x,64));

x := x+64;

end loop;

end loop;

end;

/

Script – Top SQL (Buffer Gets)

This script will list the top 5 SQL statements sorted by the most number of buffer gets or logical reads

set serverout on size 1000000

declare
top5 number;
text1 varchar2(4000);
x number;
len1 number;
Cursor c1 is
select buffer_gets,substr(sql_text,1,4000)
from v$sqlarea
order by buffer_gets desc;
begin
dbms_output.put_line('Reads'||'  '||'                          Text');
dbms_output.put_line ('-----'||'  '||'---------------------------------------------------');
dbms_output.put_line('      ');
open c1;
for i in 1 .. 5 loop
fetch c1 into top5, text1;
dbms_output.put_line(rpad(to_char(top5),9)|| ' '||substr(text1,1,66));
len1 :=length(text1);
x := 66;
while len1 > x-1 loop
dbms_output.put_line('"         '||substr(text1,x,64));
x := x+64;
end loop;
end loop;
end;
/

Script – Top database wait events

This script will list the top 5 database wait events since instance startup sorted by time waited

set linesize 120
col event format a30

select * from (
select  event, total_waits, total_timeouts, time_waited
from v$system_event
where event not like 'SQL*Net%'
and event not in ('pmon timer','rdbms ipc message','dispatcher timer','smon timer')
order by time_waited desc )
where rownum < 6;

Script – Segments with highest I/O activity

This script will list the top 10 segments in the database that have the most number of
physical reads against them.
Script can also be changed to query on 'physical writes' instead.

set pagesize 200
setlinesize 120
col segment_name format a20
col owner format a10 

select segment_name,object_type,total_physical_reads
from ( select owner||'.'||object_name as segment_name,object_type,
value as total_physical_reads
from v$segment_statistics
where statistic_name in ('physical reads')
order by total_physical_reads desc)
where rownum <=10;