Database Refresh Activity

Database Refresh Activity

On Source Database (Example: abc)

1. Take RMAN Level 0 backup

2. Create pfile from spfile

3. Transfer all the backup pieces fo datafile,controlfile,pfile & Archive logs generated during backup to the destination server.

4. Collect the info of no.of datafiles,controlfiles,redo logs etc.

5. select dbid from v$database;

                        DBID

                        ----------

                        633518243

6. select member from v$logfile;

On Destination Database (Example: xyz)

1. Collect the info of no.of datafiles,controlfiles,redo logs etc.

2. select dbid from v$database;

SQL> select dbid from v$database;

             DBID

----------

1954691695

3. Take backup of init.ora file or spfile.

4. Shutdown the database if it's open.

5. startup restrict mount;

  SQL> drop database;

6. Modify the db_name parameter of xyz as abc in init.ora parameter file

7. . oraenv

            xyz

8. echo $ORACLE_SID ( it should display xyz )

9. sqlplus '/as sysdba'

SQL> startup nomount

10. rman target /

RMAN > set dbid=633518243 ( dbid of source database )

11. Restore controlfile from backup

restore controlfile from '<backup piece which contains your controlfile backup’>

You can check this detail on source database abc by firing below command

RMAN > list backup of controlfile;

12. RMAN > alter database mount;

13. Catalog all the backups

RMAN> catalog backuppiece '< all backup pieces >’;

14. Use below query in source database abc and change the filenames as per the path of destination database xyz

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

******* First need to get output from ABC database and modify this as per XYZ database *******

set lines 200 pages 999

select 'SET NEWNAME FOR DATAFILE ' || file# || ' to ' || name || ';' from v$datafile;

set lines 150 pages 999

select 'SET NEWNAME FOR TEMPFILE ' || file# || ' to ' || name || ';' from v$tempfile;

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

15. Set newname to all the datafiles of ABC as per XYZ

RMAN>

run

{

SET NEWNAME FOR DATAFILE 1 to ‘new location for datafile 1’;

SET NEWNAME FOR DATAFILE 2 to ‘new location for datafile 2’;

SET NEWNAME FOR DATAFILE 3 to ‘new location for datafile 3’;

.

.

.

.

SET NEWNAME FOR DATAFILE last to ‘new location for datafile last’;

RESTORE DATABASE;

SWITCH DATAFILE ALL;

SWITCH TEMPFILE ALL;

}

16. Recover database

RMAN> recover database; (if all backups and archives are cataloged)

Or

sqlplus '/as sysdba'

SQL> recover database using backup controlfile until cancel;

Now provide the archive log of dap1 whichever it asks until last archive.

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

*** If we need to apply more than 100 archive log files,it would be better to take archive logs backup from Production FROM SEQUENCE <seq> UNTIL SEQUENCE <seq>***

run {

BACKUP ARCHIVELOG FROM SEQUENCE <seq> UNTIL SEQUENCE <seq> tag = '<tag_name>';

}

17. SQL>alter database open RESETLOGS;

(ORA-27040: file create error, unable to create file

Linux-x86_64 Error: 2: No such file or directory)

If you get above error then need to do below else not required

SQL> select member from v$logfile;

Then Run the below command

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

alter database rename file '<abc/redo.log>’ to ‘<xyz/redo.log>’;

.

.

.

alter database rename file '<abc/redo.log>’ to ‘<xyz/redo.log>’;

18. alter database open resetlogs;  --first check without resetlog at step 17.

If needed clear logfile group like:1/2/3/4

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

ALTER DATABASE CLEAR LOGFILE GROUP 4;

19. Change DB-ID using nid com

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

cd $ORACLE_HOME/bin

nid TARGET=/ DBNAME=<new name, here xyz>

Succesfully changed database name and ID.

DBNEWID - Completed succesfully.

20. Shut down the database and startup normally.

21. Check client connectivity 

All done !!!