RMAN duplicate database 10gR2

Yet another RMAN duplicate database post.

Duplicating a Database on a Remote Host with a Different Directory Structure

The requirement is to duplicate test database (10.2.0.4) for 11gR2 upgrade

If you want to read the manual, it’s here (open in new window)

Existing database: TEST10g

Clone database: DEV01

Configure listener for DEV01, add TEST10g to tnsnames, create password file and (server) parameter file

The following parameters were modified for DEV01:

*.background_dump_dest='/opt/u01/app/oracle/admin/dev01/bdump'
*.control_files='/oracle/oradata/dev01/control01.ctl','/oracle/oradata/dev01/control02.ctl'
*.core_dump_dest='/opt/u01/app/oracle/admin/dev01/cdump'
*.db_name='dev01'
*.instance_name='dev01'
*.log_archive_dest_1='LOCATION=/oracle/oradata/dev01/arch'
*.user_dump_dest='/opt/u01/app/oracle/admin/dev01/udump'
*.db_file_name_convert=(
'/u03/oradata/test10g','/oracle/oradata/dev01',
'/u07/oradata/10gtest','/oracle/oradata/dev01',
'/u08/oradata/test10g','/oracle/oradata/dev01',
'/u04/oradata/test10g','/oracle/oradata/dev01'
)
*.log_file_name_convert=(
'/u04/oradata/test10g','/oracle/oradata/dev01'
,'/u07/oradata/test10g','/oracle/oradata/dev01'
)

Shell script to be execute at clone database server (DEV01):

#!/bin/sh -x

NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI:SS"
NLS_LANG="AMERICAN_AMERICA.UTF8"
ORACLE_SID=dev01
export NLS_DATE_FORMAT NLS_LANG ORACLE_SID

BKDIR=/home/mdinh/rman_backups
OUTF=/home/mdinh/rman_backups/rmandupdb.log
RMAN=$ORACLE_HOME/bin/rman
$RMAN msglog $OUTF > /dev/null << END
  connect target sys/password@test10g
  connect auxiliary /;
  startup clone force nomount;
  backup as compressed backupset
    database format '$BKDIR/%d_%T_%U'
    plus archivelog format '$BKDIR/%d_%T_%U';
  duplicate target database to dev01;
  exit
END
exit

Looking for Someone

There are several people I think are geniuses and Seth Godin is one of them.

Seth Godin writes in his post: Everyone is clueless

You don’t want everyone. You want the right someone.

Someone who cares about what you do. Someone who will make a contribution that matters. Someone who will spread the word.

As soon as you start focusing on finding the right someone, things get better, fast. That’s because you can ignore everyone and settle in and focus on the people you actually want.

the three sexy skills of data geeks

I saw this post and thought it was interesting – http://dataspora.com/blog/sexy-data-geeks/

Activate Physical Standby for Read/Write

Have not posted in a long time because I have been working with Data Guard and encountering various bugs.

Lately, I was asked to activate standy database for read write to test DR.

In 10gR2, this was possible using flash back.

Don’t forget to execute for primary and activated standby.

ALTER SYSTEM SET log_archive_dest_2=” SCOPE=BOTH;

Otherwise, you will get numerous errors.

ORA-16009: remote archive log destination must be a STANDBY database

Be sure to check Bug 4676659 – ORA-16009 in alert log with standby and LGWR ASYNC.

It was a success following the instructions from Alejandro Vargas’ Blog

http://blogs.oracle.com/AlejandroVargas/2007/12/28/#a302

OPatch Utility Guide – 10.2

I have just learned about OPatch Utility Guide – 10.2 (Note:554417.1).

From the note:

NApply
It is used to apply a set of patches under a directory

opatch napply patch_location -id 1,2,3 -skip_subset -skip_duplicate

This will apply patches 1, 2, and 3 which are under the patch_location directory. OPatch will skip duplicate patches and subset patches (patches under patch_location that are subsets of patches installed in the ORACLE_HOME)
 
You must be using
OPatch version : 10.2.0.4.2

ADDM report from SQL*Plus

I have generated ADDM report from SQL*Plus using: @?/rdbms/admin/addmrpt.sql

Here is a recommendation from the report.

RECOMMENDATION 2: SQL Tuning, 4.8% benefit (54 seconds)
  ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "bunssq950snhf".
  RELEVANT OBJECT: SQL statement with SQL_ID bunssq950snhf and
  PLAN_HASH 2694099131
         insert into wrh$_sga_target_advice   (snap_id, dbid, instance_number,
         SGA_SIZE, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_READS)  select
         :snap_id, :dbid, :instance_number,    SGA_SIZE, SGA_SIZE_FACTOR,
         ESTD_DB_TIME, ESTD_PHYSICAL_READS  from    v$sga_target_advice
  RATIONALE: SQL statement with SQL_ID "bunssq950snhf" was executed 35
         times and had an average elapsed time of 1.5 seconds.

How do I get the plan for the SQL as seen in Enterprise Manager?
This can be done by passing the SQL_ID to dbms_xplan.display_cursor

 

Awareness Test

http://www.youtube.com/watch?v=Ahg6qcgoay4

 Did you get the right answer?

Block Corruption and blockrecover

Please do not try this unless you can afford to lose the database.

Create table in USERS tablespace.

Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production      

With the Partitioning, OLAP and Data Mining options  

SQL> create table t
  2  tablespace users as
  3  select * from dba_objects;      

Table created.

Determine block to corrupt.

SQL> select header_block
  2  from dba_segments
  3  where segment_name='T';  

HEADER_BLOCK
------------
         139

Backup database.

JAZZ:/home/oracle/scripts$ rman target /      

