everywhere: RMAN Backup and Recovery (Oracle 10g on AIX)

Install Oracle 10g software for RMAN catalog DB

Environment:
OS Level = AIX 5.3
Oracle DB Version = 10.2.0.3
Oracle Catalog DB SID = CATDB
Oracle RAC DB SID = PROD
Oracle RAC DB SID1 = PROD1
Oracle RAC DB SID2 = PROD2
TDPO node name = dbsrv_oracle

1. Install 10.2.0.1 software DB

login as root

cd /source/Disc1/cdrom/database/rootpre

./rootpre.sh

login as oracle

vi .profile

export TMP=/oracle/tmp

export TMPDIR=/oracle/tmp

export ORACLE_HOME=/oracle/10.2.0/db

export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

export ORACLE_SID=catdb

cd /source/Disc1/cdrom/database

export DISPLAY=catdbsrv:1.0

mkdir /oracle/tmp

./runInstaller

install software only

2. Apply 10.2.0.2 patchset

cd /source/patch/Disk1

./runInstaller

3. Create catalog database

dbca (SID=catdb)

4. Create Listener

netca

5. Create alias for PROD db

netca

Configure Catalog Database

1. Creating tablespace to store backup information

SQL> create tablespace rmancat datafile

'/rman/catdb/rmancat01.dbf' size 4g;

2. Creating the Recovery Catalog Owner

SQL> CREATE USER rman IDENTIFIED BY cat

TEMPORARY TABLESPACE temp

DEFAULT TABLESPACE rmancat

QUOTA UNLIMITED ON rmancat;

SQL> grant CONNECT,RESOURCE,RECOVERY_CATALOG_OWNER to rman;

3. Creating the Recovery Catalog
oracle> rman catalog rman/cat

RMAN> CREATE CATALOG;

4. Verify the user

sqlplus rman/cat

SQL> SELECT TABLE_NAME FROM USER_TABLES;

Configure Production Database

1. Login to db server using oracle account

2. Create tnsnames alias for catdb

netca

3. Link TSM Data Protection for Oracle

ln -s /usr/lib/libobk64.a $ORACLE_HOME/lib/libobk.a

4. Define Data Protection for Oracle options

vi /usr/tivoli/tsm/client/oracle/bin64/tdpo.opt

dsmi_orc_config /usr/tivoli/tsm/client/oracle/bin64/dsm.opt

dsmi_log /usr/tivoli/tsm/client/oracle/bin64

tdpo_node dbsrv_oracle

tdpo_pswdpath /usr/tivoli/tsm/client/oracle/bin64

vi /usr/tivoli/tsm/client/oracle/bin64/dsm.opt
SErvername tdpo

vi /usr/tivoli/tsm/client/ba/bin/dsm.sys

SErvername tdpo

CHANGINGRETRIES 0

NODENAME dbsrv_oracle

PASSWORDACCESS prompt

COMMMethod TCPip

TCPPort 1600

TCPServeraddress xx.xx.xx.xx

cd /usr/tivoli/tsm/client/api/bin64

ln -s /usr/tivoli/tsm/client/ba/bin/dsm.sys dsm.sys

dsmadmc

REG NODE dbsrv_oracle oracleprod maxnummp=2 passexp=0

cd /usr/tivoli/tsm/client/oracle/bin64

tdpoconf password (enter <password> three times)

tdpoconf showenvironment (confirm the configuration)

rm /usr/tivoli/tsm/client/oracle/bin64/tdpoerror.log

Managing Target Database in the Recovery Catalog

1. Registering a Database in the Recovery Catalog

rman TARGET / CATALOG rman/cat@catdb

RMAN> REGISTER DATABASE;

RMAN> REPORT SCHEMA;

2. Configure Default RMAN Setting

RMAN> CONFIGURE DEVICE TYPE sbt PARALLELISM 2;

RMAN> CONFIGURE CHANNEL DEVICE TYPE sbt

PARMS='ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';

RMAN> CONFIGURE CHANNEL 1 DEVICE TYPE sbt

CONNECT 'SYS/<pwd>@PROD1'

PARMS='ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';

RMAN> CONFIGURE CHANNEL 2 DEVICE TYPE sbt

CONNECT 'SYS/<pwd>@PROD2'

PARMS='ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

RMAN> crosscheck archivelog all;

PROD Database Backup Scripts

1. Level 0 Backup

rman target / catalog rman/cat@catdb

RMAN> backup incremental level 0

