Frequently Used Commands

Oracle Mosted Used Commands II

Hi, My name is Jimmy Sam. If you need DBA help, please email to jimmy_sam001@yahoo.com for further discussion. Thanks.

12c 11g
dj ds Oracle In Action rm itpub faq racsig

rman cloning

goldengate

norman

recover database using backup controlfile until cancel;

Rman Retention Information In Control File

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 8 DAYS;

sqlplus> control_file_keep

show parameter control_file_record_keep_time

alter system set control_file_record_keep_time=8 sid='*' scope=both;

Run Time Dictionary View

select table_name from dict where table_name like '%RMAN%';
select * from V$RMAN_BACKUP_TYPE;

-- show all;
select * from V$RMAN_CONFIGURATION;


$ORACLE_HOME/dbs/c-dbid-date-00
$ORACLE_HOME/dbs/c-IIIIIIIIII-YYYYMMDD-QQ
RMAN> configure controlfile autobackup off ;
RMAN> show controlfile autobackup ;
RMAN> configure controlfile autobackup on ;

RMAN> connect target /
RMAN> startup nomount;
RMAN> set dbid 9098098990;
RMAN> restore controlfile from autobackup;
RMAN> restore controlfile from autobackup maxday 20;
RMAN> restore controlfile from autobackup maxseq 10;
RMAN> restore controlfile to '/home/oracle/ctl.bk' from autobackup maxseq 10;


select * from V$RMAN_COMPRESSION_ALGORITHM;
select * from V$RMAN_ENCRYPTION_ALGORITHMS;

-- check the last couple of days rman backup status: FAILED, COMPLETED, RUNNING 
SELECT 
substr(OPERATION, 1,15) as OPERATION 
,substr(STATUS, 1,15) as STATUS 
,to_char(START_TIME,'mm/dd/yy hh24:mi') START_TIME
,to_char(END_TIME,'mm/dd/yy hh24:mi')   END_TIME
,round((END_TIME-START_TIME)*1440)  Mins
,substr(OBJECT_TYPE, 1,15)          Type
,substr(OUTPUT_DEVICE_TYPE, 1,15)   Out
,MBYTES_PROCESSED                   Mbytes
FROM V$RMAN_STATUS
WHERE OPERATION = 'BACKUP' 
AND OBJECT_TYPE = 'DB FULL'
-- AND STATUS != 'COMPLETED'  /* FAILED, COMPLETED, RUNNING */
AND START_TIME > (sysdate -2)
;

column output format A100
set pagesize 1000
set long 300000
set longchunk 300000
-- 1):
select a.sid, a.recid, b.operation, b.status, a.output, b.end_time 
from v$rman_output a, v$rman_status b 
where a.rman_status_recid=b.recid and a.rman_status_stamp=b.stamp 
and b.end_time > sysdate -1 
and b.operation='BACKUP'
and b.object_type = 'DB FULL'
and rownum < 1000
order by a.sid, a.recid;

select a.sid, a.recid, b.operation, b.status, a.output, b.end_time 
from v$rman_output a, v$rman_status b 
where a.rman_status_recid=b.recid and a.rman_status_stamp=b.stamp 
and b.end_time > sysdate -1 
and b.operation='BACKUP'
and b.object_type = 'DB FULL'
and b.status = 'RUNNING' 
and rownum < 1000
order by a.sid, a.recid;

-- 2): 
select status, a.* from v$rman_status a where end_time > sysdate -1 and 
object_type = 'DB FULL'  and operation='BACKUP' and rownum < 30;
-- 3):
select * from V$RMAN_BACKUP_JOB_DETAILS where end_time > sysdate -1;
-- 4):
select * from V$RMAN_BACKUP_SUBJOB_DETAILS where end_time > sysdate -1 and rownum < 3;

Run rman trace

rman target / catalog rman@rmn debug log=/tmp/check.log
RMAN> debug on

Change Of Behavior: RMAN backup in Dataguard Standby Database

Start from 11.2.0.4.0 ( and 12c ), for backup standby database, we need username and password.

