How to find log sequences used during the recovery

RMAN> restore database preview;

Starting restore at 2009-MAY-22 11:24:50

using channel ORA_DISK_1

List of Backup Sets

===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time     

------- ---- -- ---------- ----------- ------------ --------------------

1       Full    209.16M    DISK        00:01:50     2009-MAY-22 11:21:04

        BP Key: 1   Status: AVAILABLE  Compressed: YES  Tag: TAG20090522T111914

        Piece Name: /u01/app/oracle/flash_recovery_area/ORCL/backupset/2009_05_22/o1_mf_nnndf_TAG20090522T111914_51d6fm3b_.bkp

  List of Datafiles in backup set 1

  File LV Type Ckp SCN    Ckp Time             Name

  ---- -- ---- ---------- -------------------- ----

  1       Full 1621673    2009-MAY-22 11:19:15 /u01/app/oracle/oradata/ORCL/system01.dbf

  2       Full 1621673    2009-MAY-22 11:19:15 /u01/app/oracle/oradata/ORCL/sysaux01.dbf

  3       Full 1621673    2009-MAY-22 11:19:15 /u01/app/oracle/oradata/ORCL/undotbs01.dbf

  4       Full 1621673    2009-MAY-22 11:19:15 /u01/app/oracle/oradata/ORCL/users01.dbf



List of Archived Log Copies for database with db_unique_name ORCL

=====================================================================



Key     Thrd Seq     S Low Time            

------- ---- ------- - --------------------

30      1    97      A 2009-MAY-22 11:11:24

        Name: /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_05_22/o1_mf_1_97_51d6kvcr_.arc

Media recovery start SCN is 1621093

Recovery must be done beyond SCN 1621673 to clear datafile fuzziness

Finished restore at 2009-MAY-22 11:24:51

RMAN> exit





Recovery Manager complete.

How to find log sequences used during the recovery

From above restore preview, we confirm that all redo logs generated from SCN 1621093 and 1621673 will be used.