filesperset 50

format "PROD_df_level0_%t_%s_%p" database

plus archivelog format "PROD_arch_%t_%s_%h"

delete all input;

RMAN> delete noprompt obosolete;

(The script is create under $ORACLE_HOME/scripts/rman_bck_level0.sh)

2. Level 1 Backup

rman target / catalog rman/cat@catdb

RMAN> backup incremental level 1 cumulative

filesperset 50

format "PROD_df_level1_%t_%s_%p" database

plus archivelog format "PROD_arch_%t_%s_%h”

delete all input;

(The script is create under $ORACLE_HOME/scripts/rman_bck_level1.sh)

3. Archive Log Files Backup

rman target / catalog rman/cat@catdb

RMAN> backup

filesperset 10

format "PROD_arch_%t_%s_%h" archivelog all delete all input;

Catalog Database Backup Scripts(Use DataPump)

1. Create Directory Definition for DataPump

sqlplus “/ as sysdba”

SQL> create directory dpump_dir as ‘/rman/bck_catdb’;

SQL> grant READ, WRITE ON DIRECTORY dpump_dir TO rman;

2. Use DataPump Export to backup the catalog

expdp rman/cat DUMPFILE=dpump_dir:exp_rman.dmp \

logfile=dpump_dir:exp_rman.log

Restore & Recovery Scripts

1. Loss all database files, but control files, and online redo log files

are not loss

rman target / catalog rman/cat@catdb

RMAN> run {

startup mount;

restore database;

recover database;

alter database open;

}

2. Loss all database files, control files, and online redo log files

For this situation, you need to perform incomplete recovery. Please try to

find the latest archived log file information in the alert file, and check the

timing information

rman target / catalog rman/cat@catdb

RMAN> run {

startup nomount;

ALLOCATE CHANNEL T1 DEVICE TYPE sbt

PARMS='ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';

ALLOCATE CHANNEL T2 DEVICE TYPE sbt

PARMS='ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';

restore controlfile from autobackup;

alter database mount;

set until time = “to_date(‘<TIME_INFO>’,’YYYY/MM/DD HH24:MI:SS’)”;

restore database;

recover database;

alter database open resetlogs;

}

If the above recovery failed due to no latest archivelog file to be applied, then perform the following statements to open the database.

RMAN> shutdown immediate;

RMAN> startup mount;

RMAN> run { alter database open resetlogs; }

Remove all 2PC pending transactions

Generate the scripts first if there are many 2PC pending transactions, then edit the scripts

to add ‘commit;’ statement for every DBMS_TRANSACTION statement

sqlplus “/ as sysdba”

set linesize 120

set pages 24

set head off

spool remove_2pc_pending.sql

select 'exec DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY ('''||

LOCAL_TRAN_ID||''');' from DBA_2PC_PENDING;

spool off

Remove all expired archivelogs

$ cd /prodarch

remove restored archivelogs

RMAN> crosscheck archivelog all;

RMAN> delete noprompt expired archivelog all;

3. All the machines crash, need to find a new machine to startup the database

Install Oracle 10g Software on the new machine

Setup TSM and Data Protection for Oracle

Please refer the “Configure Production Database” Section

Create initial parameter initPROD1.ora with the following setting

background_dump_dest =/oracle/10.2.0/db/admin/PROD1/bdump

core_dump_dest =/oracle/10.2.0/db/admin/PROD1/cdump

user_dump_dest =/oracle/10.2.0/db/admin/PROD1/udump

db_name ="PROD"

instance_name =PROD1

control_files =('/prodora/control01.ctl','/proddata1/control02.ctl','/prodindx1/control03.ctl')

#Set the below to the same as the production target

undo_management =AUTO

undo_tablespace =APPS_UNDOTBS1

db_block_size = 8192

log_archive_format=%t_%s_%r.arc

log_archive_dest_1=’location=/prodarch/’

log_archive_format=%t_%s_%r.arc

sga_target=8G

db_cache_size=4G

shared_pool_size=2G

pga_aggregate_target=16G

compatible = 10.2.0.

# To clone RAC db to non-RAC db need to add this parameter

_no_recovery_through_resetlogs=TRUE

Perform the Recovery

rman target / catalog rman/cat@catdb

RMAN> run {

startup nomount;

ALLOCATE CHANNEL T1 DEVICE TYPE sbt

PARMS='ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';

ALLOCATE CHANNEL T2 DEVICE TYPE sbt

PARMS='ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';

restore controlfile from autobackup;

alter database mount;

set until time = “to_date(‘<TIME_INFO>’,’YYYY/MM/DD HH24:MI:SS’)”;

restore database;

recover database;

alter database open resetlogs;

}

If the above recovery failed due to no latest archivelog file to be applied, then perform the

following statements to open the database.

RMAN> shutdown immediate;

RMAN> startup mount;

RMAN> run { alter database open resetlogs; }

Remove all 2PC pending transactions

Generate the scripts first if there are many 2PC pending transactions, then edit the scripts

to add ‘commit;’ statement for every DBMS_TRANSACTION statement

sqlplus “/ as sysdba”

set linesize 120

set pages 24

set head off

spool remove_2pc_pending.sql

select 'exec DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY ('''||

