nid DBNAME change steps after RMAN recovery on R12.1.3 instance with 11g DB

DBNAME change using nid utility:

Once RMAN recovery is completed and DB has been open with PROD db name.now here we want to change to TARGET instance name.As I faced small confusion while using this utility.I would like to share those steps.

Here My TARGET DB is Up and Running as PROD name after using RMAN clone steps.

go to $ORACLE_HOME/dbs run " relink all".It will resolve any libray issues while executing "nid".

oracle/xxxxx/orabin/11.1.0/dbs]$ nid target=sys dbname=XXXRATE

DBNEWID: Release 11.1.0.7.0 - Production on Wed May 11 21:09:15 2016

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

Password:
Connected to database xxxxx (DBID=2579751613)

Connected to server version 11.1.0

Control Files in database:
    /oracle/xxxxx/oradata/cntrl01.dbf
    /oracle/xxxxx/oradata/cntrl02.dbf
    /oracle/xxxxx/oradata/cntrl03.dbf

Change database ID and database name xxxxx to XXXRATE? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 2579751613 to 140640575
Changing database name from xxxxx to NJVCRATE
    Control File /oracle/xxxxx/oradata/cntrl01.dbf - modified
    Control File /oracle/xxxxx/oradata/cntrl02.dbf - modified
    Control File /oracle/xxxxx/oradata/cntrl03.dbf - modified
    Datafile /oracle/xxxxx/oradata/system01.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/system02.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/system03.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/system04.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/system05.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/ctxd01.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/owad01.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_queue02.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/odm.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/olap.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/sysaux01.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/apps_ts_tools01.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/system12.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_data04.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_ind06.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_ref03.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_int02.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/sysaux02.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/xxnjvc01.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/sysaux03.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_media10.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_data05.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/sysaux04.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_ind07.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_ref04.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_data06.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_media11.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/undo11.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/xxnjvc12.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/xxnjvc13.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/xxnjvc14.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/xxnjvc15.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_media12.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_data07.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_data08.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_data09.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_data10.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_data11.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/system13.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/system14.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_media02.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_media03.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_media04.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_media05.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_media06.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_media07.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_media08.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_media09.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/ctxd02.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/odm1.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_summ02.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_int03.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_nolog02.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_ind08.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_ind09.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_ind10.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_archive02.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/xxnjvc02.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/xxnjvc03.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/xxnjvc04.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/xxnjvc05.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/xxnjvc06.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/xxnjvc07.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/xxnjvc08.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/xxnjvc09.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/xxnjvc10.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/xxnjvc11.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/njvcusers01.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_media13.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/xxnjvc16.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_data12.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_media14.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_data13.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_ind11.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_queue03.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/undo12.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_queue04.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_media15.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/xxnjvc17.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_data14.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_media16.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_ref05.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_media17.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_data15.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_ind12.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_ref06.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_media18.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_ind13.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_data16.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_ref07.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/apexebs01.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/apexfile01.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/apexuser01.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_ind14.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_data17.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/system10.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/system06.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/portal01.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/system07.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/system09.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/system08.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/system11.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_data01.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_ind01.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_ref01.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_int01.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_summ01.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_nolog01.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_archive01.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_queue01.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_media01.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_data02.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_data03.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_ind02.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_ind03.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_ind04.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_ind05.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_ref02.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/temp12.db - dbid changed, wrote new name
    Control File /oracle/xxxxx/oradata/cntrl01.dbf - dbid changed, wrote new name
    Control File /oracle/xxxxx/oradata/cntrl02.dbf - dbid changed, wrote new name
    Control File /oracle/xxxxx/oradata/cntrl03.dbf - dbid changed, wrote new name
    Instance shut down

Database name changed to XXXRATE.
Modify parameter file and generate a new password file before restarting.
Database ID for database XXXRATE changed to 140640575.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

2)Open pfile and change the dbname parameter from prod to target name and start the database.now the db has been started with target dbname but still datafile locations are not changed .

