Saturday 16 July 2011

RMAN handy commands

RMAN handy command

export NLS_DATE_FORMAT='dd-mm-yyyy hh24:mi:ss'

alias rmanme='rman target / | tee rman_`/bin/date +\%d\%m\%y:%H:%M:%S`.log'

with logfile

rman target / | tee rman_`/bin/date +\%d\%m\%y:%H:%M:%S`.log

log_date=`date "+%Y%m%d_%H:%M"`

rman target / log /home/oracle/log_${log_date}.log

oracle@apt-amd-02:~> rman

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jul 16 06:23:02 2011

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

RMAN> connect target /

connected to target database: ORCL (DBID=1267852645)

RMAN> list backupset;

using target database control file instead of recovery catalog
specification does not match any backup in the repository

RMAN> list backup of database;

specification does not match any backup in the repository

RMAN> list backup of archivelog all;

specification does not match any backup in the repository

RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 7 days
no obsolete backups found

RMAN> report obsolete redundancy = 2;

no obsolete backups found

RMAN> delete obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 7 days
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=54 device type=DISK
no obsolete backups found

RMAN> restore database validate;

Starting restore at 16-JUL-11
using channel ORA_DISK_1

datafile 6 will be created automatically during restore operation
datafile 7 will be created automatically during restore operation
datafile 8 will be created automatically during restore operation
datafile 9 will be created automatically during restore operation
datafile 10 will be created automatically during restore operation
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/16/2011 06:24:37
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 5 found to restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore

RMAN> report unrecoverable;

Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------
5 full /opt/app/oracle/oradata/orcl/example01.dbf

RMAN> report schema;

Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 810 SYSTEM *** /opt/app/oracle/oradata/orcl/system01.dbf
2 830 SYSAUX *** /opt/app/oracle/oradata/orcl/sysaux01.dbf
3 325 UNDOTBS1 *** /opt/app/oracle/oradata/orcl/undotbs01.dbf
4 352 USERS *** /opt/app/oracle/oradata/orcl/users01.dbf
5 100 EXAMPLE *** /opt/app/oracle/oradata/orcl/example01.dbf
6 10 ANUJTEST *** /opt/app/oracle/oradata/orcl/anujtest.dbf
7 50 TSAPEXF *** /opt/app/oracle/oradata/orcl/tsapexf01.dbf
8 110 TSAPEXU *** /opt/app/oracle/oradata/orcl/tsapexu01.dbf
9 20 TEST *** /opt/app/oracle/oradata/orcl/test.dbf
10 50 RMAN *** /opt/app/oracle/oradata/orcl/rman.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 276 TEMP 32767 /opt/app/oracle/oradata/orcl/temp01.dbf

RMAN> crosscheck backup;

using channel ORA_DISK_1
specification does not match any backup in the repository

RMAN> delete expired backup;

using channel ORA_DISK_1
specification does not match any backup in the repository

RMAN> LIST BACKUPSET OF DATABASE;

specification does not match any backup in the repository

RMAN>

RMAN> RESTORE DATABASE VALIDATE;

Starting restore at 16-JUL-11
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4

channel ORA_DISK_1: starting validation of datafile backupset
channel ORA_DISK_1: reading from backup piece /aptus/oracle/data/1/Rmanbackup/databasefiles_APTDB_3jmhii8t_17523_20110716
channel ORA_DISK_1: restored backup piece 1
piece handle=/aptus/oracle/data/1/Rmanbackup/databasefiles_APTDB_3jmhii8t_17523_20110716 tag=TAG20110716T073037
channel ORA_DISK_1: validation complete, elapsed time: 00:01:06
Finished restore at 16-JUL-11

===
delete archive

RMAN> backup archive log all format '/u1/oradata/db/arch-bak';
RMAN> delete archive until time 'trunc(sysdate)';

In order to skip two tablespaces issue command in RMAN twice

RMAN> CONFIGURE EXCLUDE FOR TABLESPACE abc;
RMAN> CONFIGURE EXCLUDE FOR TABLESPACE xyz;

RMAN> SHOW EXCLUDE;

You can override this exclusion feature by explicitly issuing keyword NOEXCLUDE in order to take whole database backup.

RMAN>BACKUP DATABASE NOEXCLUDE;

disable the exclusion feature for xyz tablespace
RMAN>CONFIGURE EXCLUDE FOR TABLESPACE xyz CLEAR;

===

RMAN> restore database validate preview; - <<<<<<<<<<<<<<<<<-----------------------

RMAN> list backup of controlfile;

rman> list backup;
rman> list backup of database;
rman> list backup summary;
rman> list incarnation;
rman> list backup by file;
rman> list copy of database archivelog all;
rman> list copy of datafile 1, 2, 3;
rman> list backup of datafile 11 summary;
rman> list backup of archivelog from sequence 1234;
rman> list controlfilecopy "/u01/app/oracle/ctrl1.cpy";
rman> list backupset of datafile 1;

List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
15667 Full 1.88M DISK 00:00:01 06-JUL-11
BP Key: 15667 Status: AVAILABLE Compressed: YES Tag: TAG20110706T010535
Piece Name: /aptus/oracle/data/1/Rmanbackup/databasefiles_APTDB_3hmgngbu_17521_20110706
Control File Included: Ckp SCN: 127799062 Ckp time: 06-JUL-11

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
15670 Full 1.88M DISK 00:00:02 16-JUL-11
BP Key: 15670 Status: AVAILABLE Compressed: YES Tag: TAG20110716T073037
Piece Name: /aptus/oracle/data/1/Rmanbackup/databasefiles_APTDB_3kmhiibs_17524_20110716
Control File Included: Ckp SCN: 128194984 Ckp time: 16-JUL-11

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
15671 Full 1.86M DISK 00:00:01 16-JUL-11
BP Key: 15671 Status: AVAILABLE Compressed: YES Tag: TAG20110716T073219
Piece Name: /aptus/oracle/data/1/Rmanbackup/controlfile_APTDB_3lmhiic3_17525_20110716
Control File Included: Ckp SCN: 128194995 Ckp time: 16-JUL-11

==================================================

RUN

{

set until time "to_date('02-FEB-2018 11:20:00','dd-mon-yyyyhh24:mi:ss')";

RESTORE DATABASE PREVIEW ;

}

RMAN> RUN

