Renaming the database to a new name and revert it back to its original name

Original database name: BEFORE
Rename database to   : AFTER

Step 1.

Change the cluster_database parameter to FALSE

[oracle@host1 admin]$ export ORACLE_SID=BEFORE1

[oracle@host1 admin]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Mon Sep 1 09:38:14 2014

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

USERNAME       INST_NAME    HOST_NAME                           SID   SERIAL#  VERSION    STARTED  SPID       OPID  CPID
-------------- ------------ ----------------------------------- ----- -------- ---------- -------- ---------- ----- ----------
SYS            BEFORE1       host1.intra.searshc.com    236   29       11.2.0.4.0 20140901 7868       35    7867


SQL> alter system set
  2  cluster_database=false scope=spfile sid='*';
System altered.

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

STEP 2.

Shutdown the database from srvctl command

$srvctl stop database -d BEFORE

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

STEP 3.

Start the instance 1 in mount mode

[oracle@host1 admin]$ export ORACLE_SID=BEFORE1
[oracle@host1 admin]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Mon Sep 1 09:40:05 2014

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

Connected to an idle instance.

select
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0

SQL> startup mount
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size                  2253664 bytes
Variable Size             469765280 bytes
Database Buffers         1124073472 bytes
Redo Buffers                7319552 bytes
Database Buffers         1124073472 bytes
Redo Buffers                7319552 bytes
Database mounted.
SQL>

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

STEP 4.

Run the nid command

[oracle@host1 admin]$ nid target=sys/password@BEFORE DBNAME=AFTER

DBNEWID: Release 11.2.0.4.0 - Production on Mon Sep 1 09:45:33 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to database BEFORE (DBID=2458372709)

Connected to server version 11.2.0

Control Files in database:
    +DATA_DATA1/after/control01.ctl
    +DATA_DATA1/after/control02.ctl

Change database ID and database name BEFORE to AFTER? (Y/[N]) => y

Proceeding with operation
Changing database ID from 2458372709 to 1208276477
Changing database name from BEFOR to AFTER
    Control File +DATA_DATA1/after/control01.ctl - modified
    Control File +DATA_DATA1/after/control02.ctl - modified
    Datafile +DATA_DATA1/after/system01.db - dbid changed, wrote new name
    Datafile +DATA_DATA1/after/sysaux01.db - dbid changed, wrote new name
    Datafile +DATA_DATA1/after/undotbs01.db - dbid changed, wrote new name
    Datafile +DATA_DATA1/after/users01.db - dbid changed, wrote new name
    Datafile +DATA_DATA1/after/undotbs02.db - dbid changed, wrote new name
    Datafile +DATA_DATA1/after/temp01.db - dbid changed, wrote new name
    Control File +DATA_DATA1/after/control01.ctl - dbid changed, wrote new name
    Control File +DATA_DATA1/after/control02.ctl - dbid changed, wrote new name
    Instance shut down

Database name changed to AFTER.
Modify parameter file and generate a new password file before restarting.
Database ID for database AFTER changed to 1208276477.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

[oracle@host1 admin]$

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

STEP 5.

Create pfile from spfile

[oracle@host1 admin]$

[oracle@host1 admin]$ export ORACLE_SID=BEFORE1
[oracle@host1 admin]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Mon Sep 1 09:49:14 2014

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

Connected to an idle instance.

select
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0

SQL> create pfile from SPFILE='+DATA_DATA1/BEFORE/spfilebefore.ora';

File created.

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

STEP 6.

Modify the newly created pfile

Update the below value in parameter file :

AFETR2.__db_cache_size=1124073472
AFTER1.__db_cache_size=1124073472
AFTER2.__java_pool_size=16777216
AFTER1.__java_pool_size=16777216
AFTER2.__large_pool_size=33554432
AFTER1.__large_pool_size=100663296
AFTER1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
AFTER2.__pga_aggregate_target=536870912
AFTER1.__pga_aggregate_target=536870912
AFTER2.__sga_target=1610612736
AFTER1.__sga_target=1610612736
AFTER2.__shared_io_pool_size=0
AFTER1.__shared_io_pool_size=0
AFTER2.__shared_pool_size=419430400
AFTER1.__shared_pool_size=352321536
AFTER2.__streams_pool_size=0
AFTER1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/AFTER/adump'

*.db_name='AFTRCHNG'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=AFTERXDB)'
AFTER1.instance_number=1
AFTER2.instance_number=2

