RMAN BACKUP STATUS REPORT

RMAN BACKUP STATUS REPORT

set linesize 1000

set pagesize 1000

select I.instance_name,R.SESSION_KEY, R.INPUT_TYPE, R.STATUS, R.start_time,R.end_time,"Minutes" 

from (

select

SESSION_KEY, INPUT_TYPE, STATUS,

to_char(START_TIME,'dd/mm/yyyy hh24:mi') start_time,

to_char(END_TIME,'dd/mm/yyyy hh24:mi') end_time,

round((end_time-start_time)*1440,2) "Minutes"

from V$RMAN_BACKUP_JOB_DETAILS

where to_char(start_time,'yyyymmdd') between '20230101' and '20231231' --and input_type='DB INCR'

order by session_key desc

) R, v$instance I 

where rownum < 10;

RMAN Database Full Backup & Recovery

RMAN Database Full Backup & Recovery

-Take RMAN DB FULL Backup 

-Simulate Failure 

-Start Database Recovery 

nohup rman target / cmdfile=/u01/mqm/MQM_RMAN_Backup_script.sh log=/u01/mqm/rman_fullbackup_db.log &

cat mqm_rmanbackup.txt

run

{

allocate channel t1 type disk;

backup format '/u01/mqm/full_db_%t_%p%p' database;

backup format '/u01/mqm/archive_%t_%p%p' archivelog all;

backup format '/u01/mqm/cntrl_%s_%p_%t' current controlfile;

Backup format '/u01/mqm/spfile_%s_%p_%t' spfile;

release channel t1;

}

cat rman_exec.sh 

rman target / cmdfile=mqm_rmanbackup.txt

nohup ./rman_exec.sh &

RMAN> list backup of database summary;

RMAN> list backup summary;

Simulate Failure:

Take the location of spfile, datafile & control file

SQL> select name from v$datafile;

SQL> select member from v$logfile;

SQL> select name from v$controlfile;

SQL> show parameter spfile;

SQL> create pfile from spfile;

File created.

Delete all the spfile, datafiles & Control files from the server.

SQL> startup;

ORACLE instance started.

Total System Global Area  801112064 bytes

Fixed Size     8797872 bytes

Variable Size   423625040 bytes

Database Buffers   360710144 bytes

Redo Buffers     7979008 bytes

ORA-00205: error in identifying control file, check alert log for more info

Restore Controlfile:

run

{

allocate channel c1 type disk;

set until time "to_date('2022/02/26 15:47:00','yyyy/mm/dd HH24:MI:SS')";

restore controlfile from '/u01/mqm/cntrl_23_1_1097682448';

release channel c1;

}

Bring the database at mount state

RMAN> alter database mount;

Statement processed

(or)

sqlplus / as sysdba

SQL> alter database mount;

Database altered.

Restore Database:

run 

{

allocate channel c1 type disk;

set until time "to_date('2022/02/26 15:47:00','yyyy/mm/dd HH24:MI:SS')";

restore database;

recover database;

release channel c1;

}

RMAN> alter database open resetlogs;

Statement processed

(or)

sqlplus / as sysdba

SQL> alter database open resetlogs;

Database altered.

Backup Based RMAN DUPLICATE Without Connecting To Target Database

Backup Based RMAN DUPLICATE Without Connecting To Target Database

If you are performing a backup based RMAN duplicate and using a recovery catalog as well, it is not required to connect to the source database as TARGET in RMAN.

This method is advantageous where network connections from the auxiliary host to the source database are restricted or prone to intermittent disruptions. In duplication without a TARGET connection, the source database is unaffected by the duplication.

Steps:

1.Prepare the shell script for backup and source the database:

vi rmanclone.sh

chmod 775 rmanclone.sh

ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
BACKUP_LOG_PATH=/home/oracle
export ORACLE_BASE
export ORACLE_HOME
export ORACLE_SID=GTP2PROD
export BACKUP_LOG_PATH
LOG_FILE=${BACKUP_LOG_PATH}/clone_db.log
$ORACLE_HOME/bin/rman msglog=${LOG_FILE} <<EOF

connect target /

run {
allocate channel t1 type disk;
allocate channel t2 type disk;
backup database format '/u01/rman_bkp/database_%d_%u_%s';
release channel t1;
release channel t2;
}
sql 'alter system archive log current';
run {
allocate channel a1 type disk;
allocate channel a2 type disk;
backup archivelog all format '/u01/rman_bkp/arch_%d_%u_%s';
release channel a1;
release channel a2;

}

run {
allocate channel c1 type disk;
backup current controlfile format '/u01/rman_bkp/control_%d_%u_%s';
release channel c1;
}

exit;

EOF

chmod 775 rmanclone.sh

nohup ./rmanclone.sh &

jobs -l


2.Verify the backups and transfer to destination side:

[oracle@host01 rman_bkp]$ ls -ltrh

total 1.2G

-rw-r----- 1 oracle oinstall 405M Apr 29 22:51 database_GTP2PROD_0ivtht5d_18
-rw-r----- 1 oracle oinstall 9.7M Apr 29 22:52 database_GTP2PROD_0jvtht8e_19
-rw-r----- 1 oracle oinstall 96K Apr 29 22:52 database_GTP2PROD_0kvtht8m_20
-rw-r----- 1 oracle oinstall 600M Apr 29 22:52 database_GTP2PROD_0hvtht5d_17
-rw-r----- 1 oracle oinstall 45M Apr 29 22:52 arch_GTP2PROD_0mvtht8v_22
-rw-r----- 1 oracle oinstall 52M Apr 29 22:52 arch_GTP2PROD_0lvtht8v_21
-rw-r----- 1 oracle oinstall 6.6M Apr 29 22:52 arch_GTP2PROD_0nvtht9f_23
-rw-r----- 1 oracle oinstall 9.7M Apr 29 22:52 control_GTP2PROD_0ovtht9i_24

scp -p * oracle@host02:/u01/oracle/rman_backups

3.Create the required directories in destination side:

mkdir -p /u01/app/oracle/admin/MQMARC/adump
mkdir -p /u01/app/oracle/oradata/MQMARC
mkdir -p /u01/app/oracle/fast_recovery_area/MQMARC
mkdir -p /u01/app/oracle/fast_recovery_area


4.Prepare pfile and nomount the database:

vi initMQMARC.ora';

MQMARC.__db_cache_size=2415919104
MQMARC.__java_pool_size=16777216
MQMARC.__large_pool_size=16777216
MQMARC.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
MQMARC.__pga_aggregate_target=1040187392
MQMARC.__sga_target=3103784960
MQMARC.__shared_io_pool_size=0
MQMARC.__shared_pool_size=603979776
MQMARC.__streams_pool_size=16777216
*.audit_file_dest='/u01/app/oracle/admin/MQMARC/adump'
*.audit_trail='none'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/MQMARC/control01.ctl','/u01/app/oracle/fast_recovery_area/MQMARC/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='MQMARC'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=MQMARCXDB)'
*.open_cursors=300
*.pga_aggregate_target=1032847360
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=3098542080
*.undo_tablespace='UNDOTBS1'
*.db_flashback_retention_target=4320
*.global_names=TRUE
#*.local_listener='MQMARC'
*.log_file_name_convert='/u01/app/oracle/oradata/GTP2PROD/','/u01/app/oracle/oradata/MQMARC/'
*.db_file_name_convert='/u01/app/oracle/oradata/GTP2PROD/','/u01/app/oracle/oradata/MQMARC/'

startup nomount pfile='$ORACLE_HOME/dbs/initMQMARC.ora';


5.Run the RMAN duplicate command:

vi clone_MQMARC_db.sh

ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
BACKUP_LOG_PATH=/home/oracle
export ORACLE_BASE
export ORACLE_HOME
export ORACLE_SID=MQMARC
export BACKUP_LOG_PATH
LOG_FILE=${BACKUP_LOG_PATH}/clone_mqmarc_db.log
/u01/app/oracle/product/11.2.0/dbhome_1/bin/rman msglog=${LOG_FILE} <<EOF
connect auxiliary /
run {
allocate auxiliary channel t1 type disk;
allocate auxiliary channel t2 type disk;
duplicate target database to MQMARC backup location '/u01/oracle/backup';
}
exit
EOF

