Creating Physical Standby Database on Oracle 11g from active database

In this article we are going to create physical standby database from active database (without taking backup of primary database)

Some key points before proceeding with the physical standby setup.

• Primary database should be in archivelog mode.
• Forced Logging is on in Primary database (sql>alter database force logging ;)
• Initialization parameter “db_name” should be same on both primary and standby database.
• Initialization parameter “db_unique_name” should be different on primary and standby databases.

Assumptions

• You have two servers with an operating system and Oracle installed on them.
• The primary server has a running instance.
• The standby server has a software only installation.

Parameters Used in the Article:

	Node1(Primary)	Node2(Standby)
Machine Name	REDHAT1	REDHAT2
Oracle Database Version	11.2.0	11.2.0
DB Service Name	Prim	stnd
Oracle Software Owner	O/S User – oracle	O/S User – oracle

STEP 1. Setup the connectivity (listener.ora and tnsnames.ora) for the primary and standby databases.

Listener Entries:

Edit the file "tnsnames.ora" on both the nodes and add the entries for both the nodes (redhat1,redhat2) database (prim,stnd). As shown below:

Primary:

listener.ora file

[oracle@redhat1 admin]$ vi listener.ora

# listener.ora Network Configuration File: /data/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <my_primary_host_name>)(PORT = 1521))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = prim)
(ORACLE_HOME = /data/oracle/app/oracle/product/11.2.0/dbhome_1)
)
)

ADR_BASE_LISTENER = /data/oracle/app/oracle

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON

tnsnames.ora file

[oracle@redhat1 admin]$ vi tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

PRIM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = <my_primary_host_name>)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prim)(UR=A)
)
)

STND =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = <my_standby_host_name>)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = stnd)(UR=A)
)

)

Standby:

listener.ora file

[oracle@redhat2 admin]$ vi listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <my_standby_host_name>)(PORT = 1521))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = stnd)
(ORACLE_HOME = /data/oracle/app/oracle/product/11.2.0/dbhome_1)
)
)

ADR_BASE_LISTENER = /data/oracle/app/oracle

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON

tnsnames.ora file

[oracle@redhat2 admin]$ vi tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

PRIM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = <my_primary_host_name>)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prim)(UR=A)
)
)

STND =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = <my_standby_host_name>)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = stnd)(UR=A)
)
)

[oracle@redhat1 ~]$ tnsping prim
[oracle@redhat1 ~]$ tnsping stnd

From the second node (redhat2) perform the following:

[oracle@redhat2 ~]$ tnsping prim
[oracle@redhat2 ~]$ tnsping stnd

STEP 2. Configure the Primary Database:

Do the below activities only on the first node (primary database):

Enable Archive logging Mode:

To check whether the database is in "archivelog mode", connect to the database and fire the command:
SQL> archive log list ;

If the output shows "Database Log mode - No Archive Mode", then we have to enable archive logging.

For enabling archive logging, We have to open the database in mount state.

close/shutdown the database.
If suppose we had shutdown the database, then start the database in mount mode, for doing so type the below commands:

SQL> startup mount
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

Enable Force Logging:

SQL> alter database force logging;

STEP 3. Modify and add the following parameters in the initialization parameter file of the primary database.

The below mentioned steps to be performed on the Primary Site (redhat1).

We have to set the below mentioned parameters for configuring dataguard:
—log_archive_dest_1
—log_archive_dest_state_1
—log_archive_dest_2
—log_archive_dest_state_2
—standby_file_management
—log_archive_config
—fal_server
—fal_clien

initprim.ora

prim.__db_cache_size=46137344
prim.__java_pool_size=4194304
prim.__large_pool_size=4194304
prim.__oracle_base=’/u01/home/oracle’#ORACLE_BASE set from environment
prim.__pga_aggregate_target=205520896
prim.__sga_target=239075328
prim.__shared_io_pool_size=0
prim.__shared_pool_size=167772160
prim.__streams_pool_size=8388608
*.audit_file_dest=’/u01/home/oracle/admin/prim/adump’
*.audit_trail=’db’
*.compatible=’11.2.0.0.0′
*.control_files=’/u01/app/oracle/oradata/prim/control01.ctl’,’/u01/app/oracle/oradata/prim/control02.ctl’
*.db_block_size=8192
*.db_domain=”

*.diagnostic_dest=’/u01/home/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=primXDB)’
*.db_recovery_file_dest=’/u01/app/fra’
*.db_recovery_file_dest_size=2070572032 #(I have it as nearly 2 GB)
*.memory_target=444596224
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.undo_tablespace=’UNDOTBS1′