AFTER1.thread=1
AFTER2.thread=2
AFTER1.undo_tablespace='UNDOTBS2'
AFTER2.undo_tablespace='UNDOTBS1'

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

Step 7.

Create the dump directory and password file.

$mkdir -p /u01/app/oracle/admin/AFTER/adump

$orapwd file=orapwAFTER password=n4u2ck

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

STEP 8.

Create spfile from pfile.

SQL> create SPFILE='+DATA_DATA1/BEFORE/spfileAFTER.ora' from pfile;

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

Step 9.

Rename the parameter file and delete all it contents and give value of above spfile in the pfile.

[oracle@host1 dbs]$ cp initBEFORE1.ora initAFTER1.ora

SPFILE='+DATA_DATA1/BEFORE/spfileAFTER.ora'

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

Step 10.

Start the AFTER database in mount mode

[oracle@host1 dbs]$export ORACLE_SID=AFTER1

[oracle@host1 dbs]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Mon Sep 1 08:21:21 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options


USERNAME       INST_NAME    HOST_NAME                           SID   SERIAL#  VERSION    STARTED  SPID       OPID  CPID
-------------- ------------ ----------------------------------- ----- -------- ---------- -------- ---------- ----- ----------
SYS            AFTER1       host1.intra.searshc.com    106   5        11.2.0.4.0 20140901 10444      29    10443


SQL> shut immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size                  2253664 bytes
Variable Size             469765280 bytes
Database Buffers         1124073472 bytes
Redo Buffers                7319552 bytes
Database mounted.

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

Step 11.

Open the database in resetlog mode

SQL> alter database open resetlogs;

Database altered.

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

Step 12.

Change the cluster_database parameter to TRUE

SQL> alter system set
  2  cluster_database=true scope=spfile sid='*';

System altered.

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

Step 13.

Shutdow the database

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down

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

Step 14.

Add the services to the SRVCTL

[oracle@host1 dbs]$ srvctl add database -d AFTER -o /u01/app/oracle/product/11.2.4/dbhome_1 -p +DATA_DATA1/BEFORE/spfileafter.ora -y AUTOMATIC -s OPEN -a "RAC5_DATA"
[oracle@host1 dbs]$ srvctl config database -d AFTER
Database unique name: AFTER
Database name:
Oracle home: /u01/app/oracle/product/11.2.4/dbhome_1
Oracle user: oracle
Spfile: +DATA_DATA1/BEFORE/spfileafter.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: AFTER
Database instances:
Disk Groups: RAC5_DATA
Mount point paths:
Services:
Type: RAC
Database is administrator managed
[oracle@host1 dbs]$ srvctl add instance -d AFTER -i AFTER2 -n hostb
[oracle@host1 dbs]$ srvctl add instance -d AFTER -i AFTER1 -n host1
[oracle@host1 dbs]$ srvctl config database -d AFTER
Database unique name: AFTER
Database name:
Oracle home: /u01/app/oracle/product/11.2.4/dbhome_1
Oracle user: oracle
Spfile: +DATA_DATA1/BEFORE/spfileafter.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: AFTER
Database instances: AFTER1,AFTER2
Disk Groups: RAC5_DATA
Mount point paths:
Services:
Type: RAC
Database is administrator managed
[oracle@host1 dbs]$

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


Step 15.

SQL> alter system set cluster_database=TRUE scope=spfile sid='*';

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

Steps 16.

Start the database with srvctl command

[oracle@host1 dbs]$ srvctl start database -d AFTER

[oracle@host1 dbs]$ srvctl status database -d AFTER
Instance AFTER1 is running on node host1
Instance AFTER2 is running on node hostb

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


For renaming it back to original name we need to previous BEFORE database from configuration
using srvctl command and follow the all above steps:


Stop the database and run the below command:

[oracle@host1 dbs]$ srvctl remove database -d BEFORE
Remove the database BEFORE? (y/[n]) y


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


Now we need to rename it to the original name:

Step 1.

Please verify the local listener and set it:



SQL> show parameter listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------------------------------------------------------------------------------------
listener_networks                    string
local_listener                       string
remote_listener                      string      host.com:1522


SQL> alter database open;

Database altered.

SQL> select instance_name,status from gv$instance;

INSTANCE_NAME    STATUS
---------------- ------------
AFTER1             OPEN

SQL> !tnsping NEW

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 02-SEP-2014 13:18:43

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/11.2.4/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = host.com)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = AFTER)))
OK (0 msec)

