RMAN Configuration

BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;

CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 2G;

 

To Change the archivelog destination

 

alter system set log_archive_dest_1='LOCATION=/vol2/oracle/Archives’;

 

To change the rman backup location..

 

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/vol0/RMAN_BK/%d-%T-%U';

 

To change the controlfile  location..

 

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 
'/u01/app/oracle/admin/flash/%F';

 

 

LOG_ARCHIVE_FORMAT = “LOG%s_%t_%r.arc”
Various parameters that can be used with the LOG_ARCHIVE_FORMAT parameter are given below:
%s – log sequence number
%S – log sequence number, padded with zero
%t – thread number
%T – thread number, padded with zero
%a – activation id
%d – database id
%r – reset logs id

 

SQL> alter system set log_archive_format='hasaral_%s_%t_%r.arc' scope=spfile;
System altered.

SQL> shut immediate
SQL>startup

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

 

Steps to enable archivelog—

 

 

SQL> archive log list

SQL> show parameter recovery_file_dest

 

alter system set log_archive_dest_1='LOCATION=/u02/app/oracle/oradata/orcl/arch' scope = both;

 

shutdown immediate;

 

SQL> startup mount

SQL> alter database archivelog;

SQL> alter database open;

SQL> archive log list

SQL> alter system switch logfile;

 

SQL> host

[oracle@ora1 ~]$ ls /u02/app/oracle/oradata/orcl/arch

1_27_711369564.dbf

[oracle@ora1 ~]$ exit

exit

 

SQL>

 

 

 

 

 

 

 

1. show parameter db_recovery_file_dest;

2. Alter system set  db_recovery_file_dest=’/vol1/oracle/backup’;

3. Alter system set db_recovery_file_dest_size=2G;

 

 

4. rman target /

 

Offline Backup--------

 

5. RMAN> configure retention policy to redundancy 2; (This means RMAN will try to keep                         

      2 copies of the database backups)

6. RMAN>configure controlfile autobackup on;

 

 

 CONFIGURE DEFAULT DEVICE TYPE DISK FORMAT ‘/vol0/rman_backups/cdb/%U’;

 

 

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 
'/u01/app/oracle/admin/flash/%F';

 

7. RMAN>shutdown immediate

8. RMAN>startup mount

9. RMAN>backup database;

1. RMAN>sql ’alter database open’;

 

 

 

Online Backup---------

 

1. RMAN>backup database plus archivelog delete input;

2. RMAN>backup archivelog all delete input;

 

 

-------- To Find the archive Destination---------

 

SQL> archive log list

SQL> show parameter db_recovery_file_dest

SQL> select dest_name,status,destination from v$archive_dest;

 

 

ENABLE BLOCK CHANGE TRACKING

 

SQL> alter database enable block change tracking
using file /u01/app/oracvle/mysid/data/block_change_tracking.dbf';

 

SQL> alter database enable block change tracking;

 

 

 

 

select filename, status, bytes from   v$block_change_tracking
SELECT * FROM v$sgastat WHERE name like 'CTWR%';

 

 

 

 

 

Startup nomount pfile=c:\oracle\product\10.1.0.3\database\initbooktst.ora

restore spfile from autobackup;

 

RMAN>list archivelog all;
RMAN>list copy of archivelog until time ‘SYSDATE-10′;
RMAN>list copy of archivelog from time ‘SYSDATE-10′
RMAN>list copy of archivelog from time ‘SYSDATE-10′ until time ‘SYSDATE-2′;
RMAN>list copy of archivelog from sequence 1000;
RMAN>list copy of archivelog until sequence 1500;
RMAN>list copy of archivelog from sequence 1000 until sequence 1500;

 

 

Archivelog Delete Commands

 

RMAN>delete archivelog all;
RMAN>delete archivelog until time ‘SYSDATE-10′;
RMAN>delete archivelog from time ‘SYSDATE-10′
RMAN>delete archivelog from time ‘SYSDATE-10′ until time ‘SYSDATE-2′;
RMAN>delete archivelog from sequence 1000;
RMAN>delete archivelog until sequence 1500;
RMAN>delete archivelog from sequence 1000 until sequence 1500;

Note : Also, you can use noprompt statement for do not yes-no question.
RMAN>delete noprompt archivelog until time ‘SYSDATE-10′;

 

delete noprompt force archivelog all;

delete noprompt force archivelog all completed before 'SYSDATE-2';

 

 

crosscheck backup;

Crosscheck archivelog all;

delete backup tag=TAG20131112T041335;

delete backup;

List backup summary;

 

 

select dbid from v$database;

 

 

LIST COPY;

LIST INCARNATION OF DATABASE;

DELETE EXPIRED BACKUP

DELETE EXPIRED ARCHIVELOG ALL

 

 

 

 

 

--------- Database must be in mount mode to change the archivelog mode--------

 

Disable flashback mode first if it enabled..

 

 

SQL> alter database flashback off;

alter database noarchivelog;

DELETE FORCE NOPROMPT BACKUPSET TAG 'weekly_bkup';

 

backup current controlfile;

 

backup datafile 4 include current controlfile;

backup as copy current controlfile format c:\bkp_controlfile.ctl

alter system switch logfile;

alter system archive log current

 

 

 

RESTORE DATABASE UNTIL TIME "to_date('07/23/2010 00:00:00' , 'mm/dd/yy hh24:mi:ss')";

 

 

 

restore archivelog from logseq 665 until logseq 671;

 

 

 

 

EXPDP Script