*.db_name=’prim’
*.db_unique_name=’prim’

*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(prim,stand)'
*.log_archive_dest_1=’location=use_db_recovery_file_dest
valid_for=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=prim’
*.log_archive_dest_2=’service=stnd
valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=stnd’
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable

STEP 4. Add the following parameters in the initialization parameter file of the standby database

Copy pfile from primary database to standby database and do following changes.

initstnd.ora

stnd.__db_cache_size=230686720
stnd.__java_pool_size=4194304
stnd.__large_pool_size=4194304
stnd.__pga_aggregate_target=268435456
stnd.__sga_target=394264576
stnd.__shared_io_pool_size=0
stnd.__shared_pool_size=146800640
stnd.__streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/admin/stnd/adump’
# *.audit_trail=’db’
*.compatible=’11.2.0.0.0′
*.control_files=’/u02/app/oracle/oradata/stnd/control01.ctl’,’/u02/app/oracle/oradata/stnd/control02.ctl’
*.db_block_size=8192
*.db_domain=”
*.db_name=’prim’
*.db_recovery_file_dest=’/u01/app/fra’
*.db_recovery_file_dest_size=2070572032
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=stndXDB)’
*.log_archive_format=’%t_%s_%r.dbf’
*.memory_target=660602880
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.undo_tablespace=’UNDOTBS1′
*.log_archive_dest_1=’location=use_db_recovery_file_dest
valid_for=(all_logfiles,all_roles)
db_unique_name=stnd’
*.log_archive_dest_2=’service=prim
valid_for=(online_logfiles,primary_role)
db_unique_name=prim’
*.db_unique_name=’stnd’
*.db_file_name_convert=’/data/oracle/app/oracle/oradata/prim/’,’/data/oracle/app/oracle/oradata/stnd/’
*.log_file_name_convert=’/data/oracle/app/oracle/oradata/prim/’,’/data/oracle/app/oracle/oradata/stnd/’
*.standby_file_management=AUTO
*.FAL_SERVER=’prim’
*.FAL_CLIENT=’stnd’
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=defer

STEP 5. Check if the primary database is using the password file or not. If not, then create one as below.

orapwd file=$ORACLE_HOME/dbs/orapwPRIM password=oracle force=y

STEP 6. Now copy the password file of the primary database “orapwprim” located at $ORACLE_HOME/dbs to the standby server location $ORACLE_HOME/dbs and rename the file as “orapwstnd”.

STEP 7. Now connect to the Primary database as target database and standby database as auxiliary instance through RMAN. Make sure that the Primary database is open and the standby database is in nomount stage (STARTED).

[oracle@redhat1 dbs]$ rman target sys/sys@prim auxiliary sys/sys@stnd

 Recovery Manager: Release 11.2.0.3.0 - Production on Mon Dec 29 17:41:35 2014

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

 connected to target database: prim (DBID=2649358339)

connected to auxiliary database: prim (not mounted)

Now perform the duplicate operation to create the standby database.

rman>duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 29-DEC-14

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=183 device type=DISK

contents of Memory Script:

{

   backup as copy reuse

   targetfile  '/data/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/orapwprim' auxiliary format

 '/data/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/orapwstnd'   ;

}

executing Memory Script

Starting backup at 29-DEC-14

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=225 device type=DISK

Finished backup at 29-DEC-14

contents of Memory Script:

{

   backup as copy current controlfile for stnd auxiliary format  '/data/oracle/app/oracle/oradata/stnd/control01.ctl';

   restore clone controlfile to  '/data/oracle/app/oracle/oradata/stnd/control02.ctl' from

 '/data/oracle/app/oracle/oradata/stnd/control01.ctl';

}

executing Memory Script

Starting backup at 29-DEC-14

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

copying stnd control file

output file name=/data/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_prim.f tag=TAG20141229T174152 RECID=4 STAMP=867606112

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 29-DEC-14

Starting restore at 29-DEC-14

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 29-DEC-14

contents of Memory Script:

{

   sql clone 'alter database mount stnd database';

}

executing Memory Script

sql statement: alter database mount stnd database

 contents of Memory Script:

