Recovery using RMAN backup when control file is lost

How to recover database when control file is lost using RMAN backup?

There are two ways we can recover the database when control file is lost:
1. Restore the control file and recover the database
2. Recreate the control file using backup control file and recover
Note: please follow http://orcl11gdba.blogspot.in/2014/09/recovery-using-rman-backup-when-control_18.html for this method

DB Name: TESTDB
Here we shall perform first way to recover the database.

[oracle@avalance_testserv oradata]$ ls
control.ctl  dbf  redo
[oracle@avalance_testserv oradata]$ rm control.ctl
[oracle@avalance_testserv oradata]$ ls
dbf  redo
Note: Now I lost my control file.

[oracle@avalance_testserv oradata]$ sqlplus / as sysdba
SQL> shut abort
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
SQL> exit
Observation: we are putting the database in nomount state because we can’t directly mount it without control file.to do that we have to restore it from backup as below.

[oracle@avalance_testserv oradata]$ rman target/

RMAN> restore controlfile from '/u01/app/oracle/TESTDB/flash_recovery_area/TESTDB/backupset/ORA_rman_ctrl_backup_TESTDB_0cpiqqu4_1_1.bck';

Starting restore at 18-SEP-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=99 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/app/oracle/TESTDB/oradata/control.ctl
Finished restore at 18-SEP-14

Observation: Now got our control file from backup. So we can mount  the database.

SQL> alter database mount;
Database altered.

Let’s perform recovery operation inorder to make controlfile SCN is  updated with same as that of for DB files.

RMAN> recover database ;

Starting recover at 18-SEP-14
Starting implicit crosscheck backup at 18-SEP-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=98 device type=DISK
Crosschecked 5 objects
Finished implicit crosscheck backup at 18-SEP-14

Starting implicit crosscheck copy at 18-SEP-14
using channel ORA_DISK_1
Finished implicit crosscheck copy at 18-SEP-14

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/TESTDB/flash_recovery_area/TESTDB/backupset/ORA_rman_arch_backup_TESTDB_0lpiqjtc_1_1.bck
File Name: /u01/app/oracle/TESTDB/flash_recovery_area/TESTDB/backupset/2014_09_18/o1_mf_annnn_TAG20140918T160603_b1ofdmh1_.bkp
File Name: /u01/app/oracle/TESTDB/flash_recovery_area/TESTDB/backupset/2014_09_18/o1_mf_annnn_TAG20140918T160603_b1ofdos2_.bkp
File Name: /u01/app/oracle/TESTDB/flash_recovery_area/TESTDB/backupset/2014_09_18/o1_mf_annnn_TAG20140918T160603_b1ofdnmr_.bkp
File Name: /u01/app/oracle/TESTDB/flash_recovery_area/TESTDB/backupset/ORA_rman_ctrl_backup_TESTDB_0cpiqqu4_1_1.bck
File Name: /u01/app/oracle/TESTDB/flash_recovery_area/TESTDB/backupset/ORA_rman_full_backup_TESTDB_0jpiqjsh_1_1.bck
File Name: /u01/app/oracle/TESTDB/flash_recovery_area/TESTDB/backupset/ORA_rman_ctrl_backup_TESTDB_0mpiqjtd_1_1.bck
File Name: /u01/app/oracle/TESTDB/flash_recovery_area/TESTDB/archivelog/2014_09_16/o1_mf_1_7_b1hm73wc_.arc
File Name: /u01/app/oracle/TESTDB/flash_recovery_area/TESTDB/archivelog/2014_09_12/o1_mf_1_17_b1556v45_.arc
File Name: /u01/app/oracle/TESTDB/flash_recovery_area/TESTDB/archivelog/2014_09_12/o1_mf_1_18_b1556v4m_.arc
File Name: /u01/app/oracle/TESTDB/flash_recovery_area/TESTDB/archivelog/2014_09_12/o1_mf_1_16_b154d54v_.arc
File Name: /u01/app/oracle/TESTDB/flash_recovery_area/TESTDB/archivelog/2014_09_12/o1_mf_1_15_b1543l1h_.arc
File Name: /u01/app/oracle/TESTDB/flash_recovery_area/TESTDB/archivelog/2014_09_12/o1_mf_1_16_b1556v3m_.arc
File Name: /u01/app/oracle/TESTDB/flash_recovery_area/TESTDB/archivelog/2014_09_12/o1_mf_1_17_b154d62h_.arc
File Name: /u01/app/oracle/TESTDB/flash_recovery_area/TESTDB/archivelog/2014_09_12/o1_mf_1_14_b1543l02_.arc
File Name: /u01/app/oracle/TESTDB/flash_recovery_area/TESTDB/archivelog/2014_09_15/o1_mf_1_6_b1fhgrbg_.arc
File Name: /u01/app/oracle/TESTDB/flash_recovery_area/TESTDB/archivelog/2014_09_15/o1_mf_1_5_b1ffrfmz_.arc
File Name: /u01/app/oracle/TESTDB/flash_recovery_area/TESTDB/autobackup/2014_09_18/o1_mf_n_858607535_b1o6bqs3_.bkp
File Name: /u01/app/oracle/TESTDB/flash_recovery_area/TESTDB/autobackup/2014_09_18/o1_mf_n_858607530_b1o6blp5_.bkp
File Name: /u01/app/oracle/TESTDB/flash_recovery_area/TESTDB/autobackup/2014_09_18/o1_mf_n_858610716_b1o9g4rp_.bkp

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/TESTDB/oradata/dbf/redo02.log
archived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/TESTDB/oradata/dbf/redo03.log
archived log file name=/u01/app/oracle/TESTDB/oradata/dbf/redo02.log thread=1 sequence=5
archived log file name=/u01/app/oracle/TESTDB/oradata/dbf/redo03.log thread=1 sequence=6
media recovery complete, elapsed time: 00:00:00
Finished recover at 18-SEP-14