chmod 775 clone_MQMARC_db.sh
nohup ./clone_MQMARC_db.sh &

6.Connect to database and check the status:

SQL> select name, open_mode, status from v$database,v$instance;
NAME OPEN_MODE STATUS
--------- -------------------- ------------
MQMARC READ WRITE OPEN

Note:

-RMAN will internally changed the SID during the duplicate command execution.
-It will open the database with resetlog option.
-Flashback is automatically on by duplicate command.

Script To Schedule RMAN Backups In Crontab

Script To Schedule RMAN Backups In Crontab

vi rman_full_bkp.sh

# $Header: rman_full_bkp.sh

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

# Script to be used on the crontab to schedule an RMAN Full Backup

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

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

# VARIABLES To be Modified by the user to match the Environment:

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

# INSTANCE Name: [Replace ${ORACLE_SID} with your instance SID]

ORACLE_SID=MQPROD

# ORACLE_HOME Location: [Replace ${ORACLE_HOME} with the right ORACLE_HOME path]

ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1

# Backup Location: [Replace /backup/rmanfull with the backup location path]

BACKUPLOC=/u01/app/oracle/example_backups

# Backup LOG location:

RMANLOG=/u01/app/oracle/example_backups/rmanfull.log

# COMPRESSED BACKUP option:[Y|N] [Default ENABLED]

COMPRESSION=Y

# Perform Maintenance based on below Backup & Archivelog Retention: [Y|N] [Default DISABLED]

MAINTENANCEFLAG=N

# Backup Retention "In Days": [Backups older than this retention will be deleted]

BKP_RETENTION=7

# Archives Deletion "In Days": [Archivelogs older than this retention will be deleted]

ARCH_RETENTION=7

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

# GENERIC VARIABLES: [Can be left without modification]

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

# MAX BACKUP Piece Size: [Must be BIGGER than the size of the biggest datafile in the database]

MAX_BKP_PIECE_SIZE=1g

# Show the full DATE and TIME details in the backup log:

NLS_DATE_FORMAT='DD-Mon-YYYY HH24:MI:SS'

export ORACLE_SID

export ORACLE_HOME

export BACKUPLOC

export COMPRESSION

export BKP_RETENTION

export ARCH_RETENTION

export MAX_BKP_PIECE_SIZE

export RMANLOG

export NLS_DATE_FORMAT

export MAINTENANCEFLAG

# Check the selected COMPRESSION option:

case ${COMPRESSION} in

Y|y|YES|Yes|yes|ON|on)

COMPRESSED_BKP="AS COMPRESSED BACKUPSET"

export COMPRESSED_BKP

;;

*)

COMPRESSED_BKP=""

export COMPRESSED_BKP

;;

esac

# Check the selected MAINTENANCE option:

case ${MAINTENANCEFLAG} in

Y|y|YES|Yes|yes|ON|on)

HASH_MAINT=""

export HASH_MAINT

;;

*)

HASH_MAINT="#"

export COMPRESSED_BKP

;;

esac

# Append the date to the backup log for each script execution:

echo "----------------------------" >> ${RMANLOG}

date >> ${RMANLOG}

echo "----------------------------" >> ${RMANLOG}

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

# RMAN SCRIPT Section:

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

${ORACLE_HOME}/bin/rman target / msglog=${RMANLOG} <<EOF

# Configuration Section:

# ---------------------

${HASH_MAINT}CONFIGURE BACKUP OPTIMIZATION ON;

${HASH_MAINT}CONFIGURE CONTROLFILE AUTOBACKUP ON;

${HASH_MAINT}CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '${BACKUPLOC}/%F';

${HASH_MAINT}CONFIGURE SNAPSHOT CONTROLFILE NAME TO '${ORACLE_HOME}/dbs/snapcf_${ORACLE_SID}.f';

## Avoid Deleting archivelogs NOT yet applied on the standby: [When FORCE is not used]

#CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

# Maintenance Section:

# -------------------

## Crosscheck backups/copied to check for expired backups which are physically not available on the media:

${HASH_MAINT}crosscheck backup completed before 'sysdate-${BKP_RETENTION}' device type disk;

${HASH_MAINT}crosscheck copy completed before 'sysdate-${BKP_RETENTION}' device type disk;

## Report & Delete Obsolete backups which don't meet the RETENTION POLICY:

${HASH_MAINT}REPORT OBSOLETE RECOVERY WINDOW OF ${BKP_RETENTION} DAYS device type disk;

${HASH_MAINT}DELETE NOPROMPT OBSOLETE RECOVERY WINDOW OF ${BKP_RETENTION} DAYS device type disk;

## Delete All EXPIRED backups/copies which are not physically available:

${HASH_MAINT}DELETE NOPROMPT EXPIRED BACKUP COMPLETED BEFORE 'sysdate-${BKP_RETENTION}' device type disk;

${HASH_MAINT}DELETE NOPROMPT EXPIRED COPY COMPLETED BEFORE 'sysdate-${BKP_RETENTION}' device type disk;

## Crosscheck Archivelogs to avoid the backup failure:

${HASH_MAINT}CHANGE ARCHIVELOG ALL CROSSCHECK;

${HASH_MAINT}DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;

## Delete Archivelogs older than ARCH_RETENTION days:

${HASH_MAINT}DELETE NOPROMPT archivelog all completed before 'sysdate -${ARCH_RETENTION}';

# Full Backup Script starts here: [Compressed+Controlfile+Archives]

# ------------------------------

run{

allocate channel F1 type disk;

allocate channel F2 type disk;

sql 'alter system archive log current';

BACKUP ${COMPRESSED_BKP}

#MAXSETSIZE ${MAX_BKP_PIECE_SIZE}

NOT BACKED UP SINCE TIME 'SYSDATE-2/24'

INCREMENTAL LEVEL=0

FORMAT '${BACKUPLOC}/%d_%t_%s_%p.bkp' 

FILESPERSET 100

TAG='FULLBKP'

DATABASE include current controlfile PLUS ARCHIVELOG NOT BACKED UP SINCE TIME 'SYSDATE-2/24';

## Backup the controlfile separately:

BACKUP ${COMPRESSED_BKP} CURRENT CONTROLFILE FORMAT '${BACKUPLOC}/CONTROLFILE_%d_%I_%t_%s_%p.bkp' TAG='CONTROLFILE_BKP' REUSE ;

## Trace backup of Controlfile & SPFILE:

SQL "ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS ''${BACKUPLOC}/controlfile.trc'' REUSE";

SQL "CREATE PFILE=''${BACKUPLOC}/init${ORACLE_SID}.ora'' FROM SPFILE";

}

EOF

34 12 * * * "/home/oracle/rman_full_bkp.sh" > /tmp/rmanarch.log

Tue Oct 27 12:32:39 IST 2020

Recover dropped tablespace using RMAN tablespace point in time recovery (TSPITR)

Recover dropped tablespace using RMAN tablespace point in time recovery (TSPITR)

Step 1: Create tablespace

SQL> conn / as sysdba

Connected.

SQL> select name from v$datafile;

NAME

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

/u01/app/oracle/oradata/QTEST/system01.dbf

/u01/app/oracle/oradata/QTEST/sysaux01.dbf

/u01/app/oracle/oradata/QTEST/undotbs01.dbf

/u01/app/oracle/oradata/QTEST/users01.dbf

SQL> create tablespace testtbs datafile '/u01/app/oracle/oradata/QTEST/testtbs01.dbf' size 100m;

Tablespace created.

SQL> create user testtbs identified by testtbs default tablespace testtbs;

User created.

SQL> grant dba to testtbs;

Grant succeeded.

SQL> conn testtbs/testtbs;

Connected.

SQL> create table test(empname varchar2(20),city varchar2(20));

Table created.

SQL> insert into test values('qader','bahrain');

1 row created.

SQL> insert into test values('kadar','manama');

1 row created.

SQL> commit;

Step 2: Backup database Plus archivelog

[oracle@node2 ~]$ rman target sys/Welcome1