Here is the error we get from rman backup of a standby database.

RMAN-06820: WARNING: failed to archive current log at primary database
ORACLE error from target database:
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-01017: invalid username/password; logon denied
ORA-17629: Cannot connect to the remote database server

-- ----------------
Suggested Workaround:

Instead of using "rman target /"

We need to use "rman target sys/password@stby"

Note:  The password, within the password file, for the primary and standby should be identical.

top

Cleanup Database File

set pagesize 500

col STATUS format a9
col hrs format 999.99
select SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;

set linesize 180 trimspool on
col opname   format a34
col username format a14
SELECT opname, username, 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;

set linesize 180 trimspool on pagesize 500
select OUTPUT from V$RMAN_OUTPUT
where session_key = 
( select max(session_key) from v$rman_output);

rem cleanup the database 
set pagesize 5000

select '\rm -f ' || name from (
select name from v$datafile
union select name from v$controlfile
union select member from v$logfile
union select name from v$tempfile
);

top

cleanup_archive_log_file

rman: delete archive log file
echo "DELETE noprompt archivelog until time \"sysdate - 1\";"|rman target />/dev/null 2>&1
echo "crosscheck archivelog all;"|rman target />/dev/null 2>&1

top

simple rman backup

rman backup full:
run
{
  allocate channel dsk1 DEVICE TYPE DISK maxpiecesize 6144M;
  allocate channel dsk2 DEVICE TYPE DISK maxpiecesize 6144M;
  allocate channel dsk3 DEVICE TYPE DISK maxpiecesize 6144M;
  allocate channel dsk4 DEVICE TYPE DISK maxpiecesize 6144M;
  allocate channel dsk5 DEVICE TYPE DISK maxpiecesize 6144M;
  allocate channel dsk6 DEVICE TYPE DISK maxpiecesize 6144M;
  allocate channel dsk7 DEVICE TYPE DISK maxpiecesize 6144M;
  allocate channel dsk8 DEVICE TYPE DISK maxpiecesize 6144M;
  backup incremental level $level as compressed backupset database
    format '/my_dir/RMAN_%d_201807010801_LV0_s%s_p%p.DBF' tag hot_inc_bkup include current controlfile;
  release channel dsk1;
  release channel dsk2;
  release channel dsk3;
  release channel dsk4;
  release channel dsk5;
  release channel dsk6;
  release channel dsk7;
  release channel dsk8;
}

# sql 'alter system archive log current';
# backup as compressed backupset archivelog from time 'SYSDATE-5/24' format '/my_dir//RMAN_%d_201807010801_s%s_p%p.ARC';
# backup current controlfile format '/my_dir/RMAN_%d_${DATEHM}_s%s_p%p.CTL' tag hot_control;

top

simple rman restore

RMAN> SET DBID 12345;
RMAN> STARTUP NOMOUNT;
RMAN> RESTORE CONTROLFILE FROM "/backup/rman/myrmanbackup.ctl"; 
RMAN> ALTER DATABASE MOUNT;
RMAN> catalog start with "/backup/rman/";
RMAN> -- catalog start with "/backup/rman/" noprompt;
RMAN> -- delete old backup file info from controlfile
RMAN> crosscheck backup;
RMAN> delete expired backupset;
RMAN> -- delete noprompt expired backupset;
RMAN> -- RESTORE DATABASE;
run  {
allocate channel c1 type disk ; 
allocate channel c2 type disk ; 
allocate channel c3 type disk ; 
allocate channel c4 type disk ; 
allocate channel c5 type disk ; 
allocate channel c6 type disk ;
allocate channel c7 type disk ;
allocate channel c8 type disk ;
# restore database;
release channel c1; 
release channel c2; 
release channel c3; 
release channel c4; 
release channel c5; 
release channel c6;
release channel c7;
release channel c8;
}