SQL> !nslookup host1-vip
Server:         151.149.224.164
Address:        151.149.224.164#53

Name:   host1-vip.intra.searshc.com
Address: 151.149.141.85


SQL> alter system set local_listener='(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =151.149.141.85)(PORT = 1522)))' scope=both;

System altered.

SQL> alter system register;

System altered.


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


NOW FOLLOW THE THE REMAINING STEPS FROM 1 - 16

Step by step - How to refresh UAT/DEV/QA/TEST database schemas from PRODUCTION database

                                                                            Schema Refresh

Schema refresh is one of the routine tasks in DBA's life, Whenever application team feels to test something in production database, first they do their test in dev/uat/qa/test database.

For this they need the latest data copy from production database to dev/uat/qa/test database. We can achieve this by schema refresh.

Follow below steps in source and target database sides. Lets assume we are refreshing USER1 schema from production to development database.

Source Database Side :

Step 1: Check the Schema size in the source database.

SQL> select owner, sum(bytes)/1024/1024/1024  "GB"  from dba_segments where owner='USER1';

Step 2:Check the tablespace assigned to USER1 schema.

SQL>select default_tablespace from dba_users where username='USER1';

Step 3: Check tablespace size assign to USER1 scema

Step 4:  Take the count of schema objects, this will be useful after refresh to compare both source and target schema objects.

SQL> select object_type, count(*) from dba_objetcts where owner='ORACLE' group by object_type;

Step 5: Before export, first check mount point size where we're going to store the export dump files, if mount point doesn't have sufficient space export job will fail. So make sure we have enough space in the mount point.

Step 6 : Create a data pump directory at the database level for export, if we do not create directory then it will use the default directory DATA_PUMP_DIR. So if we are using this default directory, we need to make sure that it is mapped to the correct path.

Step 7:   Now take the export of schema or schemas.

            $ nohup expdp \'/ as sysdba\' directory=DATA_PUMP_DIR
              dumpfile=proddb1_8August_%U.dmp logfile=proddb1_8August.log
              schemas=USER1 parallel=8 &


Step 8: Copy the dump file from source server to destination server. We basically use scp command but we can also use other server copy command for this. It is always better to copy the dump
files in the location which will be used in the impdp directory path. Otherwise we will have to again copy this to the impdp directory path.

We have completed almost all steps in source database side, now we are moving to target database side,

Target Database side :

Step 1: Check if we have enough space available in the data pump director path to hold the dump file coming from the source database server.

Step 2 : Check if we have enough space available in the tablespace of the user which is going to be     refreshed. For this, it is always better to check the tablespace size of that particular user in the prod database and add that much of space before refresh starts.

Step 3:It is always recommended to take the export backup of the schema on target side as well which we are going to refresh.

               $ nohup expdp \'/ as sysdba\' directory=DATA_PUMP_DIR
                  dumpfile=devdb1_8August_%U.dmp logfile=devdb1_8August.log
                  schemas=USER1 parallel=8 &

Step 4 : Drop the schema objects only on the target side i.e. on development database. Double check  it before dropping anything in the database. Make sure we only drop the schema object not the complete schema. Sometime people also drop complete schema but that is not                                recommended.


Step 5 : Import the data into target schema(Shema refresh)

               $ nohup impdp \'/ as sysdba\' directory=DATA_PUMP_DIR
                  dumpfile=proddb1_8August_%U.dmp logfile=imp_8August.log
                  schemas=USER1 parallel=8 &
       
Note: While import is running, it is always better to monitor the alert log file of the database
and also monitor the import log file. Sometime the error is reported on the alert log and is not captured in the import log. So monitor both logs.

Step 6: Gather the statistics on the schema which we just refreshed.

Step 7: Count the objects and match it from production database. Make sure all are imported    properly before confirming to anyone or application team.

Suggestions are most welcome :)

Step by step - Test/Development Database Refresh From Production Procedure

The following note describes the generic procedure to be followed to refresh a Energy Test or Development environment database from a Energy production database backup.

In this example, we are refreshing the DEVE72 database from a backup taken of the PRD22 database.

The assumption here is that the required RMAN production backup has already been either copied from the production database via scp or has been restored from tape or has been placed in an NFS shared location which is accessible from both machines.

On the target machine the backups have been restored in the location u02/backup/DEVE72

Procedure:

