Hi All,
With each newer version of Oracle from Oracle 8i to Oracle 11g rman features are enchanced making it more and more powerful and thereby simplifying backup and recovery process to large extent.
The new feature of Oracle 11g makes rman more powerful by automatically detecting the failure, advising how to recover from failure and then actually recovering from failure.
For e.g.
Letz say my database consists of following datafiles
-bash-3.00$ cd /oracle/product/oradata/OTM/datafile/
-bash-3.00$ ls -lart
total 2096616
drwxr-x— 5 oracle oinstall 4096 Oct 15 16:30 ..
-rw-r—– 1 oracle oinstall 40902656 Nov 23 11:26 o1_mf_temp_3k6l2q8q_.tmp
-rw-r—– 1 oracle oinstall 5251072 Nov 23 13:11 o1_mf_users_3ndztqcd_.dbf
drwxr-x— 2 oracle oinstall 4096 Nov 23 16:42 .
-rw-r—– 1 oracle oinstall 492838912 Nov 23 16:42 o1_mf_undotbs1_3k6l2hkr_.dbf
-rw-r—– 1 oracle oinstall 104865792 Nov 23 16:43 o1_mf_test_3nffdoxh_.dbf
-rw-r—– 1 oracle oinstall 734011392 Nov 23 16:44 o1_mf_system_3k6l1shq_.dbf
-rw-r—– 1 oracle oinstall 770514944 Nov 23 16:44 o1_mf_sysaux_3k6l2bqf_.dbf
Let us say by mistake someone deletes the o1_mf_test_3nffdoxh_.dbf or the file gets corrupted.
-bash-3.00$ rm -rf o1_mf_test_3nffdoxh_.dbf
Now I detect the failure when I try to perform a transaction accessing that database or when I try to shutdown the database , I get the following error
-bash-3.00$ sqlplus
SQL*Plus: Release 11.1.0.6.0 – Production on Fri Nov 23 16:46:00 2007
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown immediate
ORA-01116: error in opening database file 5
ORA-01110: data file 5: ‘/oracle/product/oradata/OTM/datafile/o1_mf_test_3nffdoxh_.dbf’
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Now we can use rman to detect the failure as shown below
-bash-3.00$ rman target /
Recovery Manager: Release 11.1.0.6.0 – Production on Fri Nov 23 16:46:15 2007
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: OTM (DBID=811910212)
RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
1242 HIGH OPEN 23-NOV-07 One or more non-system datafiles are missing
Now we can ask rman to advise us to repair the failure as shown.
RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
1242 HIGH OPEN 23-NOV-07 One or more non-system datafiles are missing
analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1640 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
1. If file /oracle/product/oradata/OTM/datafile/o1_mf_test_3nffdoxh_.dbf was unintentionally renamed or moved, restore it
Automated Repair Options
========================
Option Repair Description
—— ——————
1 Restore and recover datafile 5
Strategy: The repair includes complete media recovery with no data loss
Repair script: /oracle/product/diag/rdbms/otm/OTM/hm/reco_1550806931.hm
As shown above rman by itself prepares the script which can be used to repair the failure. To view the content of the script use the following commandd
RMAN> repair failure preview;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /oracle/product/diag/rdbms/otm/OTM/hm/reco_1550806931.hm
contents of repair script:
# restore and recover datafile
sql ‘alter database datafile 5 offline’;
restore datafile 5;
recover datafile 5;
sql ‘alter database datafile 5 online’;
Finally to repair the failure using rman we use the following command
RMAN> repair failure;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /oracle/product/diag/rdbms/otm/OTM/hm/reco_1550806931.hm
contents of repair script:
# restore and recover datafile
sql ‘alter database datafile 5 offline’;
restore datafile 5;
recover datafile 5;
sql ‘alter database datafile 5 online’;
Do you really want to execute the above repair (enter YES or NO)? YES
executing repair script
sql statement: alter database datafile 5 offline
Starting restore at 23-NOV-07
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 00005 to /oracle/product/oradata/OTM/datafile/o1_mf_test_3nffdoxh_.dbf
channel ORA_DISK_1: reading from backup piece /oracle/1mj173au_1_1
channel ORA_DISK_1: piece handle=/oracle/1mj173au_1_1 tag=TAG20071116T164612
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 23-NOV-07
Starting recover at 23-NOV-07
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 111 is already on disk as file /oracle/product/flash_recovery_area/OTM/archivelog/2007_11_22/o1_mf_1_111_3n9o59by_.arc
archived log for thread 1 with sequence 112 is already on disk as file /oracle/product/flash_recovery_area/OTM/archivelog/2007_11_22/o1_mf_1_112_3nbdyxbh_.arc
archived log for thread 1 with sequence 113 is already on disk as file /oracle/product/flash_recovery_area/OTM/archivelog/2007_11_22/o1_mf_1_113_3nc2wt57_.arc
archived log for thread 1 with sequence 114 is already on disk as file /oracle/product/flash_recovery_area/OTM/archivelog/2007_11_22/o1_mf_1_114_3nccpz0p_.arc
archived log for thread 1 with sequence 115 is already on disk as file /oracle/product/flash_recovery_area/OTM/archivelog/2007_11_23/o1_mf_1_115_3ncmyrhb_.arc
archived log for thread 1 with sequence 116 is already on disk as file /oracle/product/flash_recovery_area/OTM/archivelog/2007_11_23/o1_mf_1_116_3nd5stp7_.arc
archived log for thread 1 with sequence 117 is already on disk as file /oracle/product/flash_recovery_area/OTM/archivelog/2007_11_23/o1_mf_1_117_3ndxlsp4_.arc
archived log file name=/oracle/product/flash_recovery_area/OTM/archivelog/2007_11_22/o1_mf_1_111_3n9o59by_.arc thread=1 sequence=111
archived log file name=/oracle/product/flash_recovery_area/OTM/archivelog/2007_11_22/o1_mf_1_112_3nbdyxbh_.arc thread=1 sequence=112
archived log file name=/oracle/product/flash_recovery_area/OTM/archivelog/2007_11_22/o1_mf_1_113_3nc2wt57_.arc thread=1 sequence=113
archived log file name=/oracle/product/flash_recovery_area/OTM/archivelog/2007_11_22/o1_mf_1_114_3nccpz0p_.arc thread=1 sequence=114
archived log file name=/oracle/product/flash_recovery_area/OTM/archivelog/2007_11_23/o1_mf_1_115_3ncmyrhb_.arc thread=1 sequence=115
media recovery complete, elapsed time: 00:00:47
Finished recover at 23-NOV-07
sql statement: alter database datafile 5 online
repair failure complete
RMAN> exit
Recovery Manager complete.
20) What is the location of init<SID>.ora file for windows system and for unix system?
The default location of init<SID>.ora file is %ORACLE_HOME%/database folder for Windows while it is $ORACLE_HOME/dbs for UNIX System.
21) What is the use of init<SID>.ora file in Oracle Database?
init<SID>.ora Files contains all the parameters which is used to configure the Oracle Instance.
For e.g
shared_pool_size parameter in init<SID>.ora decides the size of shared pool
large_pool parameter decides the size of large pool.
22) Can the parameter of init file be changed dynamically?
No, the parameters of the init<SID>.ora does not take effect on the fly when the database
is running. To change the parameter we need to modify the parameter in init<SID>.ora
and then stop the database and start the database with the modified pfile.
23) How can we start the database with pfile which is
located in the non default location?
To start the database with pfile located in the non default location
i.e (Not in %oracle_home%/database OR $ORACLE_HOME/ dbs) we can use the following command at SQL prompt
SQL> startup pfile=<location of pfile>
24) Which parameter determines the location control files in oracle database?
CONTROL_FILES parameter decides the location of control files in oracle database.
25) What are Instance specific parameters and how are they defined in init<SID>.ora?
In RAC (Real Application Cluster) which is a Oracle HA(High Availability) Feature we can have multiple oracle instance configured on different nodes which can access the same database located in shared storage area. These instances configured on different nodes can be configured differently by using these instance specific parameters. These instance specific parameters are defined in init<SID>.ora as
Letz say the database name is ORCL
Instance1 SID: ORCL1
Instance 2 SID:ORCL2
initORCL1.ora
ORCL1.undo_tablespace=UNDOTBS1
ORCL2.undo_tablespace=UNDOTBS2
26) What is spfile<SID>.ora?
In Oracle 9i, spfile<SID>.ora was introduced as a replacement for init<SID>.ora in which
some of databaseparameters can be changed dynamically
without shutting down the database.
Spfile<SID>.ora is a binary file and cannot be modified manually
27) How can we create spfile from pfile and vice versa?
In the server set oracle_sid environment variable to the SID name
Log in to the database with sys as sysdba user
and fire the following query at the SQL prompt
SQL> create spfile from pfile;
File created
SQL> create pfile from spfile;
File created
By firing the above command the spfile/pfile is created in the default location i.e %oracle_home%/database in Windows and
$ORACLE_HOME/dbs
28) How can we dynamically change the parameter with spfile<SID>.ora?
To dynamically change the database parameter your database must be started with spfile<SID>.ora
log into the datbase with sys as sysdba
and fire the following query
SQL> alter system set <parameter name>=<value> scope=both;
If the parameter cannot be modified on the fly use the following command
SQL> alter system set <parameter-name>=<value> scope=spfile;
29) When you give startup to start the database and if both spfile and pfile are present in
default location which file is used to start the database?
When the oracle starts the instance it looks the following files in the specified order
1)spfile<SID>.ora
2) spfile.ora
3)init<SID>.ora
4) init.ora
If it finds spfile<SID>.ora it will start the database using that but if it is not
present it will look for spfile.ora file. If that file is also not present it looks for init<SID>.ora
and so on.
30) how can we create pointer to other init/spfile?
We can use
IFILE= ’<location of the pfile>’
SPFILE=’<location of spfile>’
parameters to create pointer to init/spfile which are located at some other location.