run  {
allocate channel c1 type disk ; 
allocate channel c2 type disk ; 
allocate channel c3 type disk ; 
allocate channel c4 type disk ; 
allocate channel c5 type disk ; 
allocate channel c6 type disk ;
allocate channel c7 type disk ;
allocate channel c8 type disk ;
allocate channel c9 type disk ; 
allocate channel c10 type disk ; 
allocate channel c11 type disk ; 
allocate channel c12 type disk ; 
allocate channel c13 type disk ; 
allocate channel c14 type disk ;
allocate channel c15 type disk ;
allocate channel c16 type disk ;
# recover database;
release channel c1; 
release channel c2; 
release channel c3; 
release channel c4; 
release channel c5; 
release channel c6;
release channel c7;
release channel c8;
release channel c9; 
release channel c10; 
release channel c11; 
release channel c12; 
release channel c13; 
release channel c14;
release channel c15;
release channel c16;
}

RMAN> set archivelog destination to '/u9x/oracle/orcl/archives';
RMAN> restore archivelog sequence 998;
RMAN> restore archivelog from logseq=998 until logseq=998;
RMAN> restore archivelog from logseq=990 until logseq=998;

SQL> RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;
-- RMAN> recover database;
-- RMAN> RECOVER DATABASE;
-- RMAN> RECOVER DATABASE until logseq 999; # only recover until 998
-- RMAN> RECOVER DATABASE until sequence 999; # only recover until 998
-- RMAN> RECOVER DATABASE until sequence 999 thread 1; # only recover until 998`
-- RMAN> RECOVER DATABASE until time; # only recover until 998
-- RMAN> RECOVER DATABASE until scn; # only recover until 998
RMAN> ALTER DATABASE OPEN RESETLOGS;

top

simple rman restore to different location

RUN
{  
  SET NEWNAME FOR DATAFILE 2 TO '/disk2/df2.dbf';
  SET NEWNAME FOR DATAFILE 3 TO '/disk2/df3.dbf';
  SET NEWNAME FOR DATAFILE 4 TO '/disk2/df4.dbf';
  RESTORE DATABASE;
  SWITCH DATAFILE ALL;
  RECOVER DATABASE;
}

top

rman duplicate to a different database name

-- startup nomount;

rman target sys/mypassword@mysid  auxiliary /

RUN
{
allocate auxiliary channel c1 type disk;
allocate auxiliary channel c2 type disk;
allocate auxiliary channel c3 type disk;
allocate auxiliary channel c4 type disk;
DUPLICATE DATABASE TO orcl 
BACKUP LOCATION '/backups/oracle/orcl/XR201810052145/' 
NOFILENAMECHECK
LOGFILE
      GROUP 1 ('/u11/oracle/data/orcl/orcllog01a.log', '/u12/oracle/data/orcl/orcllog01b.log')
SIZE 50M REUSE,
      GROUP 2 ('/u12/oracle/data/orcl/orcllog02a.log', '/u13/oracle/data/orcl/orcllog02b.log')
SIZE 50M REUSE,
      GROUP 3 ('/u13/oracle/data/orcl/orcllog03a.log', '/u14/oracle/data/orcl/orcllog03b.log')
SIZE 50M REUSE,
      GROUP 4 ('/u14/oracle/data/orcl/orcllog04a.log', '/u11/oracle/data/orcl/orcllog04b.log')
SIZE 50M REUSE;
release  channel c1;
release  channel c2;
release  channel c3;
release  channel c4;
}

top

rman and archivelog

-- catalog
RMAN> catalog start with '/tmp/arch_logs';
RMAN> catalog start with '/tmp/arch_logs' noprompt;
RMAN> catalog archivelog '/oradata/arch_30.dbf';
RMAN> catalog archivelog '/../v2/archlog/ORCL/ORCL_1_744818711_38690.arc';

-- list
RMAN> list archivelog all;
RMAN> LIST BACKUP OF ARCHIVELOG ALL;

RMAN>list backuppiece '/oradata/backup/ORCL_3130551611_20150504_hbq647ef_1_1';
RMAN>list backupset 554;

-- rman restore
set archivelog destination to '/u9x/oracle/orcl/archives';