SQL> select thread#,sequence# from v$archived_log

  2  where --cross begin backup only (sample SEQ# 90)

  3  (first_change#<=1621093 and next_change#>=1621093 and next_change#<=1621673)

  4  or    --within begin backup and end backup (sample SEQ# 91,92)

  5  (first_change#>=1621093 and next_change#<=1621673)

  6  or   --cross end backup only (sample SEQ# 93)

  7  (first_change#>=1621093 and first_change#<=1621673 and next_change#>=1621673)

  8  or   --cross begin backup and end backup (sample SEQ# 150)

  9  (first_change#<=1621093 and next_change#>=1621673)

 10  ;



   THREAD#  SEQUENCE#

---------- ----------

         1         97

Oracle dba Interview Questions

Oracle dba Interview Questions --------------------à>>>>>>>>>

Because you did not perform the restore and recovery when connected to the recovery catalog, the recovery catalog contains no records for any of the restored files or the procedures performed during the test. Likewise, the control file of the trgta database is completely unaffected by the test.

Q-1: One of my control file corrupted and I am unable start database, How can I perform recovery?

If one of your control file is missing or corrupted then we have 2 options to recover it. Check alert.log for exact name and location of corrupted control file. Delete it manually and copy from available rest of control file and rename it and start database. Another option is delete corrupted control file and remove name from parameter file/ spfile. After removing said control file from spfile, start your database.

Q-2: What is incremental checkpoint?

In incremental checkpoint process, CKPT process records lowest Low RBA to the control file to keep advancing the Buffer checkpoint Queue (BCQ) to make easy and fastest Active Checkpoint Queue (ACQ).

Q-3: Does incremental checkpoint recorded in Alert.log? How to disable it?

We can enable disable recording incremental checkpoint in alert.log .We can enable/disable recording using parameter log_checkpoints_to_alert=true/false.

Q-4: I am working as Oracle DBA in 24/7 running large production database. Size of database is around 800 GB. We take hot backup every day night. But one day at around 4:00 PM, by mistake one table is dropped by application user. Table is very useful. How to recover that dropped table?

If your database is running on Oracle 10g version then there is new feature available called Recyclebin. You can recover dropped table from user_recyclebin or dba_recyclebin.

Q-5: In continuation of above scenario, No. Recyclebin doesn’t enable in my database then how to I recover my table in above scenario?

Then you should need to restore backup on your UAT or test database server and enable time based recovery for applying all archives before drop command execution. For an instance, apply archives up to 3:55 PM here.

Q-6: In continuation of above scenario, why I should need to perform recovery in UAT or test database server? Can I restore backup in production database server?

No. It is not recommended because your production database is large database and running 24/7 environment. Restoration and recovery will take downtime. It is better to perform restoration and recovery process on UAT or Test database.

Q-7: What is the meaning of LGWR SYNC and LGWR ASYNC in log archive destination parameter for standby configuration?

When use LGWR with SYNC, it means once network I/O initiated, LGWR has to wait for completion of network I/O before write processing. LGWR with ASYNC, means LGWR doesn’t wait to finish network I/O and continuing write processing.

Q-8: How can I know my require table is available in export dump file or not?

Create indexfile of export dump file using import with indexfile command. A text file will be generating with all table and index object name with number of rows. You can confirm your require table object from this text file.

Q-9: Archive log are being generated around 20 GB in my production large database. But one day almost double archives were generated. What is the reason behind this? How can I check it?

There are lots of reason behind increasing size of archives like If more database changes were performed using batch jobs or any special task like merging 2 database or data etc. You can check it using enabling Log Minor utility.

Q-10: How do you recover from the loss of datafile if the DB is running in ARCHIVELOG mode?

We can perform this recovery in 2 ways. One is open database mode and another is database mount mode. After taking offline lost datafile, we can bring database open and after that restore lost datafile from last backup. After restoration of datafile we can perform datafile level recovery for applying archive logs and make it online. In database mount mode, we can restore datafile from backup and perform datafile recovery using “Recover datafile” command.

Q-11: What is a Complete Recovery?

During this recovery we are applying all database changes using archives log and make it to up to date. Complete recovery can be done full database level or datafile level or tablespace level. Main concept is to apply all data changes using archive logs and bring database up to recent time.

Q-12: Why we should need to open database using RESETLOGS after finishing incomplete recovery?

When we are performing incomplete recovery it means we bring database to past time or rewind period of time using change based, cancel based or time based recovery. These all recovery make database in prior state of database. The forward sequence number already available after performing recovery, due to mismatching of this sequence numbers and prior state of database, it needs to open database with new sequence number of Redo log and archive logs.

Q-13: Why export backup called as logical backup?

Export dump file doesn’t backup or contain any physical structure of database like datafile, control file, redo log file, parameter file, password file. Instead of physical structure, export dump contains logical structure of database like definition of tablespaces, segments, schemas, data of segments etc. Due to these reasons export dump is called as logical backup.

Q-14: Have you faced any recovery scenario? Explain us how you resolved it?

2 months back, a potential table was dropped by an application developer on our large 24/7 running production database. Immediately he informed us. We have standby database for our production database. We checked that archives didn’t apply to standby database up to dropped timing. We stopped archive applying on standby immediately and open database read only. Took export of dropped table and imported in production database. After finishing appropriate checking of table, again we started archive applying in standby database for refreshing.

Q-15: Which command I should need to execute to take backup of database which is running on NOARCHIVELOG mode?

Shutdown immediate. Because without shutdown, we can’t perform online backup of database, which running on NOARCHIVELOG mode. We should need to take cold backup of database.

Voting disk and OCR Management

The recommendations for the addition or removal of a voting disk is shutdown Oracle Cluster ware first on all nodes and then utilize the commands below as root user where path is completely qualified path for the additional voting disk. In the case of new voting disk over the network file system (NFS), create an empty voting disk file location with the correct owner and permissions before execute of commands mentioned below. Three extra raw partitions /dev/raw/raw3/, dev/raw/raw4, /dev/raw/raw5 have been created in order to practice following excercises

• Run the following command to find path of voting disks

[oracle@crs1 ~]$ crsctl query css votedisk

0. 0 /dev/raw/raw2

located 1 votedisk(s).

• Run the following command as the root user to add a voting disk

[root@crs1 oracle]# crsctl stop crs

[root@crs2 oracle]# crsctl stop crs

[root@crs1 oracle]# crsctl add css votedisk /dev/raw/raw3 -force

Now formatting voting disk: /dev/raw/raw3

successful addition of votedisk /dev/raw/raw3.

[root@crs1 oracle]# crsctl start crs

[root@crs2 oracle]# crsctl start crs

[oracle@crs2 oracle]$ crsctl query css votedisk

0. 0 /dev/raw/raw2

1. 0 /dev/raw/raw3

located 2 votedisk(s).

• Run the following command as the root user to remove a voting disk

# crsctl delete css votedisk path

• Backup and Recovery of Voting Disk

Back up the voting

dd if=voting_disk_name of=backup_file_name

[oracle@crs1 ~]$ dd if=/dev/raw/raw2 of=/home/oracle/backup/votdisk1

Recovering Voting Disks

dd if=backup_file_name of=voting_disk_name

• Administering the Oracle Cluster Registry in Oracle Real Application Clusters

OCR file location is in following file

For Sun Solaris /var/opt/oracle/ocr.loc

For Linux /etc/oracle/ocr.loc

If OCR resides on a cluster, file system file or if the OCR is on a network file system, then create the target OCR file before adding OCR.

• Use the OCRCHECK utility to verify the OCR integrity

The things displayed by OCRCHECK utility are – the version of the OCR’s block format; total space used and available; OCRID; and the OCR locations that have been configured. A block-by-block checksum operation for all of the blocks in all the configured OCRs is performed by OCRCHECK. It also returns an individual status for each file as well as a result for the overall OCR integrity check.

[root@crs1 oracle]# ocrcheck

Status of Oracle Cluster Registry is as follows :

Version : 2

Total space (kbytes) : 524184

Used space (kbytes) : 5644

Available space (kbytes) : 518540

ID : 87847809

Device/File Name : /dev/raw/raw1

Device/File integrity check succeeded

Device/File not configured

Cluster registry integrity check succeeded

Device/File not configured means ocrmirror is not configured

OCRCHECK creates a log file in the directory CRS_Home/log/hostname/client. To change amount of logging, edit the file CRS_Home/srvm/admin/ocrlog.ini.

• Run the following command to add an OCR mirror location using either destination_file or disk to designate the target location of the additional OCR

First, make sure you have proper backup of ocr

[root@crs2 oracle]# ocrconfig –showbackup

• Run the following command to add an OCR location using either destination_file or disk to designate the target location of the additional OCR

ocrconfig -replace ocr destination_file or disk

ocrconfig -replace ocrmirror destination_file or disk

First zero out the raw device

If your OCR configuration were altered while a particular node is stopped, you would be required to repair the OCR configuration on that particular node. For instance, there may be need to repair the OCR on a node that was not up at the time of removing, replacing or adding an OCR. For repairing an OCR configuration, run the following command on the node on which the Oracle Clusterware daemon was stopped.

ocrconfig –repair ocrmirror device_name

This operation only changes the OCR configuration on the node from which you run this command. For example, if the OCR mirror device name is /dev/raw1, then use the command syntax ocrconfig -repair ocrmirror /dev/raw1 on this node to repair its OCR configuration.

You cannot perform this operation on a node on which the Oracle Clusterware daemon is running.

• Run the following command on any node in the cluster to remove the OCR

ocrconfig -replace ocr

• Run the following command on any node in the cluster to remove the mirrored OCR

ocrconfig -replace ocrmirror

• The above commands update the OCR configuration on all of the nodes on which Oracle Clusterware is running.
• The ocrconfig Command Options

Option	Purpose
-backuploc	To change an OCR backup file location. For this entry , use a full path that is accessible by all of the nodes.
-downgrade	To downgrade an OCR to an earlier version.
-export	To export the contents of an OCR into a target file.
-help	To display help for the ocrconfig commands.
-import	To import the OCR contents from a previously exported OCR file.
-overwrite	To update an OCR configuration that is recorded on the OCR with the current OCR configuration information that is found on the node from which you are running this command.
-repair	To update an OCR configuration on the node from which you are running this command with the new configuration information specified by this command.
-replace	To add, replace, or remove an OCR location.
-restore	To restore an OCR from an automatically created OCR backup file.
showbackup	To display the location, timestamp, and the originating node name of the backup files that Oracle created in the past 4 hours, 8 hours, 12 hours, and in the last day and week. You do not have to be the root user to execute the -showbackup option.
-upgrade	To upgrade an OCR to a later version.

For example, to export the OCR contents to a binary file, use the ocrconfig command with the following syntax where file_name is the file to which you want to export the OCR contents as follows

ocrconfig -export file_name

• Managing Backups and Recovering the OCR Using OCR Backup Files

This section explains the two methods for the purpose of copying of OCR content and further utilizing it for recovery. The first method uses automatically generated OCR file copies and the second method uses manually created OCR export files.

The Oracle Clusterware automatically creates OCR backups every four hours. At any one time, Oracle always retains the last three backup copies of the OCR. The CRSD process that creates the backups also creates and retains an OCR backup for each full day and at the end of each week.

Restoring the Oracle Cluster Registry on UNIX-Based Systems: use oracheck to check OCR status

Use the following procedure to restore the OCR on UNIX-based systems:

Identify the OCR backups using the ocrconfig -showbackup command. Review the contents of the backup using ocrdump -backupfile file_name where file_name is the name of the backup file.

Stop Oracle Clusterware on all of the nodes in your Oracle RAC database by executing the init.crs stop command on all of the nodes.

Perform the restore by applying an OCR backup file that you identified in Step 1 using the following command where file_name is the name of the OCR that you want to restore. Make sure that the OCR devices that you specify in the OCR configuration exist and that these OCR devices are valid before running this command.

ocrconfig -restore file_name

Restart Oracle Clusterware on all of the nodes in your cluster by restarting each node or by running the init.crs start command.

Run the following command to verify the OCR integrity where the -n all argument retrieves a listing of all of the cluster nodes that are configured as part of your cluster.

cluvfy comp ocr -n all [-verbose]

Use the OCRDUMP utility to write the OCR contents to a file so that you can examine the OCR content.

ocrdump [file_name|-stdout] [-backupfile backup_file_name] [-keyname keyname] [-xml] [-noheader]

OCRDUMP Options and Option Descriptions

Options	Description
file_name	Name of a file to which you want OCRDUMP to write output.
-stdout	The predefined output location that you can redirect with, for example, a filename.
-keyname	The name of an OCR key whose subtree is to be dumped.
-xml	Writes the output in XML format.
-noheader	Does not print the time at which you ran the command and when the OCR configuration occurred.
-backupfile	Option to identify a backup file.
backup_file_name	The name of the backup file the content of which you want to view. You can query the backups using the ocrconfig -showbackup command

• OCR Exports/Imports

ocrconfig -export file_name

ocrconfig -import file_name

Restart Oracle Clusterware on all of the nodes in your cluster by restarting each node.

Run the following Cluster Verification Utility (CVU) command to verify the OCR integrity where the -n all argument retrieves a listing of all of the cluster nodes that are configured as part of your cluster:

cluvfy comp ocr -n all [-verbose]

Managing Data Guard using DGMGRL

• Make dg_broker_start= TRUE on both primary (ocmdb) and secondary (phyocm) databases.

SQL> SHOW PARAMETER dg
SQL> ALTER SYSTEM SET dg_broker_start=TRUE SCOPE=BOTH;

• Configure listener.ora for DGMGRL

These ‘_DGMGRL’ entries in the listener.ora files on each system are required for Broker operations invoked using DGMGRL that require connecting remotely to a database to function normally. Without them, the Broker cannot connect to the target database when it is down, as is the case in a switchover or failover or any other operation that requires a restart of a database.

As non-default port, local listener is used for ocmdb and phyocm. Add SID_DESC for DGMGRL for both Primary and Secondary on non-default port listener SID_LIST as mentioned in BOLD

SID_LIST_LISTENER_OCM =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ocmdb)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SID_NAME = ocmdb)
)
(SID_DESC =
(GLOBAL_DBNAME = ocmdb_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SID_NAME = ocmdb)
)
)

SID_LIST_LISTENER_PHYOCM =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = phyocm)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SID_NAME = phyocm)
)
(SID_DESC =
(GLOBAL_DBNAME = phyocm_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SID_NAME = phyocm)
)
)

