Oracle Faq

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.