-- one single file
restore archivelog sequence 726547;
restore archivelog from logseq=726547 until logseq=726547;

-- multiple files
restore archivelog from logseq=726547 until logseq=726591;

-- rman uncatalog archivelog file 
RMAN>CHANGE ARCHIVELOG FROM LOGSEQ=726547 UNTIL LOGSEQ=726591 UNCATALOG

top

rman

list archivelog all;

a datafile was created after the start of a full incremental  0 rman backup, how to restore this datafile ?
answer:  
alter database create datafile '/u03/oradata/orcl/xxx.dbf' size 10G;
recover datafile 34; /* file_id */

unset TWO_TASK
set nocfau --> turn off controlfile auto backup
rman target /
run 
{
 configure controlfile autobackup off;
 set nocfau;
 backup as copy database format '/u01/oradata/clone_base/%U' ;
}

rmanbkp.sh -r -b obsolete

rmanbkp.sh -r -b expired

list backupset summary;

delete backupset completed before 'sysdate - 14';

show all;
REM CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
REM configure device type disk parallelism 2;

RUN
{
  ALLOCATE CHANNEL c1 DEVICE TYPE sbt;
  ALLOCATE CHANNEL c2 DEVICE TYPE sbt;
  ALLOCATE CHANNEL c3 DEVICE TYPE sbt;
  BACKUP DATAFILE 5;
  BACKUP DATAFILE 6;
  BACKUP DATAFILE 7;
}


RUN
{
  ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
  ALLOCATE CHANNEL c2 DEVICE TYPE DISK;
  ALLOCATE CHANNEL c3 DEVICE TYPE DISK;
  RESTORE DATABASE;
  RECOVER DATABASE:
}

rem rman cleanup
echo crosscheck archivelog all;|rman target /
echo crosscheck backupset;|rman target /
echo delete noprompt obsolete;|rman target /
echo delete noprompt expired archivelog all;|rman target /
echo delete noprompt expired backupset;|rman target /
echo delete noprompt expired archivelog all;|rman target /
echo delete noprompt expired backupset;|rman target /
echo delete noprompt expired backup of database;|rman target /
echo delete noprompt expired archivelog all;|rman target /
echo delete noprompt archivelog all backed up 2 times to device type disk;|rman target /

DELETE BACKUP OF archivelog UNTIL TIME='sysdate-5';
DELETE OBSOLETE RECOVERY WINDOW OF 4 DAYS;
DELETE BACKUP OF archivelog UNTIL TIME='sysdate-5';

crosscheck archivelog all;
delete expired archivelog all;
DELETE NOPROMPT OBSOLETE RECOVERY WINDOW OF 1 DAYS;
DELETE OBSOLETE RECOVERY WINDOW OF 1 DAYS;

crosscheck backup;
delete  expired backup;
delete  noprompt expired backup;

Manually delete archivelog in dev/qa box using rman

in rman:
old: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
new: CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;

in sqlplus:
alter system set control_file_record_keep_time=30 scope=both sid='*';

in shell:
echo "DELETE noprompt archivelog until time \"sysdate - 7\";"|rman target />/dev/null 2>&1
echo "crosscheck archivelog all;"|rman target />/dev/null 2>&1

export NLS_DATE_FORMAT='YYYY-MON-DD HH:MI:SS'

rem in dos or windows
set NLS_DATE_FORMAT=YYYY-MON-DD HH:MI:SS

rman target /<<EOF
list backupset summary;
EOF

export NLS_DATE_FORMAT='YYYY-MON-DD HH:MI:SS'
rman target /<<EOF
list archivelog all backed up 2 times to device type disk;
EOF

export NLS_DATE_FORMAT='YYYY-MON-DD HH:MI:SS'
rman target /<<EOF
delete noprompt archivelog all backed up 2 times to device type disk;
EOF


rmanbkp.sh -r -b obsolete

rmanbkp.sh -r -b expired

list backupset summary;

delete backupset completed before 'sysdate - 14';

