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