Create Clone database
Before we create the clone database we need prepare some stuff ("create controlfile" command, ini.ora, a set of rename datafile commands) :
•Prepare cf.sql, on the TM: ALTER DATABASE BACKUP
CONTROLFILE TO TRACE + edit trace file
•Prepare init$KLON.ora
•Prepare rename.sql:
SELECT 'EXECUTE dbms_dnfs.clonedb_renamefile(
'||''''||name||''''||','||''''||REPLACE(REPLACE(REPLACE(name,'.','_'),
'TM',‘KLON'),'+DATA','+SPARSE')||''''||');'
from v$datafiles;
The stuff related to Test Master database is colored by red.
The stuff related to new clone database is colored by green.
Clone database have two parts: private - colored by green and shared between clones - colored by red :
•Clone’s private files
–parameter file (spfile or pfile), passwordfile
–Control file
–Redo log files
–Temporary tablespace files
•TM’s datafiles
1. Prepare controlfile
At 1st step you need to connect to source database and prepare text file with "create controlfile" command. Then you need to edit this file and change database name to new.
SQL>
ALTER DATABASE
BACKUP CONTROLFILE TO TRACE;
SQL>
SELECT value
FROM v$diag_info
WHERE name = 'Default Trace File';
This is example of out file (K means klone, new database name is k):
$
cat cf.sql
CREATE CONTROLFILE REUSE SET
DATABASE k
RESETLOGS ARCHIVELOG
MAXLOGFILES 1024
MAXLOGMEMBERS 5
MAXDATAFILES 32767
MAXINSTANCES 32
MAXLOGHISTORY 33012
LOGFILE
GROUP 1 '+DATAC1/k/ONLINELOG/thread_1_group_1.dbf'
SIZE 4096M ,
GROUP 2 '+DATAC1/k/ONLINELOG/thread_1_group_2.dbf'
SIZE 4096M
…
DATAFILE
'+DATAC1/TM/DATAFILE/system.260.998393855',
'+DATAC1/TM/DATAFILE/sysaux.261.998393857',
'+DATAC1/TM/DATAFILE/undotbs1.262.998393859',
'+DATAC1/TM/DATAFILE/undotbs2.264.998393867',
'+DATAC1/TM/DATAFILE/users.265.998393867',
'+DATAC1/TM/DATAFILE/tpch.291.998477197'
2. Prepare init$KLON.ora
Then we need to prepare new parameter file for K database. The best way is to take parameter file from source DB TM and change some parameters. Because my source database is RAC database I need to set cluster_database=FALSE (create controlfile command produce the error).
Also we need change path for CONTROL_FILES and set the memory for new instance SGA & PGA size:
*.cluster_database=FALSE
*.compatible='18.0.0.0.0'
*.control_files='+DATAC1/k/CONTROLFILE/CF1.dbf'
*.db_block_size=8192
*.db_create_file_dest='+SPARSE'
*.db_files=1024
*.db_name='k'
*.db_recovery_file_dest_size=48g
*.db_recovery_file_dest='+DATAC1'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=db1db1XDB)'
k2.instance_number=2
k1.instance_number=1
*.local_listener='-oraagent-dummy-'
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST'
*.log_archive_format='%t_%s_%r.dbf'
*.pga_aggregate_target=6g
*.processes=2048
*.remote_login_passwordfile='exclusive'
*.sga_target=8192m
k2.thread=2
k1.thread=1
k2.undo_tablespace='UNDOTBS2'
k1.undo_tablespace='UNDOTBS1'
DB_CREATE_FILE_DEST was set to +SPARSE in order to allow for K database create new tablespaces in SPARSE diskgroup. Sparse diskgroup can store sparse and non-sparce datafiles and databases. You can use any diskgroup for DB_CREATE_FILE_DEST.
3. Prepare a rename.sql file
Run the query below to generate a set of rename commands and store them into file:
SET newpage 0
SET linesize 999
SET pagesize 0
SET feedback off
SET heading off
SET echo off
SET space 0
SET tab off
SET trimspool on
SPOOL rename.sql
SELECT 'EXECUTE dbms_dnfs.clonedb_renamefile -
('||''''||name||''''||','||''''||REPLACE(REPLACE(REPLACE(name,'.','_'),-
'TM','K'),'+DATAC1','+SPARSE')||''''||');' FROM v$datafile;
My output is:
EXECUTE dbms_dnfs.clonedb_renamefile('+DATAC1/TM/DATAFILE/system.260.998393855','+SPARSE/K/DATAFILE/system_260_998393855');
EXECUTE dbms_dnfs.clonedb_renamefile('+DATAC1/TM/DATAFILE/sysaux.261.998393857','+SPARSE/K/DATAFILE/sysaux_261_998393857');
EXECUTE dbms_dnfs.clonedb_renamefile('+DATAC1/TM/DATAFILE/undotbs1.262.998393859','+SPARSE/K/DATAFILE/undotbs1_262_998393859');
EXECUTE dbms_dnfs.clonedb_renamefile('+DATAC1/TM/DATAFILE/undotbs2.264.998393867','+SPARSE/K/DATAFILE/undotbs2_264_998393867');
EXECUTE dbms_dnfs.clonedb_renamefile('+DATAC1/TM/DATAFILE/users.265.998393867','+SPARSE/K/DATAFILE/users_265_998393867');
EXECUTE dbms_dnfs.clonedb_renamefile('+DATAC1/TM/DATAFILE/tpch.291.998477197','+SPARSE/K/DATAFILE/tpch_291_998477197');
The "
dbms_dnfs.clonedb_renamefile ..." is completely different then "alter database rename file". There are 2 different things.
4. Prepare ASM
Oracle recommend to set READ ONLY permissions at the source datafiles before make clone (to protect source datafiles). Let demonstrate how to do it. The command is
ALTER DISKGROUP DATA set permission
owner=read ONLY,
group=read ONLY,
other=none for file 'all source data files'
For example:
SQL> ALTER DISKGROUP DATAC1 set permission owner=read ONLY, group=read ONLY, other=none for file '+DATAC1/TM/DATAFILE/users.265.998393867'
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15304: operation requires ACCESS_CONTROL.ENABLED attribute to be TRUE
This means i need to set ACCESS_CONTROL.ENABLED'='TRUE' on the diskgroup before doing "read only" :
SQL> ALTER DISKGROUP DATAC1 SET ATTRIBUTE 'ACCESS_CONTROL.ENABLED' = 'TRUE';
Diskgroup altered.
Now we're allowed to set permissions:
SQL> ALTER DISKGROUP DATAC1 set permission owner=read ONLY, group=read ONLY, other=none for file '+DATAC1/TM/DATAFILE/system.260.998393855';
Diskgroup altered.
To revert permissions back:
ALTER DISKGROUP DATAC1 set permission owner=read write, group=read write, other=none for file '+DATAC1/TM/DATAFILE/system.260.998393855';
ALTER DISKGROUP DATAC1 SET ATTRIBUTE 'ACCESS_CONTROL.ENABLED' = 'FALSE';
In my Exadata GI is under "grid" OS account and RDBMS is under "oracle" OS account.
Datafiles in ASM diskgroup have no any owner by default and this bring us to error later at rename.sql phase:
SQL>
EXECUTE dbms_dnfs.clonedb_renamefile(...);
*
ERROR
at line 1:
ORA-01565:
error in identifying file '+DATAC1/TM/DATAFILE/system.260.998393855'
ORA-17503:
ksfdopn:2 Failed to open file
+DATAC1/TM/DATAFILE/system.260.998393855
ORA-15260:
permission
denied on ASM disk group
ORA-06512:
at "SYS.X$DBMS_DNFS", line 10
ORA-06512:
at line 1
So, we need to set the permissions. Look what we have now :
[grid@ed03dbadm01 ~]$ asmcmd
ASMCMD> ls --permission +DATAC1/TM/DATAFILE/system.260.998393855
User Group Permission Name
r--r----- system.260.998393855
Empty User and Group.
Let we add "oracle" user:
SQL> ALTER DISKGROUP DATAC1 ADD USER 'oracle';
ALTER DISKGROUP DATAC1 ADD USER 'oracle'
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15261: user 'oracle' already exists in disk group 'DATAC1'
This message shows that "oracle" account is well known to ASM.
So, let do "alter diskgroup set ownership" for all datafiles:
ALTER DISKGROUP DATAC1 SET OWNERSHIP OWNER='oracle' FOR FILE '+DATAC1/DB1DB1/DATAFILE/system.260.998393855';
I demonstrated one datafile, but you need apply "set ownership" for all source datafiles.
You'll obtain something like that:
[grid@ed03dbadm01 ~]$ asmcmd
ASMCMD> ls --permission +DATAC1/DB1DB1/DATAFILE/*
User Group Permission Name
oracle@697ac408d88aff8bbfa7863a8fbb6e8d r--r----- SYSAUX.261.998393857
oracle@697ac408d88aff8bbfa7863a8fbb6e8d r--r----- SYSTEM.260.998393855
oracle@697ac408d88aff8bbfa7863a8fbb6e8d r--r----- TPCH.291.998477197
oracle@697ac408d88aff8bbfa7863a8fbb6e8d r--r----- UNDOTBS1.262.998393859
oracle@697ac408d88aff8bbfa7863a8fbb6e8d r--r----- UNDOTBS2.264.998393867
oracle@697ac408d88aff8bbfa7863a8fbb6e8d r--r----- USERS.265.998393867
You
may want to create clone under another OS account, not an "oracle". In
this case you need to add this new account to disk group owners.
5. Shutdown the Test Master
Previous actions don't require downtime, but now we need to shutdown our Test Master database.
The obvious recommendation "shutdown database clearly" with "shutdown immediate".
Not "shutdown abort" and fuzzy datafiles - this will bring you to errors later!
We forgot shutdown the 2nd instance of TM at the 2nd RAC node and obtained the error later at rename phase:
EXEC dbms_dnfs.clonedb_renamefile('+DATAC1/TM/DATAFILE/system.260.998393855',
'+SPARSE/K/DATAFILE/system_260_998393855');
ERROR at line 1:
ORA-17515: Creation of clonedb failed using snapshot file
+DATAC1/TM/DATAFILE/system.260.998393855
ORA-17515: Creation of clonedb failed using snapshot file
+DATAC1/TM/DATAFILE/system.260.998393855
ORA-06512: at "SYS.X$DBMS_DNFS", line 10
ORA-06512: at line 1
Is you got similar message check the datafile's headers. These
files have same SCN, so they good for cloning:
FHFNO HXIFZ HXNRCV
HXSTS
FHSCN
FHAFS
----- ----- ------ ------- ------------
----------
1 0 0 2368471
2368471 0
2 0 0 2368471
2368471 0
3 0 0 2368471
2368471 0
4 0 0 2368471
2368471 0
5 0 0 2368471
2368471 0
6 0 0 2368471
2368471 0
6. Cloning
Now you'd run some commands, prepared at previous steps:
•SQL> startup nomount pfile=init$K.ora
ORACLE instance started.
•SQL> @cf.sql
Control file created.
•SQL> @rename.sql
File renamed.
•SQL> alter database open resetlogs;
Database altered.
•ALTER TABLESPACE temp ADD
TEMPFILE '+…' size …g autoextend on next 1024m;
