Task 1: Prepare the Standby Database to Be Used as a Sparse Test Master

9.7.7.1 Task 1: Prepare the Standby Database to Be Used as a Sparse Test Master

The existing files for the standby database are used to support snapshots. You create a series of sparse files pointing to the existing files of the standby. Redo received from the primary database is applied to these files. These sparse files allow the standby database to be used as a sparse test master and also kept current with the primary database.

Stop redo apply at the standby.

To ensure that the structure of the database is at a quiesced state for creating the supporting files to build snapshots, redo apply should be turned off at the standby database.
```
DGMGRL> edit database tm_standby set state='APPLY-OFF';
```
Prepare the current standby database to be used as a test master.
The system must be configured as follows:
1. The disk group containing the database files must have the access_control.enabled attribute set to TRUE.
  As SYSASM, log into Oracle ASM using SQL*Plus and configure the disk group.
  
  For example:
```
SQL> alter diskgroup DATA set attribute 'ACCESS_CONTROL.ENABLED'='TRUE';
```
2. The operating system (OS) user of the database owner must be added as an explicit user of the disk group containing the database files.
  For example:
```
SQL> alter diskgroup DATA add user 'scott';
```
3. The database files that are to be used must have explicit permissions granted to the database owner OS user.
  
  You must perform this step for all OS users that will be creating snapshots using these files and for all files that will be referenced by the snapshots.
  
  The following script can be used to build SQL statements to configure the ownership settings. Run the script while connected to the standby database using SQL*Plus. If the standby is a container database (CDB) you must be connected to the cdb$root container:
```
set newpage 0
set linesize 999
set pagesize 0
set feedback offset heading off
set echo off
set space 0
set tab off
set trimspool on
spool set_owner.sql
select 'ALTER DISKGROUP DATA set ownership owner='||''''||'scott'||''''||' 
 for file '||''''||name||''''||';' from v$datafile;
exit
```
  After running the previous script, log in to Oracle ASM using SQL*Plus as the SYSASM user, and run the commands in set_owner.sql.
```
SQL> @set_owner
```
Create a backup of the controlfile.

All snapshots are created using the current state of the standby database, so they need to know all of the files that make up the standby. Create a binary backup of the control file to allow future creation of the CREATE CONTROLFILE script required for additional snapshots
```
SQL> ALTER DATABASE BACKUP CONTROLFILE TO '/home/oracle/snap_tm/control_tm.ctl';
```

Create the rename_files.sql script to create the sparse data files for the snapshot.

This script builds a series of RENAME statements to create the sparse data files to be used for the snapshot to apply redo received from the primary. Use a SQL statement similar to the following. Note that this statement uses the same directory structure as the original files, but the files will be created in the SPARSE disk group. The new file names will be created replacing ‘.’ (periods) with ‘_’ (underscores).

set newpage 0
set linesize 999
set pagesize 0
set feedback off
set heading off
set echo offset space 0
set tab off
set trimspool on
spool rename_files.sql
select 'EXECUTE dbms_dnfs.clonedb_renamefile ('||''''||name||''''||
','||''''||replace(replace(name,'.','_'),'DATA/','SPARSE/')||''''||
');' from v$datafile;
exit

This script produces output similar to the following:

EXECUTE dbms_dnfs.clonedb_renamefile ('+DATA/TM_STANDBY/DATAFILE/system.515.9304
75939','+SPARSE/TM_STANDBY/DATAFILE/system_515_930475939');

EXECUTE dbms_dnfs.clonedb_renamefile ('+DATA/TM_STANDBY/429CE0836E0166ACE05382C8
E50A1154/DATAFILE/system.567.930475945','+SPARSE/TM_STANDBY/429CE0836E0166ACE053
82C8E50A1154/DATAFILE/system_567_930475945');

EXECUTE dbms_dnfs.clonedb_renamefile ('+DATA/TM_STANDBY/DATAFILE/sysaux.571.9304
75939','+SPARSE/TM_STANDBY/DATAFILE/sysaux_571_930475939');

EXECUTE dbms_dnfs.clonedb_renamefile ('+DATA/TM_STANDBY/429CE0836E0166ACE05382C8
E50A1154/DATAFILE/sysaux.516.930475945','+SPARSE/TM_STANDBY/429CE0836E0166ACE053
82C8E50A1154/DATAFILE/sysaux_516_930475945');

EXECUTE dbms_dnfs.clonedb_renamefile ('+DATA/TM_STANDBY/DATAFILE/undotbs1.497.93
0475939','+SPARSE/TM_STANDBY/DATAFILE/undotbs1_497_930475939');

EXECUTE dbms_dnfs.clonedb_renamefile ('+DATA/TM_STANDBY/429CE0836E0166ACE05382C8
E50A1154/DATAFILE/undotbs1.564.930475945','+SPARSE/TM_STANDBY/429CE0836E0166ACE0
5382C8E50A1154/DATAFILE/undotbs1_564_930475945');

Using ASMCMD, create directories for all of the directories identified in the script rename_files.sql.

When the dbms_dnfs.clonedb_renamefile function runs, it requires that all directory structures used for the files already exist in ASM.

Use the output from the previous step to determine the structures required and then create them as needed. You can use ASMCMD to create the directories as in the following example:

cd ASMCMD [+] > cd sparse
ASMCMD [+sparse] > ls
ASMCMD [+sparse] > mkdir tm_standby
ASMCMD [+sparse] > cd tm_standby
ASMCMD [+sparse/tm_standby] > mkdir datafile
ASMCMD [+sparse/tm_standby] > mkdir 429DC0E1BCBD1B90E05382C8E50A8E80
ASMCMD [+sparse/tm_standby] > mkdir 429CE0836E0166ACE05382C8E50A1154
ASMCMD [+sparse/tm_standby] > cd 429DC0E1BCBD1B90E05382C8E50A8E80
ASMCMD [+sparse/tm_standby/429DC0E1BCBD1B90E05382C8E50A8E80] > mkdir datafile
ASMCMD [+sparse/tm_standby/429DC0E1BCBD1B90E05382C8E50A8E80] > cd ../429CE0836E0166ACE05382C8E50A1154
ASMCMD [+sparse/tm_standby/429CE0836E0166ACE05382C8E50A1154] > mkdir datafile

Parent topic: Creating Sparse Test Masters from a Single Full Database Copy