- User's Guide
- Setting up Oracle Exadata Storage Snapshots
- Managing Exadata Snapshots
- Creating Sparse Test Masters from a Single Full Database Copy
- 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:
- The disk group containing the database files
must have the
access_control.enabled
attribute set toTRUE
.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';
- 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';
- 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
- The disk group containing the database files
must have the
- 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