export NLS_DATE_FORMAT="DD-MON-YY HH24:MI:SS"
export ORACLE_SID=SIBDEV01

# clean up archivelog files
rman target /
list archivelog all;
delete archivelog all backed up 1 times to disk;
delete archivelog all backed up 2 times to disk;
backup archivelog all delete input;
list archivelog all;

rman target /<<EOF
crosscheck archivelog all;
crosscheck backupset;
crosscheck copy;
delete noprompt archivelog all backed up 2 times to device type disk;
delete noprompt expired backupset;
delete noprompt expired backup of database;
delete noprompt expired archivelog all;
delete noprompt obsolete;
EOF

1):
rman target /

2):
rman
RMAN> connect target /

RMAN> show all;

RMAN> list summary;
RMAN> list backupset summary;
RMAN> list backupset;

RMAN> list backup summary;
RMAN> list backup;

RMAN> list expired archivelog all;
RMAN> list archivelog all;
RMAN> list archivelog time between "sysdate-1" and "sysdate";

RMAN> report unrecoverable;
RMAN> report obsolete;

RMAN> backup current controlfile;
RMAN> backup current controlfile tag=cf1 format 'dbbackupora/cf_%U.bck';



RMAN> sql 'alter system archive log current';
RMAN> sql 'alter system switch logfile';
RMAN> sql 'alter database backup controlfiel to trace';
RMAN> backup archivelog all;
RMAN> backup archivelog all delete input;
RMAN> backup archivelog all format '/dbbackupora/log_t%t_s%s_p%p' delete input;
RMAN> backup as compressed backupset archivelog high sequence 679 delete input;

RMAN> backup database;
RMAN> backup database plus archivelog all delete input;

RMAN> delete archivelog all backed up 1 times to disk;
RMAN> delete archivelog all backed up 2 times to disk;
RMAN> delete archivelog all backed up 2 times to device tpe sbt;

RMAN> delete archivelog all completed before 'sysdate-7';
RMAN> delete archivelog all completed before 'sysdate -1';
RMAN> delete archivelog all completed before 'sysdate -1/4';
RMAN> delete noprompt archivelog all completed before 'sysdate-7';

RMAN> DELETE COPY OF ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';
! RMAN-08120: WARNING: archived log not deleted, not yet applied by standby

RMAN> delete expired archivelog all; 
RMAN> delete noprompt expired archivelog all; 
RMAN> delete archivelog high sequence 650;

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


RMAN> delete obsolete; 

RMAN> delete backup completed before 'sysdate-1'; 

RMAN> delete expired backupset; 

RMAN> delete noprompt expired backupset; 

RMAN> report obsolete;
RMAN> delete obsolete;

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

RMAN>set echo on

RMAN> list backupset summary;
RMAN> list backup summary;
RMAN> list backup;
RMAN> delete backupset 1234;

RMAN> crosscheck archivelog all;
RMAN> crosscheck backup;
RMAN> crosscheck backupset;

RMAN> backup check logical validate database;

RMAN> catalog start with '/tmp/arch_logs';
RMAN> catalog start with '/tmp/arch_logs' noprompt;
RMAN> catalog archivelog '/oradata/arch_30.dbf';
RMAN> catalog archivelog '/../v2/archlog/ORCL/ORCL_1_744818711_38690.arc';
RMAN> catalog backuppiece '/oradata/01dmsbj4_1_1.log';
RMAN> catalog recovery area noprompt;

SQL> alter database backup controlfile to 'disk1/controlfile01.dbf';
RMAN> catalog controlfilecopy 'rdisk1/control01.ctl';

RMAN> restore archivelog all;
RMAN> restore archivelog from sequence 20 until sequence 28;
RMAN> restore archivelog scn between 547484587 and 547662805;

RMAN> run {
set until time "to_date('05/07/11 09:00:00' , 'dd/mm/yy hh24:mi:ss')";
restore database;
recover database;
alter database open resetlogs;
}

RMAN> run {
set until time "to_date('06/09/13 22:00:00' , 'dd/mm/yy hh24:mi:ss')";
recover database;
}


