Oracle DBA Tips and Techniques

By Arjun Raja

Archive for the ‘Backup and Recovery’ Category

Web login mandatory patch for 10.3.2

Posted by Arjun Raja on July 22, 2011

Once Web Logic 10.3.2 is installed, a mandatory patch is also required to be installed.

The metalink note point is 1072763.1

The patch mentioned in this note is WDJ7.

However since I did not have an internet connection from my AIX server, I decided to use the offline option instead of the smart update GUI option..

I downloaded the patch in the note – wdj7.zip but this file is corrupt – it does not unzip….so don’t waste time downloading this file.

The trick is to download the following patch from Metalink – it is the same as WDJ7.

The patch is 9438213 – search under ” Patches and Updates” tab in Metalink.

Download the patch- p9438213_10320_Generic.zip and copy it to your AIX server under the WLS_HOME/utils/bsu/cache_dir

The directory cache_dir will not exist in a fresh installation so create it.

cd /u01/oracle/Middleware/utils/bsu/cache_dir

unzip p9438213_10320_Generic.zip

Creates the foll files –

XLXA.jar
README.txt
NIXN.jar
4D53.jar
patch-catalog_15563.xml

– A glance of the README.txt fill confirm this patch is indeed the same as WDJ7 –

This patch contains Smart Update patch WDJ7 for WebLogic Server 10.3.2.0

Description:
============
Mandatory Patch required on WLS 10.3.2 for Oracle Grid Control (GC) 11.1 install

Now start the process of applying the patch –

Make sure the file bsu.sh is edited to avoid a java error –

vi /u01/oracle/Middleware/utils/bsu/bsu.sh

This is the original file –

#!/bin/sh

JAVA_HOME=”/usr/java6″

“$JAVA_HOME/bin/java” -jar patch-client.jar $*

But if I use this file – I get this error –

Exception in thread “main” java.lang.NoClassDefFoundError:
com.bea.plateng.patch.Patch
Caused by: java.lang.ClassNotFoundException: com.bea.plateng.patch.Patch at java.net.URLClassLoader.findClass(URLClassLoader.java:423) at java.lang.ClassLoader.loadClass(ClassLoader.java:653)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:346) at java.lang.ClassLoader.loadClass(ClassLoader.java:619)
Could not find the main class: com.bea.plateng.patch.Patch. Program will exit.

So I edited the file and forced it to use the patch-client.tar file under the bsu directory.

#!/bin/sh

JAVA_HOME=”/usr/java6″

“$JAVA_HOME/bin/java”
-jar /u01/oracle/Middleware/utils/bsu/patch-client.jar $*

Then there is no java error …..

Now apply patch…

cd /u01/oracle/Middleware/utils/bsu

./bsu.sh -prod_dir=/u01/oracle/Middleware/wlserver_10.3 -patch_download_dir=/u01/oracle/Middleware/utils/bsu/cache_dir -patchlist=4D53,NIXN, ,XLXA -verbose -install -log=/tmp/error.log

If fails with errors –

Encountered unrecognized patch ID: 4D53

Encountered unrecognized patch ID: NIXN

Encountered unrecognized patch ID: XLXA

To fix this –

Rename the file patch-catalog_15563.xml as patch-catalog.xml in the cache_dir .

It worked after that.

emrep:/u01/oracle/Middleware/utils/bsu/cache_dir> ls -lrt

-rw-r–r– 1 oracle dba 6585309 Jun 10 18:31 patch-catalog.xml
-rw-r–r– 1 oracle dba 4904 Jun 10 18:31 XLXA.jar
-rw-r–r– 1 oracle dba 449 Jun 10 18:31 README.txt
-rw-r–r– 1 oracle dba 3652 Jun 10 18:31 NIXN.jar
-rw-r–r– 1 oracle dba 8683 Jun 10 18:31 4D53.jar

emrep:/u01/oracle/Middleware/utils/bsu/cache_dir>
cd /u01/oracle/Middleware/utils/bsu

emrep:/u01/oracle/Middleware/utils/bsu> ./bsu.sh -prod_dir=/u01/oracle/Middleware/wlserver_10.3
-patch_download_dir=/u01/oracle/Middleware/utils/bsu/cache_dir -patchlist=4D53,NIXN, XLXA -verbose -install -log=/tmp/error.log

Checking for conflicts..
No conflict(s) detected

Starting installation of Patch ID: 4D53
Installing /u01/oracle/Middleware/utils/bsu/cache_dir/4D53.jar Extracting /u01/oracle/Middleware/patch_wls1032/patch_jars/Bug9100465_1032.jar

Updating /u01/oracle/Middleware/patch_wls1032/profiles/default/sys_manifest_classpath/weblogic_patch.jar

Old manifest value: Class-Path=
New manifest value: Class-Path=../../../patch_jars/Bug9100465_1032.jar Result: Success

