Oracle Mosted Used Commands II

I am in the process of putting my notes into web pages, please keep checking in later for more contents

quick link

sql one liner   unlock user   expired user  
oracle job   restricted session   locked_object   kill session   Oracle Deployment Hanging   Sort Usage  
kill_session   active_session   session_blocker   session_wait?  
sqlplus   Flashback Database   Archive Log  
Oracle Startup   find_objects   find_object_source   table index   index rebuild   Chained Rows   Materialized Views   seg space  
Oracle Basics   External Links  
get dblink   create_db_link   tnsping   network_acl  
parallel_query   sort_usage  
setup sid using oraenv   init.ora   Opatch   redo logfile member and group   rename database file  
home 
12c   11g 
dj  ds  Oracle In Action  rm  itpub  faq  racsig 

rman cloning

dj 

goldengate

gv  pythian  myora  rm  ggsig  sai  top

rman

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';

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 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;