Rename Database while Cloning it.

As I recently pointed out in a thread on "Partial Database Cloning" it is perfectly possible to rename a database while cloning it.

Here, I clone a database "ORT24FS" to another name "DUPDB" on the same server, without shutting down ORT24FS. Also, I rename the copied database to DUPDB even *before* I issue the RECOVER database command. Furtheremore, DUPDB is able to use ORT24FS's ArchiveLog "as is".

First I copy my database files from the ORT24FS database directory to the DUPDB database directory :

ora10204>env |grep SID
ORACLE_SID=ORT24FS
ora10204>sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Thu May 14 23:04:08 2009

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


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> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/oracle_fs/Databases/ORT24FS/users01.dbf
/oracle_fs/Databases/ORT24FS/sysaux01.dbf
/oracle_fs/Databases/ORT24FS/undotbs01.dbf
/oracle_fs/Databases/ORT24FS/system01.dbf
/oracle_fs/Databases/ORT24FS/example01.dbf

SQL> alter database begin backup;

Database altered.

SQL> !cp -rp /oracle_fs/Databases/ORT24FS/* /oracle_fs/Databases/DUPDB/


SQL>

SQL> alter database end backup;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL>

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 

I then generate a controlfile trace that I will be using for the CREATE CONTROLFILE to rename the database consisting copied files in the "DUPDB" directory.

ora10204>sqlplus

SQL*Plus: Release 10.2.0.4.0 - Production on Thu May 14 23:09:52 2009

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

Enter user-name: / as sysdba

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> alter database backup controlfile to trace;

Database altered.

SQL> exit 

Next, I edit initDUPDB.ora. Note how I set db_name to DUPDB but I use the same log_archive_dest_1 and log_archive_format as I have in ORT24FS. This allows me to use the ORT24FS archivelogs "as is" without renaming or relocating them :

ora10204>vi $ORACLE_HOME/dbs/initDUPDB.ora
DUPDB.__db_cache_size=616562688
DUPDB.__java_pool_size=4194304
DUPDB.__large_pool_size=4194304
DUPDB.__shared_pool_size=205520896
DUPDB.__streams_pool_size=0
*.audit_file_dest='/oracle_fs/ora10204/admin/DUPDB/adump'
*.background_dump_dest='/oracle_fs/ora10204/admin/DUPDB/bdump'
*.compatible='10.2.0.4.0'
*.control_files='/oracle_fs/Databases/DUPDB/control01.ctl','/oracle_fs/Databases/DUPDB/control02.ctl','/oracle_fs/Databases/DUPDB/control03.ctl'
*.core_dump_dest='/oracle_fs/ora10204/admin/DUPDB/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_name='DUPDB'
*.db_recovery_file_dest='/oracle_fs/FRAs/DUPDB'
*.db_recovery_file_dest_size=17179869184
*.dbwr_io_slaves=0
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DUPDBXDB)'
*.filesystemio_options='NONE'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=/oracle_fs/ArchiveLogs/ORT24FS'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=179306496
*.processes=150
*.recovery_parallelism=4
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=838860800
*.sga_target=838860800
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle_fs/ora10204/admin/DUPDB/udump'
*.db_create_file_dest='/oracle_fs/Databases/DUPDB'   

I now copy the controlfile trace to DUPDB and edit it to use it to SET the database name to DUPDB for all the database files in the DUPDB directory. I must use RESETLOGS when using SET. This also means that Oracle will ignore online redo logs present in the directory and that I will have to OPEN RESETLOGS later.

ora10204>cd $ORACLE_BASE/admin/DUPDB
ora10204>cp ../ORT24FS/udump/ort24fs_ora_4432.trc create_ctrl_file.sql
ora10204>vi create_ctrl_file.sql


STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "DUPDB" RESETLOGS  ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oracle_fs/Databases/DUPDB/redo01.dbf'  SIZE 50M,
GROUP 2 '/oracle_fs/Databases/DUPDB/redo02.dbf'  SIZE 50M,
GROUP 3 '/oracle_fs/Databases/DUPDB/redo03.dbf'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/oracle_fs/Databases/DUPDB/system01.dbf',
'/oracle_fs/Databases/DUPDB/undotbs01.dbf',
'/oracle_fs/Databases/DUPDB/sysaux01.dbf',
'/oracle_fs/Databases/DUPDB/users01.dbf',
'/oracle_fs/Databases/DUPDB/example01.dbf'
CHARACTER SET WE8ISO8859P1
; 

I now set my ORACLE_SID to DUPDB and run the create script. Since my "cp /oracle_fs/Databases/ORT24FS/*" command had also copied the controlfiles, I have to remove them and run the CREATE CONTROLFILE command. Note that, at this stage, the database files in DUPDB are from a Hot Backup and are "not consistent" yet.

ora10204>ORACLE_SID=DUPDB;export ORACLE_SID
ora10204>sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Thu May 14 23:13:59 2009

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

Connected to an idle instance.

SQL> spool create_DUPDB_ctrl_file
SQL> @create_ctrl_file
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size                  2087672 bytes
Variable Size             213910792 bytes
Database Buffers          616562688 bytes
Redo Buffers                6299648 bytes
CREATE CONTROLFILE SET DATABASE "DUPDB" RESETLOGS  ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-00200: control file could not be created
ORA-00202: control file: '/oracle_fs/Databases/DUPDB/control01.ctl'
ORA-27038: created file already exists
Additional information: 1


SQL> !rm /oracle_fs/Databases/DUPDB/*.ctl

SQL> shutdown
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> set echo on
SQL> @create_ctrl_file
SQL> STARTUP NOMOUNT
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size                  2087672 bytes
Variable Size             213910792 bytes
Database Buffers          616562688 bytes
Redo Buffers                6299648 bytes
SQL> CREATE CONTROLFILE SET DATABASE "DUPDB" RESETLOGS  ARCHIVELOG
2      MAXLOGFILES 16
3      MAXLOGMEMBERS 3
4      MAXDATAFILES 100
5      MAXINSTANCES 8
6      MAXLOGHISTORY 292
7  LOGFILE
8    GROUP 1 '/oracle_fs/Databases/DUPDB/redo01.dbf'  SIZE 50M,
9    GROUP 2 '/oracle_fs/Databases/DUPDB/redo02.dbf'  SIZE 50M,
10    GROUP 3 '/oracle_fs/Databases/DUPDB/redo03.dbf'  SIZE 50M
11  -- STANDBY LOGFILE
12  DATAFILE
13    '/oracle_fs/Databases/DUPDB/system01.dbf',
14    '/oracle_fs/Databases/DUPDB/undotbs01.dbf',
15    '/oracle_fs/Databases/DUPDB/sysaux01.dbf',
16    '/oracle_fs/Databases/DUPDB/users01.dbf',
17    '/oracle_fs/Databases/DUPDB/example01.dbf'
18  CHARACTER SET WE8ISO8859P1
19  ;

Control file created.

SQL> 

I am now ready to issue a RECOVER DATABASE command for database DUPDB which datafiles are in the DUPDB directory. I use the ORT24FS ArchiveLogs to recover the database.

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 711843 generated at 05/14/2009 23:04:19 needed for thread 1
ORA-00289: suggestion : /oracle_fs/ArchiveLogs/ORT24FS/1_8_685060711.dbf
ORA-00280: change 711843 for thread 1 is in sequence #8


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 711924 generated at 05/14/2009 23:06:57 needed for thread 1
ORA-00289: suggestion : /oracle_fs/ArchiveLogs/ORT24FS/1_9_685060711.dbf
ORA-00280: change 711924 for thread 1 is in sequence #9
ORA-00278: log file '/oracle_fs/ArchiveLogs/ORT24FS/1_8_685060711.dbf' no
longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL> alter database open resetlogs;

Database altered.

SQL> 

I have RECOVERed DUPDB to a consistent state. Let me verify it :

SQL> select  * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION           STARTUP_T STATUS       PAR    THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
          1 DUPDB
linux64
10.2.0.4.0        14-MAY-09 OPEN         NO           1 STARTED
ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL    NO


SQL> select * from v$database;

  DBID NAME      CREATED   RESETLOGS_CHANGE# RESETLOGS
---------- --------- --------- ----------------- ---------
PRIOR_RESETLOGS_CHANGE# PRIOR_RES LOG_MODE     CHECKPOINT_CHANGE#
----------------------- --------- ------------ ------------------
ARCHIVE_CHANGE# CONTROL CONTROLFI CONTROLFILE_SEQUENCE# CONTROLFILE_CHANGE#
--------------- ------- --------- --------------------- -------------------
CONTROLFI OPEN_RESETL VERSION_T OPEN_MODE  PROTECTION_MODE
--------- ----------- --------- ---------- --------------------
PROTECTION_LEVEL     REMOTE_A ACTIVATION# SWITCHOVER# DATABASE_ROLE
-------------------- -------- ----------- ----------- ----------------
ARCHIVELOG_CHANGE# ARCHIVEL SWITCHOVER_STATUS    DATAGUAR GUARD_S SUPPLEME SUP
------------------ -------- -------------------- -------- ------- -------- ---
SUP FOR PLATFORM_ID
--- --- -----------
PLATFORM_NAME
--------------------------------------------------------------------------------
RECOVERY_TARGET_INCARNATION# LAST_OPEN_INCARNATION# CURRENT_SCN
---------------------------- ---------------------- -----------
FLASHBACK_ON       SUP SUP DB_UNIQUE_NAME
------------------ --- --- ------------------------------
STANDBY_BECAME_PRIMARY_SCN FS_FAILOVER_STATUS    FS_FAILOVER_CURRENT_TARGET
-------------------------- --------------------- ------------------------------
FS_FAILOVER_THRESHOLD FS_FAIL
--------------------- -------
FS_FAILOVER_OBSERVER_HOST
--------------------------------------------------------------------------------
4163910544 DUPDB     14-MAY-09            711925 14-MAY-09
             659189 24-APR-09 ARCHIVELOG               711926
          0 CURRENT 14-MAY-09                  1796              712047
14-MAY-09 NOT ALLOWED 14-MAY-09 READ WRITE MAXIMUM PERFORMANCE
MAXIMUM PERFORMANCE  ENABLED    412086823   412086823 PRIMARY
        711925 DISABLED SESSIONS ACTIVE      DISABLED NONE    NO       NO
NO  NO           13
Linux x86 64-bit
                       2                      2      712118
NO                 NO  NO  DUPDB
                     0 DISABLED
                0



SQL>
SQL> select * from v$tempfile;

no rows selected

SQL> alter tablespace temp add tempfile '/oracle_fs/Databases/DUPDB/temp.dbf' size 100M;

Tablespace altered.

SQL> 

At this stage, both Databases have the same DBID.

ora10204>ORACLE_SID=DUPDB;export ORACLE_SID
ora10204>sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Thu May 14 23:46:25 2009

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


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> select dbid, name from v$database;

   DBID NAME
---------- ---------
4163910544 DUPDB

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ora10204>ORACLE_SID=ORT24FS;export ORACLE_SID
ora10204>sqlplus

SQL*Plus: Release 10.2.0.4.0 - Production on Thu May 14 23:46:40 2009

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

Enter user-name: / as sysdba

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> select dbid, name from v$database;

   DBID NAME
---------- ---------
4163910544 ORT24FS

SQL>  

I can use the DBNEWID "nid" utility to change the DBID for "DUPDB" anytime now.

Note : My thanks to Maggie Wu who showed me, many years ago, that I could RENAME a cloned database even before I RECOVERed it.

.
.
.

Incorrectly using AUTOTRACE and EXPLAIN PLAN

Occassionally I see questions asking for help in tuning where the originator has used by AUTOTRACE and EXPLAIN PLAN together.
This can cause confusion.

Here, I pretend to be a novice using AUTOTRACE and EXPLAIN PLAN together :

SQL> set autotrace on
SQL> explain plan for select owner, created from test_query where owner = 'HEMANT';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1390056125

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |  2109 | 29526 |    61   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_QUERY       |  2109 | 29526 |    61   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST_QUERY_NDX_1 |  2109 |       |     5   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 2 - access("OWNER"='HEMANT')

14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2137789089

---------------------------------------------------------------------------------------------
| Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |         |  8168 | 16336 |    29   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY |       |       |            |          |
---------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
       14  recursive calls
       12  db block gets
       35  consistent gets
        0  physical reads
        0  redo size
     1482  bytes sent via SQL*Net to client
      492  bytes received via SQL*Net from client
        2  SQL*Net roundtrips to/from client
        1  sorts (memory)
        0  sorts (disk)
       14  rows processed

SQL> 

I got TWO Explain Plan listings and one set of Statistics. Which is which ?
The first Explain Plan listing is, correctly, for the SQL that I am testing ("select ... from test_query ...."). The second Explain Plan (with the Plan Hash Value of '2137789089') is for the execution of dbms_xplan.display and this listing is generated because I had set AUTOTRACE ON ! And it shows a CARD (expected number of rows) of 8,168 rows. That is very high and difficult to explain ? How does it relate to the CARD of 2,109 for the test query ?!
Can you guess which operation the statistics are for ?

What I should have been doing is this in TWO Parts.

The first part is for the Explain Plan itself :

SQL> set autotrace off
SQL> explain plan for select owner, created from test_query where owner = 'HEMANT';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1390056125

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |  2109 | 29526 |    61   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_QUERY       |  2109 | 29526 |    61   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST_QUERY_NDX_1 |  2109 |       |     5   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - access("OWNER"='HEMANT')

14 rows selected.

SQL> 

This listing is quite clear now. I have the Explain Plan for my test query.


The second part is to get an AUTOTRACE. Here I use AUTOTRACE TRACEONLY STATISTICS. Thus, I get *only* the statistics !

SQL> set autotrace traceonly statistics
SQL> select owner, created from test_query where owner = 'HEMANT';


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        667  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

SQL>  

These show me that the query actually fetched 4 rows, without having to view the output.

If the output had been 2,109 rows, I'd have to allow that many rows to scroll across my screen before I could see the statistics. Now, with AUTOTRACE TRACEONLY I can allow the query to execute and fetch the rows but SQLPlus will "discard" them and not show them on the screen. Database server side processing remains the same, as if the rows were displayed on-screen.
Similarly, with the STATISTICS keyword appended, I ask Oracle to only present the Statistics and NOT present the Explain Plan. I have already seen the Explain Plan and do not need to see it here again. (In my test above, the Plan has only 3 steps, but the query could have been a complex one with more than a few dozen steps !).
.
.
.

Ever wonder "what if this database contains my data ?"

My forums posting 'Ever wonder "what if this database contains my data ?"' has also been been repeated by APC on his oraclecommunity.net blog.

I do hope that there are more responses and a better discussion on this.
.
.
.