Starting installation of Patch ID: NIXN
Installing /u01/oracle/Middleware/utils/bsu/cache_dir/NIXN.jar Extracting /u01/oracle/Middleware/patch_wls1032/patch_jars/bug8990616_1032.jar

Updating /u01/oracle/Middleware/patch_wls1032/profiles/default/sys_manifest_classpath/weblogic_patch.jar

Old manifest value: Class-Path=../../../patch_jars/Bug9100465_1032.jar New manifest value:
Class-Path=../../../patch_jars/bug8990616_1032.jar ../../../patch_jars/Bug9100465_1032.jar

Result: Success

Starting installation of Patch ID: XLXA
Installing /u01/oracle/Middleware/utils/bsu/cache_dir/XLXA.jar Extracting /u01/oracle/Middleware/patch_wls1032/patch_jars/Bug9221722_1032.jar

Updating /u01/oracle/Middleware/patch_wls1032/profiles/default/sys_manifest_classpath/weblogic_patch.jar

Old manifest value:
Class-Path=../../../patch_jars/bug8990616_1032.jar ../../../patch_jars/Bug9100465_1032.jar

New manifest value:
Class-Path=../../../patch_jars/Bug9221722_1032.jar ../../../patch_jars/bug8990616_1032.jar ../../../patch_jars/Bug9100465_1032.jar

Result: Success

emrep:/u01/oracle/Middleware/utils/bsu>

Posted in Backup and Recovery, Uncategorized | Tagged: , , | 2 Comments »

TSM – DSMC BACKUP AND RESTORE COMMANDS

Posted by Arjun Raja on March 14, 2011

To query the management aliases used by Storage team –

dsmc q mgmt

One of the parameters in place is

MgmtClass Name : RET7YRS
Description : Retain Archive for 7 years

Retain backups for 7 years.

