Zounr's Oracle DBA, Developer Suit Blog: Backup and Recovery

Complete Guide to Backup & Recovery in Oracle 11g Database

Backup and Recovery

Ø Physical backup and recovery

o Cold backup and recovery

o Hot backup and recovery

Ø Logical backup and recovery

o Export

o Import

Ø RMAN backup and recovery

o Offline backup and recovery

o Online backup and recovery

Physical backup and recovery

Cold backup and recovery

Ø Offline backup

Ø NoArchive log backup

Ø Consistent backup

o Datafiles

o Control files

o Redo log files

o Password file

o pfile

SQL > select name from v$datafile;

SQL > select name from v$controlfile;

SQL > select member from v$logfile;

SQL > create pfile=’/backup/cold_prod/initprod.ora’ from spfile;

SQL > shutdown immediate;

$ cp /opt/oradata/prod/*.dbf /backup/cold_prod/

$ cp /opt/oradata/prod/*.ctl /backup/cold_prod/

$ cp /opt/oradata/prod/*.rdo /backup/cold_prod/

$ cp $ORACLE_HOME/dbs/orapwprod /backup/cold_prod/

SQL > startup

Reovery datafile/Contol file:

SQL > shutdown abort;

$ cp /backup/cold_prod/*.dbf /opt/oradata/prod/

$ cp /backup/cold_prod/*.ctl /opt/oradata/prod/

$ cp /backup/cold_prod/*.rdo /opt/oradata/prod/

SQL > startup

Hot backup and recovery:

Ø Online backups

Ø Archive log backups

Ø Inconsistent backups

o Tablespaces (datafiles)

o Control files

SQL > select tablespace_name from dba_tablespaces;

SQL> select file_name from dba_data_files where tablespace_name=’DATA’;

FILE_NAME

/opt/oradata/prod/data01.dbf

/opt/oradata/prod/data02.dbf

SQL > alter tablespace data begin backup;

SQL > ! cp /opt/oradata/prod/data01.dbf /backup/hot_prod/

SQL > ! cp /opt/oradata/prod/data02.dbf /backup/hot_prod/

SQL > alter tablespace data end backup;

SQL > alter tablespace data begin backup;

SQL > alter tablespace data end backup;

SQL > alter tablespace data begin backup;

SQL > select * from v$backup;

FILE # STATUS

1 NOT ACTIVE

2 NOT ACTIVE

3 ACTIVE

4 ACTIVE

5 CAN NOT READ HEADER

6 FILE NOT FOUND

SQL > alter tablespace data end backup;

SQL > select * from v$backup;

FILE # STATUS

1 NOT ACTIVE

2 NOT ACTIVE

3 NOT ACTIVE

4 NOT ACTIVE

5 CAN NOT READ HEADER

6 FILE NOT FOUND

SQL > alter tablespace data begin backup;

SQL > ! cp /opt/oradata/prod/data01.dbf /backup/hot_prod/

SQL > ! cp /opt/oradata/prod/data02.dbf /backup/hot_prod/

SQL > shutdown abort;

SQL > startup

Database mounted.

ORA-01157: cannot identify/lock data file 3 - see DBWR trace file

ORA-01110: data file 3: '/opt/oradata/prod/data01.dbf ‘

SQL > select * from v$backup;

FILE # STATUS

1 NOT ACTIVE

2 NOT ACTIVE

3 ACTIVE

4 ACTIVE

SQL > alter database end backup;

SQL > select * from v$backup;

FILE # STATUS

1 NOT ACTIVE

2 NOT ACTIVE

3 NOT ACTIVE

4 NOT ACTIVE

SQL > recover database;

SQL > alter database open;

SQL > alter tablespace data begin backup;

SQL > ! cp /opt/oradata/prod/data01.dbf /backup/hot_prod/

SQL > ! cp /opt/oradata/prod/data02.dbf /backup/hot_prod/

SQL > alter tablespace data end backup;

Whole Database Hot backup:

SQL> ALTER DATABASE BEGIN BACKUP;

SQL> ! cp /opt/oradata/prod/*.dbf /disk2/backup/

SQL> ALTER DATABASE END BACKUP;

How do Corrupt blocks in Datafile:

SQL > create tablespace jenitbs datafile

'/opt/oradata/prod/jenitbs01.dbf' ;

SQL > create user Stanly identified by Stanly

default tablespace jenitbs quota unlimited on jenitbs;

SQL > grant connect , resource to Stanly

SQL > conn Stanly/Stanly

SQL > create table emp as select * from all_objects

SQL > conn / as sysdba

SQL > select segment_name , header_file , header_block from dba_segments where

segment_name = 'emp' and owner = 'Stanly';

SEGMENT_NAME HEADER_FILE HEADER_BLOCK

emp 6 11

SQL> exit

$ cd /opt/oradata/prod

$ dd of=jenitbs01.dbf bs=8192 conv=notrunc seek=12

$ dd of=jenitbs01.dbf bs=8192 conv=notrunc seek=13

$ dd of=jenitbs01.dbf bs=8192 conv=notrunc seek=14

$ sqlplus / as sysdba

SQL> alter system flush buffer_cache;

SQL> conn Stanly/Stanly

SQL> select * from emp;

ORA-01578: ORACLE data block corrupted (file # 6, block # 12)

ORA-01110: data file 6: '/opt/oradata/prod/jenitbs01.dbf'

SQL> exit

Running the DBVERIFY Utility:

$ dbv file=/backup/prod_hot/users01.dbf

DBVERIFY - Verification starting : FILE =/backup/prod_hot/users01.dbf

DBVERIFY - Verification complete

Total Pages Examined : 250

Total Pages Processed (Data) : 1

Total Pages Failing (Data) : 0

Total Pages Processed (Index) : 0

Total Pages Failing (Index) : 0

Total Pages Processed (Other) : 2

Total Pages Processed (Seg) : 0

Total Pages Failing (Seg) : 0

Total Pages Empty : 247

Total Pages Marked Corrupt : 0

Total Pages Influx : 0

Recover Corrupted Datafile:

SQL > alter database datafile 4 offline drop;

SQL > ! cp /backup/hot_prod/data02.dbf /opt/oradata/prod/

SQL > recover datafile 4;

SQL > alter database datafile 4 online;

Recover Corrupted Tablespace:

SQL > alter tablespace data offline;

SQL > cp /backup/hot_prod/data01.dbf /opt/oradata/prod/

SQL > cp /backup/hot_prod/data02.dbf /opt/oradata/prod/

SQL > recover tablespace data;

SQL > alter tablespace data online;

Recover Corrupted Read only tablespace:

SQL > alter tablespace data offline;

SQL > cp /backup/hot_prod/data01.dbf /opt/oradata/prod/

SQL > alter tablespace data online;

Recover Corrupted SYSTEM tablespace:

$ cp /backup/hot_prod/system01.dbf /opt/oradata/prod/

$ sqlplus “/ as sysdba”

SQL > startup mount

SQL > SET AUTORECOVERY ON;

SQL > recover database;

SQL > alter database open;

Recover Whole database corrupted (ALL DATAFILES)

$ cp /backup/hot_prod/*.dbf /opt/oradata/prod/

$ sqlplus “/ as sysdba”

SQL > startup mount;

SQL > RECOVER AUTOMATIC DATABASE;

SQL > alter database open;

Recovery When Archived Logs Are in a Nondefault Location:

LOG_ARCHIVE_DEST_1 = 'LOCATION = /oracle/oradata/prod/arch/'

LOG_ARCHIVE_FORMAT = arcr_%t_%s.arc

SQL > SELECT NAME FROM V$ARCHIVED_LOG;

NAME

/oracle/oradata/prod/arch/arcr_1_467.arc

/oracle/oradata/prod/arch/arcr_1_468.arc

/oracle/oradata/prod/arch/arcr_1_469.arc

$ mv /oracle/oradata/prod/arch/*.arc /tmp/

$vi $ORACLE_HOME/dbs/initprod.ora

LOG_ARCHIVE_DEST_1 = 'LOCATION=/tmp/'

$sqlplus "/ as sysdba"

SQL > startup mount

SQL > RECOVER AUTOMATIC DATABASE;

SQL > alter database open;

Recovery When Archived Logs Are in a Nondefault Location without change log_archive_dest_1:

$ mv /oracle/oradata/prod/arch/*.arc /tmp/

$sqlplus "/ as sysdba"

SQL > startup mount

SQL> SET LOGSOURCE "/tmp"

SQL > RECOVER AUTOMATIC FROM '/tmp' DATABASE

SQL > alter database open;

Re-Creating Data Files When Backups Are Unavailable

SQL > alter tablespace users offline;

SQL > ALTER DATABASE CREATE DATAFILE

'/disk1/oradata/trgt/users01.dbf ' AS '/disk2/users01.dbf ';

SQL > RECOVER DATAFILE '/disk2/users01.dbf '

SQL > alter tablespace users online;

Backup control files

Ø Adding, dropping, or renaming datafiles

Ø Adding or dropping a tablespace, or altering the read-write state of the tablespace

Ø Adding or dropping redo log files or groups

Ø Two types of Control file backups

o Binary backup

o Trace backup

SQL > ALTER DATABASE BACKUP CONTROLFILE TO '/backup/hot_prod/control.bkp';

SQL > ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

SQL > ALTER DATABASE BACKUP CONTROLFILE TO TRACE as ‘/backup/hot_prod/ ‘;

Recover Corrupted control file using Binary backup:

$ vi $ORACLE_HOME/dbs/initprod.ora

Control_files=’/backup/hot_prod/control.bkp’

$ sqlplus “/ as sysdba”

SQL > startup mount

SQL > recover database using backup controlfile;

SQL > alter database open resetlogs;

Recover Corrupted Control file using Trace backup:

$ vi /backup/hot_prod/control_001_123.trc

CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS ARCHIVELOG

MAXLOGFILES 50

MAXLOGMEMBERS 5

MAXDATAFILES 100

MAXINSTANCES 1

MAXLOGHISTORY 453

LOGFILE

GROUP 1 '/opt/oradata/prod/redo01.rdo' SIZE 100M,

GROUP 2 '/opt/oradata/prod/redo02.rdo' SIZE 100M,

GROUP 3 '/opt/oradata/prod/redo03.rdo' SIZE 100M

DATAFILE

'/opt/oradata/prod/system01.dbf',

'/opt/oradata/prod/undotbs01.dbf',

'/opt/oradata/prod/sysaux01.dbf',

'/opt/oradata/prod/data01.dbf',

'/opt/oradata/prod/data02.dbf',

'/opt/oradata/prod/users01.dbf'

CHARACTER SET WE8ISO8859P1

;

$ cat > /backup/hot_prod/create_control.sql

CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS ARCHIVELOG

MAXLOGFILES 50

MAXLOGMEMBERS 5

MAXDATAFILES 100

MAXINSTANCES 1

MAXLOGHISTORY 453

LOGFILE

GROUP 1 '/opt/oradata/prod/redo01.rdo' SIZE 100M,

GROUP 2 '/opt/oradata/prod/redo02.rdo' SIZE 100M,

GROUP 3 '/opt/oradata/prod/redo03.rdo' SIZE 100M

DATAFILE

'/opt/oradata/prod/system01.dbf',

'/opt/oradata/prod/undotbs01.dbf',

'/opt/oradata/prod/sysaux01.dbf',

'/opt/oradata/prod/data01.dbf',

'/opt/oradata/prod/data02.dbf',

'/opt/oradata/prod/users01.dbf'

CHARACTER SET WE8ISO8859P1

;

$ sqlplus “/ as sysdba”

SQL > startup nomount

SQL > @/backup/hot_prod/create_control.sql

SQL > recover database using backup controlfile;

SQL > alter database open resetlogs;

Physical Backup Incomplete Recovery:

Ø Time Based incomplete recovery

Ø Change or SCN based incomplete recovery

Ø Cancel based incomplete recovery

Log Miner:

SQL > show parameter utl_file_dir;

SQL > alter system set utl_file_dir=’/backup/log’ scope=spfile;

SQL > shutdown immediate;

SQL > startup

SQL > exec DBMS_LOGMNR_D.BUILD('jeni.log','/backup/log/',

DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);

SQL > exec DBMS_LOGMNR.ADD_LOGFILE

( LOGFILENAME => '/opt/arch/arch_123.arc', OPTIONS => DBMS_LOGMNR.NEW);

SQL > exec DBMS_LOGMNR.ADD_LOGFILE

( LOGFILENAME => '/opt/arch/arch_124.arc',OPTIONS =>DBMS_LOGMNR.ADDFILE);

SQL > exec dbms_logmnr.start_logmnr ( dictfilename=>'/backup/log/jeni.log');

SQL > select username, to_char(timestamp,'mm/dd/yy hh24:mi:ss'), scn, sql_redo

from v$logmnr_contents where operation='DDL' ;

SQL > exec dbms_logmnr.end_logmnr;

Create Testing Database or Database Cloning using Physical backups:

Linux1:

SQL > select name from v$datafile;

SQL > select member from v$logfile;

SQL > select name from v$archived_log;

SQL > create pfile=’/backup/prod/initprod.ora’ from spfile;

SQL > alter database backup controlfile to trace as ‘/backup/prod/’;

SQL > shutdown immediate;

$ cp /opt/oradata/prod/*.dbf /backup/prod/

$ cp /opt/oradata/prod/*.rdo /backup/prod/

SQL > startup

$ scp –r /backup/prod/* oracle@192.168.1.101:/u01/oradata/test/

$ scp –r /opt/prod/arch/*.arc oracle@192.168.1.101:/u01/test/arch/

Linux2 : (Cloning Server or Auxiliary Server)

$ mkdir /u01/oradata/test

$ mkdir /u01/admin/test/bdump

$ mkdir /u01/admin/test/cdump

$ mkdir /u01/admin/test/udump

$ mkdir /u01/admin/test/adump

$ mkdir /u01/admin/test/create

$ mkdir /u01/admin/test/pfile

$ mv /u01/oradata/test/initprod.ora $ORACLE_HOME/dbs/inittest.ora

$ vi $ORACLE_HOME/dbs/inittest.ora

db_unique_name=test

db_name=test

control_files=’/u01/oradata/test/contorl01.ctl’

background_dump_dest=’/u01/admin/test/bdump’

core_dump_dest=’/u01/admin/test/cdump’

user_dump_dest=’/u01/admin/test/udump’

audit_dump_dest=’/u01/admin/test/adump’

log_archive_dest=’/u01/test/arch’

$ cat > /u01/oradata/prod/create_control.sql

CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS ARCHIVELOG

CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS NOARCHIVELOG

MAXLOGFILES 50

MAXLOGMEMBERS 5

MAXDATAFILES 100

MAXINSTANCES 1

MAXLOGHISTORY 453

LOGFILE

GROUP 1 '/u01/oradata/test/redo01.rdo' SIZE 100M,

GROUP 2 '/u01/oradata/test/redo02.rdo' SIZE 100M,

GROUP 3 '/u01/oradata/test/redo03.rdo' SIZE 100M

DATAFILE

'/u01/oradata/test/system01.dbf',

'/u01/oradata/test/undotbs01.dbf',

'/u01/oradata/test/sysaux01.dbf',

'/u01/oradata/test/data01.dbf',

'/u01/oradata/test/data02.dbf',

'/u01/oradata/test/users01.dbf'

CHARACTER SET WE8ISO8859P1

;

$ export ORACLE_SID=test

$ orapwd file=$ORACLE_HOME/dbs/orapwtest password=sys

$ sqlplus “/ as sysdba”

SQL > startup nomount

SQL > @/u01/oradata/test/create_control.sql

Time based incomplete Recovery:

SQL > recover database until time

to_timestamp(‘2010-11-17 13:24:12’,’YYYY-MM-DD HH24:MI:SS’) using backup controlfile;

Change or SCN based incomplete Recovery:

SQL > recover database until change 12345 using backup controlfile;

Cancel based incomplete Recovery:

SQL > recover database until cancel using backup controlfile;

SQL > alter database open resetlogs;

SQL > create spfile from pfile;

SQL > select instance_name,status from v$instance;

Client and Server Architecture

Clinet Desktop:

Ø Here install oracle 10g Client software only.

$ cd $ORACLE_HOME/network/admin

$ ls -l *.ora

tnsnames.ora

sqlnet.ora

$netca

$ cat >> tnsnames.ora

prod123 =

(DESCRIPTION =

(ADDRESS=(PROTOCOL=TCP) (HOST=192.168.1.20) (PORT=1521))

(CONNECT_DATA=

(SERVICE_NAME=PROD)

)

$ tnsping prod123

Used parameter files:

/opt/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=192.168.1.20) (PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=PROD)))

OK (80 msec)

$ sqlplus “stanly/ora123@prod123”

ORA-12541: TNS: no listener

Database Server:

$ cd $ORACLE_HOME/network/admin

$ ls -l *.ora

listener.ora

tnsnames.ora

sqlnet.ora

$ more listener.ora

LISTENER=

(DESCRIPTION=

(ADDRESS_LIST=

(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.20)(PORT=1521))

(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(SID_NAME=plsextproc)

(ORACLE_HOME=/opt/oracle/product/10.2.0/db_1)

(PROGRAM=extproc)

)

PROD_LIST=

(DESCRIPTION=

(ADDRESS_LIST=

(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.20)(PORT=1521))

(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))

SID_LIST_PROD_LIST=

(SID_LIST=

(SID_DESC=

(ORACLE_HOME=/opt/oracle/product/10.2.0/db_1)

(SID_NAME=prod)

)

$ lsnrctl status PROD_LIST

$ lsnrctl start PROD_LIST

$ lsnrctl stop PROD_LIST

$ lsnrctl reload PROD_LIST

ORA-12571: TNS:packet writer failure

ORA-12535: TNS:operation timed out

TNS-03505: Failed to resolve name

ORA-12154 TNS:could not resolve service name

$ more sqlnet.ora

NAMES.DIRECTORY_PATH= (TNSNAMES)

SQLNET.EXPIRE_TIME = 30

Logical backup:

Ø Export

Ø Import

Export Parameters	Import Parameters
Full=Y/N	Full=Y/N
$ exp sys/system@prod123 full= y file=/opt/export/full.dmp log=/opt/export/full.log $ imp sys/system@prod123 full= y file=/opt/export/full.dmp log=/opt/import/full.log
Owner=<user name>	Fromuser=<user name> Touser=<user name>
$ exp sys/system@prod123 owner=stanly file=sta.dmp log=sta.log $ imp sys/system@test123 file=sta.dmp fromuser=stanly touser=john log=john.log $ imp sys/system@prod123 file=sta.dmp fromuser=stanly touser=stanly log=sta1.log
Tables=<user name>.<table name>	Fromuser=<user name> Touser=<user name> Tables=<table name>
$ exp sys/system@prod123 tables=stanly.emp file=emp.dmp log=emp.log $ imp sys/system@test123 file=emp.dmp fromuser=stanly tables=emp touser=john log=emp.log $ imp sys/system@prod123 file=emp.dmp fromuser=stanly tables=emp touser=stanly log=emp.log
Tablespaces=<tablespace name>	Tablespaces=<tablespace name>
File=<dump_file_name.dmp>	File=<dump_file_name.dmp>
Log=<log_file_name.log>	Log=<log_file_name.log>
Query=’ ” where <condition>” ’
$ exp sys/system@prod123 tables=stanly.emp Query=’ “ where salary >= 4000 “ ‘ file=emp.dmp log=emp.log
Transport_tablespace=Y/N	Transport_tablespace=Y/N
Rows=Y/N	Rows=Y/N
Indexes=Y/N	Indexes=Y/N
Constraints= Y/N	Constraints= Y/N
Grants= Y/N	Grants= Y/N
Compress=Y/N	Ignore=Y/N
$ imp sys/system@prod123 file=emp.dmp fromuser=stanly tables=emp touser=stanly Ignore=y log=emp.log $ exp sys/system@prod123 full= y file=full.dmp Direct=Y log=full.log
Direct=Y/N
Buffer=<size> *buffer_size = rows_in_array maximum_row_size CREATE TABLE sample (name varchar(30), weight number); Maximum_row_size=(30+2+22+2)=56 rows in Array=Total number of recored example 100 Buffer_size=10056=5600*	Buffer=<size>
Recordlength=<size>	Recordlength=<size>
Filesize=<size>	Filesize=<size>
$ exp sys/system@prod123 full= y filesize = 4096M file=/opt/full1.dmp,/u01/full2.dmp,/u02/full3.dmp log=/opt/export/full.log
FLASHBACK_SCN=<SCN number>
FLASHBACK_TIME=<time>
TTS_FULL_CHECK=Y/N
	COMMIT=Y/N
	COMPILE=Y/N
	SHOW=Y/N
$ imp sys/system@test123 full=y file=full.dmp show=y log=full_ddl.log
	DATAFILES=<name of datafile>
	INDEXFILE=<index_file_name.sql>
$ imp sys/system@test123 full=y file=full.dmp indexfile=index.sql log=index.log $ imp sys/system@test123 full=y file=full.dmp indexes=N log=full1.log SQL > @index.sql

Tablespace level export:

$ export ORACLE_SID=prod

$ sqlplus “/ as sysdba”

SQL > select file_name from dba_data_files where tablespace_name=’DATA’;

FILE_NAME

/opt/oradata/prod/data01.dbf

SQL > alter tablespace data read only;

$ exp \’/ as sysdba\’ file=data.dmp tablespaces=data transport_tablespace=Y

tts_full_check=Y log=data.log

$ cp /opt/oradata/prod/data01.dbf /opt/oradata/test/data01.dbf

SQL > alter tablespace data read write;

Tablespace Level Import:

$ export ORACLE_SID=test

$ imp \’/ as sysdba\’ file=data.dmp tablespaces=data transport_tablespace=Y

datafiles=/opt/oradata/test/data01.dbf log=data1.log

SQL > alter tablespace data read write;

Datapump export and import

SQL > select * from dba_datapump_jobs;

OWNER_NAME - User that initiated the job

JOB_NAME - Name of the job

OPERATION - Type of operation being performed

JOB_MODE - Mode of operation being performed

STATE - Current job state

DEGREE - Number of worker processes performing the operation

SQL > select * from v$session_longops;

USERNAME - job owner

OPNAME - job name

TARGET_DESC - job operation

SOFAR - megabytes (MB) transferred thus far during the job

TOTALWORK - estimated number of megabytes (MB) in the job

UNITS - 'MB'

Default Locations for Dump, Log, and SQL Files

SQL> CREATE DIRECTORY dumpdir AS '/backup/export/prod';

SQL> GRANT READ, WRITE ON DIRECTORY dumpdir TO hr;

SQL> SELECT directory_name, directory_path FROM dba_directories ;

Export Parameter(expdp)		Import Parameters(impdp)
ATTACH =<job_name>		ATTACH =<job_name>
*SQL > select from dba_datapump_jobs; $ expdp hr/hr attach=exp_full $ impdp hr/hr attach=imp_full**
CONTENT ={ALL \| DATA_ONLY \| METADATA_ONLY}		CONTENT ={ALL \| DATA_ONLY \| METADATA_ONLY}
$ expdp hr/hr@test123 full=y dirctory=dumpdir dumpfile=full.dmp logfile=full.log $ impdp hr/hr@apps123 dirctory=dumpdir dumpfile=full.dmp content=metadata_only logfile=full1.log
DIRECTORY =<dump directory name>		DIRECTORY =<dump directory name>
DUMPFILE =<dump file name> expdat.dmp		DUMPFILE =<dump file name>
ESTIMATE ={BLOCKS \| STATISTICS}		ESTIMATE ={BLOCKS \| STATISTICS}
ESTIMATE_ONLY={ y \| n }
$ expdp hr/hr@test123 dirctory=dumpdir dumpfile=full.dmp ESTIMATE_ONLY=y logfile=full.log $ expdp hr/hr@test123 dirctory=dumpdir dumpfile=full.dmp ESTIMATE_ONLY=y ESTIMATE=STATISTICS logfile=full.log
EXCLUDE={default : None}		EXCLUDE={default : None}
EXCLUDE=GRANT EXCLUDE=CONSTRAINT EXCLUDE=SCHEMA:"='HR'" EXCLUDE=VIEW,PACKAGE, FUNCTION *SQL > select from DATABASE_EXPORT_OBJECTS; SQL > select * from SCHEMA_EXPORT_OBJECTS; SQL > select * from TABLE_EXPORT_OBJECTS; $ expdp hr/hr full=y DIRECTORY=dpump_dir DUMPFILE=hr_exclude.dmp EXCLUDE=VIEW,PACKAGE logfile=hr.log $ impdp hr/hr full=y DIRECTORY=dpump_dir DUMPFILE=hr_exclude.dmp EXCLUDE=CONSTRAINT logfile=hr.log**
FILESIZE=integer[B \| K \| M \| G]		FILESIZE=integer[B \| K \| M \| G]
$ expdp hr/hr full= y DIRECTORY=dpump_dir DUMPFILE=hr_3m.dmp FILESIZE=3M logfile=full.log
FLASHBACK_SCN=scn_value		FLASHBACK_SCN=scn_value
$ expdp hr/hr full=y DIRECTORY=dpump_dir1 DUMPFILE=hr_scn.dmp FLASHBACK_SCN=384632 logfile=scn.log $ impdp hr/hr full=y DIRECTORY=dpump_dir1 DUMPFILE=hr_scn.dmp FLASHBACK_SCN=384632
FLASHBACK_TIME="TO_TIMESTAMP(time-value)"	FLASHBACK_TIME="TO_TIMESTAMP(time-value)"
$ expdp hr/hr full=y DIRECTORY=dpump_dir1 DUMPFILE=hr_time.dmp FLASHBACK_TIME="TO_TIMESTAMP('25-08-2003 14:35:00', 'DD-MM-YYYY HH24:MI:SS')" logfile=time.log $ impdp hr/hr full=y DIRECTORY=dpump_dir1 DUMPFILE=hr_time.dmp FLASHBACK_TIME="TO_TIMESTAMP('25-08-2003 14:35:00', 'DD-MM-YYYY HH24:MI:SS')" logfile=time.log
FULL= { y \| n }	FULL= { y \| n }
$ expdp hr/hr@test123 full=y dirctory=dumpdir dumpfile=full.dmp logfile=full.log $ impdp hr/hr@apps123 dirctory=dumpdir dumpfile=full.dmp logfile=full1.log
INCLUDE=<default: none>	INCLUDE=<default: none>
INCLUDE=GRANT INCLUDE=CONSTRAINT INCLUDE=SCHEMA:"='HR'" INCLUDE=VIEW,PACKAGE, FUNCTION *SQL > select from DATABASE_EXPORT_OBJECTS; SQL > select * from SCHEMA_EXPORT_OBJECTS; SQL > select * from TABLE_EXPORT_OBJECTS; $ expdp hr/hr SCHEMAS=HR DIRECTORY=dpump_dir DUMPFILE=hr_INCLUDE.dmp INCLUDE=VIEW,PACKAGE logfile=hr.log $ impdp hr/hr SCHEMAS=HR DIRECTORY=dpump_dir DUMPFILE=hr_INCLUDE.dmp INCLUDE=CONSTRAINT logfile=hr.log**
JOB_NAME=job_name	JOB_NAME=job_name
$ expdp hr/hr@test123 full=y DIRECTORY=dpump_dir1 DUMPFILE=exp_job.dmp JOB_NAME=exp_job logfile=full.log $ impdp hr/hr@apps123 DIRECTORY=dpump_dir1 DUMPFILE=exp_job.dmp JOB_NAME=imp_job
LOGFILE= Default: export.log	LOGFILE = Default: import.log
$ expdp hr/hr@test123 full=y DIRECTORY=dpump_dir1 DUMPFILE=exp_job.dmp $ impdp hr/hr@test123 DIRECTORY=dpump_dir1 DUMPFILE=exp_job.dmp
NETWORK_LINK=source_database_link	NETWORK_LINK=source_database_link
SQL > CREATE DATABASE LINK apps CONNECT TO stanly IDENTIFIED BY ora123 USING 'apps123'; $ expdp hr/hr@prod123 TABLES=emp DIRECTORY=dpump_dir1 NETWORK_LINK=apps DUMPFILE=network_export.dmp $ impdp hr/hr@prod123 TABLES=emp DIRECTORY=dpump_dir1 NETWORK_LINK=apps DUMPFILE=network_export.dmp
NOLOGFILE={ y \| n }	NOLOGFILE={ y \| n }
$ expdp hr/hr@test123 full=y dirctory=dumpdir dumpfile=full.dmp NOLOGFILE=Y $ impdp hr/hr@apps123 dirctory=dumpdir dumpfile=full.dmp NOLOGFILE=Y
PARALLEL=integer	PARALLEL=integer
$ expdp hr/hr full=y DIRECTORY=dpump_dir1 DUMPFILE=par_exp%U.dmp PARALLEL=3 LOGFILE=parallel_import.log $ impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=par_exp%U.dmp PARALLEL=3 LOGFILE=parallel_import.log
PARFILE={parfile_name}	PARFILE={parfile_name}
$ cat > hr_schema.par SCHEMAS=HR DUMPFILE=exp.dmp DIRECTORY=dpump_dir1 LOGFILE=exp.log $ expdp hr/hr parfile=hr_schema.par $ impdp hr/hr parfile=hr_schema.par
QUERY =schema:table_name :' " <condition>" '	QUERY =schema:table_name :' " <condition>" '
$ expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=tables.dmp TABLES=emp QUERY='"WHERE eno> 10 AND sal > 10000"' $ imp dp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=tables.dmp TABLES=emp QUERY='"WHERE eno> 10 AND sal > 10000"'
SCHEMAS=username,...	SCHEMAS=username,...
$ expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expdat.dmp $ expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expdat.dmp SCHEMAS=hr,sh,oe $ impdp hr/hr SCHEMAS=hr DIRECTORY=dpump_dir1 DUMPFILE=expdat.dmp
	REMAP_SCHEMA=source_schema:target_schema
$ expdp hr/hr SCHEMAS=hr DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp $ impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp REMAP_SCHEMA=hr : scott
STATUS=[integer]	STATUS=[integer]
expdp hr/hr DIRECTORY=dpump_dir1 dumpdir=hr.dmp SCHEMAS=hr,sh STATUS=300 impdp hr/hr DIRECTORY=dpump_dir1 dumpdir=hr.dmp SCHEMAS=hr,sh STATUS=300
TABLES= sname.tname:pname	TABLES= sname.tname:pname
$ expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=tables.dmp TABLES=emp,jobs,dept $ expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=tables_part.dmp TABLES=sh.sales:sales_Q1_2000 $ impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=tables.dmp TABLES=employees,jobs $ impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=tables.dmp remap_schema=hr: scott TABLES=employees,jobs
	TABLE_EXISTS_ACTION={SKIP \| APPEND \| TRUNCATE \| REPLACE}
$ impdp hr/hr TABLES=emp DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp TABLE_EXISTS_ACTION=REPLACE
TABLESPACES=tablespace_name	TABLESPACES=tablespace_name
	REMAP_TABLESPACE=s_tablespace:t_tablespace
$ impdp hr/hr REMAP_TABLESPACE=tbs_1:tbs_6 DIRECTORY=dpump_dir1 DUMPFILE=tbs1.dmp $ impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=tables.dmp remap_schema=hr: scott REMAP_TABLESPACE=tbs_1:tbs_6 TABLES=employees,jobs
	SQLFILE=[directory_object:]file_name
impdp hr/hr full=y DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp SQLFILE=expfull.sql
TRANSPORT_FULL_CHECK={y \| n} TRANSPORT_TABLESPACES=tablespace_name	TRANSPORT_FULL_CHECK={y \| n} TRANSPORT_TABLESPACES=tablespace_name TRANSPORT_DATAFILES=datafile_name

Compression:

compression={ all | data_only | metadata_only | none }

$ expdp \'/ as sysdba\' full=yes directory = dbadir dumpfile=compress.dmp compression=all

$ ls -l full.compress.dmp

-rw-r----- 1 oracle oinstall 87973888 Sep 23 09:31 full.compress.dmp

$ expdp \'/ as sysdba\' full=yes dumpfile=dbadir dumpfile= full.dmp

$ ls -l full.dmp

-rw-r----- 1 oracle oinstall 260665344 Sep 23 10:48 full.dmp

Encryption Parameters è export and import operations

ENCRYPTION = {ALL | DATA_ONLY | ENCRYPTED_COLUMNS_ONLY | METADATA_ONLY | NONE}

ENCRYPTION_ALGORITHM = { AES128 | AES192 | AES256 }

ENCRYPTION_MODE = { DUAL | PASSWORD | TRANSPARENT }

$ expdp test/test schemas=TEST directory=TEST_DIR dumpfile=TEST.dmp

logfile=expdpTEST.log encryption=all encryption_password=password

encryption_mode=password

PARTITION_OPTIONS è export and import operations

PARTITION_OPTIONS={none | departition | merge}

$ expdp test/test directory=TEST_DIR dumpfile=TEST.dmp

logfile=expdpTEST.log tables=test.tab1 partition_options=merge

REUSE_DUMPFILES è export

REUSE_DUMPFILES={ Y | N }

$ expdp test/test schemas=TEST directory=TEST_DIR dumpfile=TEST.dmp

logfile=expdpTEST.log reuse_dumpfiles=y

REMAP_TABLE

REMAP_TABLE=[schema.]old_tablename[.partition]:new_tablename

$ impdp test/test tables=TAB1 directory=TEST_DIR dumpfile=TEST.dmp

logfile=TEST.log remap_table=TEST.TAB1:TAB2

DATA_OPTIONS

DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS

$ impdp test/test tables=TAB1 directory=TEST_DIR dumpfile=TEST.dmp

logfile=impdpTEST.log data_options=SKIP_CONSTRAINT_ERRORS

Data Pump Interactive-Command Mode

Ø CONTINUE_CLIENT

Ø EXIT_CLIENT

Ø KILL_JOB

Ø PARALLEL

Ø START_JOB

Ø STOP_JOB

Cross-Platform Transport Tablespace:

Source DB: Solaris

SQL > EXECUTE DBMS_TTS. TRANSPORT_SET_CHECK (‘TBS1’, TRUE);

SQL > SELECT * FROM TRANSPORT_SET_VIOLATIONS;

SQL > SELECT a.platform name, endian_format FROM v$transportable_platform b, v$database a

WHERE b.platform name = a.platform name;

Solaris[tm] OE (32—bit) Big

SQL> ALTER TABLESPACE TBS1 READ ONLY;

$ expdp system/manager dumpfile=tbs1.dmp directory=dumpdir transport_tablespace=tbs1

SQL > ! cp /opt/oradata/test/tbs01.dbf /backup/tbs01.dbf

SQL > alter tablespace tbs1 read write;

$ scp –r tbs1.dmp oracle@192.168.1.101:/u01/import/prod

$ scp –r /backup/tbs01.dbf oracle@192.168.1.101:/u01/import/prod

Target DB: Linux

SQL > SELECT a.platform name, endian_format FROM v$transportable_platform b, v$database a

WHERE b.platform name = a.platform name;

Linux IA (32—bit) Little

$ rman target /

RMAN> CONVERT DATAFILE “/u01/import/prod/tbs01.dbf”

TO PLATFORM=”Linux IA (32-bit)”

FROM PLATFORM=”Solaris[tm] OE (64-bit)”

DB_FILE_NANE CONVERT=”/u01/import/prod”, “/u01/oradata/prod” ;

$ impdp system/manager dumpfile=tbs1.dmp directory=dumpdir transport_tablespace=tbs1

transport_datafiles=/u01/oradata/prod /tbs01.dbf

SQL > alter tablespace tbs1 read write;

Flash Recovery Area:

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10G;

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u01/FRA' ;

Ø ALTER SYSTEM SET db_flashback_retention_target = <number_of_minutes>;

SQL > alter system set DB_FLASHBACK_RETENTION_TARGET = 2880;

SQL> SELECT * FROM V$RECOVERY_FILE_DEST;

NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUM_OF_FILES

/u01/FRA 10737418240 109240320 256000 28

SQL> SELECT ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG;

SQL> SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE PERCENT_USED PERCENT_RECLAIMABLE NUMB_OF_FILES

CONTROLFILE 0 0 0

ONLINELOG 2 0 22

ARCHIVELOG 4.05 2.01 31

BACKUPPIECE 3.94 3.86 8

IMAGECOPY 15.64 0.43 66

FLASHBACKLOG 0.08 0 1

SQL > ALTER SYSTEM SET DB_RECOVERY_FILE_DEST=' ';

RMAN Backup and Recovery:

$ export ORACLE_SID=PROD

$ rman target /

RMAN > backup database;

RMAN > backup database plus archivelog;

RMAN > backup database include current controlfile;

RMAN > backup database format =’/backup/rman_%U’;

RMAN > backup database skip read only skip offline skip inaccessible;

RMAN > backup database maxsetsize=1024M;

RMAN > backup tablespace users, data;

RMAN > backup datafile 10,12,14;

RMAN > backup archivelog all;

RMAN > backup archivelog all delete input;

RMAN > backup archivelog all skip inaccessible;

RMAN > backup archivelog until scn 12345 delete input;

RMAN > backup archive log until time

to_timestamp(‘2010-11-18 10:10:00’,’YYYY-MM-DD HH24:MI:SS’)

DELETE INPUT;

RMAN > backup archivelog time between ‘sysdate-30’ and ‘sysdate-1’;

RMAN > backup archivelog scn between 12345 and 12350;

RMAN > backup archivelog sequence between 101 and 150;

RMAN > backup archivelog until sequence 150;

RMAN > backup current controlfile;

RMAN > backup spfile;

RMAN> BACKUP DEVICE TYPE sbt BACKUPSET COMPLETED BEFORE 'SYSDATE-7' DELETE INPUT;

RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;

Specifying the Location of RMAN Output:

$ rman LOG /tmp/rman.log

$ rman | tee rman.log

Scripting with RMAN Substitution Variables

$ vi backup.cmd

connect sys/<sys_password>@prod1

run {

backup database

tag &1

format '/u01/app/oracle/backup/&2%U.bkp'

keep forever;

}

exit;

$ vi yearly_backup.sh

#!/bin/ksh

set tag=$1

set format =$2

rman @backup.cmd using $tag $format

$ sh yearly_backup.sh longterm_backup back0420

SQL > SELECT OPERATION, STATUS, MBYTES_PROCESSED, START_TIME, END_TIME

from V$RMAN_STATUS;

SQL> SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,

ROUND(SOFAR/TOTALWORK*100,2) "% COMPLETE"

FROM V$SESSION_LONGOPS

WHERE OPNAME LIKE 'RMAN%' AND

OPNAME NOT LIKE '%aggregate%’ AND

TOTALWORK! = 0 AND SOFAR <> TOTALWORK;

Recover Corrupted Datafiles:

SQL > SELECT FILE#, ERROR, RECOVER, TABLESPACE_NAME

FROM V$DATAFILE_HEADER

WHERE RECOVER = 'YES' OR (RECOVER IS NULL AND ERROR IS NOT NULL);

RMAN > sql ‘ alter database datafile 10 offline drop’;

RMAN > restore datafile 10;

RMAN > recover datafile 10;

RMAN > sql ‘ alter database datafile 10 online’;

RMAN > run

{ sql ‘ alter database datafile 10 offline drop’;

restore datafile 10;

recover datafile 10;

sql ‘ alter database datafile 10 online’;

}

Recover corrupted tablespaces:

RMAN > run

{ sql ‘ alter tablespace data offline’;

restore tablespace data;

recover tablespace data;

sql ‘ alter tablespace data online’;

}

RMAN > RUN

{

sql ‘ alter tablespace users offline’;

SET NEWNAME FOR DATAFILE '/disk1/oradata/prod/users01.dbf'

TO '/disk2/users01.dbf';

RESTORE TABLESPACE users;

SWITCH DATAFILE ALL;

RECOVER TABLESPACE users;

sql ‘ alter tablespace users online’;

}

Recover corrupted READ ONLY tablespaces:

RMAN > run

{ sql ‘ alter tablespace data offline’;

restore tablespace data;

sql ‘ alter tablespace data online’;

}

Recover corrupted SYSTEM tablespaces:

$ export ORACLE_SID=PROD

$ rman target /

RMAN > startup mount;

RMAN > run

{ sql ‘ alter tablespace system offline’;

restore tablespace system;

recover database;

sql ‘ alter tablespace system online’;

open database;

}

Recover corrupted ALL Datafiles:

$ export ORACLE_SID=PROD

$ rman target /

RMAN > startup mount;

RMAN > run

{ restore database;

recover database;

open database;

}

Enhanced Block Media Recovery

Identifying the Corrupt Blocks

SQL > select * from V$DATABASE_BLOCK_CORRUPTION;

ORA-01578: ORACLE data block corrupted (file # 2, block # 24)

ORA-01110: data file 2: '/u01/app/oracle/oradata/prod1/data01.dbf'

ORA-01578: ORACLE data block corrupted (file # 4, block # 10)

ORA-01110: data file 4: '/u01/app/oracle/oradata/prod1/data01.dbf'

RMAN> recover datafile 2 block 24 datafile 4 block 10;

Recovering All Corrupt Data Blocks

RMAN> validate database;

List of Datafiles

File Status MarkedCorrupt EmptyBlocks Blocks Examined High SCN

1 OK 0 12499 72960 12591563

File Name: C:\ORCL11\APP\ORACLE\ORADATA\ORCL11\SYSTEM01.DBF

Block Type Blocks Failing Blocks Processed

Data 0 48999

Index 0 9146

Other 0 2316

...

channel ORA_DISK_1: specifying datafile(s) for validation

including current control file for validation

channel ORA_DISK_1: validation complete, elapsed time: 00:00:02

List of Control File and SPFILE

File Type Status Blocks Failing Blocks Examined

Control File OK 0 594

Finished validate at 23-Nov-10

RMAN> recover corruption list;

Diagnosing and Repairing Failures with Data Recovery Advisor:

SQL> conn scott/tiger

SQL> create table t (col1 number);

ORA-01116: error in opening database file 4

ORA-01110: data file 4: '/home/oracle/oradata/PRODB3/users01.dbf'

$ rman target=/

RMAN> list failure;

Failure ID Priority Status Time Detected Summary

142 HIGH OPEN 15-Nov-10 One or more non-system

datafiles are missing

RMAN> CHANGE FAILURE 142 PRIORITY LOW;

RMAN> advise failure;

Failure ID Priority Status Time Detected Summary

142 LOW OPEN 15-Nov-10 One or more non-system

datafiles are missing

Mandatory Manual Actions

no manual actions available

Automated Repair Options

Option Repair Description

1 Restore and recover datafile 4

Repair script: /home/oracle/app/diag/rdbms/prodb3/PRODB3/hm/reco_3162589478.hm

RMAN> repair failure preview;

Repair script: /home/oracle/app/diag/rdbms/prodb3/PRODB3/hm/reco_741461097.hm

contents of repair script:

# restore and recover datafile

sql 'alter database datafile 4 offline';

restore datafile 4;

recover datafile 4;

sql 'alter database datafile 4 online';

RMAN> repair failure;

Repair script: /home/oracle/app/diag/rdbms/prodb3/PRODB3/hm/reco_3162589478.hm

contents of repair script:

# restore and recover datafile

sql 'alter database datafile 4 offline';

restore datafile 4;

recover datafile 4;

sql 'alter database datafile 4 online';

Do you really want to execute the above repair (enter YES or NO)? YES

sql statement: alter database datafile 4 online

repair failure complete

Backup Committed Undo? Why?

Ø RMAN bypasses backing up the committed undo data that is not required in recovery.

Ø The uncommitted undo data that is important for recovery is backed up as usual.

Ø This reduces the size and time of the backup (and the recovery as well).

Catalog database or Recovery Catalog

Catalog Database Server(192.168.1.101) :

$ export ORACLE_SID=APPS

$ sqlplus “/ as sysdba”

SQL > create tablespace rmantbs Datafile ‘/opt/oradata/apps/rman01.dbf’ size 500M;

SQL > create user rman identified by rman123

Default tablespace rmantbs

Temporary tablespace temp

Quota unlimited on rmantbs;

SQL > grant connect,resource to rman;

SQL > grant recovery_catalog_owner to rman;

SQL > exit

Target database Server (192.168.1.100) :

$ cat >> $ORACLE_HOME/network/admin/tnsnames.ora

apps123 =

( DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521))

(CONNECT_DATA =

(SERVICE_NAME = APPS)

)

$ export ORACLE_SID=PROD

$ rman target / catalog rman/rman123@apps123

RAMN > create catalog;

RMAN > register database;

RMAN > resync catalog;

Recover corrupted controlfile without catalog database:

$ export ORACLE_SID=PROD

$ rman target /

RMAN > startup nomount

RMAN > restore controlfile from ‘/backup/prod/C01_001_020.ctl’;

RMAN > alter database mount;

RMAN > recover database;

RMAN > open resetlogs database;

Recover corrupted controlfile with catalog database:

$ export ORACLE_SID=PROD

$ rman target / catalog rman/rman123@apps123

RMAN > startup nomount

RMAN > restore controlfile ;

RMAN > alter database mount;

RMAN > recover database;

RMAN > open resetlogs database;

Control files Auto backup:

RMAN > CONFIGURE CONTROLFILE AUTOBACKUP ON;

RMAN > CONFIGURE CONTROLFILE AUTOBACKUP FORMAT

FOR DEVICE TYPE DISK TO '/backup/prod/cf_%F';

Recover corrupted controlfile without catalog database using Autobackup:

$ export ORACLE_SID=PROD

$ rman target /

RMAN > startup nomount

RMAN > restore controlfile from ‘/backup/prod/cf_001_020.ctl’;

RMAN > alter database mount;

RMAN > recover database;

RMAN > open resetlogs database;

Recover corrupted controlfile with catalog database using Autobackup:

$ export ORACLE_SID=PROD

$ rman target / catalog rman/rman123@apps123

RMAN > startup nomount

RMAN > restore controlfile from autobackup;

RMAN > alter database mount;

RMAN > recover database;

RMAN > open resetlogs database;

Recover Corrupted SPFILE from Autobackup:

RMAN > Startup force nomount

RMAN > RUN

{

ALLOCATE CHANNEL c1 DEVICE TYPE DISK;

SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO

'/disk1/control_files/autobackup_%F';

SET DBID 001;

RESTORE SPFILE TO '/tmp/spfileTEMP.ora' FROM AUTOBACKUP ;

}

$ cat > /tmp/init.ora

SPFILE=/tmp/spfilePROD.ora

$ sqlplus "/ as sysdba"

SQL > STARTUP FORCE PFILE=/tmp/init.ora;

Recover Corrupted SPFILE with out Autobackup:

RMAN > Startup force nomount

RMAN > RUN

{

SET DBID 001;

RESTORE SPFILE TO '/tmp/spfileTEMP.ora' FROM ‘/backup/prod/spfile01_02’ ;

}

RMAN > Startup force

RMAN backups:

Ø Full backups

Ø Incremental backups

o Differential incremental backups

o Cumulative incremental backups

Differential incremental backups

1. n

2. n-1

3. < n-1

RMAN > backup database;

RMAN > backup incremental level 0 database;

RMAN > backup incremental level 1 database;

RMAN > backup incremental level 1 tablespace data,system;

RMAN > backup incremental level 1 datafile 10, 12;

RMAN> BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_update'

DATABASE;

Cumulative incremental backups

n-1
< n-1

RMAN > backup incremental level 1 cumulative database;

RMAN > backup incremental level 1 cumulative tablespace data,system;

RMAN > backup incremental level 1 cumulative datafile 10, 12;

Recovering a NOARCHIVELOG Database with Incremental Backups:

$ rman target / catalog rman/rman@catdb

RMAN > STARTUP FORCE MOUNT

RMAN > RESTORE DATABASE FROM TAG "consistent_whole_backup";

RMAN > RECOVER DATABASE NOREDO;

RMAN > ALTER DATABASE OPEN RESETLOGS;

Block Change Tracking

SQL > ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

SQL > ALTER DATABASE ENABLE BLOCK CHANGE TRACKING

USING FILE '/opt/oradata/rman_change_track_%f'

SQL > SELECT STATUS, FILENAME FROM V$BLOCK_CHANGE_TRACKING;

STATUS FILENAME

ENABLED /opt/oradata/rman_change_track_1_mf_2f71np5j_.chg

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

SQL > shutdown immediate;

$ cp opt/oradata/rman_change_track_1_mf_2f71np5j_.chg

/disk2/bct/RDBMS/changetracking/o1_mf_2f71np5j_.chg

SQL > startup mount;

SQL > ALTER DATABASE RENAME FILE

'/opt/oradata/rman_change_track_1_mf_2f71np5j_.chg ' TO

'/disk2/bct/RDBMS/changetracking/o1_mf_2f71np5j_.chg';

SQL > alter database open;

SQL > SELECT FILE#, INCREMENTAL_LEVEL, COMPLETION_TIME FROM V$BACKUP_DATAFILE

WHERE INCREMENTAL_LEVEL > 0 ORDER BY COMPLETION_TIME;

RMAN Scripts:

RMAN > create script levelonebk

{ backup incremental level 1 database;

backup archivelog until time ‘sysdate-1’ delete input;

backup current controlfile;

backup spfile;

}

RMAN > run { execute script levelonebk;}

RMAN > print script levelonebk;

RMAN > delete script levelonebk;

RMAN > list script;

Backing Up Large Files in Sections

$ rman target sys/sys@test123

RMAN > run {

allocate channel c1 device type disk format '/backup/rman%U';

allocate channel c2 device type disk format '/u01/rman%U';

allocate channel c3 device type disk format '/u02/rman%U';

backup section size 500m tablespace example;

}

SQL> select piece, multi_section from v$backup_datafile;

PIECES MULTI SECTION

1 NO

2 YES

7 YES

4 NO

RMAN incomplete recovery:

Ø Time based incomplete recovery

Ø SCN based incomplete recovery

Ø Log sequence base incomplete recovery

RMAN Database Cloning

Target DB Server:

$ export ORACLE_SID=PROD

$ rman target / catalog rman/rman123@apps123

RMAN> backup database;

RMAN > backup archivelog all;

RMAN > backup current controlfile;

RMAN > backup spfile;

RMAN > resync catalog;

RMAN> exit;

Auxiliary DB Server:

$ mkdir /u01/admin/test

$ cd /u01/admin/test

$ mkdir pfile bdump udump cdump create

$ mkdir /u01/oradata/test

Target DB server:

$ scp –r $ORACLE_HOME/dbs/initprod.ora oracle@192.168.1.101:/u01/admin/test/pfile

Auxiliary DB server:

$ mv /u01/admin/test/pfile/initprod.ora /u01/admin/test/pfile/inittest.ora

$ vi /u01/admin/test/pfile/inittest.ora

db_unique_name=test

db_name=test

control_files=’/u01/oradata/test/control01.ctl’

background_dump_dest=/u01/admin/test/bdump

user_dump_dest=/u01/admin/test/udump

core_dump_dest=/u01/admin/test/cdump

audit_dump_dest=/u01/admin/test/adump

db_file_name_convert=(‘/opt/oradata/prod’,’/u01/oradata/test’)

log_file_name_convert=(‘/opt/oradata/prod’,’/u01/oradata/test’)

$ export ORACLE_SID=test

$ orapwd file=$ORACLE_HOME/dbs/orapwtest password=sys

$ sqlplus “/ as sysdba”

SQL > startup nomount pfile=/u01/admin/test/pfile/inittest.ora;

SQL > create spfile from pfile=’/u01/admin/test/pfile/inittest.ora’;

SQL > shutdown immediate;

SQL > startup nomount;

$ cat >> $ORACLE_HOME/network/admin/tnsnames.ora

test =

( DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521))

(CONNECT_DATA =

(SERVICE_NAME = test)

)

Target DB server:

$ cat >> $ORACLE_HOME/network/admin/tnsnames.ora

test123 =

( DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521))

(CONNECT_DATA =

(SERVICE_NAME = test)

)

$ export ORACLE_SID=PROD

$ rman target /

RMAN > connect catalog rman/rman123@apps123

RMAN > connect auxiliary sys/sys@test123

RMAN > run {

Set until time to_date(‘2010-Nov-18 10:30:00’);

# Set until scn 12345;

# Set until sequence 170;

Duplicate target database to test;

}

RMAN > exit;

RMAN Active Database Cloning

Auxiliary DB Server:

$ mkdir /u01/admin/test

$ cd /u01/admin/test

$ mkdir pfile bdump udump cdump create

$ mkdir /u01/oradata/test

Target DB server:

$ scp –r $ORACLE_HOME/dbs/initprod.ora

oracle@192.168.1.101:/u01/admin/test/pfile

Auxiliary DB server:

$ mv /u01/admin/test/pfile/initprod.ora /u01/admin/test/pfile/inittest.ora

$ vi /u01/admin/test/pfile/inittest.ora

db_unique_name=test

db_name=test

control_files=’/u01/oradata/test/control01.ctl’

background_dump_dest=/u01/admin/test/bdump

user_dump_dest=/u01/admin/test/udump

core_dump_dest=/u01/admin/test/cdump

audit_dump_dest=/u01/admin/test/adump

db_file_name_convert=(‘/opt/oradata/prod’,’/u01/oradata/test’)

log_file_name_convert=(‘/opt/oradata/prod’,’/u01/oradata/test’)

$ export ORACLE_SID=test

$ orapwd file=$ORACLE_HOME/dbs/orapwtest password=sys

$ sqlplus “/ as sysdba”

SQL > startup nomount pfile=/u01/admin/test/pfile/inittest.ora;

SQL > create spfile from pfile=’/u01/admin/test/pfile/inittest.ora’;

SQL > shutdown immediate;

SQL > startup nomount;

$ cat >> $ORACLE_HOME/network/admin/tnsnames.ora

test =

( DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521))

(CONNECT_DATA =

(SERVICE_NAME = test)

)

Target DB server:

$ cat >> $ORACLE_HOME/network/admin/tnsnames.ora

test123 =

( DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521))

(CONNECT_DATA =

(SERVICE_NAME = test)

)

$ export ORACLE_SID=PROD

$ rman target /

RMAN > connect catalog rman/rman123@apps123

RMAN > connect auxiliary sys/sys@test123;

RMAN > Duplicate target database to test from active database;

DUPLICATE TARGET DATABASE TO test FROM ACTIVE DATABASE PASSWORD FILE SPFILE NOFILENAMECHECK;
DUPLICATE DATABASE TO test UNTIL TIME "TO_DATE('11/01/2012 14:00:00', 'MM/DD/YYYY HH24:MI:SS')" SPFILE BACKUP LOCATION '/prod_backups' NOFILENAMECHECK;

RMAN > exit;

RMAN > RUN

{

SET NEWNAME FOR DATAFILE 1 TO '/oradata1/system01.dbf';

SET NEWNAME FOR DATAFILE 2 TO '/oradata2/sysaux01.dbf';

SET NEWNAME FOR DATAFILE 3 TO '/oradata3/undotbs01.dbf';

SET NEWNAME FOR DATAFILE 4 TO '/oradata4/users01.dbf';

SET NEWNAME FOR DATAFILE 5 TO '/oradata5/users02.dbf';

SET NEWNAME FOR TEMPFILE 1 TO '/oradatat/temp01.dbf';

DUPLICATE TARGET DATABASE TO test SKIP TABLESPACE tools

LOGFILE

GROUP 1 ('/duplogs/redo01a.log', '/duplogs/redo01b.log') SIZE 4M REUSE,

GROUP 2 ('/duplogs/redo02a.log', '/duplogs/redo02b.log') SIZE 4M REUSE;

}

Creating a Transportable Tablespace Set at a Specified Time or SCN:

RMAN > TRANSPORT TABLESPACE tbs_2

TABLESPACE DESTINATION '/disk1/transportdest'

AUXILIARY DESTINATION '/disk1/auxdest'

UNTIL SCN 11379;

RMAN > TRANSPORT TABLESPACE tbs_2

TABLESPACE DESTINATION '/disk1/transportdest'

AUXILIARY DESTINATION '/disk1/auxdest'

TO RESTORE POINT 'before_upgrade';

RMAN > TRANSPORT TABLESPACE tbs_2

TABLESPACE DESTINATION '/disk1/transportdest'

AUXILIARY DESTINATION '/disk1/auxdest'

UNTIL TIME 'SYSDATE-1';

Checking the Database Before Cross-Platform Database Conversion:

SQL > ALTER DATABASE OPEN READ ONLY;

SQL > SET SERVEROUTPUT ON

SQL > DECLARE

db_ready BOOLEAN;

BEGIN

db_ready := DBMS_TDB.CHECK_DB('Microsoft Windows IA (32-bit)',

DBMS_TDB.SKIP_READONLY);

dbms_output.put_line(db_ready);

END;

SQL > DECLARE

external BOOLEAN;

BEGIN

external := DBMS_TDB.CHECK_EXTERNAL;

END;

The following external tables exist in the database:

SH.SALES_TRANSACTIONS_EXT

The following directories exist in the database:

SYS.DATA_PUMP_DIR, SYS.MEDIA_DIR, SYS.DATA_FILE_DIR, SYS.LOG_FILE_DIR

The following BFILEs exist in the database:

PM.PRINT_MEDIA

PL/SQL procedure successfully completed.

$ rman target /

RMAN> CONVERT DATABASE

NEW DATABASE 'newdb'

TRANSPORT SCRIPT 'D:\tmp\transportscript.sql'

TO PLATFORM 'Linux IA (32-bit)'

DB_FILE_NAME_CONVERT 'd:\oradata\prod ' 'D:\tmp';

Starting convert at 25-NOV-11

using channel ORA_DISK_1

External table SH.SALES_TRANSACTIONS_EXT found in the database

Directory SYS.DATA_PUMP_DIR found in the database

Directory SYS.MEDIA_DIR found in the database

Directory SYS.DATA_FILE_DIR found in the database

Directory SYS.LOG_FILE_DIR found in the database

BFILE PM.PRINT_MEDIA found in the database

User SYS with SYSDBA and SYSOPER privilege found in password file

User OPER with SYSDBA privilege found in password file

channel ORA_DISK_1: starting datafile conversion

input datafile fno=00001 name=d:\oradata\prod \tbs_01.f

converted datafile=D:\tmp\tbs_01.f

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:15

channel ORA_DISK_1: starting datafile conversion

input datafile fno=00002 name=d:\oradata\prod \tbs_ax1.f

converted datafile=D:\tmp\tbs_ax1.f

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01

Run SQL script D:\tmp\transportscript.sql on the destination platform

to create database

Edit init.ora file init_00gb3vfv_1_0.ora. This PFILE will be used to

create the database on the destination platform

To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on

the destination platform

To change the internal database identifier, use DBNEWID Utility

Finished backup at 25-NOV-06

SQL > alter database open read write;

$ scp -r D:\tmp\* oracle@192.168.1.100:/opt/oradata

$ scp -r $ORACLE_HOME/dbs/initprod.ora oracle@192.168.1.100:$ORACLE_HOME/dbs

control_files = "/opt/oradata/cf_D-NEWDBT_id-1778429277_00gb9u2s"

db_recovery_file_dest = "/opt/oradata/orcva"

db_recovery_file_dest_size= 10737418240

instance_name = "NEWDBT"

service_names = "NEWDBT.example.com"

db_name = "NEWDBT"

SQL> CONNECT / AS SYSDBA

SQL > /opt/oradata/transportscript.sql

Auxiliary DB server:

$ export ORACLE_SID=test

$ sqlplus “/ as sysdba”

SQL > select name from v$database;

NAME

TEST

SQL > select status from v$instance;

STATUS

OPEN

Restoring Archived Redo Logs to a New Location:

RMAN > RUN

{

SET ARCHIVELOG DESTINATION TO '/oracle/temp_restore';

RESTORE ARCHIVELOG ALL;

RESTORE DATABASE;

RECOVER DATABASE;

}

switching to a Data File Copy:

RMAN > RUN

{

SQL "ALTER DATABASE DATAFILE 4 OFFLINE";

SWITCH DATAFILE 4 TO COPY;

RECOVER DATAFILE 4;

SQL "ALTER DATABASE DATAFILE 4 ONLINE";

}

Switching to a Database Copy:

$ rman target /

RMAN > startup mount

RMAN > SWITCH DATABASE TO COPY;

RMAN > RECOVER DATABASE;

RMAN > ALTER DATABASE OPEN;

Target DB Server:

$ export $ORACLE_SID=PROD

$ sqlplus “/ as sysdba”

SQL > shutdown immediate;

SQL > exit;

$ rman target / catalog rman/rman123@apps123

RMAN > startup mount

RMAN > run {

set until time “to_date(‘2010-Nov-18 10:30:00’)”;

# set until scn 1000;

# set until sequence 9923;

restore database;

recover database;

open resetlogs database;

}

Database Incarnations:

Orphaned Backups

To reset the recovery catalog to an older incarnation for media recovery:

RMAN > LIST INCARNATION OF DATABASE PROD;

DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time

1 2 PROD 1224038686 PARENT 1 02-JUL-11

1 582 PROD 1224038686 CURRENT 59727 10-JUL-11

RMAN > RESET DATABASE TO INCARNATION 2;

RMAN > SHUTDOWN IMMEDIATE

RMAN > STARTUP NOMOUNT

RMAN > RUN

{

SET UNTIL 'SYSDATE-45';

RESTORE CONTROLFILE; # only if current control file is not available

}

RMAN > ALTER DATABASE MOUNT;

RMAN > RESTORE DATABASE;

RMAN > RECOVER DATABASE;

RMAN > ALTER DATABASE OPEN RESETLOGS;

RMAN Configurations:

RMAN> SHOW ALL;

RMAN > CONFIGURE RETENTION POLICY TO REDUNDANCY 1;

RMAN > CONFIGURE RETENTION POLICY TO recovery window of 10 days;

RMAN > CONFIGURE BACKUP OPTIMIZATION ON;

RMAN> BACKUP DATABASE FORCE;

RMAN> BACKUP ARCHIVELOG ALL FORCE;

RMAN > CONFIGURE BACKUP OPTIMIZATION OFF;

RMAN > CONFIGURE DEFAULT DEVICE TYPE TO DISK;

RMAN> BACKUP DEVICE TYPE sbt DATABASE;

RMAN > CONFIGURE DEFAULT DEVICE TYPE TO ‘SBT’;

RMAN> BACKUP DEVICE TYPE DISK DATABASE;

RMAN> CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 2;

RMAN> CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE sbt TO 3;

RMAN> CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE sbt CLEAR;

Determining the Location of the Media Management Library:

CONFIGURE CHANNEL DEVICE TYPE sbt PARMS 'SBT_LIBRARY=pathname';

Ø On Linux and UNIX, the SBT library is loaded from:

Ø /opt/oracle/extapi/[32,64]/{SBT}/{VENDOR}/{VERSION}/libobk.so

RMAN > CONFIGURE DEVICE TYPE "SBT" PARALLELISM 2;

RMAN > CONFIGURE DEVICE TYPE DISK PARALLELISM 2;

RMAN > CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/opt/rman_%U’;

RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 2G;

RMAN> BACKUP DATABASE;

RMAN > RUN {

ALLOCATE CHANNEL d1 DEVICE TYPE DISK FORMAT '/u01/rman_%U';

ALLOCATE CHANNEL d2 DEVICE TYPE DISK FORMAT '/u02/rman_%U';

BACKUP INCREMENTAL LEVEL 0 DATABASE;

RELEASE CHANNEL d1;

RELEASE CHANNEL d2; }

RMAN > CONFIGURE MAXSETSIZE TO UNLIMITED;

RMAN> CONFIGURE MAXSETSIZE TO 7500K;

RMAN> BACKUP TABLESPACE users;

RMAN> BACKUP TABLESPACE tools MAXSETSIZE 5G;

RMAN > CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/backup/cf_snap.f';

RMAN > CONFIGURE CONTROLFILE AUTOBACKUP OFF;

RMAN > CONFIGURE CONTROLFILE AUTOBACKUP ON;

RMAN > CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';

RMAN > CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET;

RMAN > CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COPY;

RMAN > CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;

Configuring an Archived Redo Log Deletion Policy

RMAN> configure archivelog deletion policy to backed up 2 times to disk;

RMAN> configure archivelog deletion policy to none;

RMAN> configure archivelog deletion policy to applied on standby;

RMAN> configure archivelog deletion policy to applied on all standby;

RMAN> configure archivelog deletion policy to shipped on standby;

RMAN> configure archivelog deletion policy to shipped on all standby;

RMAN> CONFIGURE EXCLUDE FOR TABLESPACE example;

RMAN> BACKUP DATABASE;

RMAN> BACKUP DATABASE NOEXCLUDE;

RMAN> CONFIGURE EXCLUDE FOR TABLESPACE example clear;

RMAN> CONFIGURE COMPRESSION ALGORITHM 'BASIC';

SQL > select algorithm_name, algorithm_description, is_default

from v$rman_compression_algorithm;

ALGORITHM_NAME ALGORITHM_DESCRIPTION IS_DEFAULT

ZLIB optimized for speed NO

BZIP2 optimized for maximum compression YES

RMAN> configure compression algorithm 'zlib';

Creating Archival (Long-Term) Backups or restore point:

RMAN> backup database

format '/opt/archives\db_%U.bkp'

tag quarterly

keep until time 'sysdate + 365'

restore point firstquart07;

RMAN> change backup tag 'quartely' keep forever;

RMAN> change copy of database controlfile nokeep;

Restoring an Archival Backup

RMAN> connect target sys/sys@prod1

RMAN> connect catalog rman/rman@catdb

RMAN> connect auxiliary sys/sys@newdb1

RMAN> list restore point all;

SCN RSP Time Type Time Name

3074299 21-MAY-10 FIRSTQUART07

RMAN> duplicate database to newdb until restore point firstquart07

db_file_name_convert='/u01/prod1/dbfiles/','/u01/newdb/dbfiles' ;

Scripting RMAN Operations

$cat > /tmp/rman_bk.txt

CONNECT TARGET /

BACKUP DATABASE PLUS ARCHIVELOG;

LIST BACKUP;

EXIT;

$ rman

RMAN> @/tmp/rman_bk.txt

or

$ rman @/tmp/rman_bk.txt

RMAN > list backup;

RMAN > list backup of database;

RMAN > list backup of archivelog;

RMAN > list backup of controlfile;

RMAN > list backup of tablespace users;

RMAN > list backup of datafile 10;

RMAN > list copy of database;

RMAN > list copy of archivelog;

RMAN > list copy of controlfile;

RMAN > list copy of tablespace users;

RMAN > list copy of datafile 10;

RMAN > crosscheck backup;

RMAN > crosscheck backup of database;

RMAN > crosscheck backup of archivelog;

RMAN > crosscheck backup of controlfile;

RMAN > crosscheck backup tablespace users;

RMAN > crosscheck backup of datafile 10;

RMAN > RUN

{

ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE sbt;

CROSSCHECK BACKUP;

CROSSCHECK COPY;

}

Checking for Block Corruption with the VALIDATE Command:

RMAN> VALIDATE DATABASE;

RMAN> VALIDATE BACKUPSET 22;

RMAN > RUN

{

ALLOCATE CHANNEL c1 DEVICE TYPE DISK;

ALLOCATE CHANNEL c2 DEVICE TYPE DISK;

VALIDATE DATAFILE 1 SECTION SIZE 1200M; }

RMAN > BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

RMAN > BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;

Validating Backups Before Restoring:

RMAN > RESTORE DATABASE VALIDATE;

RMAN > RESTORE ARCHIVELOG ALL VALIDATE;

RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;

RMAN> VALIDATE DATAFILE 4 BLOCK 10 TO 13;

RMAN> VALIDATE BACKUPSET 3;

RMAN > validate backup database;

RMAN > validate backup archivelog all;

RMAN > delete expired backup;

RMAN > delete noprompt expired backup of database;

RMAN > delete noprompt expired backup of archivelog;

RMAN > delete noprompt expired backup of controlfile;

RMAN > delete noprompt expired backup tablespace users;

RMAN > delete noprompt expired backup of datafile 10;

RMAN > report need backup;

RMAN retention policy will be applied to the command

RMAN retention policy is set to redundancy 1

Report of files with less than 1 redundant backups

File #bkps Name

---- ----- -----------------------------------------------------

2 0 /oracle/oradata/trgt/undotbs01.dbf

RMAN > backup datafile 2;

RMAN > report obsolete;

RMAN > delete obsolete;

RMAN > report schema;

RMAN > backup copy of database;

RMAN > backup copy of tablespace users;

RMAN > backup copy of datafile 10;

RMAN > backup copy of archivelog all;

RMAN > backup copy of controlfile;

RMAN > set maxcorrupt for datafile 1 to 10;

RMAN > CHANGE BACKUPSET 100 AVAILABLE;

RMAN > CHANGE BACKUP OF SPFILE COMPLETED BEFORE 'SYSDATE-1' UNAVAILABLE;

RMAN > BACKUP DATABASE KEEP FOREVER NOLOGS;

RMAN > BACKUP DATABASE KEEP FOREVER LOGS;

RMAN > CHANGE COPY OF DATABASE NOKEEP;

RMAN> CHANGE BACKUP DATABASE KEEP;

RMAN > BACKUP KEEP UNTIL TIME '01-JAN-11' ARCHIVELOG ALL;

RMAN > CHANGE BACKUP OF ARCHIVELOG ALL NOKEEP;

RMAN > CATALOG START WITH '/u02/arch';

RMAN > CATALOG ARCHIVELOG '/u01/arch1_30.dbf', '/u02/arch_31.dbf'';

RMAN > CATALOG DATAFILECOPY '/u01/oradata/users01.bak'

RMAN > BLOCKRECOVER DATAFILE 2 BLOCK 12, 13

RMAN > BLOCKRECOVER CORRUPTION LIST;

RMAN > DROP CATALOG

Using the UPGRADE CATALOG Command:

SQL> GRANT CREATE TYPE TO rman;

SQL> EXIT;

$ rman target / catalog rman/rman@catdb

RMAN> UPGRADE CATALOG;

recovery catalog owner is rman

enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN> UPGRADE CATALOG;

recovery catalog upgraded to version 11.01.00

DBMS_RCVMAN package upgraded to version 11.01.00

DBMS_RCVCAT package upgraded to version 11.01.00

Merging Recovery Catalogs

$ rman

RMAN> connect catalog rman/rman@rman11

RMAN> list incarnation;

DB Key Inc DB Name DB ID STATUS Reset SCN Reset Time

192 207 ELEVEN 3481526915 PARENT 1 22-NOV-06

192 193 ELEVEN 3481526915 CURRENT 909437 13-MAR-07

1 15 ORCL11 3863017760 PARENT 1 22-NOV-06

1 2 ORCL11 3863017760 CURRENT 909437 03-MAR-07

$ rman

RMAN> connect catalog rman/rman@rman10

RMAN> list incarnation;

DB Key Inc DB Name DB ID STATUS Reset SCN Reset Time

1 8 TENNER 1166569509 PARENT 1 30-AUG-05

1 2 TENNER 1166569509 CURRENT 534907 13-MAR-07

$ rman

RMAN> connect catalog rman/rman@rman11

RMAN> import catalog rman/rman@rman10;

RMAN> list incarnation;

DB Key Inc DB Name DB ID STATUS Reset SCN Reset Time

1 8 TENNER 1166569509 PARENT 1 30-AUG-05

1 2 TENNER 1166569509 CURRENT 534907 13-MAR-07

192 207 ELEVEN 3481526915 PARENT 1 22-NOV-06

192 193 ELEVEN 3481526915 CURRENT 909437 13-MAR-07

1 15 ORCL11 3863017760 PARENT 1 22-NOV-06

1 2 ORCL11 3863017760 CURRENT 909437 03-MAR-07

RMAN> import catalog rman10/rman10@tenner dbid = 123456, 123457;

RMAN> import catalog rman10/rman10@tenner db_name = testdb, mydb;

Virtual Private Catalogs

SQL> connect sys/sys@apps123 as sysdba

SQL> create user virtual1 identified by virtual1

temporary tablespace temp

default tablespace vp_users

quota unlimited on vp_users;

SQL> grant recovery_catalog_owner to virtual1;

$ rman

RMAN> connect catalog rman/rman@rman11

RMAN> connect catalog rman/rman@nick

RMAN> grant catalog for database test1, test2 to virtual1;

RMAN> connect catalog virtual1/virtual1@apps123

RMAN> create virtual catalog;

RMAN> list incarnation;

DB Key Inc DB Name DB ID STATUS Reset SCN Reset Time

192 207 TEST1 3481526915 PARENT 1 22-NOV-06

192 193 TEST1 3481526915 CURRENT 909437 13-MAR-07

1 15 TEST2 3863017760 PARENT 1 22-NOV-06

1 2 TEST2 3863017760 CURRENT 909437 03-MAR-07

RMAN> connect target sys/sys@prod1;

RMAN> connect catalog virtual1/virtual1@apps123;

RMAN> grant register database to virtual1;

RMAN> backup database plus archivelog;

RMAN> connect catalog rman/rman@rman11;

RMAN> revoke catalog for database test1 from virtual1;

RMAN> revoke all privileges from virtual1;

RMAN> revoke register database from virtual1;

RMAN> drop catalog;

Catalog DB:

SQL > CONN RMAN/RMAN123@apps123

SQL > SELECT * FROM RC_BACKUP_ARCHIVELOG_DETAILS

SQL > SELECT * FROM RC_BACKUP_CONTROLFILE_DETAILS

SQL > SELECT * FROM RC_BACKUP_COPY_DETAILS

SQL > SELECT * FROM RC_BACKUP_CORRUPTION

SQL > SELECT * FROM RC_BACKUP_DATAFILE_DETAILS

SQL > SELECT * FROM RC_BACKUP_FILES

SQL > SELECT * FROM RC_BACKUP_PIECE

SQL > SELECT * FROM RC_BACKUP_REDOLOG

SQL > SELECT * FROM RC_BACKUP_SET_DETAILS

SQL > SELECT * FROM RC_BACKUP_SPFILE_DETAILS

SQL > SELECT * FROM RC_CONTROLFILE_COPY

SQL > SELECT * FROM RC_COPY_CORRUPTION

SQL > SELECT * FROM RC_DATABASE_BLOCK_CORRUPTION

SQL > SELECT * FROM RC_DATAFILE_COPY

SQL > SELECT * FROM RC_RMAN_BACKUP_JOB_DETAILS

SQL > SELECT * FROM RC_RMAN_CONFIGURATION

SQL > SELECT * FROM RC_RMAN_STATUS

SQL > SELECT * FROM RC_STORED_SCRIPT

SQL > SELECT * FROM RC_STORED_SCRIPT_LINE

Flashback technologies

Flashback Query
Flashback Version Query
Flashback Transaction Query
Flashback Table
Flashback Drop (Recycle Bin)
Flashback Database
Total Recall and the Flashback Data Archive

Flashback Query

SQL > CREATE TABLE flashback_query_test ( id NUMBER(10));

SQL > SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS')

FROM v$database;

CURRENT_SCN TO_CHAR(SYSTIMESTAM

722452 2010-11-19 13:34:12

SQL > INSERT INTO flashback_query_test (id) VALUES (1);

SQL > COMMIT;

SQL > SELECT COUNT(*) FROM flashback_query_test;

COUNT(*)

SQL > SELECT COUNT(*) FROM flashback_query_test AS OF TIMESTAMP

TO_TIMESTAMP('2010-11-19 13:34:12', 'YYYY-MM-DD HH24:MI:SS');

COUNT(*)

SQL > SELECT COUNT(*) FROM flashback_query_test AS OF SCN 722452;

COUNT(*)

SQL > SELECT COUNT(*) FROM flashback_query_test;

COUNT(*)

Flashback Version Query

SQL > CREATE TABLE flashback_version_query_test (id NUMBER(10),

description VARCHAR2(50));

SQL > INSERT INTO flashback_version_query_test VALUES (1, 'ONE');

SQL > COMMIT;

SQL > SELECT current_scn,

TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

CURRENT_SCN TO_CHAR(SYSTIMESTAM

725202 2010-11-19 14:59:08

SQL > UPDATE flashback_version_query_test SET description = 'TWO' WHERE id = 1;

SQL > COMMIT;

SQL > UPDATE flashback_version_query_test SET description = 'THREE' WHERE id = 1;

SQL > COMMIT;

SQL > SELECT current_scn,

TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

CURRENT_SCN TO_CHAR(SYSTIMESTAM

725219 2010-11-19 14:59:36

SQL > SELECT versions_xid, versions_operation, description FROM flashback_version_query_test

VERSIONS BETWEEN TIMESTAMP

TO_TIMESTAMP('2010-11-19 14:59:08', 'YYYY-MM-DD HH24:MI:SS')

AND TO_TIMESTAMP('2010-11-19 14:59:36', 'YYYY-MM-DD HH24:MI:SS')

WHERE id = 1;

VERSIONS_XID VERSION_OPERATION DESCRIPTION

02001C0043030000 U THREE

0600030021000000 U TWO

ONE

SQL > SELECT versions_xid, versions_operation,description

FROM flashback_version_query_test

VERSIONS BETWEEN SCN 725202 AND 725219 WHERE id = 1;

VERSIONS_XID VERSION_OPERATION DESCRIPTION

02001C0043030000 U THREE

0600030021000000 U TWO

ONE

Flashback Transaction Query

SQL > SELECT undo_sql FROM flashback_transaction_query

WHERE xid = HEXTORAW('0600030021000000');

UNDO_SQL

update "SCOTT"."FLASHBACK_VERSION_QUERY_TEST" set "DESCRIPTION" = 'TWO'

where ROWID = 'AAAMP9AAEAAAAAYAAA';

Flashback Table

SQL > CREATE TABLE flashback_table_test (id NUMBER(10));

SQL > ALTER TABLE flashback_table_test ENABLE ROW MOVEMENT;

SQL > SELECT current_scn FROM v$database;

CURRENT_SCN

715315

SQL > INSERT INTO flashback_table_test (id) VALUES (1);

SQL > COMMIT;

SQL > SELECT current_scn FROM v$database;

CURRENT_SCN

715340

SQL > FLASHBACK TABLE flashback_table_test TO SCN 715315;

SQL > SELECT COUNT(*) FROM flashback_table_test;

COUNT(*)

SQL > FLASHBACK TABLE flashback_table_test TO SCN 715340;

SQL > SELECT COUNT(*) FROM flashback_table_test;

COUNT(*)

SQL > FLASHBACK TABLE flashback_table_test TO TIMESTAMP

TO_TIMESTAMP ('2010-11-19 15:30:30', 'YYYY-MM-DD HH: MI:SS’);

Flashback Drop (Recycle Bin)

SQL > CREATE TABLE flashback_drop_test (id NUMBER(10));

SQL > INSERT INTO flashback_drop_test (id) VALUES (1);

SQL > COMMIT;

SQL > DROP TABLE flashback_drop_test;

SQL > SHOW RECYCLEBIN

ORIGINAL NAME R_NAME OBJ_TYPE DROP_TIME

FLASHBACK_DROP_TEST BIN$T6 TABLE 2010-11-19:11:09:07

SQL > FLASHBACK TABLE flashback_drop_test TO BEFORE DROP;

SQL > SELECT * FROM flashback_drop_test;

SQL > DROP TABLE flashback_drop_test;

SQL > SHOW RECYCLEBIN

ORIGINAL NAME R_NAME OBJ_TYPE DROP_TIME

FLASHBACK_DROP_TEST BIN$T6 TABLE 2010-03-29:11:12:07

SQL > SELECT * FROM "BIN$6";

SQL > FLASHBACK TABLE flashback_drop_test TO BEFORE DROP

RENAME TO flashback_drop_test_old;

SQL > PURGE TABLE emp;

SQL > PURGE INDEX ind_eno;

SQL > PURGE TABLESPACE data;

SQL > PURGE TABLESPACE data USER stanly;

SQL > PURGE RECYCLEBIN;

SQL > PURGE DBA_RECYCLEBIN;

Flashback Database

$ export ORACLE_SID=PROD

$ sqlplus "/ as sysdba"

SQL > SHUTDOWN IMMEDIATE

SQL > STARTUP MOUNT

SQL > ALTER DATABASE ARCHIVELOG;

SQL > ALTER DATABASE FLASHBACK ON;

SQL > ALTER DATABASE OPEN;

SQL > SELECT FLASHBACK_ON FROM V$DATABASE;

FLASHBACK_ON

YES

SQL > SELECT current_scn,

TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

CURRENT_SCN TO_CHAR(SYSTIMESTAM

725202 2010-11-19 14:59:08

SQL > CONN stanly/stanly123

SQL > CREATE TABLE flashback_database_test (id NUMBER(10));

SQL > CONN sys/sys AS SYSDBA

SQL > SHUTDOWN IMMEDIATE

SQL > STARTUP MOUNT

SQL > FLASHBACK DATABASE TO TIMESTAMP

TO_TIMESTAMP(2010-11-19 14:59:08’, 'YYYY-MM-DD HH24:MI:SS');

SQL > FLASHBACK DATABASE TO SCN 725202

SQL > ALTER DATABASE OPEN RESETLOGS;

SQL > CONN stanly/stanly123

SQL > select * from flashback_database_test;

ORA-00942: table or view does not exist.

Total Recall and the Flashback Data Archive:

SQL> conn sys/oracle as sysdba

SQL> create tablespace FLASHBACK datafile '/opt/oradata/flashback01.dbf' size 4096m

autoextend on;

SQL> create flashback archive default fp_pr_flash tablespace flashback quota 4096M

retention 10 year;

SQL> alter table hr.emp flashback archive;

SQL> alter table hr.dept flashback archive;

SQL> alter table hr.location flashback archive;

SQL> grant flashback archive administer to hr;

SQL> select * from dba_sys_privs where privilege like '%FLASH%';

GRANTEE PRIVILEGE ADM

SYS FLASHBACK ANY TABLE NO

DBA FLASHBACK ANY TABLE YES

HR FLASHBACK ARCHIVE ADMINISTER NO

SQL> select owner_name,flashback_archive_name,retention_in_days,status

from dba_flashback_archive;

OWNER FLASHBACK_ARCHIVE_NAME RETENTION_IN_DAYS STATUS

SYS FP_PR_FLASH 3650 DEFAULT

SQL> select flashback_archive_name,tablespace_name,quota_in_mb

from dba_flashback_archive_ts;

FLASHBACK_ARCHIVE_NAME TABLESPACE_NAME QUOTA_IN_MB

FP_PR_FLASH FLASHBACK 4096

SQL> select table_name, owner_name, flashback_archive_name, archive_table_name

from dba_flashback_archive_tables;

TNAME OWNER FLASHBACK_ARCHI ARCHIVE_TABLE_NAME

EMP HR FP_PR_FLASH SYS_FBA_HIST_52126

DEPT HR FP_PR_FLASH SYS_FBA_HIST_52114

LOCATION HR FP_PR_FLASH SYS_FBA_HIST_52110

SQL > conn hr/hr

SQL> select * from DEPT where dno=10;

DNO DNAME LOC

10 Admin New York

SQL> SELECT CURRENT_TIMESTAMP FROM DUAL;

CURRENT_TIMESTAMP

12-Sep-2011 12:30:30

SQL> UPDATE DEPT SET LOC=’SAN FRANCISCO’ WHERE DNO=10;

SQL> COMMIT;

SQL> SELECT CURRENT_TIMESTAMP FROM DUAL;

CURRENT_TIMESTAMP

12-Sep-2011 12:40:30

SQL> SELECT * FROM DEPT AS OF TIMESTAMP

TO_TIMESTAMP(‘12-Sep-2011 12:30:30’,’dd-mon-yyyy hh24:mi:ss’);

DNO DNAME LOC

10 Admin New York

SQL> SELECT * FROM DEPT AS OF TIMESTAMP

TO_TIMESTAMP(‘12-Sep-2011 12:40:30’,’dd-mon-yyyy hh24:mi:ss’);

DNO DNAME LOC

10 Admin SAN FRANCISCO

SQL > select * from DEPT where dno=10;

DNO DNAME LOC

10 Admin SAN FRANCISCO

SQL> select dno,dname,loc from hr.SYS_FBA_HIST_52114;

DNO DNAME LOC

10 Admin SAN FRANCISCO

10 Admin NewYork

SQL> drop flashback archive fp_pr_flash;

SQL> alter flashback archive fp_pr_flash; /* Set as default */

SQL> alter flashback archive fp_pr_flash add tablespace FLASHBACK;

SQL> alter flashback archive fp_pr_flash modify tablespace FLASHBACK quota 10G;

SQL> alter flashback archive fp_pr_flash modify retention 2 year;

SQL> alter flashback archive fp_pr_flash purge all;

SQL> alter flashback archive fp_pr_flash purge before scn 123456;

Zounr's Oracle DBA, Developer Suit Blog

Backup and Recovery

Performance Tuning Interview Questions and Answers

Pages

Search This Blog