Recovery Manager: Release 12.1.0.2.0 - Production on Sat Aug 29 01:18:39 2020

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

connected to target database: QTEST (DBID=2589001473)

RMAN> backup database plus archivelog;

Step 3: Note Current SCN

SQL> conn / as sysdba

Connected.

SQL> SQL> select current_scn from v$database;

CURRENT_SCN

-----------

    1744628

Step 4: Drop tablespace

SQL> drop tablespace testtbs including contents and datafiles;

Tablespace dropped.

Step 5: Create auxiliary & Recover tablespace using tablespace point in time recovery.

[oracle@node2 ~]$ rman target sys/Welcome1

Recovery Manager: Release 12.1.0.2.0 - Production on Sat Aug 29 01:29:27 2020

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

connected to target database: QTEST (DBID=2589001473)

RMAN> run

{

recover tablespace testtbs

until scn 1744628

auxiliary destination '/u01/app/oracle/fast_recovery_area/QTEST'; ------> backup pieces location

}

initialization parameters used for automatic instance:

db_name=QTEST

db_unique_name=hsbr_pitr_QTEST

compatible=12.1.0.2.0

db_block_size=8192

db_files=200

diagnostic_dest=/u01/app/oracle

_system_trig_enabled=FALSE

sga_target=1168M

processes=200

db_create_file_dest=/u01/app/oracle/fast_recovery_area/QTEST

log_archive_dest_1='location=/u01/app/oracle/fast_recovery_area/QTEST'

#No auxiliary parameter file used

starting up automatic instance QTEST

Oracle instance started

Total System Global Area    1224736768 bytes

Fixed Size                     2923824 bytes

Variable Size                335545040 bytes

Database Buffers             872415232 bytes

Redo Buffers                  13852672 bytes

Automatic instance created

List of tablespaces that have been dropped from the target database:

Tablespace TESTTBS

contents of Memory Script:

{

# set requested point in time

set until  scn 1744628;

sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';

}

executing Memory Script

executing command: SET until clause

Starting restore at 29-AUG-20

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=23 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/app/oracle/fast_recovery_area/QTEST/QTEST/controlfile/o1_mf_hnm1rzmd_.ctl

Finished restore at 29-AUG-20

contents of Memory Script:

{

# set requested point in time

 "/u01/app/oracle/oradata/QTEST/testtbs01.dbf";

# switch all tempfiles

switch clone tempfile all;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile  1, 4, 3, 5;

 switch clone datafile all;

}

executing Memory Script

renamed tempfile 1 to /u01/app/oracle/fast_recovery_area/QTEST/QTEST/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 29-AUG-20

archived log file name=/u01/app/oracle/fast_recovery_area/QTEST/archivelog/2020_08_29/o1_mf_1_37_hnm1rjny_.arc thread=1 sequence=37

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

Finished recover at 29-AUG-20

database opened

contents of Memory Script:

{

sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''

Performing export of metadata...

   EXPDP> Starting "SYS"."TSPITR_EXP_hsbr_sAEw":  

   EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE

   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS

   EXPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER

   EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

   EXPDP> Master table "SYS"."TSPITR_EXP_hsbr_sAEw" successfully loaded/unloaded

   EXPDP> ******************************************************************************

   EXPDP> Dump file set for SYS.TSPITR_EXP_hsbr_sAEw is:

   EXPDP>   /u01/app/oracle/fast_recovery_area/QTEST/tspitr_hsbr_12180.dmp

   EXPDP> ******************************************************************************

   EXPDP> Datafiles required for transportable tablespace TESTTBS:

   EXPDP>   /u01/app/oracle/oradata/QTEST/testtbs01.dbf

   EXPDP> Job "SYS"."TSPITR_EXP_hsbr_sAEw" successfully completed at Sat Aug 29 04:09:12 2020 elapsed 0 00:01:11

Export completed

contents of Memory Script:

# shutdown clone before import

shutdown clone abort

executing Memory Script

Oracle instance shut down

Performing import of metadata...

   IMPDP> Master table "SYS"."TSPITR_IMP_hsbr_rhej" successfully loaded/unloaded

   IMPDP> Starting "SYS"."TSPITR_IMP_hsbr_rhej":  

   IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

   IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE

   IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS

   IMPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER

   IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

   IMPDP> Job "SYS"."TSPITR_IMP_hsbr_rhej" successfully completed at Sat Aug 29 04:09:53 2020 elapsed 0 00:00:32

Import completed

contents of Memory Script:

auxiliary instance file /u01/app/oracle/fast_recovery_area/QTEST/QTEST/onlinelog/o1_mf_1_hnm1sp84_.log deleted

auxiliary instance file /u01/app/oracle/fast_recovery_area/QTEST/QTEST/datafile/o1_mf_sysaux_hnm1s60l_.dbf deleted

auxiliary instance file /u01/app/oracle/fast_recovery_area/QTEST/QTEST/datafile/o1_mf_undotbs1_hnm1s60s_.dbf deleted

auxiliary instance file /u01/app/oracle/fast_recovery_area/QTEST/QTEST/datafile/o1_mf_system_hnm1s60g_.dbf deleted

auxiliary instance file /u01/app/oracle/fast_recovery_area/QTEST/QTEST/controlfile/o1_mf_hnm1rzmd_.ctl deleted

auxiliary instance file tspitr_hsbr_12180.dmp deleted

Finished recover at 29-AUG-20

RMAN Restore/Recover a database UNTIL TIME

RMAN Restore/Recover a database UNTIL TIME

Below are the steps for Restoring/ Recovering an Oracle database until a specific time in the past:

1) Set the environment:

on Windows:

set ORACLE_SID=DB10

echo %ORACLE_SID%

on AIX/Linux:

export $ORACLE_SID=DB10

echo $ORACLE_SID

2) Connect to rman:

rman target / nocatalog

3) Start the database in "mount" state:

startup mount;

4) Run the following :

Restore:

restore database UNTIL TIME "to_date('08/07/2020 06:15:00 pm','mm/dd/yyyy hh:mi:ss am')";

Recover:

recover database UNTIL TIME "to_date('08/07/2020 06:15:00 pm','mm/dd/yyyy hh:mi:ss am')";

alter database open resetlogs;

OR

run

{

set UNTIL TIME "to_date('08/07/2020 06:15:00 pm','mm/dd/yyyy hh:mi:ss am')";

restore database;

recover database;

alter database open resetlogs;

}

Note: 

We cannot restore/recover the database in "open" state, below error you faced while doing it.

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 08/07/2020 18:50:14

ORA-19870: error while restoring backup piece C:\XE_INSTALL\BACKUP\FULL08V79FL2

ORA-19573: cannot obtain exclusive enqueue for datafile 2

Deleting Old Archivelog Files Using RMAN

Deleting Old Archivelog Files Using RMAN

Use below rman script to delete archive logs older than 2 days.

rman target /
DELETE ARCHIVELOG ALL COMPLETED BEFORE 'sysdate-2';
CROSSCHECK ARCHIVELOG ALL;
DELETE EXPIRED ARCHIVELOG ALL;

For standby database:

rman target /
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
DELETE ARCHIVELOG ALL COMPLETED BEFORE 'sysdate-2';
CROSSCHECK ARCHIVELOG ALL;
DELETE EXPIRED ARCHIVELOG ALL;

Backup-Based Duplication Database with RMAN

Backup-Based Duplication Database with RMAN

RMAN creates the duplicate database by using pre-existing RMAN backups and copies. RMAN can perform backup-based duplication with or without either of the following connections:

* Target
* Recovery catalog

How RMAN duplicates a database:

As a part of the duplicating operation, RMAN automates the following steps:-

1) Creates a default server parameter file for the auxiliary instance.
2) Mounts the restored or copied backup control file from the active database.
3) Uses the RMAN repository to select the backups for restoring the data files to the auxiliary instance.
4) Restores and copies the duplicate data files and recovers them with incremental backups and archived redo log files to a noncurrent point in time.
5) Shuts down and restarts the database instance in NOMOUNT mode.
6) Creates a new control file, which then creates and stores the new DBID in the data files.
7) Opens the duplicate database with the RESETLOGS option and creates the online redo log for the new database.