Recovery Manager: Release 10.2.0.1.0 - Production on Sun Mar 2 13:07:30 2008
Copyright (c) 1982, 2005, Oracle.  All rights reserved.  

connected to target database: JAZZ (DBID=1044608836)      

RMAN> backup database;      

Starting backup at 02-MAR-2008 13:07:35
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=33 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/ora_data01/JAZZ/system01.dbf
input datafile fno=00002 name=/ora_data02/JAZZ/undotbs01.dbf
input datafile fno=00003 name=/ora_data01/JAZZ/sysaux01.dbf
input datafile fno=00004 name=/ora_data04/JAZZ/users01.dbf
channel ORA_DISK_1: starting piece 1 at 02-MAR-2008 13:07:37
channel ORA_DISK_1: finished piece 1 at 02-MAR-2008 13:08:02
piece handle=/oracle/10gr2/db_1/dbs/01ja8nop_1_1 tag=TAG20080302T130736 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset   channel ORA_DISK_1: starting piece 1 at 02-MAR-2008 13:08:04
channel ORA_DISK_1: finished piece 1 at 02-MAR-2008 13:08:05
piece handle=/oracle/10gr2/db_1/dbs/02ja8npi_1_1 tag=TAG20080302T130736 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 02-MAR-2008 13:08:05  

Corrupt the block.

JAZZ:/home/oracle/scripts$ dd if=/dev/zero of=/ora_data04/JAZZ/users01.dbf bs=8192 conv=notrunc seek=140 count=1
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 9.7241e-05 seconds, 84.2 MB/s

RMAN tablespace backup reveals block corruption.

JAZZ:/home/oracle/scripts$ rman target /      

Recovery Manager: Release 10.2.0.1.0 - Production on Sun Mar 2 13:08:54 2008
Copyright (c) 1982, 2005, Oracle.  All rights reserved.  

connected to target database: JAZZ (DBID=1044608836)      

RMAN> backup tablespace users;      

Starting backup at 02-MAR-2008 13:09:03
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=34 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=/ora_data04/JAZZ/users01.dbf
channel ORA_DISK_1: starting piece 1 at 02-MAR-2008 13:09:05
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 03/02/2008 13:09:06
ORA-19566: exceeded limit of 0 corrupt blocks for file /ora_data04/JAZZ/users01.dbf

Corruption found in alert log but not v$backup_corruption.

JAZZ:/home/oracle/scripts$ tail -10 /ora_admin/JAZZ/bdump/alert_JAZZ.log  

Reread of blocknum=140, file=/ora_data04/JAZZ/users01.dbf. found same corrupt data
Reread of blocknum=140, file=/ora_data04/JAZZ/users01.dbf. found same corrupt data
Reread of blocknum=140, file=/ora_data04/JAZZ/users01.dbf. found same corrupt data
Reread of blocknum=140, file=/ora_data04/JAZZ/users01.dbf. found same corrupt data
Reread of blocknum=140, file=/ora_data04/JAZZ/users01.dbf. found same corrupt data
Sun Mar  2 13:09:46 2008
Shutting down archive processes
Sun Mar  2 13:09:51 2008
ARCH shutting down
ARC2: Archival stopped      

SQL> set serverout on
SQL> exec print_table('select * from v$backup_corruption');
PL/SQL procedure successfully completed.

But what if RMAN is not used for backup?

It is still possible to check for block corruption by using

backup validate check logical tablespace users;

SQL> set serverout on
SQL> exec print_table('select * from v$backup_corruption');
.RECID                        : 1
.STAMP                        : 648306720
.SET_STAMP                    : 648306720
.SET_COUNT                    : 4
.PIECE#                       : 1
.FILE#                        : 4
.BLOCK#                       : 140
.BLOCKS                       : 1
.CORRUPTION_CHANGE#           : 0
.MARKED_CORRUPT               : YES
.CORRUPTION_TYPE              : ALL ZERO
-----------------     

PL/SQL procedure successfully completed.

Perform blockrecover and backup tablespace.

RMAN> blockrecover datafile 4 block 140;    

Starting blockrecover at 02-MAR-2008 14:26:03
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=35 devtype=DISK    

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00004
channel ORA_DISK_1: reading from backup piece /oracle/10gr2/db_1/dbs/05ja8o45_1_1
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/oracle/10gr2/db_1/dbs/05ja8o45_1_1 tag=TAG20080302T131341
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01    

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

Finished blockrecover at 02-MAR-2008 14:26:08    

RMAN> backup tablespace users;    

Starting backup at 02-MAR-2008 14:26:15
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=/ora_data04/JAZZ/users01.dbf
channel ORA_DISK_1: starting piece 1 at 02-MAR-2008 14:26:15
channel ORA_DISK_1: finished piece 1 at 02-MAR-2008 14:26:16
piece handle=/oracle/10gr2/db_1/dbs/0aja8sc7_1_1 tag=TAG20080302T142615 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 02-MAR-2008 14:26:16

DEFAULT_PERMANENT_TABLESPACE

Prior to 10g, a created users would have SYSTEM tablespace as default. 

In 10g, the database can have a specified default tablespace for created users.

The information is stored in DATABASE_PROPERTIES view.

SQL> select PROPERTY_VALUE
  2  from DATABASE_PROPERTIES
  3  where PROPERTY_NAME='DEFAULT_PERMANENT_TABLESPACE';  

PROPERTY_VALUE
--------------------------------------------------------------------------------
USERS

10gR2 New Features in Backup and Recovery

Something to remember – Temporary Datafiles Are Re-Created on RMAN Recovery

10gR2 New Features in Backup and Recovery