• Shutdown the DEVE72 database if it is already running

[oracle@DEVE72 DEVE72]$ ps -ef |grep pmon

  oracle 12701 29275   0 15:36:00 pts/3       0:00 grep pmon

  oracle  7377  2235   0   May 19 ?          84:59 ora_pmon_DEVE72

[oracle@DEVE72 DEVE72]$ echo $ORACLE_SID

DEVE72

[oracle@DEVE72 DEVE72]$ sqlplus sys as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jun 17 15:36:22 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Enter password:

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

• Delete the database data files, redo log files and control files

Note:

**Ensure we are connected to the right server and are in the appropriate directory location**

[oracle@DEVE72 DEVE72]$ hostname

DEVE72

[oracle@DEVE72 DEVE72]$ pwd

/u03/oradata/DEVE72

[oracle@DEVE72 DEVE72]$ rm *.dbf

[oracle@DEVE72 DEVE72]$ cd /u04/oradata/DEVE72

[oracle@DEVE72 DEVE72]$ ls

control2.ctl  redo01a.log   redo02a.log   redo03a.log

[oracle@DEVE72 DEVE72]$ rm *.ctl

[oracle@DEVE72 DEVE72]$ rm *.log

[oracle@DEVE72 DEVE72]$ cd /u05/oradata/DEVE72

[oracle@DEVE72 DEVE72]$ ls

control3.ctl  redo01b.log   redo02b.log   redo03b.log

• Copy the current init.ora parameter file of the DEVE72 database  and create a parameter file with the name of the source production database (PRD22)

[oracle@DEVE72 ~]$ cd $ORACLE_HOME/dbs

[oracle@DEVE72 dbs]$ pwd

/u01/app/oracle/product/10.2.0/db_1/dbs

[oracle@DEVE72 dbs]$ cp initDEVE72.ora initprd22.ora

• Make the following changes to the initprd22.ora

*.db_name='prd22'

• Set the environment to reflect the source production database and start the instance in NOMOUNT mode

[oracle@DEVE72 dbs]$ export ORACLE_SID=prd22

[oracle@DEVE72 dbs]$ sqlplus sys as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jun 21 12:58:32 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Enter password:

Connected to an idle instance.

SQL> startup nomount pfile=$ORACLE_HOME/dbs/initprd22.ora

ORACLE instance started.

Total System Global Area 3154116608 bytes

Fixed Size                  2043904 bytes

Variable Size             637538304 bytes

Database Buffers         2499805184 bytes

Redo Buffers               14729216 bytes

SQL> quit

• Restore the control file from the backup location

The control file backup exists in the format  “c-<DBID>-<DATE>-<BACKUP SEQUENCE NUMBER>

Select the controlfile appropriate to the period of time that we wish to restore the database from

[oracle@DEVE72 DEVE72]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Fri Jun 18 11:05:20 2010

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

connected to target database: prd22 (not mounted)

RMAN> restore controlfile from '/u02/backup/DEVE72/c-4031762323-20100616-00';

Starting restore at 18-JUN-10

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=538 devtype=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:06

output filename=/u03/oradata/DEVE72/control1.ctl

output filename=/u04/oradata/DEVE72/control2.ctl

output filename=/u05/oradata/DEVE72/control3.ctl

Finished restore at 18-JUN-10

• Mount the database

RMAN> alter database mount

2> ;

database mounted

released channel: ORA_DISK_1

• Catalog the RMAN backup sets which have been copied from the source production database

RMAN> catalog start with '/u02/backup/DEVE72';

searching for all files that match the pattern /u02/backup/DEVE72

List of Files Unknown to the Database

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

File Name: /u02/backup/DEVE72/c-4031762323-20100616-00

File Name: /u02/backup/DEVE72/21lgatsk_1_1

File Name: /u02/backup/DEVE72/c-2263349373-20100419-00

...

...

Do you really want to catalog the above files (enter YES or NO)? YES

cataloging files...

cataloging done

List of Cataloged Files

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

File Name: /u02/backup/DEVE72/c-4031762323-20100616-00

File Name: /u02/backup/DEVE72/21lgatsk_1_1

File Name: /u02/backup/DEVE72/2flgdi89_1_1

...

...

List of Files Which Where Not Cataloged

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

File Name: /u02/backup/DEVE72/c-2263349373-20100419-00

  RMAN-07518: Reason: Foreign database file DBID: 2263349373  Database Name: DEVE72