Actual Steps:

1) Create a backup of the source database:

$ rman target /

RMAN> backup AS COMPRESSED BACKUPSET full database tag ORCL_FULL format '/u01/backup/full%u' ;
RMAN> backup current controlfile format '/u01/backup/ctl%u';
RMAN> backup tag ORCL_ARCHIVE format '/u01/backup/%d_%T_%s_%p_ARCHIVE' archivelog all;
RMAN> backup spfile format '/u01/backup/spfile%u';

2) Transfer the backup from source to target:

scp -r * rac2:/u01/backup

3) Create a password file for the duplicate instance or source and target side.

cd $ORACLE_HOME/dbs
orapwd file=orapw$ORACLE_SID password=oracle force=y

Notes: The Oracle orapwd command line utility assists the DBA with granting SYSDBA and SYSOPER privileges to other users.

4) Prepare pfile and add only two parameters:

vi initQTEST.ora

db_name=QTEST
audit_file_dest=/u01/app/oracle/admin/QPROD/adump/

5) Startup the database in nomount with a pfile.

SQL> startup nomount pfile='initQTEST.ora';
ORACLE instance started.
Total System Global Area  238034944 bytes
Fixed Size                  2227136 bytes
Variable Size             180356160 bytes
Database Buffers           50331648 bytes
Redo Buffers                5120000 bytes
SQL> host

5) Connect to rman with auxiliary command.

[oracle@rac2 dbs]$ rman auxiliary /
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Mar 3 00:22:39 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to auxiliary database: QTEST (not mounted)

RMAN> DUPLICATE DATABASE TO QTEST
SPFILE set control_files='/u01/QTEST/c1.ctl' set db_file_name_convert='/u09/QPROD','/u01/QTEST' set log_file_name_convert='/u09/QPROD','/u01/QTEST'
BACKUP LOCATION '/u01/backup';

RMAN Duplicate Output:

Starting Duplicate Db at 03-MAR-20
contents of Memory Script:
{
   restore clone spfile to  '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileQTEST.ora' from
 '/u01/backup/spfile1iuq48k6';
   sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileQTEST.ora''";
}
executing Memory Script
Starting restore at 03-MAR-20
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK
channel ORA_AUX_DISK_1: restoring spfile from AUTOBACKUP /u01/backup/spfile1iuq48k6
channel ORA_AUX_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 03-MAR-20

sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileQTEST.ora''

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''QTEST'' comment=
 ''duplicate'' scope=spfile";
   sql clone "alter system set  control_files =
 ''/u01/QTEST/c1.ctl'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_file_name_convert =
 ''/u09/QPROD'', ''/u01/QTEST'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_file_name_convert =
 ''/u09/QPROD'', ''/u01/QTEST'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script
sql statement: alter system set  db_name =  ''QTEST'' comment= ''duplicate'' scope=spfile
sql statement: alter system set  control_files =  ''/u01/QTEST/c1.ctl'' comment= '''' scope=spfile
sql statement: alter system set  db_file_name_convert =  ''/u09/QPROD'', ''/u01/QTEST'' comment= '''' scope=spfile
sql statement: alter system set  log_file_name_convert =  ''/u09/QPROD'', ''/u01/QTEST'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area    2689060864 bytes
Fixed Size                     2231392 bytes
Variable Size                587203488 bytes
Database Buffers            2097152000 bytes
Redo Buffers                   2473984 bytes
contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''QPROD'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''QTEST'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile from  '/u01/backup/ctl1fuq48c8';
   alter clone database mount;
}
executing Memory Script
sql statement: alter system set  db_name =  ''QPROD'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set  db_unique_name =  ''QTEST'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area    2689060864 bytes
Fixed Size                     2231392 bytes
Variable Size                587203488 bytes
Database Buffers            2097152000 bytes
Redo Buffers                   2473984 bytes

Starting restore at 03-MAR-20
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 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=/u01/QTEST/c1.ctl
Finished restore at 03-MAR-20

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

contents of Memory Script:
{
   set until scn  1082431;
   set newname for datafile  1 to
 "/u01/QTEST/system01.dbf";
   set newname for datafile  2 to
 "/u01/QTEST/sysaux01.dbf";
   set newname for datafile  3 to
 "/u01/QTEST/undotbs01.dbf";
   set newname for datafile  4 to
 "/u01/QTEST/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 03-MAR-20
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 /u01/QTEST/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/QTEST/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/QTEST/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/QTEST/users01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/backup/full1euq48b5
channel ORA_AUX_DISK_1: piece handle=/u01/backup/full1euq48b5 tag=ORCL_FULL
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 03-MAR-20
contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=1034036763 file name=/u01/QTEST/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=1034036763 file name=/u01/QTEST/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=1034036763 file name=/u01/QTEST/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=1034036763 file name=/u01/QTEST/users01.dbf
contents of Memory Script:
{
   set until scn  1082431;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script
executing command: SET until clause
Starting recover at 03-MAR-20
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=9
channel ORA_AUX_DISK_1: reading from backup piece /u01/backup/QPROD_20200303_48_1_ARCHIVE
channel ORA_AUX_DISK_1: piece handle=/u01/backup/QPROD_20200303_48_1_ARCHIVE tag=ORCL_ARCHIVE
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=/u01/app/oracle/fast_recovery_area/QTEST/archivelog/2020_03_03/o1_mf_1_9_h5top40c_.arc thread=1 sequence=9
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/QTEST/archivelog/2020_03_03/o1_mf_1_9_h5top40c_.arc RECID=1 STAMP=1034036764
media recovery complete, elapsed time: 00:00:00
Finished recover at 03-MAR-20
Oracle instance started
Total System Global Area    2689060864 bytes
Fixed Size                     2231392 bytes
Variable Size                587203488 bytes
Database Buffers            2097152000 bytes
Redo Buffers                   2473984 bytes
contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''QTEST'' 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
sql statement: alter system set  db_name =  ''QTEST'' 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    2689060864 bytes
Fixed Size                     2231392 bytes
Variable Size                587203488 bytes
Database Buffers            2097152000 bytes
Redo Buffers                   2473984 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "QTEST" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP   1 ( '/u01/QTEST/redo01.log' ) SIZE 50 M  REUSE,
  GROUP   2 ( '/u01/QTEST/redo02.log' ) SIZE 50 M  REUSE,
  GROUP   3 ( '/u01/QTEST/redo03.log' ) SIZE 50 M  REUSE
 DATAFILE
  '/u01/QTEST/system01.dbf'
 CHARACTER SET WE8MSWIN1252
contents of Memory Script:
{
   set newname for tempfile  1 to
 "/u01/QTEST/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/u01/QTEST/sysaux01.dbf",
 "/u01/QTEST/undotbs01.dbf",
 "/u01/QTEST/users01.dbf";
   switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/QTEST/temp01.dbf in control file
cataloged datafile copy
datafile copy file name=/u01/QTEST/sysaux01.dbf RECID=1 STAMP=1034036771
cataloged datafile copy
datafile copy file name=/u01/QTEST/undotbs01.dbf RECID=2 STAMP=1034036771
cataloged datafile copy
datafile copy file name=/u01/QTEST/users01.dbf RECID=3 STAMP=1034036771

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=1034036771 file name=/u01/QTEST/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=1034036771 file name=/u01/QTEST/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=1034036771 file name=/u01/QTEST/users01.dbf

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 03-MAR-20

RMAN Active cloning method

RMAN Active cloning method 

EXAMPLE:

SOURCE DB – QPROD ( Also called target instance )
DESTINATION DB – QTEST ( Also called auxiliary instance )

NOTE – If your auxiliary instance already exists, then drop the database before starting the cloning.


1. Add the tns entry of the both database in tnsnames.ora file of DESTINATION host :

-- source db tns :

QPROD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.oracle.com)(PORT = 1528))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = QPROD)
    )
  )


--Target db tns :


QTEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.oracle.com)(PORT = 1533))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = QTEST)
    )
  )


2. Create a listener for the target db ( with static registration)

 LISTENER_QTEST =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.oracle.com)(PORT = 1533))
    ))


