Requirement: To clone an existing test database for the application team on the same database server with a planned downtime window for the test database.
Solution: The test database can be cloned by using the traditional cold backup technique, which is as follows -
1. Collect metadata (information about the control file trace, data files, temp files and online redo log files) about the test database using SQL*Plus.
2. Create a pfile for the new database, which will be cloned from the existing test database.
3. Perform a clean shutdown of the test database on the database server.
4. Perform an image copy of the test database files to the corresponding new locations.
5. Startup the new database in NOMOUNT mode.
6. Create the control file of the new database.
7. Open the new database using the RESETLOGS option.
8. Create the tempfile(s) of the new database.
9. Create the spfile of the new database, and bounce the new database.
10. Create the listener entry and TNS entry for the new database, and register the database with the listener.
11. Add the database instance entry to the oratab file.
12. Startup the existing test database on the database server.
Kindly note that the above technique cannot be used in a production environment unless an outage window has been agreed with the customer. In a production environment, we generally use the RMAN Active Duplication technique to clone a database, which is mostly in ARCHIVELOG mode.
Total Duration: 1-2 Hour(s)
Implementation Steps:
1. Collect metadata (information about the control file trace, data files, temp files and online redo log files) about the test database using SQL*Plus by firing the following query in the test database with SYSDBA privileges -
SQL> select name "Database Files" from v$datafile
union all
select name from v$tempfile
union all
select member from v$logfile
;
Database Files
--------------------------------------------------------------------------------
/u02/oradata/FILECLD/datafile/o1_mf_system_994b5h39_.dbf
/u02/oradata/FILECLD/datafile/o1_mf_sysaux_994b5h59_.dbf
/u02/oradata/FILECLD/datafile/o1_mf_undotbs1_994b5h5j_.dbf
/u02/oradata/FILECLD/datafile/o1_mf_users_994b5h67_.dbf
/u02/oradata/FILECLD/datafile/o1_mf_temp_994b8pyx_.tmp
/u02/oradata/FILECLD/onlinelog/o1_mf_3_994b86h3_.log
/u02/oradata/FILECLD/onlinelog/o1_mf_2_994b85mz_.log
/u02/oradata/FILECLD/onlinelog/o1_mf_1_994b84mr_.log
8 rows selected.
Backup the test database control file to trace, as follows -
SQL> alter database backup controlfile to trace as '/tmp/filecld_control.txt';
Database altered.
You may optionally find out whether the test database is running on the pfile, or spfile, as follows -
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfilefilecld.or
a
2. Create a pfile for the new database using vi editor -
oracle@s11-virtualmachine:~$ cp -p $ORACLE_HOME/dbs/initfilecld.ora $ORACLE_HOME/dbs/initfilecld2.ora
In my example, I have cloned a test database called filecld as a new database called filecld2, so I have duplicated the pfile (initfilecld.ora) of the test database (filecld) as initfilecld2.ora, which will be used by the new database (filecld2).
oracle@s11-virtualmachine:~$ vi $ORACLE_HOME/dbs/initfilecld2.ora
I have changed the below parameters to appropriate new values by using the following command:
%s/old_string/new_string/g
In the below example, I have used the following commands:
%s/filecld/filecld2/g
%s/FILECLD/FILECLD2/g
Old values:
filecld.__db_cache_size=520093696
filecld.__java_pool_size=16777216
filecld.__large_pool_size=33554432
filecld.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
filecld.__pga_aggregate_target=520093696
filecld.__sga_target=771751936
filecld.__shared_io_pool_size=0
filecld.__shared_pool_size=184549376
filecld.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/filecld/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u02/oradata/FILECLD/controlfile/o1_mf_994b822s_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u02/oradata'
*.db_domain='virtualmachine'
*.db_name='filecld'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=filecldXDB)'
*.memory_target=1286602752
*.nls_territory='INDIA'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
New Values:
filecld2.__db_cache_size=520093696
filecld2.__java_pool_size=16777216
filecld2.__large_pool_size=33554432
filecld2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
filecld2.__pga_aggregate_target=520093696
filecld2.__sga_target=771751936
filecld2.__shared_io_pool_size=0
filecld2.__shared_pool_size=184549376
filecld2.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/filecld2/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u02/oradata/FILECLD2/controlfile/o1_mf_994b822s_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u02/oradata'
*.db_domain='virtualmachine'
*.db_name='filecld2'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=filecld2XDB)'
*.memory_target=1286602752
*.nls_territory='INDIA'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
Save the changes and exit from the vi editor.
Create the audit_dump_dest folder (adump) on the server to store the audit files of the new database -
oracle@s11-virtualmachine:~$ mkdir -p /u01/app/oracle/admin/filecld2/adump
3. Perform a clean shutdown of the test database on the database server -
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
FILECLD READ WRITE
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
4. Perform an image copy of the test database files to the corresponding new locations, as follows -
Before starting the image copy process, create the destination folders to store the test database file image copies -
oracle@s11-virtualmachine:~$ mkdir -p /u02/oradata/FILECLD2/controlfile
oracle@s11-virtualmachine:~$ mkdir -p /u02/oradata/FILECLD2/datafile
oracle@s11-virtualmachine:~$ mkdir -p /u02/oradata/FILECLD2/onlinelog
Note: We are creating the control file directory, so that we can create a new control file after completing the image copy process.
Next, perform an image copy of the test database files to the corresponding new locations using the nohup and cp -p commands:
nohup cp -p /u02/oradata/FILECLD/datafile/o1_mf_system_994b5h39_.dbf /u02/oradata/FILECLD2/datafile/o1_mf_system_994b5h39_.dbf &
nohup cp -p /u02/oradata/FILECLD/datafile/o1_mf_sysaux_994b5h59_.dbf /u02/oradata/FILECLD2/datafile/o1_mf_sysaux_994b5h59_.dbf &
nohup cp -p /u02/oradata/FILECLD/datafile/o1_mf_undotbs1_994b5h5j_.dbf /u02/oradata/FILECLD2/datafile/o1_mf_undotbs1_994b5h5j_.dbf &
nohup cp -p /u02/oradata/FILECLD/datafile/o1_mf_users_994b5h67_.dbf /u02/oradata/FILECLD2/datafile/o1_mf_users_994b5h67_.dbf &
nohup cp -p /u02/oradata/FILECLD/datafile/o1_mf_temp_994b8pyx_.tmp /u02/oradata/FILECLD2/datafile/o1_mf_temp_994b8pyx_.tmp &
nohup cp -p /u02/oradata/FILECLD/onlinelog/o1_mf_3_994b86h3_.log /u02/oradata/FILECLD2/onlinelog/o1_mf_3_994b86h3_.log &
nohup cp -p /u02/oradata/FILECLD/onlinelog/o1_mf_2_994b85mz_.log /u02/oradata/FILECLD2/onlinelog/o1_mf_2_994b85mz_.log &
nohup cp -p /u02/oradata/FILECLD/onlinelog/o1_mf_1_994b84mr_.log /u02/oradata/FILECLD2/onlinelog/o1_mf_1_994b84mr_.log &
To check the jobs currently running, use the jobs -l command:
oracle@s11-virtualmachine:~$ jobs -l
[1]- 2427 Running nohup cp -p /u02/oradata/FILECLD/datafile/o1_mf_system_994b5h39_.dbf /u02/oradata/FILECLD2/datafile/o1_mf_system_994b5h39_.dbf &
[2]+ 2429 Running nohup cp -p /u02/oradata/FILECLD/datafile/o1_mf_sysaux_994b5h59_.dbf /u02/oradata/FILECLD2/datafile/o1_mf_sysaux_994b5h59_.dbf &
5. Startup the new database in NOMOUNT mode, as follows -
oracle@s11-virtualmachine:~$ export ORACLE_SID=filecld2
oracle@s11-virtualmachine:~$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Jul 27 22:05:41 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1286066176 bytes
Fixed Size 2250528 bytes
Variable Size 805308640 bytes
Database Buffers 469762048 bytes
Redo Buffers 8744960 bytes
6. Create the control file of the new database using the control file trace of the existing test database -
Note: The control file of the new database can be created by using either the RESETLOGS option (used when a point-in-time consistent copy of the online redo logs is unavailable), or the NORESETLOGS option (used when a point-in-time consistent copy of the online redo logs is available). However, we cannot use the NORESETLOGS option when we use the SET clause, and so in my example, I have used the RESETLOGS option to create the new control file.
SQL>CREATE CONTROLFILE SET DATABASE "FILECLD2" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u02/oradata/FILECLD2/onlinelog/o1_mf_1_994b84mr_.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u02/oradata/FILECLD2/onlinelog/o1_mf_2_994b85mz_.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u02/oradata/FILECLD2/onlinelog/o1_mf_3_994b86h3_.log' SIZE 50M BLOCKSIZE 512
DATAFILE
'/u02/oradata/FILECLD2/datafile/o1_mf_system_994b5h39_.dbf',
'/u02/oradata/FILECLD2/datafile/o1_mf_sysaux_994b5h59_.dbf',
'/u02/oradata/FILECLD2/datafile/o1_mf_undotbs1_994b5h5j_.dbf',
'/u02/oradata/FILECLD2/datafile/o1_mf_users_994b5h67_.dbf'
CHARACTER SET AL32UTF8
;
Control file created.
7. Open the new database using the RESETLOGS option -
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
FILECLD2 READ WRITE
8. Create the tempfile(s) of the new database, as follows -
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/FILECLD2/datafile/o1_mf_temp_994b8pyx_.tmp'
SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Tablespace altered.
9. Create the spfile of the new database, and bounce the new database -
SQL> create spfile from pfile;
File created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 1286066176 bytes
Fixed Size 2250528 bytes
Variable Size 805308640 bytes
Database Buffers 469762048 bytes
Redo Buffers 8744960 bytes
Database mounted.
Database opened.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
FILECLD2 READ WRITE
10. Create the listener entry and the TNS entry for the new database, and register the new database with the existing listener -
oracle@s11-virtualmachine:~$ vi $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = filecld2.s11-virtualmachine)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = filecld2)
)
)
Save the changes made to listener.ora and exit.
oracle@s11-virtualmachine:~$ vi $ORACLE_HOME/network/admin/tnsnames.ora
FILECLD2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = s11-virtualmachine)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = filecld2.virtualmachine)
)
)
Save the changes made to tnsnames.ora and exit.
Register the new database with an existing running listener -
SQL> alter system register;
System altered.
Check the status of the listener to find out whether the new database has got registered with it successfully -
oracle@s11-virtualmachine:~$ lsnrctl status LISTENER
LSNRCTL for Solaris: Version 11.2.0.4.0 - Production on 27-JUL-2014 23:04:47
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=s11-virtualmachine)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Solaris: Version 11.2.0.4.0 - Production
Start Date 27-JUL-2014 23:04:36
Uptime 0 days 0 hr. 0 min. 11 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/s11-virtualmachine/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=s11-virtualmachine)(PORT=1521)))
Services Summary...
Service "filecld2.s11-virtualmachine" has 1 instance(s).
Instance "filecld2", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
Note: If the database does not get registered with the listener by using the above technique, then you can use the lsnrctl reload <LISTENER-NAME> command to restart that listener.
11. Add the database instance entry to the oratab file, as follows -
vi /etc/oratab (for Linux)
OR
vi /var/opt/oracle/oratab (for Solaris)
Entry to be added -
filecld2:/u01/app/oracle/product/11.2.0/dbhome_1:N
Save the changes and exit the oratab file.
12. Startup the existing test database on the database server, as follows -
oracle@s11-virtualmachine:~$ . oraenv
ORACLE_SID = [filecld2] ? filecld
The Oracle base remains unchanged with value /u01/app/oracle
oracle@s11-virtualmachine:~$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Jul 27 23:47:34 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup;
ORACLE instance started.
Total System Global Area 1286066176 bytes
Fixed Size 2250528 bytes
Variable Size 805308640 bytes
Database Buffers 469762048 bytes
Redo Buffers 8744960 bytes
Database mounted.
Database opened.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
FILECLD READ WRITE
Congratulations! You have successfully cloned an Oracle 11gR2 database from an existing Oracle 11gR2 database using the traditional cold backup technique.
Hope you had a good time while reading the article! :)
Solution: The test database can be cloned by using the traditional cold backup technique, which is as follows -
1. Collect metadata (information about the control file trace, data files, temp files and online redo log files) about the test database using SQL*Plus.
2. Create a pfile for the new database, which will be cloned from the existing test database.
3. Perform a clean shutdown of the test database on the database server.
4. Perform an image copy of the test database files to the corresponding new locations.
5. Startup the new database in NOMOUNT mode.
6. Create the control file of the new database.
7. Open the new database using the RESETLOGS option.
8. Create the tempfile(s) of the new database.
9. Create the spfile of the new database, and bounce the new database.
10. Create the listener entry and TNS entry for the new database, and register the database with the listener.
11. Add the database instance entry to the oratab file.
12. Startup the existing test database on the database server.
Kindly note that the above technique cannot be used in a production environment unless an outage window has been agreed with the customer. In a production environment, we generally use the RMAN Active Duplication technique to clone a database, which is mostly in ARCHIVELOG mode.
Total Duration: 1-2 Hour(s)
Implementation Steps:
1. Collect metadata (information about the control file trace, data files, temp files and online redo log files) about the test database using SQL*Plus by firing the following query in the test database with SYSDBA privileges -
SQL> select name "Database Files" from v$datafile
union all
select name from v$tempfile
union all
select member from v$logfile
;
Database Files
--------------------------------------------------------------------------------
/u02/oradata/FILECLD/datafile/o1_mf_system_994b5h39_.dbf
/u02/oradata/FILECLD/datafile/o1_mf_sysaux_994b5h59_.dbf
/u02/oradata/FILECLD/datafile/o1_mf_undotbs1_994b5h5j_.dbf
/u02/oradata/FILECLD/datafile/o1_mf_users_994b5h67_.dbf
/u02/oradata/FILECLD/datafile/o1_mf_temp_994b8pyx_.tmp
/u02/oradata/FILECLD/onlinelog/o1_mf_3_994b86h3_.log
/u02/oradata/FILECLD/onlinelog/o1_mf_2_994b85mz_.log
/u02/oradata/FILECLD/onlinelog/o1_mf_1_994b84mr_.log
8 rows selected.
Backup the test database control file to trace, as follows -
SQL> alter database backup controlfile to trace as '/tmp/filecld_control.txt';
Database altered.
You may optionally find out whether the test database is running on the pfile, or spfile, as follows -
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfilefilecld.or
a
2. Create a pfile for the new database using vi editor -
oracle@s11-virtualmachine:~$ cp -p $ORACLE_HOME/dbs/initfilecld.ora $ORACLE_HOME/dbs/initfilecld2.ora
In my example, I have cloned a test database called filecld as a new database called filecld2, so I have duplicated the pfile (initfilecld.ora) of the test database (filecld) as initfilecld2.ora, which will be used by the new database (filecld2).
oracle@s11-virtualmachine:~$ vi $ORACLE_HOME/dbs/initfilecld2.ora
I have changed the below parameters to appropriate new values by using the following command:
%s/old_string/new_string/g
In the below example, I have used the following commands:
%s/filecld/filecld2/g
%s/FILECLD/FILECLD2/g
Old values:
filecld.__db_cache_size=520093696
filecld.__java_pool_size=16777216
filecld.__large_pool_size=33554432
filecld.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
filecld.__pga_aggregate_target=520093696
filecld.__sga_target=771751936
filecld.__shared_io_pool_size=0
filecld.__shared_pool_size=184549376
filecld.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/filecld/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u02/oradata/FILECLD/controlfile/o1_mf_994b822s_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u02/oradata'
*.db_domain='virtualmachine'
*.db_name='filecld'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=filecldXDB)'
*.memory_target=1286602752
*.nls_territory='INDIA'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
New Values:
filecld2.__db_cache_size=520093696
filecld2.__java_pool_size=16777216
filecld2.__large_pool_size=33554432
filecld2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
filecld2.__pga_aggregate_target=520093696
filecld2.__sga_target=771751936
filecld2.__shared_io_pool_size=0
filecld2.__shared_pool_size=184549376
filecld2.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/filecld2/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u02/oradata/FILECLD2/controlfile/o1_mf_994b822s_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u02/oradata'
*.db_domain='virtualmachine'
*.db_name='filecld2'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=filecld2XDB)'
*.memory_target=1286602752
*.nls_territory='INDIA'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
Save the changes and exit from the vi editor.
Create the audit_dump_dest folder (adump) on the server to store the audit files of the new database -
oracle@s11-virtualmachine:~$ mkdir -p /u01/app/oracle/admin/filecld2/adump
3. Perform a clean shutdown of the test database on the database server -
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
FILECLD READ WRITE
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
4. Perform an image copy of the test database files to the corresponding new locations, as follows -
Before starting the image copy process, create the destination folders to store the test database file image copies -
oracle@s11-virtualmachine:~$ mkdir -p /u02/oradata/FILECLD2/controlfile
oracle@s11-virtualmachine:~$ mkdir -p /u02/oradata/FILECLD2/datafile
oracle@s11-virtualmachine:~$ mkdir -p /u02/oradata/FILECLD2/onlinelog
Note: We are creating the control file directory, so that we can create a new control file after completing the image copy process.
Next, perform an image copy of the test database files to the corresponding new locations using the nohup and cp -p commands:
nohup cp -p /u02/oradata/FILECLD/datafile/o1_mf_system_994b5h39_.dbf /u02/oradata/FILECLD2/datafile/o1_mf_system_994b5h39_.dbf &
nohup cp -p /u02/oradata/FILECLD/datafile/o1_mf_sysaux_994b5h59_.dbf /u02/oradata/FILECLD2/datafile/o1_mf_sysaux_994b5h59_.dbf &
nohup cp -p /u02/oradata/FILECLD/datafile/o1_mf_undotbs1_994b5h5j_.dbf /u02/oradata/FILECLD2/datafile/o1_mf_undotbs1_994b5h5j_.dbf &
nohup cp -p /u02/oradata/FILECLD/datafile/o1_mf_users_994b5h67_.dbf /u02/oradata/FILECLD2/datafile/o1_mf_users_994b5h67_.dbf &
nohup cp -p /u02/oradata/FILECLD/datafile/o1_mf_temp_994b8pyx_.tmp /u02/oradata/FILECLD2/datafile/o1_mf_temp_994b8pyx_.tmp &
nohup cp -p /u02/oradata/FILECLD/onlinelog/o1_mf_3_994b86h3_.log /u02/oradata/FILECLD2/onlinelog/o1_mf_3_994b86h3_.log &
nohup cp -p /u02/oradata/FILECLD/onlinelog/o1_mf_2_994b85mz_.log /u02/oradata/FILECLD2/onlinelog/o1_mf_2_994b85mz_.log &
nohup cp -p /u02/oradata/FILECLD/onlinelog/o1_mf_1_994b84mr_.log /u02/oradata/FILECLD2/onlinelog/o1_mf_1_994b84mr_.log &
To check the jobs currently running, use the jobs -l command:
oracle@s11-virtualmachine:~$ jobs -l
[1]- 2427 Running nohup cp -p /u02/oradata/FILECLD/datafile/o1_mf_system_994b5h39_.dbf /u02/oradata/FILECLD2/datafile/o1_mf_system_994b5h39_.dbf &
[2]+ 2429 Running nohup cp -p /u02/oradata/FILECLD/datafile/o1_mf_sysaux_994b5h59_.dbf /u02/oradata/FILECLD2/datafile/o1_mf_sysaux_994b5h59_.dbf &
5. Startup the new database in NOMOUNT mode, as follows -
oracle@s11-virtualmachine:~$ export ORACLE_SID=filecld2
oracle@s11-virtualmachine:~$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Jul 27 22:05:41 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1286066176 bytes
Fixed Size 2250528 bytes
Variable Size 805308640 bytes
Database Buffers 469762048 bytes
Redo Buffers 8744960 bytes
6. Create the control file of the new database using the control file trace of the existing test database -
Note: The control file of the new database can be created by using either the RESETLOGS option (used when a point-in-time consistent copy of the online redo logs is unavailable), or the NORESETLOGS option (used when a point-in-time consistent copy of the online redo logs is available). However, we cannot use the NORESETLOGS option when we use the SET clause, and so in my example, I have used the RESETLOGS option to create the new control file.
SQL>CREATE CONTROLFILE SET DATABASE "FILECLD2" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u02/oradata/FILECLD2/onlinelog/o1_mf_1_994b84mr_.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u02/oradata/FILECLD2/onlinelog/o1_mf_2_994b85mz_.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u02/oradata/FILECLD2/onlinelog/o1_mf_3_994b86h3_.log' SIZE 50M BLOCKSIZE 512
DATAFILE
'/u02/oradata/FILECLD2/datafile/o1_mf_system_994b5h39_.dbf',
'/u02/oradata/FILECLD2/datafile/o1_mf_sysaux_994b5h59_.dbf',
'/u02/oradata/FILECLD2/datafile/o1_mf_undotbs1_994b5h5j_.dbf',
'/u02/oradata/FILECLD2/datafile/o1_mf_users_994b5h67_.dbf'
CHARACTER SET AL32UTF8
;
Control file created.
7. Open the new database using the RESETLOGS option -
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
FILECLD2 READ WRITE
8. Create the tempfile(s) of the new database, as follows -
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/FILECLD2/datafile/o1_mf_temp_994b8pyx_.tmp'
SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Tablespace altered.
9. Create the spfile of the new database, and bounce the new database -
SQL> create spfile from pfile;
File created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 1286066176 bytes
Fixed Size 2250528 bytes
Variable Size 805308640 bytes
Database Buffers 469762048 bytes
Redo Buffers 8744960 bytes
Database mounted.
Database opened.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
FILECLD2 READ WRITE
10. Create the listener entry and the TNS entry for the new database, and register the new database with the existing listener -
oracle@s11-virtualmachine:~$ vi $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = filecld2.s11-virtualmachine)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = filecld2)
)
)
Save the changes made to listener.ora and exit.
oracle@s11-virtualmachine:~$ vi $ORACLE_HOME/network/admin/tnsnames.ora
FILECLD2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = s11-virtualmachine)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = filecld2.virtualmachine)
)
)
Save the changes made to tnsnames.ora and exit.
Register the new database with an existing running listener -
SQL> alter system register;
System altered.
Check the status of the listener to find out whether the new database has got registered with it successfully -
oracle@s11-virtualmachine:~$ lsnrctl status LISTENER
LSNRCTL for Solaris: Version 11.2.0.4.0 - Production on 27-JUL-2014 23:04:47
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=s11-virtualmachine)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Solaris: Version 11.2.0.4.0 - Production
Start Date 27-JUL-2014 23:04:36
Uptime 0 days 0 hr. 0 min. 11 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/s11-virtualmachine/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=s11-virtualmachine)(PORT=1521)))
Services Summary...
Service "filecld2.s11-virtualmachine" has 1 instance(s).
Instance "filecld2", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
Note: If the database does not get registered with the listener by using the above technique, then you can use the lsnrctl reload <LISTENER-NAME> command to restart that listener.
11. Add the database instance entry to the oratab file, as follows -
vi /etc/oratab (for Linux)
OR
vi /var/opt/oracle/oratab (for Solaris)
Entry to be added -
filecld2:/u01/app/oracle/product/11.2.0/dbhome_1:N
Save the changes and exit the oratab file.
12. Startup the existing test database on the database server, as follows -
oracle@s11-virtualmachine:~$ . oraenv
ORACLE_SID = [filecld2] ? filecld
The Oracle base remains unchanged with value /u01/app/oracle
oracle@s11-virtualmachine:~$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Jul 27 23:47:34 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup;
ORACLE instance started.
Total System Global Area 1286066176 bytes
Fixed Size 2250528 bytes
Variable Size 805308640 bytes
Database Buffers 469762048 bytes
Redo Buffers 8744960 bytes
Database mounted.
Database opened.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
FILECLD READ WRITE
Congratulations! You have successfully cloned an Oracle 11gR2 database from an existing Oracle 11gR2 database using the traditional cold backup technique.
Hope you had a good time while reading the article! :)
Hello Mr. Sunkersett, I am a student in the United States learning about database administration. Could you tell me about your day-to-day work and how the job market is in India?
ReplyDeleteOkay, day-to-day work is very routine and mostly predefined unless there are critical or high priority incidents in the data center. Changes are interesting activities for a DBA in which you perform 'surgeries' on the databases/systems. Example of a major change would be upgrading or migrating a database within a planned business outage window. Changes are almost always tested on non-production systems before they are implemented on the production systems. DBAs also deal a lot with My Oracle Support on a day-to-day basis to get advice on issues DBAs are unable to investigate/resolve. A DBA spends most of his/her day-to-day time in ensuring that databases fulfil the CIA - confidentiality, integrity, availability - criteria of the organisation. DBAs work closely in concordance with senior managers, architects, developers, disaster recovery analysts, security consultants, and other support professionals such as system/application/network/storage administrators to ensure that the data in a data center is available to the right people at the right time and for the right reason.
ReplyDeleteAs far as my experience goes, the job market in India is mostly for offshore support! The data centers are located in far more developed countries and the support is almost always offshore. Hence, you may miss the opportunity of visiting a data center if you work as a DBA in India.
Kindly let me know if you have any more questions. We could connect on LinkedIn if required.
Very nice post, keep it up and share more info with us EBTS
ReplyDelete