Exadata Storage Snapshots, Part 4, Technical details

(start is here)

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;