SID_LIST_LISTENER_QTEST =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = QTEST)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = TESTDB )
    ))


-- START THE LISTENER

lsnrctl start LISTENER_TESTDB


3. Copy the pfile from source host :

Copy the pfile from source host to target host and modify the parameters like control_files,diagnostic_dest,audit_dump .
Apart from that add below two (mandatory) parameters in the the pfile of target db pfile.

*.db_file_name_convert = '/u09/QPROD', '/u01/QTEST'('< source db db file location> ','< target db db file location>')
*.log_file_name_convert= '/u09/QPROD', '/u01/QTEST'('< sourcec db redo log location>','<target db redo log location')


NOTE – FOR RAC DATABASE:

alter system set cluster_database=FALSE scope=spfile sid='*';
alter system set log_file_name_convert=’+REDOA/PROD/ONLINELOG’,’+REDO01/TEST/ONLINELOG’,’+REDOB/PROD/ONLINELOG’,’+REDO02/TEST/ONLINELOG’,’+PRODARCH02′,’+TESTARCH’  scope=spfile sid=’*’;
alter system set db_create_file_dest=’+DATA’ scope=spfile sid='*';

4. Create password file on both source and target db ( keep same password )

-- SOURCE DB ( QPROD)

cd $ORACLE_HOME/dbs
orapwd file=orapw$ORACLE_SID password=oracle force=y

-- TARGET DB ( QTEST)

cd $ORACLE_HOME/dbs
orapwd file=orapw$ORACLE_SID password=oracle force=y

NOTE – FOR RAC DB,create password file as below.
orapwd file=’+DATA’ dbuniquename=TESTDB
Enter password for SYS:

5. Start the target db/auxiliary instance ( QTEST) in nomount state:

export ORACLE_SID=QTEST
SQL> create spfile from pfile ;
SQL> startup nomount

6. Check the connection to both source and target as below

run the below command from QTEST:
rman target sys/oracle@< source db_tns_name>  auxiliary sys/oracle@<target db_tns_name>
i.e
rman target sys/oracle@QPROD auxiliary sys/oracle@QTEST
If you are getting any error while running this command, then fix the same, before proceeding further.

7 . Start the cloning:

Now run the below rman script from target db host:
rman target sys/oracle@PRODDB auxiliary sys/oracle@TESTDB

run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate auxiliary channel aux1 type disk;
allocate auxiliary channel aux2 type disk;
allocate auxiliary channel aux3 type disk;
allocate auxiliary channel aux4 type disk;
duplicate target database to QTEST from active database USING  BACKUPSET ;
}

Once this script completed, it will open the target db (QTEST) in resetlog mode.  With this cloning completes.

NOTE: If your oracle version is 11g, then use the below rman script.

run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate auxiliary channel aux1 type disk;
allocate auxiliary channel aux2 type disk;
allocate auxiliary channel aux3 type disk;
allocate auxiliary channel aux4 type disk;
duplicate target database to QTEST from active database;
}

for excluding particular tablespace while cloning:

Excluding tablespace Q_DATA.

run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate auxiliary channel aux1 type disk;
allocate auxiliary channel aux2 type disk;
allocate auxiliary channel aux3 type disk;
allocate auxiliary channel aux4 type disk;
duplicate target database to QTEST from active database SKIP TABLESPACE "Q_DATA";
}

RMAN Encrypt Backup in Oracle

RMAN Encrypt Backup in Oracle

Configure RMAN Backups With Password Protection

rman target /
RMAN> set encryption on identified by ‘yourpassword’ only;

You can backup with backup database command. Do not need to change any backup script.

You can change encryption algorithm on RMAN Configuration.

Show Encryption Algorithm

SQL> select algorithm_id, algorithm_name, algorithm_description, is_default from v$rman_encryption_algorithms;

Change Encryption Algorithm

RMAN> show encryption algorithm;
RMAN>configure encryption algorithm ‘AES256’;

If you want to restore from encrypt backup, you can use below decrypt backup.

Decrypt RMAN Backup

RMAN> set decryption identified by ‘yourpassword’;

RMAN 11G : Data Recovery Advisor - RMAN command line example 

What Is the Data Recovery Advisor?

The Data Recovery Advisor is a tool that helps you to diagnose and repair data failures and corruptions. The Data Recovery Advisor analyzes failures based on symptoms and intelligently determines optimal repair strategies. The tool can also automatically repair diagnosed failures.

The Data Recovery Advisor is available from Enterprise Manager (EM) Database Control and Grid Control. You can also use it via the RMAN command-line.

This DRA commands are available within RMAN:

List Failure     # lists the results of previously executed failure assessments. Revalidates existing failures and closes them, if possible.
Advise Failure   # presents manual and automatic repair options
Repair Failure   # automatically fix failures by running optimal repair option, suggested by ADVISE FAILURE. Revalidates existing failures when completed.
Change Failure # enables you to change the status of failures.

Restrctions:

Data Recovery Advisor supports single-instance databases. Oracle Real Application Clusters databases are not supported in 11.1.0.6 -> 11.1.0.8. Data Recovery Advisor cannot use blocks or files transferred from a standby database to repair failures on a primary database. Also, you cannot use Data Recovery Advisor to diagnose and repair failures on a standby database. However, the Data Recovery Advisor does support failover to a standby database as a repair option (as mentioned above).

Examples:

RMAN> list failure;
RMAN> advise failure;
RMAN> repair failure;
RMAN> change failure 522 closed;

Others Command:

RMAN> list failure low;
RMAN> list failure high;
RMAN> list failure critical;
RMAN> repair failure preview;
RMAN> change failure 522 priority low;

Not able to connect as rman catalog "RMAN-04004: error from recovery catalog database: ORA-12154: TNS:could not resolve the connect identifier specified"

If you have created catalog user and you want to connect to rman with that user and you are getting the following error-

RMAN-04004: error from recovery catalog database:
ORA-12154: TNS:could not resolve the connect identifier specified

see below errors--

rman catalog = catalog/metalog@catdb
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Feb 23 15:02:03 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04004: error from recovery catalog database: ORA-12154: TNS:could not resolve the connect identifier specified

You are trying with connect identifier on same machine on which you have database like @catdb in this command.

Action - Don't use @catdb because if you are using same server to connect there is no need to give connect identifier.

export ORACLE_SID=catdb

-bash-4.1$ rman catalog = catalog/metalog

by giving this you will easily connect to your rman.

If you are trying to connect to catalog database on different host then you have to create tns entry in tnsnames.ora file .

like  ----

catdb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = hostname )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = DB_NAME)

RMAN Configurations

RMAN Configurations

-CONFIGURE RETENTION POLICY

It used with the 2 different options. Recovery Window or Redundancy

Redundancy: CONFIGURE RETENTION POLICY TO REDUNDANCY 1;

If you set Redundancy to 1, RMAN will keep one backup. If you take second backup , RMAN will sign previous backup as obsolete. You can delete obsolete backup with “delete obsolete” command.

Recovery Windows: CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;

If you set Recovey windows to 3 days, RMAN will sign backups as obsolete older than 3 days.
For example, we have 4 backups. And our backup dates are 16 may, 18 may, 21 may and 23 may.

If we set Recovery window to 3 days, RMAN must sign 16may and 18 may backups as obsolete
(23may – 3 days=20may). But RMAN signs 18may backup as not obsolete. Because RMAN automatically detects backups. And 18may backup is needed for recovery of 20may.

-CONFIGURE DEFAULT DEVICE TYPE

Backup can be taken two different locations. Tape and Disk

For backing up to disk: CONFIGURE DEFAULT DEVICE TYPE TO DISK;

For backing up to tape: CONFIGURE DEFAULT DEVICE TYPE TO SBT;

-CONFIGURE CONTROLFILE AUTOBACKUP ON/OFF

Automatically backups the controlfile to fra. (If you dont set FRA then it puts backup of

controlfile to $ORACLE_HOME/dbs by default)

-CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F’