• Run “netmgr” and verify that the Oracle Net pre-requisites for using the DGMGRL command line interface have been satisfied.
• Commands to get dgmgrl help

DGMGRL>help edit
DGMGRL>help remove
DGMGRL> help enable
DGMGRL> help start

• DGMGRL Create Configuration

[oracle@ocm ~]$ dgmgrl

DGMGRL> connect sys@ocmdb

• Create configuration with “ocmdb” as the Primary database. Add in the information about the physical standby database

DGMGRL> CREATE CONFIGURATION OCMDB AS PRIMARY DATABASE IS OCMDB
CONNECT IDENTIFIER IS OCMDB;
Configuration “ocmdb” created with primary database “ocmdb”

DGMGRL> ADD DATABASE phyocm AS CONNECT IDENTIFIER IS phyocm
MAINTAINED AS PHYSICAL;
Database “phyocm” added

DGMGRL> enable configuration;
Enabled.

DGMGRL> show configuration;
Configuration
Name: ocmdb
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
ocmdb – Primary database
phyocm – Physical standby database

Current status for “ocmdb”:
SUCCESS

DGMGRL> show database verbose ocmdb ;
Database
Name: ocmdb
Role: PRIMARY
Enabled: YES
Intended State: ONLINE
Instance(s):
ocmdb

Properties:
InitialConnectIdentifier = ‘ocmdb’
LogXptMode = ‘ASYNC’
Dependency = ”
DelayMins = ’0′
Binding = ‘OPTIONAL’
MaxFailure = ’0′
MaxConnections = ’1′
ReopenSecs = ’300′
NetTimeout = ’180′
LogShipping = ‘ON’
PreferredApplyInstance = ”
ApplyInstanceTimeout = ’0′
ApplyParallel = ‘AUTO’
StandbyFileManagement = ‘AUTO’
ArchiveLagTarget = ’0′
LogArchiveMaxProcesses = ’2′
LogArchiveMinSucceedDest = ’1′
DbFileNameConvert = ”
LogFileNameConvert = ‘phyocm, ocmdb’
FastStartFailoverTarget = ”
StatusReport = ‘(monitor)’
InconsistentProperties = ‘(monitor)’
InconsistentLogXptProps = ‘(monitor)’
SendQEntries = ‘(monitor)’
LogXptStatus = ‘(monitor)’
RecvQEntries = ‘(monitor)’
HostName = ‘ocm.prusolutions.com’
SidName = ‘ocmdb’
LocalListenerAddress = ‘(ADDRESS=(PROTOCOL=TCP)(HOST=ocm.prusolutions.com)(PORT=2010))’
StandbyArchiveLocation = ‘location=use_db_recovery_file_dest’
AlternateLocation = ”
LogArchiveTrace = ’0′
LogArchiveFormat = ‘%t_%s_%r.dbf’
LatestLog = ‘(monitor)’
TopWaitEvents = ‘(monitor)’