In sqlplus:
SQL> alter system set controlfile_keep_time=30; 
SQL> select name,status from v$archived_log;
SQL> select file#,name from v$datafile;

rman backup status
SELECT  
   substr(OPERATION, 1,15) as OPERATION 
   ,substr(STATUS, 1,15) as STATUS 
   ,to_char(START_TIME,'mm/dd/yy hh24:mi') START_TIME 
   ,to_char(END_TIME,'mm/dd/yy hh24:mi')   END_TIME 
   ,round((END_TIME-START_TIME)*1440)  
   ,substr(OBJECT_TYPE, 1,15)
   ,substr(OUTPUT_DEVICE_TYPE, 1,15) 
   ,MBYTES_PROCESSED     
   FROM V$RMAN_STATUS  
   WHERE OPERATION = 'BACKUP' 
   AND OBJECT_TYPE = 'DB FULL' 
--   AND STATUS != 'COMPLETED' 
   AND START_TIME > (sysdate -2)
order by end_time;

rman target=/ <<EOF >> ${LOGFILE}
show all;
backup incremental level ${BLEVEL} cumulative ${scope} tag 'L${BLEVEL}_BKP_${EXT2}' ;
backup filesperset=${FILEPERSET} device type disk tag 'ARCH_${EXT2}' archivelog all;
delete noprompt archivelog all backed up 1 times to device type disk;
exit;
EOF

rman target=/ <<EOF>>${LOGFILE}
show all;
shutdown immediate;
startup mount;
backup database tag 'COLD_BKP_${EXT2}';
alter database open;
exit;
EOF

rman target=/ <<EOF>>${LOGFILE}
show all;
backup device type disk tag 'ARCH_${EXT2}' archivelog all not backed up 2 times;
exit;
EOF


rman target=/ <<EOF >> ${LOGFILE}
show all;
backup filesperset=${FILEPERSET} device type disk tag 'ARCH_${EXT2}' archivelog all not backed up 2 times;
delete noprompt archivelog all backed up 2 times to device type disk;
exit;
EOF

rman target=/ <<EOF >> ${LOGFILE}
show all;
crosscheck backupset;
crosscheck copy;
exit;
EOF

rman target=/ <<EOF >> ${LOGFILE}
show all;
delete noprompt expired archivelog all;
delete noprompt expired backup of database;
exit;
EOF

rman target=/ <<EOF >> ${LOGFILE}
show all;
delete noprompt obsolete;
exit;
EOF

-- rman restore
run {
allocate channel c1 type disk;
SET DBID 744057840;
RESTORE CONTROLFILE from '/u03/oracle/10.2.0/dbbackups/RMAN_WBP4_201506301205_s37577_p1.CTL';
release channel c1;
}

restore controlfile to '/tmp/cntrl.bak' from '/u03/oracle/dbbackups_nocat/RMAN_WBP4_201506301205_s37577_p1.CTL';

run {
replicate controlfile from '/tmp/cntrl.bak';
}

RMAN> list backup of archivelog all;
RMAN> restore archivelog all;

export NLS_DATE_FORMAT="DD-MON-YY HH24:MI:SS"
export ORACLE_SID=SIBDEV01

# clean up archivelog files
rman target /
list archivelog all;
delete archivelog all backed up 1 times to disk;
backup archivelog all delete input;
list archivelog all;

rman target /<<EOF
crosscheck archivelog all;
crosscheck backupset;
crosscheck copy;
delete noprompt archivelog all backed up 2 times to device type disk;
delete noprompt expired backupset;
delete noprompt expired backup of database;
delete noprompt expired archivelog all;
delete noprompt obsolete;
EOF

RMAN> catalog start with '/tmp/arch_logs';
RMAN> catalog start with '/tmp/arch_logs' noprompt;

RMAN> catalog archivelog '/oradata/arch_30.arc';
RMAN> catalog archivelog '/oradata/v2/archlog/ORCL/ORCL_1_744818711_38690.arc' noprompt;