{

set until time "to_date('02-FEB-2018 11:20:00','dd-mon-yyyyhh24:mi:ss')";

restore database validate;

RMAN> RUN
{
set until time "to_date('02-FEB-2018 11:20:00','dd-mom-yyyyhh24:mi:ss')";
restore controlfile validate;
restore spfile validate;
}

RESTORE DATABASE UNTIL TIME "TO_DATE('18.03.2018 01:04:00','DD.MM.YYYY HH24:MI:SS')" PREVIEW;

BS Key Size Device Type Elapsed Time Completion Time

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

730 5.54M DISK 00:00:01 18-03-2018 01:04:50

BP Key: 730 Status: AVAILABLE Compressed: YES Tag: TAG20180318T010449

Piece Name: /dumps/IBRAC/20180318_ibrac1_742_1_971053489

List of Archived Logs in backup set 730

Thrd Seq Low SCN Low Time Next SCN Next Time

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

1 163 239713659 18-03-2018 01:00:03 239721594 18-03-2018 01:04:49

2 149 239713662 18-03-2018 01:00:04 239721591 18-03-2018 01:04:49

recovery will be done up to SCN 239715207

Media recovery start SCN is 228048219

Recovery must be done beyond SCN 239715207 to clear datafile fuzziness

Finished restore at 07-07-2022 11:18:32

RUN

{

set until time "to_date('18.03.2018 01:04:00','DD.MM.YYYY HH24:MI:SS')";

RESTORE DATABASE;

RECOVER DATABASE;

}

alter database open resetlogs;

RMAN> run {
set until time to_date('04-Aug-2004 00:00:00', 'DD-MON-YYYY HH24:MI:SS');
restore database;
recover database;
}

=============================

RMAN> run {
2> set until time '27-oct-2011 12:11:00';
3> }

executing command: SET until clause

RMAN> sql "alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss''";

using target database control file instead of recovery catalog
sql statement: alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss''

recover database UNTIL TIME '2011-11-15:14:07:00'
The time format must be in the format YYYY-MM-DD:HH24:MM:SS irrespective of NLS_DATE_FORMAT

run {
shutdown immediate;
startup mount;
sql "alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss''";
set until time '15-aug-2006 14:11:00';
restore database;
recover database;
alter database open resetlogs;}

col group# for 9999
col status for a8
col member for a40
select f.group#,
sequence#,
l.status,
member
from v$logfile f, v$log l
where f.group#=l.group#
order by group#

GROUP# SEQUENCE# STATUS MEMBER
------ ---------- -------- ----------------------------------------
4 58 INACTIVE /opt/app/oracle/oradata/orcl/redo04.log
5 59 INACTIVE /opt/app/oracle/oradata/orcl/redo05.log
6 60 CURRENT /opt/app/oracle/oradata/orcl/redo06.log

run {
shutdown immediate;
startup mount;
set until sequence 59 thread 1;
restore database;
recover database;
alter database open resetlogs;
}

UNTIL CHANGE / UNTIL SCN Recovery

* SQL> shutdown immediate;
* SQL> startup mount;
* —No restore b/c OS or other means are used to restore necessary files—
* SQL> recover database UNTIL CHANGE 309121;
* SQL> alter database open resetlogs;
* —–ALTERNATIVE in RMAN—–

run {
shutdown immediate;
startup mount;
set until scn 309121;
restore database;
recover database;
alter database open resetlogs;}

oracle@novagenesis$ rman target=/ log=anujrman.log <<<<< --- for RMAN log file

to duplicate database .. from asm file system non ASM filesystem

[oracle@rac1 dupdb]$ rman target=sys/oracle@demodb auxiliary=/
RMAN> duplicate target database to dupdb nofilenamecheck;

to restore RMAN

RMAN> restore controlfile from 'e:\backup\CTL_SP_BAK_xxxxxxxxxx-20050228-00';

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/aptus/oracle/data/1/Rmanbackup/cotrolfile%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/aptus/oracle/data/1/Rmanbackup/%d_DB_%u_%s_%p';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/aptus/oracle/product/10g/dbs/snapcf_aptdb.f'; # default

====

Listing Incarnations

A new incarnation of the database is created when you perform the open resetlogs operation. To view the list of database incarnations,

RMAN> List incarnation;

List Summary of Backups

The summary of backups include backupset key, the status, device type, completion time etc,

RMAN> List Backup Summary;
RMAN> List expired Backup of archivelog all summary;
RMAN> List Backup of tablespace Test summary;

List Backups of various files

It provides the summary of the backups available for each datafile, controlfile, archivelog file and spfile.

RMAN> List Backup By File;

f you want the detailed report on the backups, then issue the following command.

RMAN> List Backup;

It lists the all available information about the backups.

Backups used for Recovery

To list the backups used for restore and recovery,

RMAN> list recoverable backup;

Expired Backups

The list backup shows both available and expired backups. To view only the expired backups,

RMAN> List expired Backup;
RMAN> List expired Backup summary;
RMAN> List expired Backup of Archivelog all;
RMAN> List expired Backup of datafile 10;

Listing Tablespace and Datafile Backups

RMAN> List Backup of Tablespace Test;
RMAN> List Backup of Datafile 4;

Listing Archivelog Backups

RMAN> List Archivelog all;
RMAN> List Archivelog all backedup 2 times to device type sbt;

Listing Controlfile and Spfile Backups

RMAN> List Backup of Controlfile;
RMAN> List Backup of Spfile;

The above list commands displayed information about the backusets.
If you have performed Image copy backups then you must use the list copy command as shown below,

RMAN> List Copy;
RMAN> List Copy of database;
RMAN> List Copy of tablespace test;
RMAN> List Copy of archivelog all;
RMAN> List Copy of archivelog from sequence 12345;
RMAN> List Copy of archivelog from sequence 1000 until sequence 1010;
RMAN> List Copy of Controlfile;
RMAN> List Copy of Spfile;

recover database from tag="TAG20051024T1XXXXX" validate;
restore database from tag="XXXXXXXX";

RMAN> SET DBID=228033884;

RMAN> CONNECT TARGET

4. Restore the controlfile from autobackup

% rman trace recocf.log

RMAN> SET DBID=228033884;

RMAN> CONNECT TARGET

RMAN> RUN
{ SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE disk TO '/rman/V920/%F';
ALLOCATE CHANNEL d1 DEVICE TYPE disk;
RESTORE CONTROLFILE FROM AUTOBACKUP
MAXSEQ 5 # start at sequence 5 and count down (optional)
MAXDAYS 5; # start at UNTIL TIME and search back 5 days (optional)
MOUNT DATABASE;
}

5. Verify what is available for incomplete recovery.

We will recover to the highest SCN log sequence and thread. We will use the log sequence in this case.

The options are "until time", "until scn", or "until sequence".

* First we need to fine the highest sequence of each thread:

SQL> select max(sequence#) from v$archived_log where thread#=1;

MAX(SEQUENCE#)
--------------
25

SQL> select max(sequence#) from v$archived_log where thread#=2;

MAX(SEQUENCE#)
--------------
13

* Next is to find the thread with highest SCN ( NEXT_CHANGE# )

In this case the SCN is greater in thread 2 sequence 13 than in sequence 25 thread 1. So we will use thread 2. We will set sequence 14 thread 2 for RMAN 'until sequence' recovery, because RMAN stops the recovery before applying the indicated sequence. Log sequence for recovery needs always be sequence+1 to end at +1 after applying the prior sequence.

SQL> select sequence#, thread#, first_change#, next_change#
from v$archived_log
where sequence# in (13,25);

SEQUENCE# THREAD# FIRST_CHANGE# NEXT_CHANGE#
-------------------- -------------- ------------------------- -------------------------
25 1 1744432 1744802
13 2 1744429 1744805

SQL> select sequence#, thread#, first_change#, next_change#
from v$backup_redolog
where sequence# in (13,25);

SEQUENCE# THREAD# FIRST_CHANGE# NEXT_CHANGE#
-------------------- -------------- ------------------------- -------------------------
25 1 1744432 1744802
13 2 1744429 1744805

--- SET UNTIL SEQUENCE 14 THREAD 2 ----

6. Get the sentences to add TEMPFILES after opening DB.

Locally Managed Temporary Tablespaces are not restored by RESTORE command, we need to create them manually after recovery is complete.

If using LMT Temporary tablespace the controlfile will have the syntax to add the tempfile after recovery is complete. The following command will give us the create controlfile sencence:

SQL> alter database backup controlfile to trace;

Example:

# Commands to add tempfiles to temporary tablespaces.
# Online tempfiles have complete space information.
# Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/dev/db/rV92B_temp_01.dbf' SIZE 41943040 REUSE AUTOEXTEND OFF;
# End of tempfile additions.
#

7. Run the rman script

Since log sequence 13 thread 2 next_change# is 3 changes ahead of thread 1 sequence 25 we are using sequence 14 to stop recovery. This will restore the data files and recover them completely using the online logs.

run {
SET UNTIL SEQUENCE 14 THREAD 2;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
}

Finally add the tempfiles with sentences from step 6.

# Preview
RESTORE DATABASE PREVIEW;
RESTORE TABLESPACE users PREVIEW;

# Preview Summary
RESTORE DATABASE PREVIEW SUMMARY;
RESTORE TABLESPACE users PREVIEW SUMMARY;

RMAN> list incarnation of database aptdb ;

RMAN> LIST INCARNATION ;

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 APTDB 204677159 CURRENT 1 01-JUN-11

Debug the restore session using:

RMAN trace

C:\>rman target / log=RmanLog.txt trace=rmanTrace.txt
RMAN> debug on;
RMAN> restore datafile 1;
RMAN> debug off;
RMAN> exit;

Resetting RMAN to a Previous Incarnation in NOCATALOG Mode

CONNECT TARGET / NOCATALOG

# step 1: start and mount a control file that knows about the incarnation to which
# you want to return. Refer to the RESTORE command for appropriate options.
STARTUP NOMOUNT;
RESTORE CONTROLFILE FROM AUTOBACKUP;
ALTER DATABASE MOUNT;

# step 2: obtain the primary key of old incarnation
LIST INCARNATION OF DATABASE trgt;

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ------------- ------- ---------- ----------
1 2 TRGT 1334358386 PARENT 154381 OCT 30 2007 16:02:12
1 116 TRGT 1334358386 CURRENT 154877 OCT 30 2007 16:37:39

# step 3: in this example, reset database to incarnation key 2
RESET DATABASE TO INCARNATION 2;

# step 4: restore and recover the database to a point before the RESETLOGS
RESTORE DATABASE UNTIL SCN 154876;
RECOVER DATABASE UNTIL SCN 154876;

# step 5: make this incarnation the current incarnation and list incarnations:
ALTER DATABASE OPEN RESETLOGS;
LIST INCARNATION OF DATABASE trgt;

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- ------- ---------- ----------
1 2 TRGT 1334358386 PARENT 154381 OCT 30 2007 16:02:12
1 116 TRGT 1334358386 PARENT 154877 OCT 30 2007 16:37:39
1 311 TRGT 1334358386 CURRENT 156234 AUG 13 2007 17:17:03

Backupset Maintenance using the configured retention policy

RMAN> list backup summary;
list backup by datafile;
list backup of database;
list backup of archivelog all;
list backup of controlfile;

Start RMAN in debug mode:
rman target debug trace=rman.trc log=rman.log

Activate sql traces
rman> set echo on;
rman> sql "alter system set max_dump_file_size=UNLIMITED";
rman> sql "alter session set events ''10046 trace name context forever, level 12''" ;

###########################
# RMAN Format Description
###########################

%a Current database activation id

%A Zero-filled activation ID

%c The copy number of the backup piece within a set of duplexed backup pieces.bMaximum value is 256

%d Database name

%D Current day of the month from the Gregorian calendar in format DD

%e Archived log sequence number

%f Absolute file number

%F Combines the DBID, day, month, year, and sequence into a unique and repeatable generated name

%h Archived redo log thread number

%I DBID

%M Month in the Gregorian calendar in the format MM

%n Database name, padded on the right with x characters to a total length of eight characters

%N Tablespace name. Only valid when backing up datafiles as image copies.

%p Piece number within the backup set. This value starts at 1 for each backup set and is incremented by 1
for each backup piece created. If a PROXY is specified, the %p variable must be included in the FORMAT
string either explicitly or implicitly within %U.

%r Resetlogs ID

%s Backup set number. This number is a counter in the control file that is incremented for each backup set.
The counter value starts at 1 and is unique for the lifetime of the control file. If you restore a backup
control file, then duplicate values can result. CREATE CONTROLFILE initializes the counter at 1.

%S Zero-filled sequence number

%t Backup set time stamp, a 4-byte value derived as the number of seconds elapsed since a fixed reference time.
The combination of %s and %t can be used to form a unique name for the backup set.

%T Year, month, and day in the Gregorian calendar in the format: YYYYMMDD

%u An 8-character name constituted by compressed representations of the backup set or image copy number and the
time the backup set or image copy was created

%U A system-generated unique filename (default). %U is different for image copies and backup pieces.
For a backup piece, %U is a shorthend for %u_%p_%c and guarantees uniqueness in generated backup filenames.
For an image copy of a datafile, %U means the following: data-D-%d_id-%I_TS-%N_FNO-%f_%u

%Y Year in this format: YYYY

%% Percent (%) character. For example, %%Y translates to the string %Y

RMAN> LIST FAILURE;

no failures found that match specification

RMAN> ADVISE FAILURE;

no failures found that match specification

RMAN> ADVISE FAILURE 101;

show COMPRESSION ALGORITHM;

CONFIGURE COMPRESSION ALGORITHM TO 'alg_name';

SQL> col ALGORITHM_NAME format a10
select * from v$rman_encryption_algorithms

ALGORITHM_ID ALGORITHM_ ALGORITHM_DESCRIPTION IS_ RES
------------ ---------- ---------------------------------------------------------------- --- ---
1 AES128 AES 128-bit key YES NO
2 AES192 AES 192-bit key NO NO
3 AES256 AES 256-bit key NO NO

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

restore database preview and validate

RMAN> restore database preview;

RMAN> restore database preview;

RMAN> restore database from tag FULL_BKP preview;

RMAN> restore datafile 1, 2 preview;

RMAN> restore archivelog all preview summary;

RMAN> restore archivelog from time 'sysdate - 1/24' preview summary;

RMAN> restore archivelog from scn 25 preview summary;

RMAN> restore database validate check logical;

RMAN> restore database validate;

RMAN> restore database from tag FULL_BKP validate;

RMAN> restore datafile 1 validate;

RMAN> restore archivelog all validate;

RMAN> restore controlfile validate;

RMAN> restore tablespace users validate;

—————————————————————

RMAN> validate backupset 999 check logical;

Use the RMAN> list backup; command to obtain the backupset key (99 above)

RMAN> validate database check logical;

RMAN> validate database;

SET DBID 669001291;

restore until time 'sysdate-3' CONTROLFILE to 'c:\temp\cfile' from autobackup;

restore until time 'sysdate-3' CONTROLFILE to 'c:\temp\cfile' from autobackup maxdays 100;

startup force nomount;

RMAN> RUN
{ SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'C:\Backup\%F';
RESTORE CONTROLFILE FROM AUTOBACKUP; }

select DBID, NAME ,STATUS from RC_DATABASE_INCARNATION;

LIST BACKUP; # lists backup sets, image copies, and proxy copies
LIST BACKUPSET; # lists only backup sets and proxy copies
LIST COPY; # lists only disk copies
LIST BACKUP BY FILE; # shows backup sets, proxy copies, and image copies
LIST COPY BY FILE; # shows only disk copies
LIST EXPIRED BACKUP BY FILE;
LIST BACKUP SUMMARY; # lists backup sets, proxy copies, and disk copies

LIST EXPIRED BACKUP SUMMARY;

# lists backups of all files in database
LIST BACKUP OF DATABASE;
# lists copy of specified datafile
LIST COPY OF DATAFILE 'ora_home/oradata/trgt/system01.dbf';
# lists specified backup set
LIST BACKUPSET 213;
# lists datafile copy
LIST DATAFILECOPY '/tmp/tools01.dbf';
# specify a backup set by tag
LIST BACKUPSET TAG 'weekly_full_db_backup';
# specify a backup or copy by device type
LIST COPY OF DATAFILE 'ora_home/oradata/trgt/system01.dbf' DEVICE TYPE sbt;
# specify a backup by directory or path
LIST BACKUP LIKE '/tmp/%';
# specify a backup or copy by a range of completion dates
LIST COPY OF DATAFILE 2 COMPLETED BETWEEN '10-DEC-2002' AND '17-DEC-2002';
# specify logs backed up at least twice to tape
LIST ARCHIVELOG ALL BACKED UP 2 TIMES TO DEVICE TYPE sbt;

RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 VIHAAN 1611193673 PARENT 1 18-SEP-11
2 2 VIHAAN 1611193673 CURRENT 787897 13-DEC-11

LIST EXPIRED BACKUP

RMAN> show all;

RMAN configuration parameters for database with db_unique_name VIHAAN are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_vihaan.f'; # default

contralfile autobackup location

oracle_home/dba

Piece Name: /u01/app/oracle/product/11.2.0/db_1/dbs/c-1611193673-20120120-00

rman target=sys/{password}@ldg auxiliary=/ @create_standby.rman

Useful RMAN COMMANDS

Duplicate from earlier disk backup
RUN
{
ALLOCATE AUXILIARY CHANNEL c1 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL c2 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL c3 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL c4 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL c5 DEVICE TYPE DISK;
DUPLICATE TARGET DATABASE to ERPSAND;
}
---------------------------------------------------
restore a particular sequence of a thread
restore archivelog from sequence 73 thread 3;
----------------------------------------------------------------------
Which datafile is in which backup piece

SQL> select handle from v$backup_piece where set_count in(select set_count from v$backup_datafile where file#=5);
--------------------------------------------------------------
Delete old archive logs

1. delete noprompt archivelog all backed up 1 times to device type disk
2. delete noprompt archivelog all backed up 1 times to device type disk completed before '(sysdate-0.2)';
------------------------------------------------------
catalogging a backup piece in rman
catalog backuppiece '/erpuat/orashr/pju_bkp4cloning/ccm4aoeu_1_1' ;
or
CATALOG START WITH '/erpuat/orashr/pju_bkp4cloning/c';
---------------------------------------------------
backing up log sequneces

run{
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '/erp/ohr/bkp4cloning/ARCH_2_%U.rman';
backup archivelog from logseq=75 until logseq=88 thread 2 ;
}
----------------------------------------------------------
To clean rman tape config

rman> configure CHANNEL DEVICE TYPE 'SBT_TAPE' clear;
---------------------------------------------------------------
Restore the archive logs to production diskgroup using the following commands if not present in the ASM diskgroup or if rman backup deleted them

Rman target /
restore archivelog from sequence 17958 until sequence 17970 thread 1;
restore archivelog from sequence 16967 until sequence 16980 thread 2;
----------------------------------------------------------
To check database complete backup.

list backup of database summary completed between '15-JAN-2009' and '16-JAN-2009';
-------------------------------------------------
to check archive log backup

list backup of archivelog time between "to_date('16-JAN-2009 00:00:00','DD-MON-YYYY HH24:MI:SS')" and "to_date('16-JAN-2009 00:00:00','DD-MON-YYYY HH24:MI:SS')";
-----------------------------------------------
To monitor the Rman progress

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 ;
----------------------------------------------------
Few Rman commands

copy archivelog '+ARCHLOGS/SPPWP_EWDC/ARCHIVELOG/2010_04_29/thread_1_seq_3544.1229.717603011' to '/orabkup/thread_1_seq_3544.1229.717603011';

registering logfile in dr database thru sqlplus

SQL> alter database register logfile '/spdb/data/arch/sppw/thread_2_seq_3521.1228.717601065';

Database altered.
------------------------------------------------------------
Few Rman commands

Restore controlfile:
restore controlfile from '/u02/oraback/iiss/ARCH.iiss1.20100528.1212.CTRL.cf_D-IISS_id-3057886536_4qleqchk.compr.rman';
-----------------------------------------------------------------------------
To restore individual archived logs from rman backups to a specified directory

RMAN> run
{
#optional line to override default location for a restore
set archivelog destination to "/spdb/data/backup/psdrp1";
restore archivelog from logseq=101837 until logseq=101839;
}
----------------------------------------------------
Clean archivelogs

delete force archivelog all completed before '(sysdate-100)';
--------------------------------------------------
List database backups

list backup of database completed after '(SYSDATE-.5)';
list backup summary completed after '(SYSDATE-.5)';
list backupset 20459 ;

    list archivelog all;
    delete noprompt expired archivelog all ;
    crosscheck backup ;
    delete noprompt obsolete;
    backup archivelog all delete input ;
    list archivelog all;
    list copy of archivelog until time 'SYSDATE-10' ;
    list copy of archivelog from time 'SYSDATE-10'
    list copy of archivelog from time 'SYSDATE-10′ until time 'SYSDATE-2';
    list copy of archivelog from sequence 1000 ;
    list copy of archivelog until sequence 1500 ;
    list copy of archivelog from sequence 1000 until sequence 1500 ;

-- Archivelog Delete Commands

    delete archivelog all;
    delete archivelog until time 'SYSDATE-10' ;
    delete archivelog from time 'SYSDATE-10';
    delete archivelog from time 'SYSDATE-10' until time 'SYSDATE-2';
    delete archivelog from sequence 1000 ;
    delete archivelog until sequence 1500 ;
    delete archivelog from sequence 1000 until sequence 1500 ;
    DELETE NOPROMPT ARCHIVELOG UNTIL SEQUENCE = 300;
    DELETE NOPROMPT ARCHIVELOG ALL;
    delete noprompt expired archivelog all;

-- Deleting all backups:

   DELETE BACKUP;

-- noprompt statement for do not yes-no question.

   delete noprompt archivelog until time 'SYSDATE-10' ;

-- Deleting Image copy backup:

   DELETE COPY;

-- Deleting obsolete backups older than 3 days:

      delete obsolete recovery window of 3 days;

-- Deleting expired backup

      delete expired backup;

-- Checking Archive log files with crosscheck:

      crosscheck archivelog all;

-- Checking whole backup with crosscheck:

      crosscheck backup;

-- Checking image copy backup with crosscheck:

      crosscheck copy;

-- Checking tagged backup with crosscheck:

      crosscheck backuppiece tag = 'nightly_backup';

-- Showing Backup Preview (In which objects are listed in):

      restore database preview;

      restore tablespace users preview;

-- Verification whole backup:

      backup validate;

-- Verification archive log files backup:

      backup validate database archivelog all;

-- Verification whole database restore:

      restore database validate;

-- Verification control file restore:

      restore controlfile validate;

-- To debug and trace RMAN backup

     $rman target=/ debug=all trace=rman.trc

-- Create the backup log file

     $export NLS_DATE_FORMAT=’dd.mm.yyyy hh24:mi:ss’;
     $rman target=/ log=rman.log

-- then confirm this

    report need backup;

convert tablespace tbs_2 format '/tmp/tbs_2_%U.df';

RUN {

change archivelog all crosscheck;

report obsolete orphan;

report obsolete;

crosscheck backup;

crosscheck copy;

crosscheck backup of controlfile;

delete noprompt expired backup;

delete noprompt expired archivelog all;

delete noprompt expired backup of controlfile;

delete force noprompt expired copy;

delete force noprompt obsolete orphan;

delete force noprompt obsolete;

}

===================================

http://anuj-singh.blogspot.com/2021/10/rman-restore-database.html

rman script

export NLS_DATE_FORMAT='dd-mm-yyyy hh:mi:ss'

rman target / | tee rman_`/bin/date +\%d\%m\%y:%H:%M:%S`.log

run {

allocate channel ch1 device type DISK;

allocate channel ch2 device type DISK;

sql "alter system archive log current";

backup incremental level=0 tag "RMANFULL" format '/dumps/UGARY/%d_T%T_db_s%s_p%p_t%t' database filesperset 4 plus archivelog tag "RMANFULL";

sql "alter system archive log current";

backup format '/dumps/UGARY/%d_T%T_arch_s%s_p%p_t%t' archivelog all filesperset 4 tag "RMANFULL";

backup format '/dumps/UGARY/%d_T%T_cf%s_p%p_t%t' current controlfile tag "RMANFULL";

restore database preview;

}

rman target / | tee rman_`/bin/date +\%d\%m\%y:%H:%M:%S`.log

run {

restore controlfile from '/dumps/UGARY/UGARY_T20220716_cf34_p1_t1110180865';

alter database mount;

}

#!/bin/bash

export ORACLE_SID=vihcdbd8

export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1

export PATH=$ORACLE_HOME/bin:/usr/bin:/usr/sbin:/bin:/usr/local/bin

export LOG=/home/oracle/delete.log

echo "Archive Delete :`date`" >$LOG

rman target / <<EOF>>$LOG

RUN {

delete noprompt ARCHIVELOG ALL ;

}

exit;

EOF

echo "Backup finished at `date`" >> $LOG

exit ;

RMAN> list backup ;

List of Archived Logs in backup set 33

Thrd Seq Low SCN Low Time Next SCN Next Time

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

1 7 6688434 16-JUL-2022 07:34:18 6688443 16-JUL-2022 07:34:21

1 8 6688443 16-JUL-2022 07:34:21 6688451 16-JUL-2022 07:34:21 <<<<<< check this date for restore

RUN

{

set until time "to_date('16.07.2022 07:34:21','DD.MM.YYYY HH24:MI:SS')";

restore database;

recover database;

alter database open resetlogs;

}

select 'restore validate archivelog from scn '||min_first_change#||' until scn '||max_next_change# "Restore" from v$backup_archivelog_summary;

set numf 999999999999999999
alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss';
select
NUM_FILES_BACKED,
NUM_DISTINCT_FILES_BACKED,
MIN_FIRST_CHANGE# ,
MAX_NEXT_CHANGE# ,
MIN_FIRST_TIME ,
MAX_NEXT_TIME ,
INPUT_BYTES ,
OUTPUT_BYTES ,
COMPRESSION_RATIO ,
--INPUT_BYTES_DISPLAY
--OUTPUT_BYTES_DISPLAY
CON_ID
from v$backup_archivelog_summary;

NUM_FILES_BACKED NUM_DISTINCT_FILES_BACKED MIN_FIRST_CHANGE# MAX_NEXT_CHANGE# MIN_FIRST_TIME MAX_NEXT_TIME INPUT_BYTES OUTPUT_BYTES COMPRESSION_RATIO CON_ID
------------------- ------------------------- ------------------- ------------------- ------------------- ------------------- ------------------- ------------------- ------------------- -------------------
340 340 203747990 6696255128 18-02-2018 01:00:04 25-07-2022 01:00:04 48832481792 14235590656 3 1

====

SQL> RECOVER automatic DATABASE UNTIL TIME '2019-10-21:06:00:00' USING BACKUP CONTROLFILE;

alter database recover automatic database until time '2023-06-30:23:59:59' using backup controlfile
alter database recover automatic database until time

select to_char(controlfile_time,'dd/mm/yyyy hh24:mi:ss') from v$database;

select r.file#, to_char(r.time, 'dd/mm/yyyy hh24:mi:ss'), f.name
from v$recover_file r, v$datafile f where r.file# = f.file#;

alter database recover managed standby cancel;
recover automatic standby database until time '2020-08-15:08:00:00';

====

restore validate preview summary archivelog from time "TO_DATE('24-JUL-2022 22:03:32','DD-MON-YYYY HH24:MI:SS')";

.
.
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:01:05
Finished restore at 25-JUL-22

For before 10 min RMAN recovery
set until time "SYSDATE -10/24/60" ;

restore validate archivelog UNTIL TIME "to_date('23/07/2022 23:59:59','dd/mm/yyyy hh24:mi:ss')";

rman target / log="/home/oracle/rman_log.log"

rman target sys log="/home/oracle/rman_log.log" append

RMAN> spool log to '/home/oracle/rman_log2.log' append

====

cat $HOME/rman_scripts/dgbdr_level0.rcv
connect target /

run {
backup as compressed backupset
incremental level 0
format '/dumps/DGBDR/%T_@_%s_%p_%t'
database
include current controlfile
plus archivelog;
}

run {
delete obsolete;
}

exit;
=====

run { backup as compressed backupset
incremental level 0
format '/dumps/VIHAAN8/rman/%T_@_%s_%p_%t'
database
include current controlfile
plus archivelog;
}

---
cat $HOME/rman_scripts/dgbdr_arclogs.rcv
connect target /

run {
backup as compressed backupset
format '/dumps/DGBDR/%T_@_%s_%p_%t'
(archivelog all delete input);
}

run {
delete obsolete;
}

run {
backup tag vihaan8_CONTROL current controlfile format '/dumps/DGBDR/%d_%T_%s_%p_CONTROL';
}

w
backup as compressed backupset incremental level 0 format '/dumps/VIHAAN8/rman/%T_@_%s_%p_%t' database include current controlfile plus archivelog;

good link

RMAN block recovery & ORA-600

http://oraclehandson.wordpress.com/2011/04/01/rman-block-recovery-ora-600/

https://www.youtube.com/watch?v=Qt8eV5WA7yw

Recover a database with JUST the datafiles?

rman backup script


run {
        allocate channel dev0 type disk format '/u02/DISK_BACKUP/%d_%t_%s_%p.bkp';
        allocate channel dev1 type disk format '/u02/DISK_BACKUP/%d_%t_%s_%p.bkp';
        allocate channel dev2 type disk format '/u02/DISK_BACKUP/%d_%t_%s_%p.bkp';
        allocate channel dev3 type disk format '/u02/DISK_BACKUP/%d_%t_%s_%p.bkp';
        backup
             incremental level 0
             skip inaccessible
             tag DiskFull_Oracle_Backup
             filesperset 1
             database
             ;
        sql 'alter system archive log current';
        backup
             filesperset 1
             format '/u02/DISK_BACKUP/A_%d_%u_%s_%T'
             archivelog all
             ;
        backup
             filesperset 1
             format '/u02/DISK_BACKUP/%d_%t_%s_%p.cf'
             current controlfile
             ;
        backup spfile;
        }


run { 
crosscheck backupset of database;
crosscheck backupset of controlfile;
crosscheck backupset of archivelog all;
crosscheck backup;
delete noprompt expired backup;
delete force  noprompt obsolete;
delete expired archivelog all;
delete noprompt obsolete;
delete noprompt obsolete orphan;
crosscheck archivelog all;
delete expired archivelog all;
CROSSCHECK COPY OF CONTROLFILE;
}

export NLS_DATE_FORMAT='DD-MON-YY HH24:MI:SS'


CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/Rman/%F';

run {
        allocate channel dev0 type disk format '/u01/app/Rman/%d_%t_%s_%p.bkp';
         backup
             incremental level 0
             skip inaccessible
             tag DiskFull_Oracle_Backup
             filesperset 1
             database
             ;
        sql 'alter system archive log current';
        backup
             filesperset 1
             format '/u01/app/Rman/A_%d_%u_%s_%T'
             archivelog all delete input
             ;
        backup
             filesperset 1
             format '/u01/app/Rman/%d_%t_%s_%p.cf'
             current controlfile
             ;
        sql 'alter system archive log current';
        backup
             filesperset 1
             format '/u01/app/Rman/A_%d_%u_%s_%T'
             archivelog all delete input
             ;
        backup spfile format '/u01/app/Rman/spfile_%d_%s_%T.bak' tag 'spfile backup';
        }

http://anuj-singh.blogspot.com/2016/09/

http://anuj-singh.blogspot.com/2021/10/rman-restore-database.html 

run   
    {   
      Shutdown Immediate   
      Startup Mount   
      Set Until Sequence 15 Thread 1;   
     #set until scn  42773496;
     #set until time "to_date('21-OCT-2020 14:45:00','DD-MON-YYYY HH24:MI:SS')";
      Restore Database;   
      Recover Database;   
      Alter Database Open Resetlogs;

====

as per metalink RMAN: RAC Backup, Restore and Recovery using RMAN (Doc ID 243760.1)

RMAN: RAC Backup and Recovery using RMAN [ID 243760.1]

select thread#,max(sequence#) sequence# from v$archived_log L, v$database D
where L.resetlogs_change# = D.resetlogs_change# 
group by thread#
;

   THREAD#  SEQUENCE#
---------- ----------
         1     981393
         2     744280





set numf 99999999999999
select sequence#, thread#, first_change#, next_change#  ,COMPLETION_TIME  from v$archived_log L, v$database D
where L.resetlogs_change# = D.resetlogs_change# 
and  sequence# in (981393,744280)
;




set numf 99999999999999
select sequence#, thread#, first_change#, next_change#    from v$archived_log L, v$database D
where L.resetlogs_change# = D.resetlogs_change# 
and  sequence# in (981393,744280)
;



      SEQUENCE#         THREAD#   FIRST_CHANGE#    NEXT_CHANGE#
--------------- --------------- --------------- ---------------
         981393               1     10540998662     10541024661
         744280               2     10541014332     10541024657  * In a RAC environment, use the lower to ensure we have the redo required from threads



select sequence#, thread#, first_change#, next_change#  from v$backup_redolog
           where 1=1 
and sequence# in (981393,744280);

      SEQUENCE#         THREAD#   FIRST_CHANGE#    NEXT_CHANGE#
--------------- --------------- --------------- ---------------
         981393               1     10540998662     10541024661
         744280               2     10541014332     10541024657 *  In a RAC environment, use the lower to ensure we have the redo required from threads

for sequnce no 744280+1

  The options are "until time", "until scn", or "until sequence".

to create restore script

for RAC

define oldname='ibrac'
define newname='xbrac'


set pages 0 head off feed off veri off lines 500 pages 2000 echo off termout off trimspool on

column cmd format a140
spool restore_recover_&&newname\.rman

select 'RUN '||CHR(10)||'{' cmd from dual
union all
--Rename the datafiles
select 'SET NEWNAME FOR DATAFILE '||file#||' TO '''||NNAME||''';' cmd
from (SELECT FILE# ,NAME,replace(replace(name,upper('&&oldname'),upper('&&newname')),lower('&&oldname'),lower('&&newname')) NNAME FROM V$DATAFILE) df
union all
--Rename the online redo logs
select 'SQL "ALTER DATABASE RENAME FILE '''''||MEMBER||''''' TO '||chr(10)||' '''''||replace(replace(member,upper('&&oldname'),upper('&&newname')),lower('&&oldname'),lower('&&newname'))||''''' ";' cmd
from (SELECT GROUP#,MEMBER FROM V$LOGFILE)
union all
--Do a SET UNTIL to prevent recovery of the online logs
select 'SET UNTIL SCN '||MINSCN||';' cmd
from (select (min(next_change# )+1) MINSCN from (select thread#,sequence#,  first_change#, next_change#    from v$archived_log L, v$database D
where L.resetlogs_change# = D.resetlogs_change# 
and  (thread#,sequence#) in (select thread#,max(sequence#) sequence# from v$archived_log L, v$database D
                             where L.resetlogs_change# = D.resetlogs_change# 
                             group by thread#
                             )
)
      )
union all
--restore the database and switch the datafile names
select  'RESTORE DATABASE CHECK READONLY;' cmd from dual
union all
select 'SWITCH DATAFILE ALL;' cmd from dual
union all
--recover the database
select 'RECOVER DATABASE DELETE ARCHIVELOG;' from dual
union all
select '}'||CHR(10)||'EXIT' from dual
/

select sequence#, thread#, first_change#, next_change#  from v$backup_redolog
           where 1=1 
and sequence# in (981393,744280);


--with scn no !!!

set linesize 200 heading off feedback off 
col file_name format a100
col "Query For RMAN Restoration" for a70

select 'run {' "Query For RMAN Restoration" from dual 
union all
select 'set newname for datafile ' ||FILE#|| ' to '||'''/u01/datafile/'|| substr(name,instr(name,'/',-1)+1, instr(substr(name,instr(name,'/',-1)+1),'.')-1 ) ||'.dbf'' ;' file_name from v$datafile
union all
select 'set until scn ' ||(min(next_change# )+1) ||';'
from (select thread#,sequence#,  first_change#, next_change#    from v$archived_log L, v$database D
where L.resetlogs_change# = D.resetlogs_change# 
and  (thread#,sequence#) in (select thread#,max(sequence#) sequence# from v$archived_log L, v$database D
                             where L.resetlogs_change# = D.resetlogs_change# 
                             group by thread#
                             )
)
union all
select 'restore database;' from dual 
union all
select 'switch datafile all;' from dual 
union all
select 'recover database;' from dual 
union all
select '} ' from dual ;







run {
set newname for datafile 1 to '/u01/datafile/system01.dbf' ;
set newname for datafile 2 to '/u01/datafile/apex_data01.dbf' ;
set newname for datafile 3 to '/u01/datafile/sysaux01.dbf' ;
set newname for datafile 4 to '/u01/datafile/undotbs01.dbf' ;
set newname for datafile 5 to '/u01/datafile/undotbs02.dbf' ;
set newname for datafile 7 to '/u01/datafile/users01.dbf' ;
set newname for datafile 8 to '/u01/datafile/test_data01.dbf' ;
set newname for datafile 9 to '/u01/datafile/test_data_ind_01.dbf' ;
set newname for datafile 10 to '/u01/datafile/lobtest.dbf' ;
set newname for datafile 11 to '/u01/datafile/test_uniform.dbf' ;
set newname for datafile 12 to '/u01/datafile/test.dbf' ;
set newname for datafile 13 to '/u01/datafile/test1.dbf' ;
set newname for datafile 14 to '/u01/datafile/test1.dbf' ;
set newname for datafile 15 to '/u01/datafile/bigtabs.dbf' ;
set newname for datafile 16 to '/u01/datafile/test_data02.dbf' ;
set until scn 2773770359;
restore database;
switch datafile all;
recover database;
}






---with Sequence 

c
set linesize 200 heading off feedback off 
col file_name format a100
col "Query For RMAN Restoration" for a70

select 'run {' "Query For RMAN Restoration" from dual 
union all
select 'set newname for datafile ' ||FILE#|| ' to '||'''/u01/datafile/'|| substr(name,instr(name,'/',-1)+1, instr(substr(name,instr(name,'/',-1)+1),'.')-1 ) ||'.dbf'' ;' file_name from v$datafile
union all
select 'Set Until Sequence '||(sequence# +1) || ' ' ||'Thread '||thread# ||';' 
--, next_change# 
   from v$archived_log L, v$database D
where L.resetlogs_change# = D.resetlogs_change# 
and  next_change#  in (select min(next_change# ) from (select thread#,sequence#,  first_change#, next_change#    from v$archived_log L, v$database D
                       where L.resetlogs_change# = D.resetlogs_change# 
                        and  (thread#,sequence#) in (select thread#,max(sequence#) sequence# from v$archived_log L, v$database D
                             where L.resetlogs_change# = D.resetlogs_change# 
                             group by thread#
                             )
                     )
					 )
union all
select 'restore database;' from dual 
union all
select 'switch datafile all;' from dual 
union all
select 'recover database;' from dual 
union all
select '} ' from dual ;


run {
set newname for datafile 1 to '/u01/datafile/system01.dbf' ;
set newname for datafile 2 to '/u01/datafile/apex_data01.dbf' ;
set newname for datafile 3 to '/u01/datafile/sysaux01.dbf' ;
set newname for datafile 4 to '/u01/datafile/undotbs01.dbf' ;
set newname for datafile 5 to '/u01/datafile/undotbs02.dbf' ;
set newname for datafile 7 to '/u01/datafile/users01.dbf' ;
set newname for datafile 8 to '/u01/datafile/test_data01.dbf' ;
set newname for datafile 9 to '/u01/datafile/test_data_ind_01.dbf' ;
set newname for datafile 10 to '/u01/datafile/lobtest.dbf' ;
set newname for datafile 11 to '/u01/datafile/test_uniform.dbf' ;
set newname for datafile 12 to '/u01/datafile/test.dbf' ;
set newname for datafile 13 to '/u01/datafile/test1.dbf' ;
set newname for datafile 14 to '/u01/datafile/test1.dbf' ;
set newname for datafile 15 to '/u01/datafile/bigtabs.dbf' ;
set newname for datafile 16 to '/u01/datafile/test_data02.dbf' ;
Set Until Sequence 10 Thread 1;
restore database;
switch datafile all;
recover database;
}


allocate channel from different nodes

$rman target /



run {
allocate channel d0_1 device type disk maxopenfiles 32 connect 'sys/sys@//ibrac01.anuj.net:1521/ibrac' ;
allocate channel d0_2 device type disk maxopenfiles 32 connect 'sys/sys@//ibrac02.anuj.net:1521/ibrac' ;

DELETE EXPIRED ARCHIVELOG ALL;

release channel d0_1;
release channel d0_2;
}

using target database control file instead of recovery catalog
allocated channel: d0_1
channel d0_1: SID=1836 instance=ibrac1 device type=DISK

allocated channel: d0_2
channel d0_2: SID=1346 instance=ibrac2 device type=DISK

specification does not match any archived log in the repository

released channel: d0_1

released channel: d0_2

validate




export NLS_DATE_FORMAT='dd-mm-yyyy hh24:mi:ss' ;

RUN
 {
set until time "to_date('28-AUG-2023 01:30:00','DD-MON-YYYY HH24:MI:SS')";
restore spfile validate;
restore controlfile validate;
restore archivelog from time 'sysdate-23/24' validate;
}

cat Rman.sh

cat: Rman.sh: No such file or directory

[oracle@wcp12cr2 ~]$ cd RmanBackup/

[oracle@wcp12cr2 RmanBackup]$ cat Rman.sh

#!/bin/bash

export ORACLE_SID=orcl

export ORACLE_HOME=/oracle/db/ohome

export PATH=$PATH:$ORACLE_HOME/bin

export LOG=/home/oracle/backup_orcl.log

echo " Backup started at :`date`" >$LOG

rman target / <<EOF>>$LOG

run {

allocate channel c1 type disk ;

allocate channel c2 type disk ;

backup AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 1 database tag ORCL_FULL format '/home/oracle/RmanBackup/rman_bkp_inc_%d_%t_%s_%p_%U';

sql 'alter system archive log current';

backup archivelog all delete all input FORMAT '/home/oracle/RmanBackup/Arc_%t_set%s_piece%p.rman';

backup spfile format '/home/oracle/RmanBackup/spfile_%d_%s_%T_dbid%I.rman';

backup current controlfile format '/home/oracle/RmanBackup/ctl_%t_dbid%I.rman';

release channel c1;

release channel c2;

}

exit

EOF

echo " Backup completed at :`date`" >>$LOG

export NLS_DATE_FORMAT='dd-mm-yyyy hh24:mi:ss'

rman target / | tee /opt/Rman/rman_`/bin/date +\%d\%m\%y:%H:%M:%S`.log

RUN

{

set CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/opt/Rman/%F';

ALLOCATE CHANNEL ch11 TYPE DISK MAXPIECESIZE 10G;

ALLOCATE CHANNEL ch12 TYPE DISK MAXPIECESIZE 10G;

ALLOCATE CHANNEL ch13 TYPE DISK MAXPIECESIZE 10G;

BACKUP FORMAT '/opt/Rman/%d_D_%T_%u_s%s_p%p'

INCREMENTAL LEVEL 0 DATABASE CURRENT CONTROLFILE FORMAT '/opt/Rman/%d_C_%T_%u'

SPFILE FORMAT '/opt/Rman/%d_S_%T_%u' PLUS ARCHIVELOG FORMAT '/opt/Rman/%d_T%T_arch_s%s_p%p_t%t';

sql "alter system archive log current";

backup format '/opt/Rman/%d_T%T_arch_s%s_p%p_t%t' archivelog all filesperset 4 tag "RMANFULL";

sql "alter system archive log current";

backup format '/opt/Rman/%d_T%T_arch_s%s_p%p_t%t' archivelog all filesperset 4 tag "RMANFULL";

SQL "alter database backup controlfile to trace as ''/opt/Rman/ctl_@_trace.sql'' reuse resetlogs";

sql "ALTER DATABASE CREATE STANDBY CONTROLFILE AS ''/opt/Rman/STANDBY_control01.ctl'' REUSE";

SQL "create pfile=''/opt/Rman/init@.ora'' from spfile";

report schema;

restore database preview;

RELEASE CHANNEL ch11;

RELEASE CHANNEL ch12;

RELEASE CHANNEL ch13;

}

RMAN> run { set until sequence 120;
            restore database root ;  ------------------------->CDB$ROOT
            restore database "PDB$SEED";  -------------------------------->PDB$SEED is required
            restore database PDB1; }    ------------------------------->PDB needing to restore
OR

RMAN>run { Set until sequence 120 ;
          restore database root database "PDB$SEED" database PDB1; } 

If directory structure on test server is different than the target/source database, 'SET NEWNAME' will be needed:

RMAN> report schema ; 
This would show the file number associated with CDB$ROOT/PDB$SEED/PDB1.
Use 'set newname for datafile <fileno> to <new path>' to specify a new path for the datafiles.    
RMAN> run { set newname for datafile <fileno> to 'system.dbf' ;
            set newname for datafile <fileno> to 'system.dbf' ;
            ......
            restore database root; ------------------------->CDB$ROOT
            restore database "PDB$SEED"; -------------------------------->PDB$SEED is required 
            restore database PDB1;  ------------------------------->PDB we want to restore
            switch datafile all; }  

RMAN>Run {Set until sequence 120;
recover database skip forever tablespace PDB2:SYSTEM,PDB2:USER,PDB2:SYSAUX,..... ; }

spfile info

===========

SELECT

distinct p.handle

FROM v$backup_piece_details p

, v$backup_spfile sp

, v$backup_spfile_details s

WHERE p.bs_key = s.bs_key

AND p.status = 'A'

--AND p.device_type = 'DISK'

ORDER BY 1 DESC

export NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI:SS"
[oracle@<NODE>]$ rman target sys/<PASSWORD>@<TNS_ALIAS> auxiliary sys/<PASSWORD>@<TNS_ALIAS>| tee /tmp/rmanDUPLICATE.log

Anuj Singh Oracle DBA

Search This Blog

Total Pageviews

Saturday 16 July 2011

RMAN handy commands

Useful RMAN COMMANDS

RMAN block recovery & ORA-600

Recover a database with JUST the datafiles?

Oracle DBA

anuj blog Archive