Current status for “ocmdb”:
SUCCESS

• Managing Databases using DGMGRL

You can view and change database properties using dgmgrl

DGMGRL> show database ocmdb logxptmode;
LogXptMode = ‘ASYNC’

DGMGRL> show database ocmdb nettimeout;
NetTimeout = ’180′

DGMGRL> show database phyocm logxptmode;
LogXptMode = ‘ASYNC’

DGMGRL> show database phyocm nettimeout;
NetTimeout = ’180′

• Using sqlplus connect to primary database and change value of log_archive_max_processes parameter

SQL> SHOW PARAMETER log_archive_max_processes;

The current value is two, increase it to five and again check database configuration from dgmgrl

SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=5 SCOPE=MEMORY;

• Use show configuration command to get Dataguard status

DGMGRL> show configuration;

Configuration
Name: ocmdb
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
ocmdb – Primary database
phyocm – Physical standby database

Current status for “ocmdb”:
Warning: ORA-16608: one or more databases have warnings

• Use show database verbose ocmdb to get ocmdb database details

DGMGRL> show database verbose ocmdb ;

Database
Name: ocmdb
Role: PRIMARY
Enabled: YES
Intended State: ONLINE
Instance(s):
ocmdb

Properties:
InitialConnectIdentifier = ‘ocmdb’
LogXptMode = ‘ASYNC’
Dependency = ”
DelayMins = ’0′
Binding = ‘OPTIONAL’
MaxFailure = ’0′
MaxConnections = ’1′
ReopenSecs = ’300′
NetTimeout = ’180′
LogShipping = ‘ON’
PreferredApplyInstance = ”
ApplyInstanceTimeout = ’0′
ApplyParallel = ‘AUTO’
StandbyFileManagement = ‘AUTO’
ArchiveLagTarget = ’0′
LogArchiveMaxProcesses = ’2′
LogArchiveMinSucceedDest = ’1′
DbFileNameConvert = ”
LogFileNameConvert = ‘phyocm, ocmdb’
FastStartFailoverTarget = ”
StatusReport = ‘(monitor)’
InconsistentProperties = ‘(monitor)’
InconsistentLogXptProps = ‘(monitor)’
SendQEntries = ‘(monitor)’
LogXptStatus = ‘(monitor)’
RecvQEntries = ‘(monitor)’
HostName = ‘ocm.prusolutions.com’
SidName = ‘ocmdb’
LocalListenerAddress = ‘(ADDRESS=(PROTOCOL=TCP)(HOST=ocm.prusolutions.com)(PORT=2010))’
StandbyArchiveLocation = ‘location=use_db_recovery_file_dest’
AlternateLocation = ”
LogArchiveTrace = ’0′
LogArchiveFormat = ‘%t_%s_%r.dbf’
LatestLog = ‘(monitor)’
TopWaitEvents = ‘(monitor)’

Current status for “ocmdb”:
Warning: ORA-16792: configuration property value is inconsistent with database setting

• Get database inconsistent properties and fix the problem

DGMGRL> show database ocmdb inconsistentproperties;

INCONSISTENT PROPERTIES
INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE
ocmdb LogArchiveMaxProcesses 5 2 2

SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=2 SCOPE=MEMORY;

DGMGRL> show database ocmdb inconsistentproperties;

INCONSISTENT PROPERTIES
INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE

DGMGRL> show configuration;

Configuration
Name: ocmdb
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
ocmdb – Primary database
phyocm – Physical standby database

Current status for “ocmdb”:
SUCCESS

• Open new terminals connect to ocmdb and phyocm using sqlplus and execute following sql

SQL>SELECT db_unique_name,
database_role,
protection_mode,
protection_level
FROM v$database;

• Edit Database property using DG Broker

DGMGRL> edit database ocmdb set property LogArchiveMaxProcesses=10;
Property “logarchivemaxprocesses” updated

DGMGRL> edit database ocmdb set property LogArchiveMaxProcesses=10;
Property “logarchivemaxprocesses” updated

• Change configuration set Protection Mode to MaxAvailability from MaxPerformance

When you try to change set Protection Mode to MaxAvailability, it will give error, as Standby database phyocm property logxptmode is ‘ASYNC’

DGMGRL> edit configuration set protection mode as maxavailability;
Error: ORA-16627: operation disallowed since no standby databases would remain to support protection mode
Failed.

Check database property logxptmode

DGMGRL> show database ocmdb logxptmode;
LogXptMode = ‘ASYNC’

DGMGRL> show database phyocm logxptmode;
LogXptMode = ‘ASYNC’

Change LogXptMode to SYNC of Physical Standby database PHYOCM ONLY and then change the protection mode

DGMGRL> edit database phyocm set property logxptmode=’SYNC’;
Property “logxptmode” updated

DGMGRL> show database phyocm logxptmode;
LogXptMode = ‘SYNC’

DGMGRL> edit configuration set protection mode as maxavailability;
Operation requires shutdown of instance “ocmdb” on database “ocmdb”
Shutting down instance “ocmdb”…
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance “ocmdb” on database “ocmdb”
Starting instance “ocmdb”…
ORACLE instance started.
Database mounted.

Now change the property LogXptMode of ocmdb database to ‘SYNC’

DGMGRL> edit database ocmdb set property logxptmode=’SYNC’;
Property “logxptmode” updated

DGMGRL> show database ocmdb logxptmode;
LogXptMode = ‘SYNC’

DGMGRL> show configuration;

Configuration
Name: ocmdb
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: DISABLED
Databases:
ocmdb – Primary database
phyocm – Physical standby database

Current status for “ocmdb”:
SUCCESS