#!/bin/bash
export ORACLE_HOME=/vol0/app/oracle/product/11.2.0/db_1
export ORACLE_SID=XLR
export EXPORT_FOLDER=/vol0/pump
DATE=$(date +"%Y%m%d")
$ORACLE_HOME/bin/expdp \"/ as sysdba\" full=y directory=PUMP \
dumpfile=$DATE-${ORACLE_SID}_full_export.dmp logfile=$DATE-${ORACLE_SID}_full_export.log \
flashback_time=SYSTIMESTAMP
tar cjf $EXPORT_FOLDER/$DATE-${ORACLE_SID}_full_export.tar.bz2 \
$EXPORT_FOLDER/$DATE-${ORACLE_SID}_full_export.dmp \
$EXPORT_FOLDER/$DATE-${ORACLE_SID}_full_export.log
rm $EXPORT_FOLDER/$DATE-${ORACLE_SID}_full_export.dmp $EXPORT_FOLDER/$DATE-${ORACLE_SID}_full_export.log
find $EXPORT_FOLDER/*${ORACLE_SID}_full_export.tar.bz2 -mtime +15 -delete

Re-Create Controlfile

 

 

The restored RAMN open the database with resetlogs encountered ORA-00392 error:

ORA-00392: log 7 of thread 1 is being cleared, operation not allowed

RMAN> alter database open resetlogs; 
RMAN-00571: ============================================== ============= 
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== 
RMAN-00571: ============================================== ============= 
RMAN-03002: failure of alter db command at 05/25/2012 09:41:46 
ORA-00392: log 7 of thread 1 is being cleared, operation not allowed 
ORA-00312: online log 7 thread 1: '/ opt/ora11g/oradata/acscnprd/redo/redo07.rdo'

Get the script to create the control file 
SQL>

ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/tmp/control.sql' resetlogs;

(2) modify / tmp / control.sql the location of the control file is created on the RESETLOGS option. 
CREATE CONTROLFILE REUSE DATABASE "ACSCNPRD" FORCE LOGGING ARCHIVELOG 
.... 
LOGFILE 
...... 
RESETLOGS 
DATAFILE

3 Close the database 
SQL> SHUTDOWN IMMEDIATE

Database state started to mount 
SQL> STARTUP FORCE NOMOUNT

Execute the generated control.sql 
SQL> @/tmp/control.sql 
ORA-01081: cannot start already-running ORACLE - shut it down first 
Control file created. 
PL / SQL procedure successfully completed. 
PL / SQL procedure successfully completed. 
PL / SQL procedure successfully completed. 
PL / SQL procedure successfully completed. 
ORA-00279: change 733410844 generated at 05/24/2012 05:15:53 ??needed for thread1 
ORA-00289: suggestion: 
/ Opt/ora11g/fast_recovery_area/ACSCNPRD/archivelog/2012_05_25/o1_mf_1_7293_% u_.arc 
ORA-00280: change 733410844 for thread 1 is in sequence # 7293

ORA-00308: cannot open archived log 
'/ Opt/ora11g/fast_recovery_area/ACSCNPRD/archivelog/2012_05_25/o1_mf_1_7293_% u_.arc' 
ORA-27037: unable to obtain file status 
Linux-x86_64 Error: 2: No such file or directory 
Additional information: 3 
ALTER DATABASE OPEN RESETLOGS 
* 
ERROR at line 1: 
ORA-01113: file 1 needs media recovery 
ORA-01110: data file 1: '/ opt/ora11g/oradata/acscnprd/system01.dbf'

ALTER TABLESPACE TEMP ADD TEMPFILE '/ opt/ora11g/oradata/acscnprd/temp01.dbf' REUSE 
* 
ERROR at line 1: 
ORA-01109: database not open

Database altered.

6 Recovery 
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL; 
ORA-00279: change 733410844 generated at 05/24/2012 05:15:53 ??needed for thread1 
ORA-00289: suggestion: 
/ Opt/ora11g/fast_recovery_area/ACSCNPRD/archivelog/2012_05_25/o1_mf_1_7293_% u_.arc 
ORA-00280: change 733410844 for thread 1 is in sequence # 7293

Specify log: {<RET> = suggested | filename | AUTO | CANCEL} 
CANCEL 
Media recovery canceled.

7 RESETLOGS open the database. 
SQL> ALTER DATABASE OPEN RESETLOGS; 
Database altered.

View the database state 
SQL> select open_mode from v $ database;

OPEN_MODE 
-------------------- 
READ WRITE 

 

RMAN Script

ORACLE_SID=BACKLIFE
ORACLE_HOME=/u01/oracle/product/12.2.0/dbhome_1
PATH=$PATH:${ORACLE_HOME}:${ORACLE_HOME}/bin
export ORACLE_SID
export ORACLE_HOME
export LD_LIBRARY_PATH
export PATH

rman target / <<EOF
spool log to /u02/RMAN/BACKLIFE/BACKLIFE_RMAN.log
run {
CROSSCHECK ARCHIVELOG ALL;
delete noprompt expired archivelog all;
CROSSCHECK BACKUP;
delete noprompt expired backup;
BACKUP AS BACKUPSET DATABASE FORMAT '/u02/RMAN/BACKLIFE/%d-%T-%U' PLUS ARCHIVELOG FORMAT '/u02/RMAN/BACKLIFE/%d-%T-%U';
BACKUP CURRENT CONTROLFILE;
delete noprompt archivelog until time 'SYSDATE-7';
delete noprompt backup completed before 'sysdate-14';
delete noprompt obsolete;
delete noprompt expired backup;
}
exit
EOF