By default, RMAN automatically names control file backups and  saves them into FRA. To configure RMAN  to write control file backups to the specific directory:  %F will generate a unique filename likes  c-‘IIIIIIIIII-YYYYMMDD-QQ’ . Here  ‘IIIIIIIIII’ is DBID, ‘YYYYMMDD’ is date and  ‘QQ’ is hexadecimal id. You can also backup controlfile to specific location. For example;

-CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘uo1/oradata/cf_%F’;

You can also reset configuration.

-CONFIGURE CONTROLFILE AUTOBACKUP FOR DEVICE TYPE DISK CLEAR;

-To change  the location of RMAN backups:

RMAN> configure channel device type disk format '/u01/app/oracle/oradata/orcl/backup/%U' maxpiecesize 8 G;


You can also write in RUN script.

RMAN> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE sbt TO 'controlfile_%F';
RMAN> BACKUP AS COPY DATABASE;
RMAN> RUN {
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/tmp/%F.bck';
BACKUP AS BACKUPSET DEVICE TYPE DISK DATABASE;
}

Format Description

%a Specifies the activation id number of the database.
%A Specifies the activation id number of the database completed 0
%c Specifies the copy number of the backup piece within a set of duplexed backup pieces. The maximum value is 256.
%d Specifies the name of the database
%D Specifies the current day of the month in format DD
%e Specifies the archived log sequence number
%f Specifies the absolute file number
%F Combines the DBID, day, month, year, and sequence into a unique and repeatable generated name
%h Specifies the archived redo log thread number
%I Specifies the DBID
%M Specifies the month in format MM
%n Specifies the name of the database. padded on the right with x characters to a total length of eight characters.
%N Specifies the tablespace name.
%p Specifies the piece number within the backup set
%r Resetlogs ID
%s Specifies the backup set number
%S Specifies the backup set number completed 0
%t Specifies the backup set time stamp.
%T Specifies the year, month, and day in format YYYYMMDD
%u Specifies an 8-character name constituted by compressed representations of the backup set or image copy number.
%U Specifies a system-generated unique file name.
%Y Specifies the year in this format: YYYY.

-CONFIGURE BACKUP OPTIMIZATION OFF/ON

If you set this configuration to ON then the backup command skips backing up files when the identical file has already been backed up. RMAN uses to determine whether a file is identical to a file that it is backed up with following criterias.

-CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET
Specifies number of channels which determines whether RMAN reads or writes in parallel. You must also specify DEVICE TYPE.

-CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 3

The configure “datafile backup copies” used to specify how many copies of each backup piece
should be created on the specified device type for the datafile. In my example above, it will take backup 3 copy. It is known as mirror backup. You can specify 3 different location with FORMAT option.

BACKUP DEVICE TYPE DISK COPIES 3 DATAFILE 7 FORMAT
‘/tmp/%U’,’?/oradata/%U’,’?/%U’;

-CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1
The configure “archivelog backup copies” used to specify how many copies of each backup piece should be created on the specified device type for the arhive log files. You can specify 2 different location with FORMAT option.

-CONFIGURE MAXSETSIZE TO UNLIMITED

You can use the MAXSETSIZE parameter on the BACKUP and configure commands to set a limit for the size of backup sets. If you set maxsetsize less than backing up datafile size then you will get an error. Default value of this configuration is “unlimited”.

-CONFIGURE ENCRYPTION FOR DATABASE OFF/ON

Encrypted backups cannot be read if they are obtained by unauthorized users. This configuration specifies whether encryption will be used or not.

-CONFIGURE ENCRYPTION ALGORITHM ‘AES128’

-CONFIGURE COMPRESSION ALGORITHM ‘BASIC’ AS OF RELEASE ‘DEFAULT’ OPTIMIZE FOR LOAD TRUE

There are two compression method ZLIB and BZIP2. ZLIB consumes less cpu but the compression
rate is low. BZIP2 consumes more cpu but the compression rate is high.

-CONFIGURE COMPRESSION ALGORITHM ‘ZLIB’;

-CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;

There are 3 compression level. LOW, MEDIUM and HIGH. HIGH level is not recommended because of suited for backups over slower networks. MEDIUM level is recommended.

-CONFIGURE ARCHIVELOG DELETION POLICY TO NONE

Specifies the archive log deletion policy.

In 10g you can set;

-CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO {ARCHIVERETENTION};

In 11g you can set;

-CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;

The archived log deletion policy also has option specific to Data Guard. For example, if you set archive log deletion policy to the “APPLIED ON STANDBY” then RMAN can delete logs after they have been applied at all mandatory remote destinations.

-CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

-CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE

You can use maxpiecesize channel parameter to set limits on the size of backup pieces. In my example below, I limit the backup piece size to 2G.

-CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE = 2G;

-CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/oracle/ora10g/dbs/snapcf_test11g.f’

RMAN needs a snapshot control file when resynchronizing with the recovery catalog or taking a backup of the current control file. The default location is platform specific and depends on the Oracle home of each target database. In Linux environment it locates in $ORACLE_HOME/dbs directory. You can change its location with this configuration.

RMAN – crosscheck archivelog – validation failed for archived log

RMAN – crosscheck archivelog – validation failed for archived log 

Error:

name=/u01/oracle/archives/LOG0000007225_0705278833_0001.ARC RECID=7216 STAMP=896284282
validation failed for archived log

Cause:

Cause of this message is because archive log file destination was changed or someone deleted/moved the archive log files manually using OS commands.
Use “DELETE EXPIRED ARCHIVELOG ALL” will alleviate the VALIDATION FAILED messages.

Solution:

RMAN> crosscheck archivelog all;
RMAN> delete expired archivelog all;

(or)

run {
allocate channel d1 device type disk;
crosscheck archivelog all;
delete noprompt expired archivelog all;
release channel d1;
}

EXIT;

Drop Database Using RMAN

Drop Database Using RMAN

Most  of the time  we  generally  use  DBCA  to  delete  the  database  because  it  is easy  and  simple . But , there are  some  scenario's  where we  have to drop  database  without using  the graphics  i,e, without DBCA . In such case  , we can  delete  or drop  the  database either by   manually  or  by using  sql*plus  or  RMAN prompt . As  compare  to  sql*plus , rman  is  much  more effective  because  it  consume  less   time  and secondly  we  can   delete  the  archivelogs  and backups  also . Starting  with Oracle  10gR1 onwards,  we  can  drop  a database  and  remove all  its records  from  the  rman catalog .

There are basically 4 syntax available to drop the database using RMAN

1) Drop Database : This command  deletes the datafiles, logfiles, Controlfiles and Spfile. If we do not want to delete backups then we can use this command.

2) Drop Database Noprompt : When “NOPROMPT”  is specified RMAN does not prompt for the confirmation before deleting the database. It delete the above files as in case of drop database.

3)DROP DATABASE INCLUDING BACKUPS : This command delete the datafiles, ogfiles,Controlfiles and Spfile plus the archivelogs and backup pieces  generated by RMAN.

4) DROP DATABASE INCLUDING BACKUPS NOPROMPT  : When “NOPROMPT” is specified RMAN does not prompt for the confirmation before deleting the database.

Let's have a look on the following steps to drop the Database using RMAN

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount exclusive
ORACLE instance started.
Total System Global Area   285212672 bytes
Fixed Size                          1218992 bytes
Variable Size                      100664912 bytes
Database Buffers                180355072 bytes
Redo Buffers                      2973696 bytes
Database mounted.

SQL> alter system enable restricted session;
System altered.
SQL> exit
C:\> rman target /

RMAN> DROP DATABASE INCLUDING BACKUPS;
database name is "ORACLE" and DBID is 1574601275

Do you really want to drop all backups and the database (enter YES or NO)? yes   // (by defaults it prompts)
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
18      18      1   1   AVAILABLE   DISK        D:\RMAN\ORACLE_1
19      19      1   1   AVAILABLE   DISK        D:\RMAN\ARCH_ORACLE_1
deleted backup piece
backup piece handle=D:\RMAN\ORACLE_1 recid=18 stamp=711242843
deleted backup piece
backup piece handle=D:\RMAN\ARCH_ORACLE_1 recid=19 stamp=711243739
Deleted 2 objects

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