• If due to some reason, the Dataguard Protection modes is not change to MaxAvailability using DGMGRL then do it from SQLPLUS

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

• Open new terminals connect to ocmdb and phyocm using sqlplus and execute following sql

SQL>SELECT db_unique_name,
database_role,
protection_mode,
protection_level
FROM v$database;

• Switch Over to Standby database phyocm

DGMGRL> switchover to phyocm;
Performing switchover NOW, please wait…
Operation requires shutdown of instance “ocmdb” on database “ocmdb”
Shutting down instance “ocmdb”…
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance “phyocm” on database “phyocm”
Shutting down instance “phyocm”…
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance “ocmdb” on database “ocmdb”
Starting instance “ocmdb”…
ORACLE instance started.
Database mounted.
Operation requires startup of instance “phyocm” on database “phyocm”
Starting instance “phyocm”…
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is “phyocm”

If instances are down start them in mount mode, the primary database will automatically open

SQL> STARTUP MOUNT;

DGMGRL> show configuration;

Configuration
Name: ocmdb
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: DISABLED
Databases:
ocmdb – Physical standby database
phyocm – Primary database

Current status for “ocmdb”:
SUCCESS

Open new terminals connect to ocmdb and phyocm using sqlplus and execute following sql

SQL>SELECT db_unique_name,
database_role,
protection_mode,
protection_level
FROM v$database;

• Switchover back to ocmdb

DGMGRL> switchover to ocmdb;

DGMGRL> show configuration;

SQL>SELECT db_unique_name,
database_role,
protection_mode,
protection_level
FROM v$database;

• Failover to phyocm

From sqlplus shutdown, abort primary database

[oracle@ocm ~]$ echo $ORACLE_SID
ocmdb

SQL> SHUTDOWN abort;

• Using dgmgrl connect to new phyocm

[oracle@ocm ~]$ dgmgrl sys/oracle@phyocm

DGMGRL> failover to phyocm;

DGMGRL> show configuration;

Configuration
Name: ocmdb
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
ocmdb – Physical standby database (disabled)
phyocm – Primary database

Current status for “ocmdb”:
SUCCESS

• The Protection Mode automatically goes back to MaxPerformance, but the logxptmode properties remains ‘SYNC’

DGMGRL> show database ocmdb logxptmode;
LogXptMode = ‘SYNC’

DGMGRL> show database phyocm logxptmode;
LogXptMode = ‘SYNC’

• Open a new terminal and set ORACLE_SID for phyocm

[oracle@ocm scripts]$ export ORACLE_SID=phyocm

SQL>SELECT db_unique_name,
database_role,
protection_mode,
protection_level
FROM v$database;

• Connect to ocmdb and startup database in mount state

SQL> STARTUP mount;

• Now reinstate database ocmdb as physical standby

DGMGRL> reinstate database ocmdb;
Reinstating database “ocmdb”, please wait…
Operation requires shutdown of instance “ocmdb” on database “ocmdb”
Shutting down instance “ocmdb”…
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance “ocmdb” on database “ocmdb”
Starting instance “ocmdb”…
ORACLE instance started.
Database mounted.
Continuing to reinstate database “ocmdb” …
Reinstatement of database “ocmdb” succeeded

If reinstate fails for first time again start ocmdb database in mount state and reinstate, it will succeed in second or third attempt.

• Switchover back to ocmdb to make ocmdb as Primary Database

DGMGRL> switchover to ocmdb;

Performing switchover NOW, please wait…
Operation requires shutdown of instance “phyocm” on database “phyocm”
Shutting down instance “phyocm”…
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance “ocmdb” on database “ocmdb”
Shutting down instance “ocmdb”…
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance “phyocm” on database “phyocm”
Starting instance “phyocm”…
ORACLE instance started.
Database mounted.
Operation requires startup of instance “ocmdb” on database “ocmdb”
Starting instance “ocmdb”…
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is “ocmdb”

DGMGRL> show configuration;

Configuration
Name: ocmdb
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
ocmdb – Primary database
phyocm – Physical standby database

Current status for “ocmdb”:
SUCCESS

• Fast Start Failover

Connect to physical standby using dgmgrl

[oracle@ocm ~]$ dgmgrl sys/oracle@phyocm

• Currently the Protection Mode is MaxPerformance, For fast start failover the Protection Mode should be MaxAvailability.

Anytime to change configuration to “MaxAvailability” make sure the primary database property is logxptmode is “ASYNC” and standby database “logxptmode” is “SYNC then only it can be changed to “MaxAvailability” otherwise it will fail. If maxavailability does not work with DGMGRL then you do it from SQLPLUS

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

DGMGRL> show database phyocm logxptmode;
LogXptMode = ‘SYNC’

DGMGRL> show database ocmdb logxptmode;
LogXptMode = ‘SYNC’

DGMGRL> edit database ocmdb set property logxptmode=’ASYNC’;
Property “logxptmode” updated

DGMGRL> show database ocmdb logxptmode;
LogXptMode = ‘ASYNC’

DGMGRL> edit configuration set protection mode as maxavailability;

Operation requires shutdown of instance “ocmdb” on database “ocmdb”
Shutting down instance “ocmdb”…
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance “ocmdb” on database “ocmdb”
Starting instance “ocmdb”…
ORACLE instance started.
Database mounted.
Now make primary database “logxptmode” is “SYNC
DGMGRL> edit database ocmdb set property logxptmode=’SYNC’;
Property “logxptmode” updated

• Make sure both primary and secondary databases have FLASHBACK ON

SQL> SELECT db_unique_name,
database_role,
open_mode,
flashback_on
FROM v$database;

• Edit database property faststartfailovertarget and enable fast_start failover

DGMGRL> edit database phyocm set property faststartfailovertarget=ocmdb;
Property “faststartfailovertarget” updated

DGMGRL> edit database ocmdb set property faststartfailovertarget=phyocm;
Property “faststartfailovertarget” updated

DGMGRL> enable fast_start failover;
Enabled.

DGMGRL> show configuration;

Configuration
Name: ocmdb
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: ENABLED
Databases:
ocmdb – Primary database
phyocm – Physical standby database
– Fast-Start Failover target

Current status for “ocmdb”:
Warning: ORA-16608: one or more databases have warnings

DGMGRL> show database verbose ocmdb;

You will see warning in the end mentioned below