Note – ignore any errors reported for files that are not cataloged

• Determine the last archivelog sequence included in the backup. We will be recovering the database until this particular sequence number.

Look for the string "List of archived logs"

RMAN > list backup of archivelog all

 List of Archived Logs in backup set 69

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

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

  1    79      7970987    16-JUN-10 7973402    16-JUN-10

In this case, the last archivelog backed up belongs to sequence number 79. If we wish to recover the database until the last archived log which has been backed up, we need to increment the last sequence number by 1. So in this case it will be 79+1  or 80

• Create the following files in the location “/u02/backup/{DB_NAME}”

vi rman_head

RUN

{

# allocate a channel to the tape device

        ALLOCATE CHANNEL d1 DEVICE TYPE disk;

# rename the datafiles and online redo logs

vi rman_tail

# Do a SET UNTIL to prevent recovery of the online logs

 SET UNTIL SEQUENCE 80;

# restore the database and switch the datafile names

    RESTORE DATABASE;

            SWITCH DATAFILE ALL;

# recover the database

    RECOVER DATABASE;

}

vi generate_datafiles.sql

set head off pages 0 feed off echo off verify off

set lines 200

spool rename_datafiles.lst

select 'SET NEWNAME FOR DATAFILE ' || FILE# || ' TO ''' || '/u03/oradata/&1/' || substr(name,instr(name,'/',-1)+1) || ''';' from v$datafile;

spool off

exit;

vi generate_logfiles.sql

set head off pages 0 feed off echo off

spool rename_logfiles.lst