LOCAL_TRAN_ID||''');' from DBA_2PC_PENDING;

spool off

Remove all expired archivelogs

$ cd /prodarch

remove restored archivelogs

RMAN> crosscheck archivelog all;

RMAN> delete noprompt expired archivelog all;

Duplicate Database

1. create an alias entry in tnsnames.ora file for PROD and catdb(optional)

2. confirm the connection to PROD and catdb(optional) working

PROD =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = tcp)(HOST = db1srv_vip.domain)(PORT = 1521))

(CONNECT_DATA =

(SERVICE_NAME = PROD)

(INSTANCE_NAME = PROD1)

)

CATDB =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = catdbsrv.domain)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = catdb)

)

sqlplus system/<pwd>@prod

sqlplus rman/cat@catdb

3. determine the files location and space to be restored

this information is used to set ‘db_file_name_convert’ parameter in

init<SID>.ora file

4. create the init<SID>.ora parameter file and administration directories

for the duplicate database, the parameter file should have the following

setting:

background_dump_dest =/newprod/10.2.0/db/admin/NEWPROD_otsrv/bdump

core_dump_dest =/newprod/10.2.0/db/adminNEWPROD_otsrv/cdump

user_dump_dest =/newprod/10.2.0/db/admin/NEWPROD_otsrv/udump

db_name ="NEWPROD"

instance_name =NEWPROD

control_files =('/newprod/data/control01.ctl','/newprod/data /control02.ctl','/newprod/data /control03.ctl')

db_file_name_convert =("/proddata1/","/newprod/data/", "/prodindx1/","/newprod/data/", "/prodindx1/","/

newprod/data/", "/prodora/","/newprod/data/")

log_file_name_convert =("/prodora/PROD/", "/newprod/data/",”/proddata1/”,”/newprod/data/”)

#Set the below to the same as the production target

undo_management =AUTO

undo_tablespace =APPS_UNDOTBS1

db_block_size = 8192

log_archive_format=%t_%s_%r.arc

log_archive_dest_1=’location=/arch/’

compatible = 10.2.0.

# To clone RAC db to non-RAC db need to add this parameter

_no_recovery_through_resetlogs=TRUE

5. Startup the duplicate database instance

Sqlplus “/ as sysdba”

SQL> startup nomount;

6. Prepare RMAN duplicate script

run {

allocate auxiliary channel t1 type sbt

parms="ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)";

allocate auxiliary channel t2 type sbt

parms="ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)";

set until time "to_date('<the_time_within_RMAN_backup>','YYYY/MM/DD

HH24:MI:SS')";

duplicate target database to NEWPROD;

}

7. run RMAN to duplicate the database

rman target sys/<pwd>@prod nocatalog auxiliary /

RMAN> @duplicate.script

everywhere

標籤

2014年12月8日星期一

RMAN Backup and Recovery (Oracle 10g on AIX)

Install Oracle 10g software for RMAN catalog DB

Configure Catalog Database

Configure Production Database

Managing Target Database in the Recovery Catalog

PROD Database Backup Scripts

Catalog Database Backup Scripts(Use DataPump)

Restore & Recovery Scripts

Duplicate Database

沒有留言:

張貼留言

標籤

2014年12月8日 星期一

RMAN Backup and Recovery (Oracle 10g on AIX)

Install Oracle 10g software for RMAN catalog DB

Configure Catalog Database

Configure Production Database

Managing Target Database in the Recovery Catalog

PROD Database Backup Scripts

Catalog Database Backup Scripts(Use DataPump)

Restore & Recovery Scripts

Duplicate Database

沒有留言:

張貼留言

2014年12月8日星期一