Current status for “ocmdb”:
Warning: ORA-16819: Fast-Start Failover observer not started

• Connect to ocmdb using sqlplus and execute following commands

SQL> SELECT fs_failover_status, fs_failover_current_target, fs_failover_threshold
FROM v$database;

SQL> SELECT fs_failover_observer_present, fs_failover_observer_host
FROM v$database;

Now go to FSFO Observer window and start it >it is best recommended to use the ‘-logfile’ qualifier on the DGMGRL command to direct any of its output message to a file that can be examined in case of a problem. Additionally it is also suggested to specify unique data file name that Observer uses. This can be done by adding the ‘FILE=’ command to the ‘start observer’ command.

This will also allow you to start multiple Observers for multiple Primary/Standby pairs on the same Observer system using the same Oracle Home. In this example, since everything is on the same system, the log in was made using the current ORACLE_SID, which was setup to be the standby database phyocm. In a real setup neither of your databases should be on the Observer system so you would have to use the ‘@tnsname’ qualifier on the ‘sys/password’ login

[oracle@ocm ~]$ export ORACLE_SID=phyocm

[oracle@ocm ~]$ echo $ORACLE_SID
phyocm

[oracle@ocm ~]$ dgmgrl -logfile $ORACLE_HOME/rdbms/log/dgmgrlOCMDB.LOG sys/oracle
DGMGRL for Linux: Version 10.2.0.1.0 – Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type “help” for information.
Connected.

DGMGRL> start observer file=’/u01/app/oracle/afoocmdb.dat’;

Make sure to correct observer file correctly having “afo.dat” in start

Connect to ocmdb using sqlplus and execute following commands
SQL> SELECT fs_failover_status, fs_failover_current_target, fs_failover_threshold
FROM v$database;

FS_FAILOVER_STATUS FS_FAILOVER_CURRENT_TARGET FS_FAILOVER_THRESHOLD
——————— —————————— ———————
SYNCHRONIZED phyocm 30

SQL> SELECT fs_failover_observer_present, fs_failover_observer_host
FROM v$database;

FS_FAIL FS_FAILOVER_OBSERVER_HOST
——- —————————————-
YES ocm.prusolutions.com

• Open a new session using dgmgrl and connect to standby database phyocm

[oracle@ocm ~]$ dgmgrl sys/oracle@phyocm
DGMGRL> show configuration;

Configuration
Name: ocmdb
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: ENABLED
Databases:
ocmdb – Primary database
phyocm – Physical standby database
– Fast-Start Failover target

Current status for “ocmdb”:
SUCCESS

• Connect to ocmdb database and shutdown abort;

SQL> SHUTDOWN abort;
ORACLE instance shut down.

OR

$ps –ef | grep –i pmon

Kill pmon process to primary database ocmdb to failover

DGMGRL> show configuration;

Configuration
Name: ocmdb
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: ENABLED
Databases:
ocmdb – Physical standby database (disabled)
– Fast-Start Failover target
phyocm – Primary database

Current status for “ocmdb”:
Warning: ORA-16608: one or more databases have warnings

DGMGRL> show database ocmdb;

Database
Name: ocmdb
Role: PHYSICAL STANDBY
Enabled: NO
Intended State: ONLINE
Instance(s):
ocmdb

Current status for “ocmdb”:
Error: ORA-16661: the standby database needs to be reinstated

• Connect to ocmdb instance and startup

[oracle@ocm ~]$ export ORACLE_SID=ocmdb

[oracle@ocm ~]$ sqlplus / as sysdba

SQL> STARTUP;
ORACLE instance started.

Total System Global Area 536870912 bytes
Fixed Size 1220460 bytes
Variable Size 209715348 bytes
Database Buffers 314572800 bytes
Redo Buffers 11362304 bytes
Database mounted.
ORA-16649: database will open after Data Guard broker has evaluated Fast-Start
Failover status

• It will take few minutes to get database synchronized. Execute following sql on both primary and secondary.

SQL> SELECT db_unique_name, open_mode, database_role FROM v$database;

SQL> SELECT fs_failover_status, fs_failover_current_target, fs_failover_threshold
FROM v$database;

DGMGRL> show configuration;

Configuration
Name: ocmdb
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: ENABLED
Databases:
ocmdb – Physical standby database
– Fast-Start Failover target
phyocm – Primary database

Current status for “ocmdb”:
SUCCESS

DGMGRL> STOP OBSERVER;
Done.

DGMGRL> disable fast_start failover;
Disabled.

DGMGRL> show configuration;

Configuration
Name: ocmdb
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: DISABLED
Databases:
ocmdb – Physical standby database
phyocm – Primary database

Current status for “ocmdb”:
SUCCESS

• Switchover back to ocmdb

DGMGRL> switchover to ocmdb;

Performing switchover NOW, please wait…
Operation requires shutdown of instance “phyocm” on database “phyocm”
Shutting down instance “phyocm”…
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance “ocmdb” on database “ocmdb”
Shutting down instance “ocmdb”…
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance “phyocm” on database “phyocm”
Starting instance “phyocm”…
ORACLE instance started.
Database mounted.
Operation requires startup of instance “ocmdb” on database “ocmdb”
Starting instance “ocmdb”…
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is “ocmdb”

Recovery Scenarios through RMAN

Complete Recovery when SYSTEM tablespace is missing.

Problem Generation

Delete system tablespace datafile from operating system when database is up and running.

[oracle@ocm ocmdb]$ rm -i /u01/app/oracle/oradata/disk3/ocmdb/system01.dbf

Solution

i. [oracle@ocm ocmdb]$ sqlplus / as sysdba
ii. SQL> SHUTDOWN abort;
iii. SQL> STARTUP mount;
iv. SQL> SELECT file#, name FROM v$datafile;
v. Using rman connect to target database and catalog database or directly to target database.
[oracle@oem scripts]$ rman target sys/oracle@ocmdb catalog rman/rman@oemdb
vi. RMAN> run
{
restore datafile 1;
recover datafile 1;
sql ‘ alter database open ‘;
}

• Complete Recovery when NON-SYSTEM tablespace is missing and database is open and restoring datafile to different location.

Problem Generation