Recovery is completed all the archived logs and transactions are applied.

[oracle@avalance_testserv oradata]$  sqlplus / as sysdba


SQL> alter database open resetlogs;

Database altered.


It is suggested to take RMAN backup immediately after the activity is over. please follow the link to take RMAN backup: http://orcl11gdba.blogspot.in/2014/05/taking-database-backup-using-rman-method.html


Cheers…!!!

RMAN-05537: DUPLICATE without TARGET connection when auxiliary instance is started with spfile cannot use SPFILE clause

How to resolve the error   RMAN-05537: DUPLICATE without TARGET connection when auxiliary instance is started with spfile cannot use SPFILE clause ?


Error:
Starting Duplicate Db at 9-SEP-14
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 9/12/2014 11:27:52
RMAN-05537: DUPLICATE without TARGET connection when auxiliary instance is started with spfile cannot use SPFILE clause

I got this error    while trying to duplicate a database from backup location.
duplicating database:
 http://orcl11gdba.blogspot.in/2014/03/duplicate-or-clone-database-from_10.html
  http://orcl11gdba.blogspot.in/2014/03/duplicate-or-clone-database-from.html

Cause:
 it is required to startup the database with pfile before we are going to connect to auxiliary instance.

Action:
shut down the database
sql> shut immediate /shut abort
startup the database with pfile
sql> startup nomount pfile ='initpavantes.ora'

How to find Unused indexes?

What is index monitoring?

Oracle databases consist of several custom indexes which are of different types useful for fast result retrieval of query. There are indexes which are not at all used and created just with an assumption that optimizer going to consider it in it's plan and our application will speed up. But creating unnecessary indexes for tables consume space. So, how to figure out these unused indexes in our database?

One of the best ways to identify this is index monitoring despite its disadvantages. Index monitoring gives the complete list of used/unused indexes that we enabled for. We can enable index monitoring on any index of any schema.

Enabling index monitoring:

ALTER INDEX sales.ground_idx MONITORING USAGE;

From the moment we enabled the index monitoring it starts gathering information and puts in V$OBJECT_USAGE table. We can't conclude the index usability on one single day. index monitoring has to be enabled for some more days and in the long run only we can decide whether index can be deleted or not.

select   * from  V$OBJECT_USAGE where index_name = 'GROUND_IDX';

INDEX_NAME	TABLE_NAME	MONITORING	USED	START_MONITORING	END_MONITORING
GROUND_IDX	GROUND_SELLER_DETAILS	YES	NO	7/18/2014 10:43

After few days we are going to disable the index monitoring and will see the report

Disabling index monitoring:

ALTER INDEX sales.ground_idx   NOMONITORING USAGE;

This is the result we get from v$object_usage with end_monitoring(date on which we stopped the monitoring)

INDEX_NAME	TABLE_NAME	MONITORING	USED	START_MONITORING	END_MONITORING
GROUND_IDX	GROUND_SELLER_DETAILS	YES	NO	7/18/2014 10:43	9/18/2014 7:24

Now the span of two months none of my application/users used this index. So I can drip this index now.

Note: Even though I have enabled the index monitoring I am not getting any result from V$OBJECT_USAGE. I don’t find rows in this table .Why?

Reason:  As per oracle documents, V$OBJECT_USAGE displays the index usage information belong to that schema only.so any other schema apart from sales won’t get the above result. So we have to login to individual schemas to check their respective index usage details.

Scripts:

    a) Enabling index monitoring on bulk number of indexes

spool start_index_monitoring.sql

SELECT 'alter index ' || owner || '.' || index_name || ' monitoring usage;' FROM dba_indexes WHERE owner NOT IN ('SYS', 'SYSTEM','SYSMAN','DBSNMP');

SPOOL off

@start_index_monitoring.sql

b)Disabling  index monitoring on bulk number of indexes 

spool stop_index_monitoring.sql

SELECT 'alter index ' || owner || '.' || index_name || ' nomonitoring usage;' FROM dba_indexes WHERE owner NOT IN ('SYS', 'SYSTEM','SYSMAN','DBSNMP');

SPOOL off

@stop_index_monitoring.sql