List of Control File Copies
Key     S Completion Time Ckp SCN    Ckp Time        Name
------- - --------------- ---------- --------------- ----
2       A 27-DEC-09       2754635    27-DEC-09       D:\ORACLE\PRODUCT\10.2.0\STANDBY\STANDBYCTL01.CTL

List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
------- ---- ------- - --------- ----
202     1    132     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_132_701523840_1
203     1    133     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_133_701523840_1
204     1    134     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_134_701523840_1
205     1    135     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_135_701523840_1
206     1    136     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_136_701523840_1
207     1    137     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_137_701523840_1
208     1    138     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_138_701523840_1
209     1    139     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_139_701523840_1
210     1    140     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_140_701523840_1
211     1    141     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_141_701523840_1
212     1    142     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_142_701523840_1
213     1    143     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_143_701523840_1
214     1    144     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_144_701523840_1
215     1    145     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_145_701523840_1
216     1    146     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_146_701523840_1
217     1    147     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_147_701523840_1
218     1    148     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_148_701523840_1
219     1    149     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_149_701523840_1
220     1    150     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_150_701523840_1
221     1    151     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_151_701523840_1
222     1    152     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_152_701523840_1
223     1    153     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_153_701523840_1
224     1    154     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_154_701523840_1
225     1    155     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_155_701523840_1
226     1    156     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_156_701523840_1
227     1    157     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_157_701523840_1
228     1    158     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_158_701523840_1
229     1    159     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_159_701523840_1
230     1    160     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_160_701523840_1
231     1    161     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_161_701523840_1
232     1    162     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_162_701523840_1
233     1    163     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_163_701523840_1
235     1    164     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_164_701523840_1
234     1    165     A 18-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_165_701523840_1
236     1    166     A 18-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_166_701523840_1
237     1    167     A 18-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_167_701523840_1
238     1    168     A 26-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_168_701523840_1
239     1    169     A 26-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_169_701523840_1
240     1    170     A 27-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_170_701523840_1
241     1    171     A 28-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_171_701523840_1
242     1    172     A 30-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_172_701523840_1
243     1    173     A 31-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_173_701523840_1
244     1    174     A 01-FEB-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_174_701523840_1
245     1    175     A 03-FEB-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_175_701523840_1
246     1    176     A 04-FEB-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_176_701523840_1
247     1    177     A 04-FEB-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_177_701523840_1
248     1    178     A 04-FEB-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_178_701523840_1
249     1    179     A 04-FEB-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_179_701523840_1

deleted control file copy
control file copy filename=D:\ORACLE\PRODUCT\10.2.0\STANDBY\STANDBYCTL01.CTL recid=2 stamp=706679151
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_132_701523840_1 recid=202 stamp=708474283
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_133_701523840_1 recid=203 stamp=708475725
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_134_701523840_1 recid=204 stamp=708475725
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_135_701523840_1 recid=205 stamp=708477082
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_136_701523840_1 recid=206 stamp=708477082
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_137_701523840_1 recid=207 stamp=708477174
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_138_701523840_1 recid=208 stamp=708477177
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_139_701523840_1 recid=209 stamp=708477957
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_140_701523840_1 recid=210 stamp=708478193
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_141_701523840_1 recid=211 stamp=708523128
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_142_701523840_1 recid=212 stamp=708523129
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_143_701523840_1 recid=213 stamp=708523194
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_144_701523840_1 recid=214 stamp=708527072
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_145_701523840_1 recid=215 stamp=708527201
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_146_701523840_1 recid=216 stamp=708527278
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_147_701523840_1 recid=217 stamp=708527459
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_148_701523840_1 recid=218 stamp=708527641
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_149_701523840_1 recid=219 stamp=708527711
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_150_701523840_1 recid=220 stamp=708531955
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_151_701523840_1 recid=221 stamp=708532250
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_152_701523840_1 recid=222 stamp=708532331
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_153_701523840_1 recid=223 stamp=708537657
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_154_701523840_1 recid=224 stamp=708537715
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_155_701523840_1 recid=225 stamp=708537936
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_156_701523840_1 recid=226 stamp=708538012
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_157_701523840_1 recid=227 stamp=708538174
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_158_701523840_1 recid=228 stamp=708538195
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_159_701523840_1 recid=229 stamp=708538201
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_160_701523840_1 recid=230 stamp=708539593
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_161_701523840_1 recid=231 stamp=708555515
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_162_701523840_1 recid=232 stamp=708555516
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_163_701523840_1 recid=233 stamp=708555536
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_164_701523840_1 recid=235 stamp=708648593
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_165_701523840_1 recid=234 stamp=708648592
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_166_701523840_1 recid=236 stamp=708648853
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_167_701523840_1 recid=237 stamp=709329114
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_168_701523840_1 recid=238 stamp=709330932
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_169_701523840_1 recid=239 stamp=709423232
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_170_701523840_1 recid=240 stamp=709515047
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_171_701523840_1 recid=241 stamp=709678641
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_172_701523840_1 recid=242 stamp=709728779
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_173_701523840_1 recid=243 stamp=709857486
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_174_701523840_1 recid=244 stamp=710029358
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_175_701523840_1 recid=245 stamp=710118379
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_176_701523840_1 recid=246 stamp=710118402
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_177_701523840_1 recid=247 stamp=710118425
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_178_701523840_1 recid=248 stamp=710118565
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_179_701523840_1 recid=249 stamp=710248827
Deleted 49 objects

Database name is "ORACLE" and DBID is 1574601275
Database dropped.

Oracle Advanced Compression

Oracle Advanced Compression

Oracle Advanced Compression and Oracle Database 11g Release 2 helps manage more data in a cost-effective manner. With data volumes, on average, tripling every two years, Oracle Advanced Compression delivers compression rates of 2-4x across all types of data and applications.storage savings from compression will cascade throughout the data center, reducing network traffic and data backups as well. And by reading fewer blocks off disk, Oracle Advanced Compression also improves query performance.

Oracle Advanced Compression is an option of the Oracle 11g database (separately licensed) that allows data in the database to be compressed. Oracle Advanced Compression offers the following advantages:


1) OLTP Compression  : It allows structured and unstructured data to be compressed on insert,update and delete  operations.The following are features :
New compression algorithm uses deferred or batched approach
Data is inserted as is without compression until  PCTFREE  value is reached.
Compression of data starts once PCTFREE threshold is reached
Can be enabled at  table, partition or tablespace level
No need of decompressing the data during reads
Recommended for low update activity tables

2) Data Pump Compression   : In Data Pump, the compression of metadata was introduced in 10g and compression of "data" was introduced in 11g.This covers the following features :
Both are Inline operation
Save on storage allocation
No need to uncompress before Import
Implemented with COMPRESSION attribute, Values supported are ALL, DATA_ONLY, METADATA_ONLY .

3) Data guard Compression : It includes the following features :
Redo is compressed as it is transmitted over a network .
Helps efficiently utilize network bandwidth when data guard  is across data centers
Faster re-synchronization of Data guard  during gap resolution.
Recommended for low network bandwidth .
Implemented with attribute “COMPRESSION”  of initialization parameter log_archive_dest_n

4) RMAN Backup Compression  : It compresses the RMAN backups.The followinf features are
Supports compression of backups using "ZLIB"  algorithm .
Faster compression and low CPU utilization compared to default BZIP2 (10g) .
Low compression ratio  compared to BZIP2 .
Implement  with  CONFIGURE COMPRESSION ALGORITHM  ‘value’ command where value can be High , Medium(ZLIB) and Low(LZO) .


The Oracle Database 11g Advanced Compression option introduces a comprehensive set of compression capabilities to help customers maximize resource utilization and reduce costs. It allows IT administrators to significantly reduce their overall database storage footprint by enabling compression for all types of data – be it relational (table), unstructured (file), or backup data . Although storage cost savings are often seen as the most tangible benefit of compression, innovative technologies included in the Advanced Compression Option are designed to reduce resource requirements and technology costs for all components of our IT infrastructure, including memory and network bandwidth .

The benefits of compression are manyfold