i. Delete USERS tablespace datafile from operating system
[oracle@ocm ocmdb]$ rm -i users01.dbf
ii. Connect as user sh/sh and create a table in users tablespace
SQL> CREATE TABLE sales_test
TABLESPACE users
AS
SELECT *
FROM sales
WHERE ROWNUM < 10;

ERROR at line 3:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: ‘/u01/app/oracle/oradata/disk3/ocmdb/users01.dbf’
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

Solution

i. As DBA user make datafile users01.dbf off line
SQL> SELECT file#, name FROM v$datafile;
SQL> ALTER DATABASE DATAFILE 4 OFFLINE;
ii. Restoring and recovering datafile 4 at new location
RMAN> run
{
set newname for datafile 4 to ‘/u01/app/oracle/oradata/disk5/ocmdb/users01.dbf’;
restore datafile 4;
switch datafile 4;
recover datafile 4;
sql ‘ alter database datafile 4 online’;
}

iii. Connect as user sh/sh and create a table in users tablespace

SQL> CREATE TABLE sales_test
TABLESPACE users
AS
SELECT *
FROM sales
WHERE ROWNUM < 10;

Table created.

• Complete Recovery when NON-SYSTEM tablespace is missing and database is closed and restoring datafile to different location.

Problem Generation

i. Shutdown the database.
SQL> SHUTDOWN immediate;
ii. Delete USERS tablespace datafile from operating system
[oracle@ocm ocmdb]$ rm -i users01.dbf
iii. Start the database
SQL> STARTUP;

ORACLE instance started.

Total System Global Area 536870912 bytes
Fixed Size 1220460 bytes
Variable Size 213909652 bytes
Database Buffers 318767104 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 – see DBWR trace file
ORA-01110: data file 4: ‘/u01/app/oracle/oradata/disk5/ocmdb/users01.dbf’

Solution

i. Make datafile 4 offline
SQL> ALTER DATABASE DATAFILE 4 OFFLINE;
ii. Open the database with datafile 4 oflfine
SQL> ALTER DATABASE OPEN;
iii. Using RMAN again connect to target database
[oracle@oem scripts]$ rman target sys/oracle@ocmdb catalog rman/rman@oemdb
iv. Restoring and recovering datafile 4 at new location
RMAN> run
{
set newname for datafile 4 to ‘/u01/app/oracle/oradata/disk3/ocmdb/users01.dbf’;
restore datafile 4;
switch datafile 4;
recover datafile 4;
sql ‘ alter database datafile 4 online’;
}

• Recovery of datafile which has no backups

Problem Generation

i. Create Oracle Managed File tablespace
SQL> CREATE TABLESPACE reco_test;

ii. Create table sh_sales on tablespace reco_test.

SQL> CREATE TABLE sh_sales
TABLESPACE reco_test
AS
SELECT *
FROM sh.sales
WHERE ROWNUM < 10;

iii. Delete RECO_TEST tablespace datafile from operating system.
[oracle@ocm ~]$ rm –i /u01/app/oracle/oradata/disk5/OCMDB/datafile/o1_mf_reco_tes_6n8dnc7z_.dbf

iv. Select from table sh_sales.
SQL> SELECT COUNT (*) FROM sh_sales;

*
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5:
‘/u01/app/oracle/oradata/disk5/OCMDB/datafile/o1_mf_reco_tes_6n8dnc7z_.dbf’
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

Solution

i. Recover Datafile 5.
RMAN> run {
sql ‘ alter database datafile 5 offline ‘;
restore datafile 5;
recover datafile 5;
sql ‘ alter database datafile 5 online ‘;
}

ii. Select from table sh_sales.
SQL> SELECT COUNT (*) FROM sh_sales;

• Recover of missing controlfile ( Copy other controlfile and mount database).

Problem Generation

i. Shutdown the Database
SQL> SHUTDOWN immediate;
ii. Delete Control file control01.ctl from operating system
[oracle@ocm ocmdb]$ rm -i /u01/app/oracle/oradata/disk1/ocmdb/control01.ctl
iii. Startup Database.
SQL> STARTUP;

ORACLE instance started.

Total System Global Area 536870912 bytes
Fixed Size 1220460 bytes
Variable Size 218103956 bytes
Database Buffers 314572800 bytes
Redo Buffers 2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info

Solution

i. Check the control file location
SQL> SHOW PARAMETER control

NAME TYPE VALUE
———————————— ———– ——————————
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/disk1/
ocmdb/control01.ctl, /u01/app/
oracle/admin/ocmdb/control/con
trol02.ctl
ii. Copy control01.ctl from control02.ctl
[oracle@ocm ocmdb]$ cp /u01/app/oracle/admin/ocmdb/control/control02.ctl /u01/app/oracle/oradata/disk1/ocmdb/control01.ctl
iii. Mount the Database and Open it.
SQL> ALTER DATABASE MOUNT;

SQL> ALTER DATABASE OPEN;

• Incomplete recovery is required when archived log, redo log is missing, then recovery can only be made until the previous sequence, or when an important object was dropped and recovery needs to be made until before the object was dropped. Use until sequence or until time or until cancel for incomplete recovery.

Problem Generation

i. Shutdown Database.
SQL> SHUTDOWN immediate;
ii. Delete redo log files from operating system
[oracle@ocm ocmdb]$ rm -i /u01/app/oracle/oradata/disk4/ocmdb/redo01b.log[oracle@ocm ocmdb]$ rm -i /u01/app/oracle/oradata/disk2/ocmdb/redo01a.log

If redo log file is lost when database is up , always switch logfile few times until sql statement hangs to archive the available redo logfiles, which are still not archived.

iii. Startup the Database.
SQL> STARTUP;

ORACLE instance started.

Total System Global Area 536870912 bytes
Fixed Size 1220460 bytes
Variable Size 218103956 bytes
Database Buffers 314572800 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1:
‘/u01/app/oracle/oradata/disk2/ocmdb/redo01a.log’
ORA-00312: online log 1 thread 1:
‘/u01/app/oracle/oradata/disk4/ocmdb/redo01b.log’

Solution

i. Complete database restore is required ,execute sql below to get latest sequence# archived ,add 1 to last sequence and then use in RMAN to recover database.
SQL> SELECT thread#,
resetlogs_change#,
archived,
sequence#,
TO_CHAR (completion_time, ‘YYYY-MM-DD:HH24:MI:SS’) completion_time
FROM v$archived_log
WHERE archived = ‘YES’
AND completion_time = (SELECT MAX (completion_time)
FROM v$archived_log
WHERE archived = ‘YES’);

