Oracle 10g Release1 have given an excellent feature which can serve as an replacement for all the jobs that we schedule with the help of cron or anacron.
We can schedule a job and run a shell script inside oracle.
The advantage is that we can access non oracle program like any OS executable file through some shell script and call them as and when required if database running is Oracle.
The Steps are easy and can be done in the following way:
1. Create a Shell Script.
vi /tmp/test_schedular.sh and type the following to test
#!/usr/bin/ksh
echo "Oracle scheduler running shell script succeeded `date`">> /tmp/testscheduler.log
2. Give access to that script to oracle user or make oracle user in the same group as the script executable owner .
3. If security is not a factory then change the script permisson to chmod 755
chmod 755 /tmp/test_schedular.sh
4. Create a job inside oracle.
This can be done by login in sqlplus with user sys or any other user having “CREATE JOB” privilege
5. Create the following procedure.This procedure assumes that schedule is already created.
BEGIN
DBMS_SCHEDULER.CREATE_JOB (job_name => 'Test_Schedular',
job_type => 'EXECUTABLE',
job_action => '/tmp/test_schedular.sh',
start_date => sysdate,
repeat_interval => 'freq=hourly; minute=0', /* Will excute after every one hour */
enabled => TRUE,
comments => 'Testing shell script from Oracle');
END;
/
6. Now we may check in the /tmp/testscheduler.log if the schedular is working fine or not.
7. If not required we may drop the job using the following procedure
BEGIN
DBMS_SCHEDULER.drop_job (job_name => 'Test_Schedular');
END
/
The Use of Dbms_Schedular can vary from sending email to some predefined location,collection of stats and many more.
How to take a cold backup using RMAN:-
C:\oracle>set NLS_LANG=English.UTF8
C:\oracle>rman target / nocatalog
connected to target database: DBCAMIN (DBID=898998132, not open)
using target database control file instead of recovery catalog
#RMAN> shutdown immediate;
database dismounted
Oracle instance shut down
RMAN> startup mount;
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 313860096 bytes
Fixed Size 1332892 bytes
Variable Size 159385956 bytes
Database Buffers 146800640 bytes
Redo Buffers 6340608 bytes
RMAN> show all;
RMAN configuration parameters for database with db_unique_name DBCAMIN are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BZIP2'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\ORACLE\PRODUCT\11.1.0\DB_1\DATABASE\SNCFDBCAMIN.ORA'; # default
#
RMAN> backup full database;
Starting backup at 26-FEB-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=55 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=C:\ORACLE\ORADATA\DBCAMIN\SYSTEM01.DBF
input datafile file number=00003 name=C:\ORACLE\ORADATA\DBCAMIN\UNDOTBS01.DBF
input datafile file number=00002 name=C:\ORACLE\ORADATA\DBCAMIN\SYSAUX01.DBF
input datafile file number=00004 name=C:\ORACLE\ORADATA\DBCAMIN\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 26-FEB-08
channel ORA_DISK_1: finished piece 1 at 26-FEB-08
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\DBCAMIN\BACKUPSET\2008_02_26\O1_MF_NNNDF_TAG20080226T104828_3W6VZKT1_.BKP tag=TAG20080226T104828 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 26-FEB-08
channel ORA_DISK_1: finished piece 1 at 26-FEB-08
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\DBCAMIN\BACKUPSET\2008_02_26\O1_MF_NCSNF_TAG20080226T104828_3W6W0H9X_.BKP tag=TAG20080226T104828 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 26-FEB-08
#
RMAN> backup current controlfile;
Starting backup at 26-FEB-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 26-FEB-08
channel ORA_DISK_1: finished piece 1 at 26-FEB-08
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\DBCAMIN\BACKUPSET\2008_02_26\O1_MF_NCNNF_TAG20080226T104920_3W6W17FC_.BKP tag=TAG20080226T104920 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 26-FEB-08
#
RMAN> sql 'alter database backup controlfile to trace';
sql statement: alter database backup controlfile to trace
#
RMAN> sql 'create pfile from spfile';
sql statement: create pfile from spfile
#
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 180.15M DISK 00:00:22 26-FEB-08
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20080226T104828
Piece Name: C:\ORACLE\FLASH_RECOVERY_AREA\DBCAMIN\BACKUPSET\2008_02_26\O1_MF_NNNDF_TAG20080226T104828_3W6VZKT1_.BKP
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 211645 26-FEB-08 C:\ORACLE\ORADATA\DBCAMIN\SYSTEM01.DBF
2 Full 211645 26-FEB-08 C:\ORACLE\ORADATA\DBCAMIN\SYSAUX01.DBF
3 Full 211645 26-FEB-08 C:\ORACLE\ORADATA\DBCAMIN\UNDOTBS01.DBF
4 Full 211645 26-FEB-08 C:\ORACLE\ORADATA\DBCAMIN\USERS01.DBF
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 9.36M DISK 00:00:05 26-FEB-08
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20080226T104828
Piece Name: C:\ORACLE\FLASH_RECOVERY_AREA\DBCAMIN\BACKUPSET\2008_02_26\O1_MF_NCSNF_TAG20080226T104828_3W6W0H9X_.BKP
SPFILE Included: Modification time: 26-FEB-08
SPFILE db_unique_name: DBCAMIN
Control File Included: Ckp SCN: 211645 Ckp time: 26-FEB-08
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 9.33M DISK 00:00:07 26-FEB-08
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20080226T104920
Piece Name: C:\ORACLE\FLASH_RECOVERY_AREA\DBCAMIN\BACKUPSET\2008_02_26\O1_MF_NCNNF_TAG20080226T104920_3W6W17FC_.BKP
Control File Included: Ckp SCN: 211645 Ckp time: 26-FEB-08
#
RMAN> sql 'alter database open';
sql statement: alter database open
#
RMAN> backup database plus archivelog delete all input;
RMAN> quit
Recovery Manager complete.
How to restore and recover from RMAN once backup is completed.
After copying the pfile from the old server to the new machine where recovery need to be done.
If there is no change in the location of datafiles we want to keep the DB file location as usual.
Keep the same dir structure as the old one for datafile and ctl,adump,bdump etc.
Use the following steps
[oracle@puatCPI400db800 glCPI400uat]$ cd $ORACLE_HOME/dbs
[oracle@puatCPI400db800 dbs]$ ls -l
total 4813136
-rw------- 1 oracle oinstall 1283949056 Apr 17 07:27 03r388e8_1_1
-rw------- 1 oracle oinstall 3623133184 Apr 17 07:26 04r388fc_1_1
-rw------- 1 oracle oinstall 21528576 Apr 17 07:22 c-3727215433-20160417-00
-rw-rw---- 1 oracle oinstall 1544 Apr 17 20:40 hc_glCPI400uat.dat
-rw-r--r-- 1 oracle oinstall 1852 Apr 17 00:54 initglCPI400uat.ora
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r----- 1 oracle oinstall 24 Apr 17 01:34 lkGLCPI400UAT
-rw-r----- 1 oracle oinstall 1536 Apr 16 08:37 orapwdglCPI400uat
-rw-r----- 1 oracle oinstall 5632 Apr 17 08:50 spfileglCPI400uat.ora
[oracle@puatCPI400db800 dbs]$ rm -rf spfileglCPI400uat.ora
[oracle@puatCPI400db800 dbs]$ cd $ORACLE_HOME/bin
[oracle@puatCPI400db800 bin]$ pwd
Connect to rman
RMAN> connect target /
connected to target database (not started)
RMAN> startup nomount
Oracle instance started
Total System Global Area 5444943872 bytes
Fixed Size 2238016 bytes
Variable Size 1476397504 bytes
Database Buffers 3959422976 bytes
Redo Buffers 6885376 bytes
RMAN> restore controlfile from ''/u01/app/oracle/product/10.2.0.3/dbs/c-3727215433-20160417-00';
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01006: error signaled during parse
RMAN-02001: unrecognized punctuation symbol "/"
RMAN> restore controlfile from '/u01/app/oracle/product/10.2.0.3/dbs/c-3727215433-20160417-00';
Starting restore at 17-APR-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=4524 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/oradata/glCPI400uat/control01.ctl
output file name=/u01/oradata/glCPI400uat/control03.ctl
output file name=/u02/oradata/glCPI400uat/control02.ctl
Finished restore at 17-APR-08
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> exit
Recovery Manager complete.
Connect to DB as sysdba.
SQL> select group#, first_change#, status, archived from v$log;
GROUP# FIRST_CHANGE# STATUS ARC
---------- ------------- ---------------- ---
1 134176401 INACTIVE YES
2 134176573 ACTIVE YES
3 134176675 CURRENT NO
8 134176125 INACTIVE YES
5 134153854 INACTIVE YES
6 134163473 INACTIVE YES
7 134163882 INACTIVE YES
4 134123242 INACTIVE YES
8 rows selected.
Connect to RMAN
RMAN> connect target /
connected to target database: GLCPI400UAT (DBID=3727215433, not open)
RMAN> CROSSCHECK backup;
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=4524 device type=DISK
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/product/10.2.0.3/dbs/03r388e8_1_1 RECID=3 STAMP=909386184
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/product/10.2.0.3/dbs/04r388fc_1_1 RECID=4 STAMP=909386220
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/product/10.2.0.3/dbs/05r388m4_1_1 RECID=5 STAMP=909386436
Crosschecked 3 objects
RMAN> CROSSCHECK copy;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=4524 device type=DISK
specification does not match any datafile copy in the repository
specification does not match any control file copy in the repository
validation failed for archived log
archived log file name=/a01/oradata/glCPI400uat/arch/glCPI400uat_754325961_1_1698.log RECID=1697 STAMP=909219643
validation failed for archived log
archived log file name=/a01/oradata/glCPI400uat/arch/glCPI400uat_754325961_1_1699.log RECID=1698 STAMP=909313269
validation failed for archived log
archived log file name=/a01/oradata/glCPI400uat/arch/glCPI400uat_754325961_1_1700.log RECID=1699 STAMP=909358776
validation failed for archived log
archived log file name=/a01/oradata/glCPI400uat/arch/glCPI400uat_754325961_1_1701.log RECID=1700 STAMP=909372464
validation failed for archived log
archived log file name=/a01/oradata/glCPI400uat/arch/glCPI400uat_754325961_1_1702.log RECID=1701 STAMP=909373575
validation failed for archived log
archived log file name=/a01/oradata/glCPI400uat/arch/glCPI400uat_754325961_1_1703.log RECID=1702 STAMP=909385576
validation failed for archived log
archived log file name=/a01/oradata/glCPI400uat/arch/glCPI400uat_754325961_1_1704.log RECID=1703 STAMP=909386084
validation failed for archived log
archived log file name=/a01/oradata/glCPI400uat/arch/glCPI400uat_754325961_1_1705.log RECID=1704 STAMP=909386184
validation failed for archived log
archived log file name=/a01/oradata/glCPI400uat/arch/glCPI400uat_754325961_1_1706.log RECID=1705 STAMP=909386436
Crosschecked 9 objects
RMAN> CROSSCHECK backup of database;
using channel ORA_DISK_1
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/product/10.2.0.3/dbs/04r388fc_1_1 RECID=4 STAMP=909386220
Crosschecked 1 objects
RMAN> CROSSCHECK backup of controlfile;
using channel ORA_DISK_1
specification does not match any backup in the repository
RMAN> CROSSCHECK archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=4524 device type=DISK
validation failed for archived log
archived log file name=/a01/oradata/glCPI400uat/arch/glCPI400uat_754325961_1_1698.log RECID=1697 STAMP=909219643
validation failed for archived log
archived log file name=/a01/oradata/glCPI400uat/arch/glCPI400uat_754325961_1_1699.log RECID=1698 STAMP=909313269
validation failed for archived log
archived log file name=/a01/oradata/glCPI400uat/arch/glCPI400uat_754325961_1_1700.log RECID=1699 STAMP=909358776
validation failed for archived log
archived log file name=/a01/oradata/glCPI400uat/arch/glCPI400uat_754325961_1_1701.log RECID=1700 STAMP=909372464
validation failed for archived log
archived log file name=/a01/oradata/glCPI400uat/arch/glCPI400uat_754325961_1_1702.log RECID=1701 STAMP=909373575
validation failed for archived log
archived log file name=/a01/oradata/glCPI400uat/arch/glCPI400uat_754325961_1_1703.log RECID=1702 STAMP=909385576
validation failed for archived log
archived log file name=/a01/oradata/glCPI400uat/arch/glCPI400uat_754325961_1_1704.log RECID=1703 STAMP=909386084
validation failed for archived log
archived log file name=/a01/oradata/glCPI400uat/arch/glCPI400uat_754325961_1_1705.log RECID=1704 STAMP=909386184
validation failed for archived log
archived log file name=/a01/oradata/glCPI400uat/arch/glCPI400uat_754325961_1_1706.log RECID=1705 STAMP=909386436
Crosschecked 9 objects
RMAN> exit
Recovery Manager complete.
[oracle@puatCPI400db800 bin]$ cd /a01/oradata/glCPI400uat/arch/
[oracle@puatCPI400db800 arch]$ ls
[oracle@puatCPI400db800 arch]$ cd $ORACLE_HOME/bin
Connect rman
connected to target database: GLCPI400UAT (DBID=3727215433, not open)
RMAN> CATALOG backuppiece '/u01/app/oracle/product/10.2.0.3/dbs/04r388fc_1_1';
using target database control file instead of recovery catalog
cataloged backup piece
backup piece handle=/u01/app/oracle/product/10.2.0.3/dbs/04r388fc_1_1 RECID=6 STAMP=909435283
RMAN> CATALOG backuppiece '/u01/app/oracle/product/10.2.0.3/dbs/03r388e8_1_1';
cataloged backup piece
backup piece handle=/u01/app/oracle/product/10.2.0.3/dbs/03r388e8_1_1 RECID=7 STAMP=909435342
RMAN> delete expired backup;
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=4524 device type=DISK
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
5 5 1 1 EXPIRED DISK /u01/app/oracle/product/10.2.0.3/dbs/05r388m4_1_1
Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/u01/app/oracle/product/10.2.0.3/dbs/05r388m4_1_1 RECID=5 STAMP=909386436
Deleted 1 EXPIRED objects
RMAN> run {
restore database;
recover database until scn 134176675;
}2> 3> 4>
Starting restore at 17-APR-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/oradata/glCPI400uat/system_01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/oradata/glCPI400uat/sysaux_01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/oradata/glCPI400uat/undotbs_01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/oradata/glCPI400uat/users_01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/oradata/glCPI400uat/CPIORAdata01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/oradata/glCPI400uat/CPIORAdata02.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/oradata/glCPI400uat/CPIORAdata03.dbf
channel ORA_DISK_1: restoring datafile 00008 to /u01/oradata/glCPI400uat/CPIORAindexes01.dbf
channel ORA_DISK_1: restoring datafile 00009 to /u01/oradata/glCPI400uat/CPIORAindexes02.dbf
channel ORA_DISK_1: restoring datafile 00010 to /u01/oradata/glCPI400uat/CPIORAindexes03.dbf
channel ORA_DISK_1: restoring datafile 00011 to /u01/oradata/glCPI400uat/CPIORAblobdata01.dbf
channel ORA_DISK_1: restoring datafile 00012 to /u01/oradata/glCPI400uat/CPIORAblobindexes01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/10.2.0.3/dbs/04r388fc_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/product/10.2.0.3/dbs/04r388fc_1_1 tag=TAG20160417T071659
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:04:25
starting media recovery
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/17/2008 21:03:07
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 1706 and starting SCN of 134176573 found to restore
RMAN> alter database open resetlogs;
database opened
RMAN> exit
We can schedule a job and run a shell script inside oracle.
The advantage is that we can access non oracle program like any OS executable file through some shell script and call them as and when required if database running is Oracle.
The Steps are easy and can be done in the following way:
1. Create a Shell Script.
vi /tmp/test_schedular.sh and type the following to test
#!/usr/bin/ksh
echo "Oracle scheduler running shell script succeeded `date`">> /tmp/testscheduler.log
2. Give access to that script to oracle user or make oracle user in the same group as the script executable owner .
3. If security is not a factory then change the script permisson to chmod 755
chmod 755 /tmp/test_schedular.sh
4. Create a job inside oracle.
This can be done by login in sqlplus with user sys or any other user having “CREATE JOB” privilege
5. Create the following procedure.This procedure assumes that schedule is already created.
BEGIN
DBMS_SCHEDULER.CREATE_JOB (job_name => 'Test_Schedular',
job_type => 'EXECUTABLE',
job_action => '/tmp/test_schedular.sh',
start_date => sysdate,
repeat_interval => 'freq=hourly; minute=0', /* Will excute after every one hour */
enabled => TRUE,
comments => 'Testing shell script from Oracle');
END;
/
6. Now we may check in the /tmp/testscheduler.log if the schedular is working fine or not.
7. If not required we may drop the job using the following procedure
BEGIN
DBMS_SCHEDULER.drop_job (job_name => 'Test_Schedular');
END
/
The Use of Dbms_Schedular can vary from sending email to some predefined location,collection of stats and many more.
How to take a cold backup using RMAN:-
C:\oracle>set NLS_LANG=English.UTF8
C:\oracle>rman target / nocatalog
connected to target database: DBCAMIN (DBID=898998132, not open)
using target database control file instead of recovery catalog
#RMAN> shutdown immediate;
database dismounted
Oracle instance shut down
RMAN> startup mount;
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 313860096 bytes
Fixed Size 1332892 bytes
Variable Size 159385956 bytes
Database Buffers 146800640 bytes
Redo Buffers 6340608 bytes
RMAN> show all;
RMAN configuration parameters for database with db_unique_name DBCAMIN are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BZIP2'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\ORACLE\PRODUCT\11.1.0\DB_1\DATABASE\SNCFDBCAMIN.ORA'; # default
#
RMAN> backup full database;
Starting backup at 26-FEB-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=55 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=C:\ORACLE\ORADATA\DBCAMIN\SYSTEM01.DBF
input datafile file number=00003 name=C:\ORACLE\ORADATA\DBCAMIN\UNDOTBS01.DBF
input datafile file number=00002 name=C:\ORACLE\ORADATA\DBCAMIN\SYSAUX01.DBF
input datafile file number=00004 name=C:\ORACLE\ORADATA\DBCAMIN\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 26-FEB-08
channel ORA_DISK_1: finished piece 1 at 26-FEB-08
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\DBCAMIN\BACKUPSET\2008_02_26\O1_MF_NNNDF_TAG20080226T104828_3W6VZKT1_.BKP tag=TAG20080226T104828 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 26-FEB-08
channel ORA_DISK_1: finished piece 1 at 26-FEB-08
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\DBCAMIN\BACKUPSET\2008_02_26\O1_MF_NCSNF_TAG20080226T104828_3W6W0H9X_.BKP tag=TAG20080226T104828 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 26-FEB-08
#
RMAN> backup current controlfile;
Starting backup at 26-FEB-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 26-FEB-08
channel ORA_DISK_1: finished piece 1 at 26-FEB-08
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\DBCAMIN\BACKUPSET\2008_02_26\O1_MF_NCNNF_TAG20080226T104920_3W6W17FC_.BKP tag=TAG20080226T104920 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 26-FEB-08
#
RMAN> sql 'alter database backup controlfile to trace';
sql statement: alter database backup controlfile to trace
#
RMAN> sql 'create pfile from spfile';
sql statement: create pfile from spfile
#
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 180.15M DISK 00:00:22 26-FEB-08
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20080226T104828
Piece Name: C:\ORACLE\FLASH_RECOVERY_AREA\DBCAMIN\BACKUPSET\2008_02_26\O1_MF_NNNDF_TAG20080226T104828_3W6VZKT1_.BKP
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 211645 26-FEB-08 C:\ORACLE\ORADATA\DBCAMIN\SYSTEM01.DBF
2 Full 211645 26-FEB-08 C:\ORACLE\ORADATA\DBCAMIN\SYSAUX01.DBF
3 Full 211645 26-FEB-08 C:\ORACLE\ORADATA\DBCAMIN\UNDOTBS01.DBF
4 Full 211645 26-FEB-08 C:\ORACLE\ORADATA\DBCAMIN\USERS01.DBF
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 9.36M DISK 00:00:05 26-FEB-08
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20080226T104828
Piece Name: C:\ORACLE\FLASH_RECOVERY_AREA\DBCAMIN\BACKUPSET\2008_02_26\O1_MF_NCSNF_TAG20080226T104828_3W6W0H9X_.BKP
SPFILE Included: Modification time: 26-FEB-08
SPFILE db_unique_name: DBCAMIN
Control File Included: Ckp SCN: 211645 Ckp time: 26-FEB-08
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 9.33M DISK 00:00:07 26-FEB-08
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20080226T104920
Piece Name: C:\ORACLE\FLASH_RECOVERY_AREA\DBCAMIN\BACKUPSET\2008_02_26\O1_MF_NCNNF_TAG20080226T104920_3W6W17FC_.BKP
Control File Included: Ckp SCN: 211645 Ckp time: 26-FEB-08
#
RMAN> sql 'alter database open';
sql statement: alter database open
#
RMAN> backup database plus archivelog delete all input;
RMAN> quit
Recovery Manager complete.
How to restore and recover from RMAN once backup is completed.
After copying the pfile from the old server to the new machine where recovery need to be done.
If there is no change in the location of datafiles we want to keep the DB file location as usual.
Keep the same dir structure as the old one for datafile and ctl,adump,bdump etc.
Use the following steps
[oracle@puatCPI400db800 glCPI400uat]$ cd $ORACLE_HOME/dbs
[oracle@puatCPI400db800 dbs]$ ls -l
total 4813136
-rw------- 1 oracle oinstall 1283949056 Apr 17 07:27 03r388e8_1_1
-rw------- 1 oracle oinstall 3623133184 Apr 17 07:26 04r388fc_1_1
-rw------- 1 oracle oinstall 21528576 Apr 17 07:22 c-3727215433-20160417-00
-rw-rw---- 1 oracle oinstall 1544 Apr 17 20:40 hc_glCPI400uat.dat
-rw-r--r-- 1 oracle oinstall 1852 Apr 17 00:54 initglCPI400uat.ora
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r----- 1 oracle oinstall 24 Apr 17 01:34 lkGLCPI400UAT
-rw-r----- 1 oracle oinstall 1536 Apr 16 08:37 orapwdglCPI400uat
-rw-r----- 1 oracle oinstall 5632 Apr 17 08:50 spfileglCPI400uat.ora
[oracle@puatCPI400db800 dbs]$ rm -rf spfileglCPI400uat.ora
[oracle@puatCPI400db800 dbs]$ cd $ORACLE_HOME/bin
[oracle@puatCPI400db800 bin]$ pwd
Connect to rman
RMAN> connect target /
connected to target database (not started)
RMAN> startup nomount
Oracle instance started
Total System Global Area 5444943872 bytes
Fixed Size 2238016 bytes
Variable Size 1476397504 bytes
Database Buffers 3959422976 bytes
Redo Buffers 6885376 bytes
RMAN> restore controlfile from ''/u01/app/oracle/product/10.2.0.3/dbs/c-3727215433-20160417-00';
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01006: error signaled during parse
RMAN-02001: unrecognized punctuation symbol "/"
RMAN> restore controlfile from '/u01/app/oracle/product/10.2.0.3/dbs/c-3727215433-20160417-00';
Starting restore at 17-APR-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=4524 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/oradata/glCPI400uat/control01.ctl
output file name=/u01/oradata/glCPI400uat/control03.ctl
output file name=/u02/oradata/glCPI400uat/control02.ctl
Finished restore at 17-APR-08
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> exit
Recovery Manager complete.
Connect to DB as sysdba.
SQL> select group#, first_change#, status, archived from v$log;
GROUP# FIRST_CHANGE# STATUS ARC
---------- ------------- ---------------- ---
1 134176401 INACTIVE YES
2 134176573 ACTIVE YES
3 134176675 CURRENT NO
8 134176125 INACTIVE YES
5 134153854 INACTIVE YES
6 134163473 INACTIVE YES
7 134163882 INACTIVE YES
4 134123242 INACTIVE YES
8 rows selected.
Connect to RMAN
RMAN> connect target /
connected to target database: GLCPI400UAT (DBID=3727215433, not open)
RMAN> CROSSCHECK backup;
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=4524 device type=DISK
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/product/10.2.0.3/dbs/03r388e8_1_1 RECID=3 STAMP=909386184
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/product/10.2.0.3/dbs/04r388fc_1_1 RECID=4 STAMP=909386220
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/product/10.2.0.3/dbs/05r388m4_1_1 RECID=5 STAMP=909386436
Crosschecked 3 objects
RMAN> CROSSCHECK copy;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=4524 device type=DISK
specification does not match any datafile copy in the repository
specification does not match any control file copy in the repository
validation failed for archived log
archived log file name=/a01/oradata/glCPI400uat/arch/glCPI400uat_754325961_1_1698.log RECID=1697 STAMP=909219643
validation failed for archived log
archived log file name=/a01/oradata/glCPI400uat/arch/glCPI400uat_754325961_1_1699.log RECID=1698 STAMP=909313269
validation failed for archived log
archived log file name=/a01/oradata/glCPI400uat/arch/glCPI400uat_754325961_1_1700.log RECID=1699 STAMP=909358776
validation failed for archived log
archived log file name=/a01/oradata/glCPI400uat/arch/glCPI400uat_754325961_1_1701.log RECID=1700 STAMP=909372464
validation failed for archived log
archived log file name=/a01/oradata/glCPI400uat/arch/glCPI400uat_754325961_1_1702.log RECID=1701 STAMP=909373575
validation failed for archived log
archived log file name=/a01/oradata/glCPI400uat/arch/glCPI400uat_754325961_1_1703.log RECID=1702 STAMP=909385576
validation failed for archived log
archived log file name=/a01/oradata/glCPI400uat/arch/glCPI400uat_754325961_1_1704.log RECID=1703 STAMP=909386084
validation failed for archived log
archived log file name=/a01/oradata/glCPI400uat/arch/glCPI400uat_754325961_1_1705.log RECID=1704 STAMP=909386184
validation failed for archived log
archived log file name=/a01/oradata/glCPI400uat/arch/glCPI400uat_754325961_1_1706.log RECID=1705 STAMP=909386436
Crosschecked 9 objects
RMAN> CROSSCHECK backup of database;
using channel ORA_DISK_1
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/product/10.2.0.3/dbs/04r388fc_1_1 RECID=4 STAMP=909386220
Crosschecked 1 objects
RMAN> CROSSCHECK backup of controlfile;
using channel ORA_DISK_1
specification does not match any backup in the repository
RMAN> CROSSCHECK archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=4524 device type=DISK
validation failed for archived log
archived log file name=/a01/oradata/glCPI400uat/arch/glCPI400uat_754325961_1_1698.log RECID=1697 STAMP=909219643
validation failed for archived log
archived log file name=/a01/oradata/glCPI400uat/arch/glCPI400uat_754325961_1_1699.log RECID=1698 STAMP=909313269
validation failed for archived log
archived log file name=/a01/oradata/glCPI400uat/arch/glCPI400uat_754325961_1_1700.log RECID=1699 STAMP=909358776
validation failed for archived log
archived log file name=/a01/oradata/glCPI400uat/arch/glCPI400uat_754325961_1_1701.log RECID=1700 STAMP=909372464
validation failed for archived log
archived log file name=/a01/oradata/glCPI400uat/arch/glCPI400uat_754325961_1_1702.log RECID=1701 STAMP=909373575
validation failed for archived log
archived log file name=/a01/oradata/glCPI400uat/arch/glCPI400uat_754325961_1_1703.log RECID=1702 STAMP=909385576
validation failed for archived log
archived log file name=/a01/oradata/glCPI400uat/arch/glCPI400uat_754325961_1_1704.log RECID=1703 STAMP=909386084
validation failed for archived log
archived log file name=/a01/oradata/glCPI400uat/arch/glCPI400uat_754325961_1_1705.log RECID=1704 STAMP=909386184
validation failed for archived log
archived log file name=/a01/oradata/glCPI400uat/arch/glCPI400uat_754325961_1_1706.log RECID=1705 STAMP=909386436
Crosschecked 9 objects
RMAN> exit
Recovery Manager complete.
[oracle@puatCPI400db800 bin]$ cd /a01/oradata/glCPI400uat/arch/
[oracle@puatCPI400db800 arch]$ ls
[oracle@puatCPI400db800 arch]$ cd $ORACLE_HOME/bin
Connect rman
connected to target database: GLCPI400UAT (DBID=3727215433, not open)
RMAN> CATALOG backuppiece '/u01/app/oracle/product/10.2.0.3/dbs/04r388fc_1_1';
using target database control file instead of recovery catalog
cataloged backup piece
backup piece handle=/u01/app/oracle/product/10.2.0.3/dbs/04r388fc_1_1 RECID=6 STAMP=909435283
RMAN> CATALOG backuppiece '/u01/app/oracle/product/10.2.0.3/dbs/03r388e8_1_1';
cataloged backup piece
backup piece handle=/u01/app/oracle/product/10.2.0.3/dbs/03r388e8_1_1 RECID=7 STAMP=909435342
RMAN> delete expired backup;
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=4524 device type=DISK
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
5 5 1 1 EXPIRED DISK /u01/app/oracle/product/10.2.0.3/dbs/05r388m4_1_1
Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/u01/app/oracle/product/10.2.0.3/dbs/05r388m4_1_1 RECID=5 STAMP=909386436
Deleted 1 EXPIRED objects
RMAN> run {
restore database;
recover database until scn 134176675;
}2> 3> 4>
Starting restore at 17-APR-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/oradata/glCPI400uat/system_01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/oradata/glCPI400uat/sysaux_01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/oradata/glCPI400uat/undotbs_01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/oradata/glCPI400uat/users_01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/oradata/glCPI400uat/CPIORAdata01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/oradata/glCPI400uat/CPIORAdata02.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/oradata/glCPI400uat/CPIORAdata03.dbf
channel ORA_DISK_1: restoring datafile 00008 to /u01/oradata/glCPI400uat/CPIORAindexes01.dbf
channel ORA_DISK_1: restoring datafile 00009 to /u01/oradata/glCPI400uat/CPIORAindexes02.dbf
channel ORA_DISK_1: restoring datafile 00010 to /u01/oradata/glCPI400uat/CPIORAindexes03.dbf
channel ORA_DISK_1: restoring datafile 00011 to /u01/oradata/glCPI400uat/CPIORAblobdata01.dbf
channel ORA_DISK_1: restoring datafile 00012 to /u01/oradata/glCPI400uat/CPIORAblobindexes01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/10.2.0.3/dbs/04r388fc_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/product/10.2.0.3/dbs/04r388fc_1_1 tag=TAG20160417T071659
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:04:25
starting media recovery
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/17/2008 21:03:07
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 1706 and starting SCN of 134176573 found to restore
RMAN> alter database open resetlogs;
database opened
RMAN> exit