RMAN> catalog backuppiece '/oradata/01dmsbj4_1_1.log';
RMAN> catalog recovery area noprompt;

SQL> alter database backup controlfile to 'disk1/controlfile01.dbf';
RMAN> catalog controlfilecopy 'rdisk1/control01.ctl';

-- sometime we need to manual register archivelog using sqlplus if rman catalog don't work
SQL> alter database register logfile '/../v2/archlog/ORCL/ORCL_1_744818711_38690.arc';

1):
rman target /

2):
rman
RMAN> connect target /

RMAN> show all;

RMAN> list summary;
RMAN> list backupset summary;
RMAN> list backupset;

RMAN> list backup summary;
RMAN> list backup;

RMAN> list expired archivelog all;
RMAN> list archivelog all;
RMAN> list archivelog time between "sysdate-1" and "sysdate";

RMAN> report unrecoverable;
RMAN> report obsolete;

RMAN> backup current controlfile;
RMAN> backup current controlfile tag=cf1 format 'dbbackupora/cf_%U.bck';

RMAN> sql 'alter system archive log current';
RMAN> sql 'alter system switch logfile';
RMAN> sql 'alter database backup controlfiel to trace';

RMAN> backup archivelog all;
RMAN> backup archivelog all delete input;
RMAN> backup archivelog all format '/dbbackupora/log_t%t_s%s_p%p' delete input;
RMAN> backup as compressed backupset archivelog high sequence 679 delete input;

RMAN> backup database;
RMAN> backup database plus archivelog all delete input;

RMAN> delete archivelog all backed up 1 times to disk;
RMAN> delete archivelog all backed up 2 times to disk;
RMAN> delete archivelog all backed up 2 times to device tpe sbt;

RMAN> delete archivelog all completed before 'sysdate-7';
RMAN> delete archivelog all completed before 'sysdate -1';
RMAN> delete noprompt archivelog all completed before 'sysdate-7';

RMAN> DELETE COPY OF ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';
! RMAN-08120: WARNING: archived log not deleted, not yet applied by standby

RMAN> delete expired archivelog all; 
RMAN> delete noprompt expired archivelog all; 
RMAN> delete archivelog high sequence 650;

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


RMAN> delete obsolete; 
RMAN> delete backup completed before 'sysdate-1'; 
RMAN> delete expired backupset; 
RMAN> delete noprompt expired backupset; 

RMAN> report obsolete;
RMAN> delete obsolete;

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

RMAN>set echo on

RMAN> list backupset summary;
RMAN> list backup summary;
RMAN> list backup;
RMAN> delete backupset 1234;

RMAN> crosscheck archivelog all;
RMAN> crosscheck backup;
RMAN> crosscheck backupset;

RMAN> backup check logical validate database;

RMAN> catalog start with '/tmp/arch_logs';
RMAN> catalog archivelog '/oradata/arch_30.dbf';
RMAN> catalog archivelog '/../v2/archlog/ORCL/ORCL_1_744818711_38690.arc';
RMAN> catalog backuppiece '/oradata/01dmsbj4_1_1.log';
RMAN> catalog recovery area noprompt;

SQL> alter database backup controlfile to 'disk1/controlfile01.dbf';
RMAN> catalog controlfilecopy 'rdisk1/control01.ctl';

RMAN> restore archivelog all;
RMAN> restore archivelog from sequence 20 until sequence 28;
RMAN> restore archivelog scn between 547484587 and 547662805;

RMAN> run {
set until time "to_date('05/07/11 09:00:00' , 'dd/mm/yy hh24:mi:ss')";
restore database;
recover database;
alter database open resetlogs;
}

RMAN> run {
set until time "to_date('06/09/13 22:00:00' , 'dd/mm/yy hh24:mi:ss')";
recover database;
}

In sqlplus:
SQL> alter system set controlfile_keep_time=30; 
SQL> select name,status from v$archived_log;
SQL> select file#,name from v$datafile;