{

   set newname for tempfile  1 to

 "/data/oracle/app/oracle/oradata/stnd/temp1.dbf";

   switch clone tempfile all;

   set newname for datafile  1 to

 "/data/oracle/app/oracle/oradata/stnd/system.dbf";

   set newname for datafile  2 to

 "/data/oracle/app/oracle/oradata/stnd/user04.dbf";

   set newname for datafile  3 to

 "/data/oracle/app/oracle/oradata/stnd/sysaux.dbf";

   set newname for datafile  4 to

 "/data/oracle/app/oracle/oradata/stnd/undo.dbf";

   set newname for datafile  5 to

 "/data/oracle/app/oracle/oradata/stnd/test1_tmp.dbf";

   set newname for datafile  6 to

 "/data/oracle/app/oracle/oradata/stnd/test_corrupt.dbf";

   backup as copy reuse

   datafile  1 auxiliary format

"/data/oracle/app/oracle/oradata/stnd/system.dbf"   datafile

2 auxiliary format

 "/data/oracle/app/oracle/oradata/stnd/user04.dbf"   datafile

3 auxiliary format

 "/data/oracle/app/oracle/oradata/stnd/sysaux.dbf"   datafile

4 auxiliary format

 "/data/oracle/app/oracle/oradata/stnd/undo.dbf"   datafile

5 auxiliary format

 "/data/oracle/app/oracle/oradata/stnd/test1_tmp.dbf"   datafile

6 auxiliary format

"/data/oracle/app/oracle/oradata/stnd/test_corrupt.dbf"   ;

sql 'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

 renamed tempfile 1 to /data/oracle/app/oracle/oradata/stnd/temp1.dbf in control file

executing command: SET NEWNAME

 executing command: SET NEWNAME

executing command: SET NEWNAME

 executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

 Starting backup at 29-DEC-14

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00005 name=/data/oracle/app/oracle/oradata/prim/test1_tmp.dbf

output file name=/data/oracle/app/oracle/oradata/stnd/test1_tmp.dbf tag=TAG20141229T174159

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=/data/oracle/app/oracle/oradata/prim/undo.dbf

output file name=/data/oracle/app/oracle/oradata/stnd/undo.dbf tag=TAG20141229T174159

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=/data/oracle/app/oracle/oradata/prim/system.dbf

output file name=/data/oracle/app/oracle/oradata/stnd/system.dbf tag=TAG20141229T174159

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=/data/oracle/app/oracle/oradata/prim/user04.dbf

output file name=/data/oracle/app/oracle/oradata/stnd/user04.dbf tag=TAG20141229T174159

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=/data/oracle/app/oracle/oradata/prim/sysaux.dbf

output file name=/data/oracle/app/oracle/oradata/stnd/sysaux.dbf tag=TAG20141229T174159

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

channel ORA_DISK_1: starting datafile copy

input datafile file number=00006 name=/data/oracle/app/oracle/oradata/prim/test_corrupt.dbf

output file name=/data/oracle/app/oracle/oradata/stnd/test_corrupt.dbf tag=TAG20141229T174159

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 29-DEC-14

sql statement: alter system archive log current

contents of Memory Script:

{

   switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=4 STAMP=867606131 file name=/data/oracle/app/oracle/oradata/stnd/system.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=5 STAMP=867606131 file name=/data/oracle/app/oracle/oradata/stnd/user04.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=6 STAMP=867606131 file name=/data/oracle/app/oracle/oradata/stnd/sysaux.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=7 STAMP=867606131 file name=/data/oracle/app/oracle/oradata/stnd/undo.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=8 STAMP=867606131 file name=/data/oracle/app/oracle/oradata/stnd/test1_tmp.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=9 STAMP=867606131 file name=/data/oracle/app/oracle/oradata/stnd/test_corrupt.dbf

Finished Duplicate Db at 29-DEC-14

RMAN>

Recovery Manager complete.

STEP 8. Once the duplication is completed, close the RMAN prompt and connect to the standby database through SQL.

sqlplus sys/<password>@stnd as sysdba

Check the status of the standby database by making sure it is in mount stage.

sql>select status,instance_name,database_role from v$instance,v$database;

 STATUS       INSTANCE_NAME    DATABASE_ROLE

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

MOUNTED      STND          PHYSICAL STANDBY

STEP 9. Now start the managed recovery process on the standby database.

sql>alter database recover managed standby database disconnect from session;

Database altered.

STEP 10. Now check if the managed recovery process (MRP) has been started on the standby database or not.

SQL> select process,status,sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#
——— ———— ———-
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
MRP0      WAIT_FOR_LOG         40

Here, the MRP has been started and is waiting for the log sequence# 40. If MRP is not started, then the above query would not show up the MRP0 under the process column.

STEP 11. On the primary database, perform a few log switches and check if the logs are applied to the standby database.

sqlplus sys/<password>@prim as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Dec 30 10:24:12 2014

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

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

sql>alter system switch logfile;

System altered.

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
————–

39

sqlplus sys/<password>@stnd as sysdba

SQL> select thread#,max(sequence#) from v$archived_log where applied=’YES’ group by thread#;

THREAD# MAX(SEQUENCE#)
———- ————–
     1             39

Here, the maximum sequence# generated on the Primary database is 38 and the maximum sequence# applied on the standby database is also 38 which means that the standby database is in sync with the primary database.

This finishes physical standby database cretion steps.

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

HOW TO CHANGE DATABASE NAME IN ORACLE

To change Database name, we have two options.

1. By recreating controlfile

2 Using nid (DBNEWID utility).

Let’s first see how to change DB name using manual method (By recreating control file)

METHOD 1: BY RE-CREATING CONTROLFILE

STEP1:- CREATE CONTROLFILE CREATATION SCRIPT.

[oracle@NVMBD1BZY150D00 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 24 16:29:40 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database backup controlfile to trace;

Database altered.

STEP2:- EDIT/MODIFY CONTROLFILE CREATION SCRIPT.

 

[oracle@oralinux trace]$ ls -lrt

total 16
-rw-r-----. 1 oracle oinstall 1.3K Dec 24 16:26 CLONEDB_dbrm_41909.trc

-rw-r-----. 1 oracle oinstall   73 Dec 24 16:27 CLONEDB_ora_42007.trm

-rw-r-----. 1 oracle oinstall 6.8K Dec 24 16:27 CLONEDB_ora_42007.trc

-rw-r-----. 1 oracle oinstall 494M Dec 24 16:27 alert_CLONEDB.log

[oracle@oralinux trace]$ cp CLONEDB_ora_42007.trc control_clonedb.sql
[oracle@oralinux trace]$ vi control_clonedb.sql

This will generate script in above location

[oracle@dbaserver1 ~ ]$ vi control_clonedb.sql
Here change the database name and replace word REUSE with SET and make sure it is having RESETLOGS

CREATE CONTROLFILE SET DATABASE "CLONEDBN" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 5
    MAXLOGMEMBERS 5
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/data/oracle/app/oracle/oradata/CLONEDB/redo1.log'  SIZE 10M BLOCKSIZE 512,
  GROUP 2 '/data/oracle/app/oracle/oradata/CLONEDB/redo2.log'  SIZE 10M BLOCKSIZE 512,
  GROUP 3 '/data/oracle/app/oracle/oradata/CLONEDB/redo3.log'  SIZE 10M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/data/oracle/app/oracle/oradata/CLONEDB/system.dbf',
  '/data/oracle/app/oracle/oradata/CLONEDB/user04.dbf',
  '/data/oracle/app/oracle/oradata/CLONEDB/sysaux.dbf',
  '/data/oracle/app/oracle/oradata/CLONEDB/undo.dbf',
  '/data/oracle/app/oracle/oradata/CLONEDB/test1_tmp.dbf',
  '/data/oracle/app/oracle/oradata/CLONEDB/test_corrupt.dbf'
CHARACTER SET UTF8; 

 STEP3: CHANGE DB_NAME PARAMETER IN SPFILE/PFILE.

SQL> sho parameter control

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      /data/oracle/app/oracle/oradat
                                                 a/CLONEDB/control01.ctl, /data
                                                 /oracle/app/oracle/oradata/CLO
                                                 NEDB/control02.ctl
control_management_pack_access       string      DIAGNOSTIC+TUNING

SQL> shut immediate;

ORA-01013: user requested cancel of current operation

SQL> shut abort;

 

ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area  601272320 bytes

Fixed Size                  2230712 bytes

Variable Size             276825672 bytes

Database Buffers          310378496 bytes

Redo Buffers               11837440 bytes

SQL> alter system set db_name=CLONEDBN scope=spfile;
System altered.

STEP4:- NOW REMOVE OR MOVE OLD CONTROL FILES.

 Before removing old controlfile do take backup of all controlfiles.

[oracle@NVMBD1BZY150D00 dbs]$cd

/data/oracle/app/oracle/product/11.2.0/dbhome_1/dbs

[oracle@NVMBD1BZY150D00 CLONEDB]$ mv control01.ctl control01.ctl_bkp

[oracle@NVMBD1BZY150D00 CLONEDB]$ mv control02.ctl control02.ctl_bkp

STEP5: SHUT DOWN THE DATABASE AND STARTUP IN NOMOUNT MODE TO REFLECT DB_NAME PARAMETER CHANGES.

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

[oracle@NVMBD1BZY150D00 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 24 16:34:38 2014

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

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

SQL> startup nomount;

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area  601272320 bytes
Fixed Size                  2230712 bytes
Variable Size             276825672 bytes
Database Buffers          310378496 bytes
Redo Buffers               11837440 bytes

STEP6: NOW EXECUTE NEWLY CREATED CONTROLFILE SCRIPT.

SQL> @control_clonedb.sql

Control file created.

STEP7: OPEN DATABASE WITH RESETLOGS OPTION.

SQL> alter database open resetlogs;

Database altered.

SQL> select database_name from v$database;

DATABASE_NAME

--------------------------------------------------------------------------------
CLONEDBN

METHOD 2: USING NID(DBNEWID UTILITY)

DBNEWID is a database utility that can change the internal database identifier (DBID) and the database name (DBNAME) for an operational database.

 

The DBNEWID utility solves this problem by allowing you to change any of the following:

 

• Only the DBID of a database
• Only the DBNAME of a database
• Both the DBNAME and DBID of a database

I prefer to change both DBNAME and DBID at the same time as a best practice during creation of test environments.

 

Step-1. We will change both db_name to CLONE and dbid belongs to cloned database.

Check db_id and db_name for new environment before operation.

SQL> select dbid,name from v$database;

 

DBID NAME

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

 953825422 CLONEDBN

Step-2. Startup instance with mount state.

export ORACLE_SID=CLONEDB
sqlplus / as sysdba
shutdown immediate;
startup mount;

Step-3. Execute nid command and check the log file “/tmp/nid.log”:

oracle@NVMBD1BZY150D00 dbs]$ nid target=/ dbname=CLONE logfile=/tmp/nid.log
[oracle@NVMBD1BZY150D00 dbs]$ cat /tmp/nid.log

DBNEWID: Release 11.2.0.3.0 - Production on Thu Dec 25 12:07:16 2014

 

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

 

Connected to database CLONEDBN (DBID=953825422)
 
Connected to server version 11.2.0

Control Files in database:

    /data/oracle/app/oracle/oradata/CLONEDB/control01.ctl

    /data/oracle/app/oracle/oradata/CLONEDB/control02.ctl

Changing database ID from 953825422 to 1066065334

Changing database name from CLONEDBN to CLONE

    Control File /data/oracle/app/oracle/oradata/CLONEDB/control01.ctl - modified

    Control File /data/oracle/app/oracle/oradata/CLONEDB/control02.ctl - modified

    Datafile /data/oracle/app/oracle/oradata/CLONEDB/system.db - dbid changed, wrote new name

    Datafile /data/oracle/app/oracle/oradata/CLONEDB/user04.db - dbid changed, wrote new name

    Datafile /data/oracle/app/oracle/oradata/CLONEDB/sysaux.db - dbid changed, wrote new name

    Datafile /data/oracle/app/oracle/oradata/CLONEDB/undo.db - dbid changed, wrote new name

    Datafile /data/oracle/app/oracle/oradata/CLONEDB/test1_tmp.db - dbid changed, wrote new name

    Datafile /data/oracle/app/oracle/oradata/CLONEDB/test_corrupt.db - dbid changed, wrote new name

    Control File /data/oracle/app/oracle/oradata/CLONEDB/control01.ctl - dbid changed, wrote new name

    Control File /data/oracle/app/oracle/oradata/CLONEDB/control02.ctl - dbid changed, wrote new name

    Instance shut down

 

Database name changed to CLONE.

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

Database ID for database CLONE changed to 1066065334.

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.

After DBNEWID completed successful, instance has been also closed automatically.

Step-4. Startup instance with nomount option and change the db_name to CLONE.

Then shutdown and startup mount instance again for activate new db_name. At last, open database with resetlogs option.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  601272320 bytes

Fixed Size                  2230712 bytes

Variable Size             276825672 bytes

Database Buffers          310378496 bytes

Redo Buffers               11837440 bytes

SQL> show parameter db_name

NAME                                 TYPE        VALUE

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

db_name                              string      CLONE

SQL> alter system set db_name=CLONE scope=spfile;



System altered.

SQL> shutdown immediate;



ORA-01507: database not mounted
ORACLE instance shut down.

SQL> startup nomount;



ORACLE instance started.

Total System Global Area 1663012104 bytes
Fixed Size 744712 bytes
Variable Size 805306368 bytes
Database Buffers 855638016 bytes
Redo Buffers 1323008 bytes

SQL> show parameter db_name

 

NAME                                 TYPE        VALUE

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

db_name                              string      CLONE

 

SQL> alter database mount;

Database altered.

SQL> alter database open resetlogs;

Database altered.

Step-5. Control the value of dbid and name of the new database.

SQL> select dbid, name from v$database;

DBID NAME

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

1066065334 CLONE

Step-6. You should create new password file for the new environment if you need

cd $ORACLE_HOME/dbs
orapwd file=orapwCLONE password=clone entries=3

 

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!