THREAD# RESETLOGS_CHANGE# ARC SEQUENCE# COMPLETION_TIME
———- —————– — ———- ——————-
1 1 YES 58 2011-01-29:21:02:46

ii. Connect to target database using RMAN catalog
[oracle@oem scripts]$ rman target sys/oracle@ocmdb catalog rman/rman@oemdb
iii. RMAN command to recover database.
RMAN> run
{
restore database;
recover database until sequence 59 thread 1;
sql ‘ alter database open resetlogs ‘;
}
iv. After database is open switch logfile few times
SQL> ALTER SYSTEM SWITCH LOGFILE;

SQL> ALTER SYSTEM SWITCH LOGFILE;

SQL> ALTER SYSTEM SWITCH LOGFILE;

• Recovery if all controlfiles are missing

Problem Generation

i. Shutdown Database.
SQL> SHUTDOWN immediate;
ii. Delete all Control files from operating system
[oracle@ocm ocmdb]$ rm -i /u01/app/oracle/oradata/disk1/ocmdb/control01.ctl

[oracle@ocm ocmdb]$ rm -i /u01/app/oracle/admin/ocmdb/control/control02.ctl
iii. Connect as sysdba and startup Database.
[oracle@ocm ocmdb]$ sqlplus / as sysdba

SQL> STARTUP;

ORACLE instance started.

Total System Global Area 536870912 bytes
Fixed Size 1220460 bytes
Variable Size 222298260 bytes
Database Buffers 310378496 bytes
Redo Buffers 2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info

Solution

i. Using RMAN restore contolfile from Autobackup and Recover Database
RMAN> run
{
restore controlfile from autobackup;
sql ‘ alter database mount’;
recover database;
sql ‘ alter database open resetlogs’;
}
ii. List Database Incarnation
RMAN> list incarnation;

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
——- ——- ——– —————- — ———- ———-
1 2 OCMDB 4083722939 PARENT 1 15-JAN-11
1 1415 OCMDB 4083722939 PARENT 503961 29-JAN-11
1 1867 OCMDB 4083722939 CURRENT 519078 29-JAN-11
iii. After database is open switch logfile few times
SQL> ALTER SYSTEM SWITCH LOGFILE;

SQL> ALTER SYSTEM SWITCH LOGFILE;

SQL> ALTER SYSTEM SWITCH LOGFILE;
v. Always backup controlfile after open resetlogs even if CONTROLFILE AUTOBACKUP ON. Let us NOT BACKUP controlfile and see what happens in next scenario.
RMAN> backup current controlfile; — For now do not execute this command

• Recover if all controlfiles,datafiles,spfile,redologs are lost

Problem Generation

i. Shutdown Database.

SQL> SHUTDOWN immediate;
ii. Delete all the datafiles ,controlfiles ,redo logfiles ,spfile ,initocmdb.ora files from Operating System
iii. Startup Database
SQL> STARTUP;

ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file ‘/u01/app/oracle/product/10.2.0/db_1/dbs/initocmdb.ora’

Make sure you have lost all the files not just spfile, if only spfile is lost , then too you will get above error you need to create only spfile.

Solution

i. Using RMAN connect to target database using catalog
[oracle@oem scripts]$ rman target sys/oracle@ocmdb catalog rman/rman@oemdb

Recovery Manager: Release 10.2.0.1.0 – Production on Sun Jan 30 06:02:44 2011

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

connected to target database (not started)
connected to recovery catalog database
ii. Set Database DBID
RMAN> set dbid — dbid 4083722939
iii. Startup Database in Nomount State
RMAN> startup nomount;
iv. Restore spfile from Autobackup
RMAN> restore spfile from autobackup;

v. Again start the Database in Nomount State
RMAN> startup force nomount;
vi. Restore controlfile from Autobackup
RMAN> restore controlfile from autobackup;
vii. Mount Database
RMAN> alter database mount;
viii. Open a terminal in OCMDB machine and check archive log completion time
[oracle@ocm ~]$ sqlplus / as sysdba

SQL> SELECT thread#,
resetlogs_change#,
archived,
sequence#,
TO_CHAR (completion_time, ‘YYYY-MM-DD:HH24:MI:SS’)
completion_time
FROM v$archived_log
WHERE archived = ‘YES’
AND completion_time = (SELECT MAX (completion_time)
FROM v$archived_log
WHERE archived = ‘YES’);

THREAD# RESETLOGS_CHANGE# ARC SEQUENCE# COMPLETION_TIME
———- —————– — ———- ——————-
1 519078 YES 1 2011-01-29:23:18:17

ix. By executing the command below we may not be able to recover database because control file is from older incarnation. This is because we have not taken backup of controlfile after reset logs and immediately crashed the database after recovery in previous scenario.
RMAN> run
{
restore database;
recover database until sequence 1 thread 1;
sql ‘ alter database open resetlogs’;
}
x. Now, we have to reset database to older incarnation and recover the database.
xi. List Database Incarnation.
RMAN> list incarnation;

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
——- ——- ——– —————- — ———- ———-
1 2 OCMDB 4083722939 PARENT 1 15-JAN-11
1 1415 OCMDB 4083722939 PARENT 503961 29-JAN-11
1 1867 OCMDB 4083722939 CURRENT 519078 29-JAN-11

xii. Open a terminal in OCMDB machine and check archive log completion time.
SQL> SELECT thread#,
resetlogs_change#,
archived,
sequence#,
TO_CHAR (completion_time, ‘YYYY-MM-DD:HH24:MI:SS’)
completion_time
FROM v$archived_log
WHERE archived = ‘YES’
ORDER BY completion_time;

THREAD# RESETLOGS_CHANGE# ARC SEQUENCE# COMPLETION_TIME
———- —————– — ———- ——————-
1 503961 YES 4 2011-01-29:22:18:18

Add 1 to the latest sequence of last incarnation i.e. 4+1 =5

xiii. Reset the Database to old incarnation and recover.
RMAN> reset DATABASE TO incarnation 1415;

RMAN> run
{
restore database;
recover database until sequence 5 thread 1;
sql ‘ alter database open resetlogs’;
}

• Recovery if Temporary tablespace is lost

Recreate the temporary tablespace.