3)Take the control file backup on open Db which is target instance.

SQL> alter database backup control file to trace as '/tmp/control.sql';

Database altered.

Shutdown the Instance and take the the control files backup ,as we are going to recreate the control file again for change the data file locations.

4) Control File creation:

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size                  2166536 bytes

Variable Size             427819256 bytes

Database Buffers          624951296 bytes

Redo Buffers               14000128 bytes

SQL> @/tmp/control.sql

Control file created.

SQL> alter database open resetlogs;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE

----------

READ WRITE

Now start the lsnrctl on target instance.Now your DB Instance is ready.

Clean the FND_NODEs and run auto config on DB node with production apps password.Then after Your DB is ready .

RMAN corrupt data file recovery

As we have situation like our production db files are corrupted more than 90%.Initially we thought of to go full recovery but then after we decided to go as table space level.

RMAN: Recovering from corrupteddatafile

Any kind of recovery needs a few very basic steps:

1) Checking the availability of valid backup
2) Deciding upon the recovery procedure
3) Actual recovery

When I know the table space name and datfiles are corrupted ,then check as below:

SELECT FILE#, STATUS, ERROR, RECOVER, TABLESPACE_NAME, NAME

FROM V$DATAFILE_HEADER

WHERE RECOVER = 'YES' OR (RECOVER IS NULL AND ERROR IS NOT NULL);

RMAN> restore datafile 4 preview;

RMAN> restore datafile 4 preview;

SQL> select * from v$log;

The Procedure
Recovery can be complete or incomplete. A complete recovery has no data loss whereas an incomplete recovery has some data loss. An incomplete recovery is accompanied by RESETLOGS. An incomplete recovery is necessary when some or all of the data is unavailable to recover the object from its restore point to the point of dataloss. An object here could be a datafile, tablespace, or database. Restore point here is the point until where the DB has been restored to using the backup, and to make the DB current until the point of object loss we will need recovery to be performed. Recovery requires availability of archived and online redo logs. If the archived logs or redo logs are missing, then incomplete recovery is the only option. We may have to use RECOVER…UNTIL TIME option for point-in-time recovery of the missing object. (more on the point-in-time recovery in future posts).

In our case, since we have all the required archives we will perform a complete recovery. There is no data loss in this recovery procedure. The DB can remain in MOUNT or OPEN state during this procedure. Only the datafile will be made offline during the restore/recovery operation if the DB is in OPEN state.

The Recovery Itself
Since all the backups are available, the actual recovery procedure is quite simple.

Connect to RMAN and issue the below statements:


RUN {
SQL ‘ALTER TABLESPACE users OFFLINE IMMEDIATE’;
RESTORE TABLESPACE users;
RECOVER TABLESPACE users;
SQL ‘ALTER TABLESPACE users ONLINE’;
}


The snapshot of the same, in my DB, is below. In my snapshot, the ALTER TABLESPACE users ONLINE statement is missing. I wanted to confirm that the RECOVER TABLESPACE command runs successfully before bringing the tablespace online. I ran the ONLINE tablespace command later at the end.
Also note that this RMAN command OFFLINE’s and ONLINE’s the entire tablespace. Since in our case this tablespace had only one datafile (which was missing), it wont much matter OFFLINE’ing the tablespace or datafile. If we had more than one datafile in this tablespace and one among those datafiles was missing or corrupt, we would OFFLINE only that datafile instead of the entire tablespace. The command is ‘ALTER DATABASE DATAFILE  OFFLINE’.



Once you recover all the Tablespace has been recovered.System table space we can't take offline when the DB is in online.

Take the DB is Offline mode and startup in nomount state.


RUN {

RESTORE TABLESPACE SYSTEM;

RECOVER TABLESPACE  SYSTEM;

}



Once SYSTEM TS recovery is done.start the DB as "alter database open".



Note: Please make sure RMAN backup location and archive logs need to be accessible all the time where you want to recovery.