dsmc archive -archmc=RET7YRS -description=”test_backup”
“/ora_export_2/backup/u01/*.*” -su=yes >> test_copy.logs

Once backup is completed….

dsmc query archive -description=”testfin_backup”
“/ora_export_2/backup/u01/*.*” -su=yes >> testfin_archive.logs

view testfin_archive.logs

Node Name: XXXXXXX_X
Session established with server TSMPROD3: Windows
Server Version 5, Release 5, Level 5.0
Server date/time: 03/14/11 13:54:03 Last access: 03/14/11 13:52:25

Size Archive Date – Time File – Expires on – Description
—- ——————- ——————————-
0 B 03/14/11 13:32:33 /ora_export_2/backup/u01/test.dbf 03/12/18 testfin_backup

To restore files –

dsmc retrieve archive -description=”testfin_backup” “/ora_export_2/backup/u01/*.*” -su=yes >> testfin_archive.logs

Posted in Backup and Recovery | Tagged: , , | Leave a Comment »

DSMC – RETAIN FOR 2 YEARS AND QUERY MANAGEMENT CLASS

Posted by Arjun Raja on December 1, 2009

To query the MANAGMENT CLASS set up for your machine by the TSM administrator –

dsmc q mgmt

Domain Name : XXXUNIX
Activated Policy Set Name : STANDARD
Activation date/time : 05/11/09 13:16:54
Default Mgmt Class Name : STANDARD
Grace Period Backup Retn. : 30 day(s)
Grace Period Archive Retn.: 730 day(s)

MgmtClass Name : XXXXXXX_DB
Description : Dont include in daily incremental

MgmtClass Name : RETAIN2YEARS
Description : Retention period of Two Years

ON XXXXXX –

cd $COMMON_TOP/admin/out/XXXXXX

dsmc archive -archmc=RETAIN2YEARS -description=”ebus_logs”
“/u01/fXXXXX/finXXXXX/admin/out/XXXXXX/*.*” -su=yes > EBUS_COPY.logs

cd $COMMON_TOP/admin/log/XXXXXX

dsmc archive -archmc=RETAIN2YEARS -description=”ebus_admin_logs” “/u01/XXXX/XXXXXcomn/admin/log/XXXXXXX/*.*” -su=yes > EBUS_ADMIN_COPY.logs

On XXXXXXX – 2 YEARS RETENTION.

cd /u01/XX/XX/admin/log/XXXXX

nohup dsmc archive -archmc=ARCY2 -description=”ebus_web_admin_logs” “/u01/fXX/fXXn/admin/log/XXX/*.*” -su=yes > EBUS_WEB_ADMIN_COPY.logs &

To query archived files –

dsmc query archive -description=”ebus_web_admin_logs” “/u01/fXX/fXXn/admin/log/XXX/*.*” -su=yes > archived_files.logs

Posted in Backup and Recovery | Leave a Comment »

11g RELEASE 2 – RMAN DUPLICATION OF DATABASE

Posted by Arjun Raja on December 1, 2009

11g release 2 has a very good feature for RMAN duplication which will allow you to duplicate a database even if the target database is not available.

In case a disk backup is available, there is no need to even connect to a catalog, otherwise a conenction to the catalog is necessary to get info from tape.

11g release 1 –

We will try the duplicate command without connecting to the target database – this will fail as can be seen below.

connect catalog rman10d/d10rman@rcatd
connect auxiliary /

run {
allocate auxiliary channel ch1 device type sbt_tape;

SET UNTIL TIME “TO_DATE (’23-11-09 10:57:00′, ‘DD-MM-YY HH24:MI:SS’)”; duplicate target database to test
nofilenamecheck;
}
connected to recovery catalog database

connected to auxiliary database: TEST (not mounted)

RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of set command at 11/30/2009 13:32:01
RMAN-06171: not connected to target database

Recovery Manager complete.
~
~
11g RELEASE 2 –

export ORACLE_SID=TESTDB

TAKE FULL BACKUP TO DISK –

RMAN> run {
configure channel device type disk format
‘/u02/oradata/backup_TESTDB/TESTDB_%U’;
backup database plus archivelog;
}

piece handle=/u02/oradata/backup_TESTDB/TESTDB_08kvld2f_1_1
tag=TAG20091130T134655 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 30-NOV-09

RMAN>

NOW CREATE DUPLICATE DATABASE – WITHOUT CONNECTING TO TARGET OR CATALOG –

The backup must be accessible on disk in this case.

Ascertain entry for TESTDB1 – duplicate database exisit in tnsnames.ora and listener.ora

Now create an init.ora file for the new duplicate database –

cd $ORACLE_HOME/dbs

export ORACLE_SID=TESTDB1

copy the init.ora of the target database as initTESTDB1.ora – and make changes to reflect TESTDB1

Add foll in new init.ora –

*.db_file_name_convert=’/u02/oradata/TESTDB’,’/u02/oradata/TESTDB1′ *.log_file_name_convert=’/u02/oradata/TESTDB’,’/u02/oradata/TESTDB1′

create spfile from pfile;

startup nomount duplicate database – TESTDB1.

export ORACLE_SID=TESTDB1

sql > startup nomount;

exit;

Now begin duplication by only connecting to auxiliary – no connection to target or even catalog –

export ORACLE_SID=TESTDB1

[oracle@redhat346 ~]$ rman auxiliary /

Recovery Manager: Release 11.2.0.1.0 – Production on Mon Nov 30 14:06:32 2009

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to auxiliary database: TESTDB1 (not mounted)

RMAN> RUN {
DUPLICATE DATABASE TO TESTDB1
BACKUP LOCATION ‘/u02/oradata/backup_TESTDB’; – specify backup location on disk.
}
2> 3> 4>
Starting Duplicate Db at 30-NOV-09

contents of Memory Script:
{
sql clone “alter system set db_name =
”TESTDB” comment=
”Modified by RMAN duplicate” scope=spfile”;
sql clone “alter system set db_unique_name =
”TESTDB1” comment=
”Modified by RMAN duplicate” scope=spfile”;
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile from
‘/u02/oradata/backup_TESTDB/TESTDB_07kvld2d_1_1’;
alter clone database mount;
}
executing Memory Script

sql statement: alter system set db_name = ”TESTDB” comment= ”Modified by RMAN duplicate” scope=spfile

sql statement: alter system set db_unique_name = ”TESTDB1” comment= ”Modified by RMAN duplicate” scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 626327552 bytes

Fixed Size 2215944 bytes
Variable Size 390074360 bytes
Database Buffers 226492416 bytes
Redo Buffers 7544832 bytes

Starting restore at 30-NOV-09
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=63 device type=DISK

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/u02/oradata/TESTDB1/control01.ctl
output file name=/u02/oradata/flash_recovery_area/TESTDB1/control02.ctl Finished restore at 30-NOV-09

database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=63 device type=DISK

contents of Memory Script:
{
set until scn 1000239;
set newname for datafile 1 to
“/u02/oradata/TESTDB1/system01.dbf”;
set newname for datafile 2 to
“/u02/oradata/TESTDB1/sysaux01.dbf”;
set newname for datafile 3 to
“/u02/oradata/TESTDB1/undotbs01.dbf”;
set newname for datafile 4 to
“/u02/oradata/TESTDB1/users01.dbf”;
restore
clone database
;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 30-NOV-09
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00001 to
/u02/oradata/TESTDB1/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to
/u02/oradata/TESTDB1/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to
/u02/oradata/TESTDB1/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to
/u02/oradata/TESTDB1/users01.dbf
channel ORA_AUX_DISK_1: reading from backup piece
/u02/oradata/backup_TESTDB/TESTDB_06kvld1u_1_1
channel ORA_AUX_DISK_1: piece
handle=/u02/oradata/backup_TESTDB/TESTDB_06kvld1u_1_1
tag=TAG20091130T134638
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25 Finished restore at 30-NOV-09

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=704297238 file
name=/u02/oradata/TESTDB1/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=704297238 file
name=/u02/oradata/TESTDB1/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=704297238 file
name=/u02/oradata/TESTDB1/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=704297238 file
name=/u02/oradata/TESTDB1/users01.dbf

contents of Memory Script:
{
set until scn 1000239;
recover
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 30-NOV-09
using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=8
channel ORA_AUX_DISK_1: reading from backup piece
/u02/oradata/backup_TESTDB/TESTDB_08kvld2f_1_1
channel ORA_AUX_DISK_1: piece
handle=/u02/oradata/backup_TESTDB/TESTDB_08kvld2f_1_1
tag=TAG20091130T134655
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 archived log file
name=/u02/oradata/flash_recovery_area/TESTDB1/archivelog/2009_11_30/o1_mf_1_8_5k6r8qnm_.arc thread=1 sequence=8
channel clone_default: deleting archived log(s)
archived log file
name=/u02/oradata/flash_recovery_area/TESTDB1/archivelog/2009_11_30/o1_mf_1_8_5k6r8qnm_.arc RECID=1 STAMP=704297239
media recovery complete, elapsed time: 00:00:01
Finished recover at 30-NOV-09

contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
sql clone “alter system set db_name =
”TESTDB1” comment=
”Reset to original value by RMAN” scope=spfile”;
sql clone “alter system reset db_unique_name scope=spfile”; shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 626327552 bytes

Fixed Size 2215944 bytes
Variable Size 390074360 bytes
Database Buffers 226492416 bytes
Redo Buffers 7544832 bytes

sql statement: alter system set db_name = ”TESTDB1” comment= ”Reset to original value by RMAN” scope=spfile

sql statement: alter system reset db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 626327552 bytes

Fixed Size 2215944 bytes
Variable Size 390074360 bytes
Database Buffers 226492416 bytes
Redo Buffers 7544832 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE “TESTDB1” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( ‘/u02/oradata/TESTDB1/redo01.log’ ) SIZE 50 M REUSE, GROUP 2 ( ‘/u02/oradata/TESTDB1/redo02.log’ ) SIZE 50 M REUSE, GROUP 3 ( ‘/u02/oradata/TESTDB1/redo03.log’ ) SIZE 50 M REUSE DATAFILE
‘/u02/oradata/TESTDB1/system01.dbf’
CHARACTER SET AL32UTF8

contents of Memory Script:
{
set newname for tempfile 1 to
“/u02/oradata/TESTDB1/temp01.dbf”;
switch clone tempfile all;
catalog clone datafilecopy “/u02/oradata/TESTDB1/sysaux01.dbf”, “/u02/oradata/TESTDB1/undotbs01.dbf”,
“/u02/oradata/TESTDB1/users01.dbf”;
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u02/oradata/TESTDB1/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/u02/oradata/TESTDB1/sysaux01.dbf RECID=1 STAMP=704297257
cataloged datafile copy
datafile copy file name=/u02/oradata/TESTDB1/undotbs01.dbf RECID=2 STAMP=704297257
cataloged datafile copy
datafile copy file name=/u02/oradata/TESTDB1/users01.dbf RECID=3 STAMP=704297257

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=704297257 file
name=/u02/oradata/TESTDB1/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=704297257 file
name=/u02/oradata/TESTDB1/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=704297257 file
name=/u02/oradata/TESTDB1/users01.dbf

contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 30-NOV-09

RMAN> exit

Recovery Manager complete.

Posted in Backup and Recovery | Tagged: , , | Leave a Comment »

Script – Check RMAN Backup Status

Posted by Arjun Raja on July 28, 2009

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;

Posted in Administration, Backup and Recovery, Scripts | Tagged: , , | Leave a Comment »

Change DBID of database

Posted by Gavin Soorma on July 14, 2009

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>

Posted in Backup and Recovery | Tagged: , | Leave a Comment »

ORA-19755 error using block change tracking file.

Posted by Arjun Raja on July 13, 2009

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.

Posted in Administration, Backup and Recovery | Tagged: , , | Leave a Comment »

RMAN – Create Catalog

Posted by Arjun Raja on July 13, 2009

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 .

Posted in Backup and Recovery | Tagged: , | Leave a Comment »

RMAN unregister database from catalog

Posted by Gavin Soorma on July 7, 2009

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);

Posted in Backup and Recovery | Tagged: , , | Leave a Comment »

10g RMAN restore on another machine

Posted by Gavin Soorma on July 7, 2009

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.

Posted in Backup and Recovery | Tagged: , , | Leave a Comment »