Oracle DBA Script - Backup

Backup using EM12c

http://www.oracle.com/technetwork/articles/oem/havewala-rman-em12c-2104270.html

Backup Setting (Reference: Oracle Distilled  )

Clear a particular backup configuration to default:
CONFIGURE BACKUP OPTIMIZATION CLEAR;  
configured to automatically backup the control file when a data file is added or when a backup is taken with CONTROLFILE AUTOBACKUP
configure controlfile autobackup on;

Set control file backup destination:
configure controlfile autobackup format for device type disk to '/u01/app/oracle/oradata/orcl/backup/%F';

Set backup file dest:

configure channel device type disk format 'c:\temp\%U' maxpiecesize 2 G;

keep the last two backups regardless of how old they are.

configure retention policy to redundancy 2;

backup database;

To clear Archive Log

Recovery Manager: Release 11.2.0.2.0 - Production on Thu Jan 30 13:59:05 2014

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

RMAN> 
connected to target database: XXXXX (DBID=9999999999)
using target database control file instead of recovery catalog

RMAN> 
echo set on


RMAN> set command id to 'BACKUP_XXXXX_00003_013014015902';
executing command: SET COMMAND ID


RMAN> backup device type disk tag 'BACKUP_XXXXX_00003_013014015902' archivelog all not backed up delete all input;
Starting backup at 30-JAN-14
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=204 instance=xxxxx2 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=2 sequence=423 RECID=759 STAMP=836918007
input archived log thread=1 sequence=337 RECID=761 STAMP=836918325
input archived log thread=2 sequence=424 RECID=760 STAMP=836918279

....

channel ORA_DISK_1: starting piece 1 at 30-JAN-14
channel ORA_DISK_1: finished piece 1 at 30-JAN-14
piece handle=+RECO_EUX/ccrpp/backupset/2014_01_30/annnf0_backup_ccrpp_00003_0130140159_0.3464.838216767 tag=BACKUP_XXXXX_00003_013014015902 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:05:36
channel ORA_DISK_1: deleting archived log(s)
archived log file name=+RECO_EUX/xxxxx/archivelog/2014_01_15/thread_2_seq_423.3689.836917961 RECID=759 STAMP=836918007
archived log file name=+RECO_EUX/xxxxx/archivelog/2014_01_15/thread_1_seq_337.3691.836918287 RECID=761 STAMP=836918325
archived log file name=+RECO_EUX/xxxxx/archivelog/2014_01_15/thread_2_seq_424.3690.836918231 RECID=760 STAMP=836918279

....

channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=373 RECID=815 STAMP=837035068
input archived log thread=2 sequence=443 RECID=817 STAMP=837037285
input archived log thread=1 sequence=374 RECID=816 STAMP=837036182

...

channel ORA_DISK_1: starting piece 1 at 30-JAN-14
channel ORA_DISK_1: finished piece 1 at 30-JAN-14
piece handle=+RECO_EUX/xxxxx/backupset/2014_01_30/annnf0_backup_ccrpp_00003_0130140159_0.3787.838217109 tag=BACKUP_XXXXX_00003_013014015902 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:05:55
channel ORA_DISK_1: deleting archived log(s)
archived log file name=+RECO_EUX/xxxxx/archivelog/2014_01_16/thread_1_seq_373.3789.837034999 RECID=815 STAMP=837035068
archived log file name=+RECO_EUX/xxxxx/archivelog/2014_01_16/thread_2_seq_443.3795.837037281 RECID=817 STAMP=837037285

....

Finished backup at 30-JAN-14

Starting Control File and SPFILE Autobackup at 30-JAN-14
piece handle=+RECO_EUX/xxxxx/autobackup/2014_01_30/s_838218137.3459.838218143 comment=NONE
Finished Control File and SPFILE Autobackup at 30-JAN-14
ORA-00245: control file backup operation failed

RMAN-08132: WARNING: cannot update recovery area reclaimable file list


RMAN> exit;

Recovery Manager complete.

To crosscheck expire backup
CROSSCHECK BACKUPSET;
CROSSCHECK COPY;

Delete all expired backup
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT EXPIRED COPY;

Daily backup using Oracle suggested backup to FRA
Daily Script:
run {
allocate channel oem_disk_backup device type disk;
recover copy of database with tag 'ORA$OEM_LEVEL_0';
backup incremental level 1 cumulative  copies=1 for recover of copy with tag 'ORA$OEM_LEVEL_0' database;
}

Add one more command to delete expired archivelogs:

RMAN> DELETE noprompt ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-3'; 

-----
Check archive mode
archive log list
select sum(bytes)/1024/1024/1024 from v$datafile;

check mount point free
df –h

connect to RMAN
rman targget /

cut and paste the script
another mont point /tmp/ovmdata
remeber to clean archivelog regular
show parameter db_name
show parameter archive log dest
RMAN> sql ‘alter system switch logfile’   (for non-RAC)

CONFIGURE CONTROLFILE AUTOBACK ON;
CONFIGGURE CONTROL
ASMCMD
myinstance1
rman target / log=/var/log/myRMANlog.txt
srvctl stop databse –d myinstance –o immediate
mount –t nfs IP:/data1 /tmp/ovmdata

Clear archivelog AFTER BACKUP
DELETE NOPROMPT ARCHIVE LOG ALL;
BACKUP ARCHIVELOG ALL DELETE ALL INPUT;
Restore
su – oracle
ps –ef | grep smon
. oraenv
ORACLE SID = myinstance2

rman target /
(connected to target database (not started)
startup nomount
restore spfile form ‘/tmp/ovmdata/dbm/test/c-xxxxxxxx-xxxx-xxxxx’;

mount -t nfs fileserver02:/Exadata_NFS /mnt/Exadata_NFS/

perform the log switch command below in both instances (e.g. myinstance1, myinstance2)

[oracle@xdb01 tmp]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Mon Mar 18 12:50:39 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> alter system switch logfile;

System altered.

SQL>



CONFIGURE CONTROLFILE AUTOBACKUP ON;
#CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/tmp/ovmdata/dbm/test/%F' ;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/mnt/Exadata_NFS/myinstance1_CTRL_FILE_%F' ;
Run
{
allocate channel d1 type disk ;
backup database format '/mnt/Exadata_NFS/myinstance1_db_%u' ;
sql 'alter system archive log current';
backup archivelog from time "sysdate -1 " format '/mnt/Exadata_NFS/myinstance1_archive_%u' ;
backup current controlfile format '/mnt/Exadata_NFS/myinstance1_control_%u';
delete noprompt archivelog all;
release channel d1 ;
}

mount -t nfs 192.168.1.2:/data1 /tmp/ovmdata

delete noprompt archivelog all;

BACKUP ARCHIVELOG ALL DELETE ALL INPUT;

Restore

RMAN> set dbid 4022273593

executing command: SET DBID

RMAN> run {

set controlfile autobackup format

for device type disk to '/tmp/ovmdata/dbm/test/%F';

restore controlfile from autobackup;

}

RMAN>run {

     set newname for datafile 1 to '+DATA';

     set newname for datafile 2 to '+DATA';

     set newname for datafile 3 to '+DATA';

     set newname for datafile 4 to '+DATA';

     set newname for datafile 5 to '+DATA';

     set newname for datafile 6 to '+DATA';

     set newname for datafile 7 to '+DATA';

     set newname for datafile 8 to '+DATA';

     restore database;

     switch datafile all;

     set newname for tempfile 1 to '+DATA';

     switch tempfile all;

     recover database;

     sql 'alter database open resetlogs';

}