rman target=/ <<EOF >> ${LOGFILE}
show all;
backup incremental level ${BLEVEL} cumulative ${scope} tag 'L${BLEVEL}_BKP_${EXT2}' ;
backup filesperset=${FILEPERSET} device type disk tag 'ARCH_${EXT2}' archivelog all;
delete noprompt archivelog all backed up 1 times to device type disk;
exit;
EOF

rman target=/ <<EOF >> ${LOGFILE}
show all;
shutdown immediate;
startup mount;
backup database tag 'COLD_BKP_${EXT2}';
alter database open;
exit;
EOF

rman target=/ <<EOF >> ${LOGFILE}
show all;
backup device type disk tag 'ARCH_${EXT2}' archivelog all not backed up 2 times;
exit;
EOF


rman target=/ <<EOF >> ${LOGFILE}
show all;
backup filesperset=${FILEPERSET} device type disk tag 'ARCH_${EXT2}' archivelog all not backed up 2 times;
delete noprompt archivelog all backed up 2 times to device type disk;
exit;
EOF

rman target=/ <<EOF >> ${LOGFILE}
show all;
crosscheck backupset;
crosscheck copy;
exit;
EOF

rman target=/ <<EOF >> ${LOGFILE}
show all;
delete noprompt expired archivelog all;
delete noprompt expired backup of database;
exit;
EOF

rman target=/ <<EOF >> ${LOGFILE}
show all;
delete noprompt obsolete;
exit;
EOF

Recover Database To the lastest archive log file or redo log file


1): start database nomount
sqlplus "/ as sysdba"
SQL> startup nomount;

2): restore controlfile (need database in nomount state )
rman target /
RMAN> set dbid=nnnnnnnnn;
RMAN> restore controlfile from 'D:/rmanbackup/RMAN_ORCL_3.25.2017_4_5_S266147_P1.CTL';

3): change database to mount status 
sqlplus "/ as sysdba"
SQL> alter database mount

4): catalog database backup files (need database in mount state )
rman target /
RMAN> catalog start with 'D:/rmanbackup/';

5): need to restore some archive logfile ( optional )
RMAN> restore archivelog all;
RMAN> restore archivelog from sequence 20 until sequence 28;
RMAN> restore archivelog scn between 547484587 and 547662805;

6): restore and recover database ( There may be no enough disk space for archivelog files )
RMAN> run {
set until time "to_date('25/03/2017 04:47:00' , 'dd/mm/yyyy hh24:mi:ss')";
restore database;
recover database;
}

7): manually recover to put last archive logfile or redo log files in
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1792324589 generated at 03/25/2017 04:45:46 needed for thread1
ORA-00289: suggestion :
D:\ORACLE\ARCHIVES\ORCL_0000273289_0822385088_0001.ARC
ORA-00280: change 1792324589 for thread 1 is in sequence #273289
Specify log: {=suggested | filename | AUTO | CANCEL}
put archivelog or redo file ( full path)


8): Need open database with resetlogs option
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

-- sample rman backup script
-- 1):
run {    
allocate channel dsk1 DEVICE TYPE DISK maxpiecesize 10G ;   
allocate channel dsk2 DEVICE TYPE DISK maxpiecesize 10G ;         
sql 'alter system archive log current';         
backup as compressed backupset database             
  format '\\...\RMAN\RMAN_%d_2.24.2019_23_55_s%s_p%p.DBF'             
  tag hot_datafiles_toDisk             include current controlfile;         backup             
  archivelog from time 'SYSDATE-4/24'             
  format '\\...\RMAN_%d_2.24.2019_23_55_s%s_p%p.ARC'             
  tag hot_archive_toDisk ;         

backup current controlfile             
   format '\\...\RMAN\RMAN_%d_2.24.2019_23_55_s%s_p%p.CTL'             
   tag hot_control_toDisk ;             
   CROSSCHECK BACKUP;             
   CROSSCHECK ARCHIVELOG ALL;             
   DELETE NOPROMPT OBSOLETE RECOVERY WINDOW OF 1 DAYS;             
   release channel dsk1 ;             
release channel dsk2 ; 
}