1) Reduction of disk space used for storage .
2) Reduction in I/O bandwidth requirements .
3) Faster full table scans .
4) Lower server memory usage.

RMAN- Maintenance Commands

RMAN- Maintenance Commands

crosscheck backup
Verifies that the backups that rman thinks are available actually are. Lists any that aren’t as expired with a ‘X’ in the status column.

crosscheck backup completed between 'sysdate-5' and 'sysdate-2';
Verifies that the backups that rman thinks are available actually are. Lists any that aren’t as expired with a ‘X’ in the status column.

validate backupset ##
Verifies the backupset identified by ## can be restored. Throws an error if it can’t be.

list backup
Very verbose listing of all backups of which rman knows. Rather unreadable output

list backup summary
A much more readable version of the above. Useful for gettng the backup set key from which you can get a more detailed and readable report.

list backup of [ tablespace ${ts} | datafile # ] [ summary ]
Lists backups that have tablespace or datafile specifically. Useful for finding the latest backup of the tablespace for tspitr, for example.

list backupset ##
Much more readable version of list backup

list incarnation [ of database ]
Lists the incnations of the database, information of which will be needed if you need to restore through a resetlogs

list archivelog all
Lists the archivelogs in the FRA(?) that haven’t been backed up yet.

list backup of archivelog all [ summary ]
Lists the backups of the archive logs.

report need backup [ days | incremental | redundancy ]
Reports on the datafiles that need to be backed up. W/o args, uses the default retention period.

report obsolete
IDs the backups that are considered obsolete based on the configured retention policy

report schema [ at [ time | SCN | sequence ]
Displays the database schema. In order to use the ‘at time’ syntax, need to be connected to a recovery catalog.

report unrecoverable
Reports any datafiles, tablespaces, etc, that are unrecoverable. Would seem to be a fairly important command.

delete obsolete
Deletes backupsets that are considered obsolete based on the configured retention policy

delete backupset ##, ##, ##
Deletes specific backupsets.

RMAN - Logging Commandline RMAN Output

RMAN - Logging Commandline RMAN Output

Problem:

You want to log the output of RMAN commands you issue in command-line mode.

Solution:

If you want RMAN to log all its output when you use RMAN from the operating system command line, just add the keyword log to the command line, and supply the name of the log file to use. For example:

$ rman target / cmdfile commandfile1.rcv log /u01/app/oracle/outfile.txt

In this case, RMAN will write the output of the RMAN commands in the command file named commandfile.rcv to the log file outfile.txt. If you later want to run another set of RMAN commands and want to append the log messages to the same log file, you can do this by using the append option along with the log option. Here’s an example:

$ rman target / cmdfile commandfile2.rcv log /u01/app/oracle/outfile.txt append

The previous command will append the output from executing the command file commandfile2.rcv to the text file outfile.txt.

How It Works

The command-line argument log causes RMAN to send all its output to the log file you specify.Failure to add the keyword append when referring to an already existing log file will result in the overwriting of that older log file. If you are running RMAN interactively and you want to see output on your terminal screen as well as have it written to a log file, you can take advantage of the Unix/Linux tee command. The tee command sends output both to a text file and to the terminal. Here’s how you use the tee command:

$ rman | tee rman.log
RMAN>

All is not lost if you don’t specify a log file to capture the RMAN output. The view V$RMAN_OUTPUT returns detailed information about RMAN jobs in progress. For example, if your media manager runs into a problem with a tape drive, RMAN records the associated error messages in V$RMAN_OUTPUT and also outputs the message to the terminal or to a log file. As with all dynamic performance views, the contents of the V$RMAN_OUTPUT view are refreshed when you restart the database. The V$RMAN_STATUS view contains information about completed RMAN jobs as well as all RMAN jobs in progress

RMAN-Executing Operating System Commands From Within RMAN

RMAN-Executing Operating System Commands From Within RMAN

Problem:

You’ve invoked the RMAN client, and now you need to issue some operating system commands.

Solution:

Use the RMAN command host to invoke an operating system subshell. You can execute this command in two ways: you can issue it from the RMAN prompt, or you can execute it from inside a run block, which is a group of RMAN commands executed as a single unit. If you issue the host command stand-alone, without any parameters, RMAN will take you to the operating
system command line. Thus, the host command works the same in RMAN as it does from within SQL*Plus. If you issue the command host followed by a valid operating system command as a parameter, then RMAN will execute that operating system command and continue to process the rest of the commands in the run block, if there are any.
In the following example, we use the host command to list all files ending with dbf, after backing up a datafile from the RMAN prompt:

RMAN> shutdown immediate;
RMAN> startup mount;
RMAN> backup datafile '/u01/app/oracle/oradata/targ/system01.dbf' format '/tmp/system01.dbf';
RMAN> host 'ls -l /tmp/*dbf';
RMAN> alter database open;

The following example uses the host command with no parameters to temporarily escape to the operating system level during an interactive RMAN session:

RMAN> backup datafile 3 format '/u01/app/oracle/oradata/targ_db/dbs01.cpy';
RMAN> host;
$ ls $ORACLE_HOME/oradata/dbs01.cpy /net/oracle/oradata/dbs01.cpy
$ exit
RMAN>

How It Works

As you can see in the two examples, you can use the host command with or without an operating system command as a parameter. If you run the host command as part of a series of RMAN commands, RMAN executes the host command and continues with the rest of the commands. When you execute the host command by itself, RMAN displays the operating
system command prompt and resumes after you exit the command-line subshell.

RMAN-Checking the Syntax of RMAN Commands

RMAN-Checking the Syntax of RMAN Commands

Problem:

You want to check the syntax of your RMAN commands without actually executing the commands.

Solution:

To check the syntax of RMAN commands, you must start the RMAN client with the operating system command-line argument checksyntax. You can easily check the syntax of commands prior to their execution either by entering them at the command prompt or by reading in the commands through a command file. Here’s how you check the syntax of a single RMAN command (run {backup database;}) by first starting the RMAN client with the checksyntax argument:

$. /rman checksyntax

Recovery Manager: Release 11.1.0.1.0 - Beta on Mon Apr 2 08:31:11 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN> run {backup database;}
The command has no syntax errors
RMAN>

In this example, there were no errors in the syntax of the simple run block, and RMAN confirms that. You can also use the checksyntax argument to check the syntax of RMAN commands that are part of a command file. Simply specify the checksyntax argument before invoking the command file that consists of the RMAN commands. In the following example,
the file goodcmdfile contains a couple of restore and recovery commands:

$ rman checksyntax @/tmp/goodcmdfile
Recovery Manager: Release 11.1.0.1.0 - Beta on Mon Apr 2 08:31:11 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN> # file with legal syntax
2> restore database;
3> recover database;
4>
The cmdfile has no syntax errors
Recovery Manager complete.
$

You can also open an RMAN session solely for the purpose of checking the syntax of commands
that you type interactively:

$ rman checksyntax

An important point about the checksyntax argument is that you can’t use it after starting RMAN. That is, you can’t include the checksyntax argument from the RMAN command line. You must pass checksyntax as an argument to the rman command when you start the RMAN client and without connecting to any target or recovery catalog.

How It Works
When you either execute an RMAN command file by preceding it with the checksyntax argument or enter any RMAN commands after starting RMAN with the checksyntax argument, RMAN won’t actually execute any RMAN commands. RMAN will check and report only on the syntax of those commands. If the RMAN commands that you type at the command line or that you include as part of a command file have no errors, you get the “the command (cmdfile) has no errors” message from RMAN. Otherwise, RMAN will issue an error, as shown in the following example:

$ rman checksyntax @/tmp/badcmdfile
Recovery Manager: Release 11.1.0.1.0 - Beta on Mon Apr 2 08:31:11 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN> # file with illegal syntax
RMAN> run (backup database);
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "(": expecting one of: "{"
RMAN-01007: at line 1 column 5 file: standard input
RMAN>

The output of the checksyntax command reveals there is a syntax error in your run block. The checksyntax command is handy for checking scripts for syntax errors. With RMAN, there’s no need for a script to fail unexpectedly because you mangled the syntax of a command. If you’re surprised by an error, it’s because you didn’t test with checksyntax first.