SELECT 'SQL "ALTER DATABASE RENAME FILE '''''||  MEMBER ||'''''' ||chr(10)||'to ''''' || member || '''''" ;' FROM V$LOGFILE;

exit

• Generate data file rename script

While in directory /u02/backup/{DB_NAME}, connect as sys as sysdba via SQL*PLUS session  and run the generate_datafiles.sql script.

The generate_datafiles.sql script accepts a parameter which is the target database name.

SQL> @generate_datafiles DEVE72

It will create a file rename_datafiles.lst . The contents of this file will be like this:

SET NEWNAME FOR DATAFILE 1 TO '/u03/oradata/DEVE72/system01.dbf';

SET NEWNAME FOR DATAFILE 2 TO '/u03/oradata/DEVE72/undotbs01.dbf';

SET NEWNAME FOR DATAFILE 3 TO '/u03/oradata/DEVE72/sysaux01.dbf';

….

….

• Generate redo log file rename script

While in directory /u02/backup/{DB_NAME}, connect as sys as sysdba via SQL*PLUS session  and run the generate_logfiles.sql script.

SQL> @generate_logfiles.sql

It will create a file called rename_logfiles.lst

Edit the rename_logfiles.lst file and change values of prd22 to DEVE72

SQL "ALTER DATABASE RENAME FILE ''/u04/oradata/prd22/redo01a.log'' to ''/u04/oradata/DEVE72/redo01a.log''" ;

SQL "ALTER DATABASE RENAME FILE ''/u05/oradata/prd22/redo01b.log'' to ''/u05/oradata/DEVE72/redo01b.log''" ;

SQL "ALTER DATABASE RENAME FILE ''/u04/oradata/prd22/redo02a.log'' to ''/u04/oradata/DEVE72/redo02a.log''" ;

SQL "ALTER DATABASE RENAME FILE ''/u05/oradata/prd22/redo02b.log'' to ''/u05/oradata/DEVE72/redo02b.log''" ;

SQL "ALTER DATABASE RENAME FILE ''/u04/oradata/prd22/redo03a.log'' to ''/u04/oradata/DEVE72/redo03a.log''" ;

SQL "ALTER DATABASE RENAME FILE ''/u05/oradata/prd22/redo03b.log'' to ''/u05/oradata/DEVE72/redo03b.log''" ;

• Specify the archive log sequence until which recovery will be performed

Edit the rman_tail file  and change the line with the words “>> SET UNTIL SEQUENCE 80” to include the appropriate archive log sequence which was noted in an earlier step.

• Prepare the RMAN restore and recover database script

[oracle@DEVE72 DEVE72]$ cat rman_head rename_datafiles.lst rename_logfiles.lst rman_tail > rman_recovery.rcv

• Connect to the target database via RMAN and execute the rman_recovery.rcv script

[oracle@DEVE72 dbs]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Mon Jun 21 13:04:04 2010

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

connected to target database (not started)

RMAN> @rman_recovery.rcv

Note:

At this stage, we can continue to recover the database and keep it in sync with the source production database by manually applying the archive log files which are copied from the production server to the log archive destination of the test database on the target server.

We can do this via SQL*PLUS connected as SYS by issuing the command

RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE

When there are no more archive log files to apply, we enter CANCEL

• Open the database with RESETLOGS

After the RMAN script has successfully run and recovered the database until the last archive log sequence, we will now open the database using the ALTER DATABASE OPEN RESETLOGS command executed either via RMAN or from SQL*PLUS connected as SYS.

SQL> alter database open resetlogs;

Database altered.

• Temporary Tablespace Reconfiguration

After the restore, we will note that the temporary tablespace files are still pointing to the source production database as these tempfiles have not been renamed when we renamed all the database data files in an earlier step.

Obtain the name of the current tempfile -

SQL> select name from v$tempfile;

NAME

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

/u03/oradata/prd22/temp01.dbf

Drop the tempfile-

SQL> ALTER DATABASE TEMPFILE '/u03/oradata/prd22/temp01.dbf' drop including datafiles;

Database altered.

Add a new tempfile for the refreshed database in the appropriate location-

SQL> ALTER TABLESPACE temp ADD TEMPFILE '/u03/oradata/DEVE72/temp01.dbf' size 2G;

Tablespace altered.

• At this stage we will change the passwords if required for the SYS and  SYSTEM or any other database accounts.

• Change the database name using nid

We will now shutdown the database and then mount it.

We will then run the nid utility to change the database name – we need to provide the appropriate password for the user SYS and the new value we want for the database name.

[oracle@DEVE72 dbs]$ nid target=sys dbname=DEVE72

DBNEWID: Release 10.2.0.4.0 - Production on Fri Jun 18 13:55:14 2010

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

Password:

Connected to database PRD22 (DBID=4031762323)

Connected to server version 10.2.0

Control Files in database:

    /u03/oradata/DEVE72/control1.ctl

    /u04/oradata/DEVE72/control2.ctl

    /u05/oradata/DEVE72/control3.ctl

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

Proceeding with operation

Changing database ID from 4031762323 to 2271553224

Changing database name from PRD22 to DEVE72

    Control File /u03/oradata/DEVE72/control1.ctl - modified

    Control File /u04/oradata/DEVE72/control2.ctl - modified

    Control File /u05/oradata/DEVE72/control3.ctl - modified

    Datafile /u03/oradata/DEVE72/system01.dbf - dbid changed, wrote new name

    Datafile /u03/oradata/DEVE72/undotbs01.dbf - dbid changed, wrote new name

    Datafile /u03/oradata/DEVE72/sysaux01.dbf - dbid changed, wrote new name

    Datafile /u03/oradata/DEVE72/users01.dbf - dbid changed, wrote new name

...

...

...

    Datafile /u03/oradata/DEVE72/COGNOSPAD_CLOB01.dbf - dbid changed, wrote new name

    Datafile /u03/oradata/DEVE72/temp01.dbf - dbid changed, wrote new name

    Control File /u03/oradata/DEVE72/control1.ctl - dbid changed, wrote new name

    Control File /u04/oradata/DEVE72/control2.ctl - dbid changed, wrote new name

    Control File /u05/oradata/DEVE72/control3.ctl - dbid changed, wrote new name

    Instance shut down

Database name changed to DEVE72.

Modify parameter file and generate a new password file before restarting.

Database ID for database DEVE72 changed to 2271553224.

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.

At this stage the database has been shutdown and now we need to mount it and issue the RESETLOGS command after the database change.

Note:

We will now set the environment to the target database (until this stage, for example, ORACLE_SID had been set to the production database value)

[oracle@DEVE72 backup] export ORACLE_SID=DEVE72

[oracle@DEVE72 backup]$ sqlplus sys as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jun 21 14:12:00 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Enter password:

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 3154116608 bytes

Fixed Size                  2043904 bytes

Variable Size             637538304 bytes

Database Buffers         2499805184 bytes

Redo Buffers               14729216 bytes

SQL> alter database open resetlogs;

Database altered.

• Post Database Refresh Tasks

Create import and export directories required for Data Pump

/u02/export/{DB_NAME} - export_dir

/u02/import/{DB_NAME} - import_dir

RMAN – run the appropriate script to register details in the RMAN catalog database where required.