Transcript Two-Minute Ultra-Small Instant Database Clones

co•he•sion noun \kō-ˈhē-zhən\ 1 : the act or state of sticking together tightly; especially: unity
2-Minute Ultra-Small
Instant Database
Clones
Presented by: Stephen Rea,
N2N Services, Inc.
Tuesday, November 5, 8:30 a.m.
Course ID #243
Introduction
Cloning a database can take up a lot of time and
disk space, so, we usually limit copies of our PROD
database to just a few clones, such as TEST, PPRD,
and UPGD.
Oracle’s new Direct NFS cloning, introduced in
11gR2 and documented in 12c, blows away those
time and space restrictions, allowing us to create as
many clones as we need in under 2 minutes apiece
utilizing 1/100’th of the disk space.
2
CoHEsion Summit
Benefits: An Understanding of
Direct NFS Cloning
After attending this session, you will come away
knowing how to create local Direct NFS clones
(no separate DNFS server and NAS storage
device server for the base database copy
needed) in under 2 minutes apiece, which
occupy minimal disk space.
3
CoHEsion Summit
What is Direct NFS?
What is Direct NFS?
Oracle Direct NFS (DNFS) is an NFS (Network
File System) client that provides faster and
more scalable access to NFS storage located
on NAS (Network-Attached Storage) storage
devices. NAS appliances and their client
systems typically communicate via the
Network File System (NFS) protocol. NFS
allows client systems to access files over the
network as easily as if the underlying
storage was directly attached
to the client.
5
CoHEsion Summit
What is Direct NFS?
With a standard DNFS setup, you have one
or more NAS storage devices, a DNFS server
to connect those storage devices to your
database (or other) servers using the NFS
protocol, and your current database (or
other) servers that access data on those NAS
storage device mount points.
6
That’s a lot of hardware, especially
if you just want to clone a
database!
CoHEsion Summit
What is Direct NFS?
Actually, all that we need is to enable and
configure the Direct NFS client on our
database server to loop back to the
database server itself, so the DNFS client
accesses a mount point mapped to a
directory on the database server as a “virtual
NAS storage device”, and transmits that data
back and forth to the database server itself.
So, we have eliminated the need for a
separate NAS storage device and
a separate DNFS server!
7
CoHEsion Summit
What is a Direct NFS Clone?
What is a Direct NFS Clone?
A Direct NFS Clone consists of:
1) A base image copy of the database being cloned
(this is read-only), referenced by all of the DNFS
clones.
2) A mapped set of local tablespace datafiles
containing the changes made to the base
image data (DML, DDL) for each DNFS clone.
3) Other local Oracle tablespaces and files, such as
the control file, the TEMP tablespace,
and the redo logs for each
DNFS clone.
9
CoHEsion Summit
How Do We Create a Base Image Copy?
How Do We Create a Base Image
Copy?
su - oracle
. oraenv
TEST
mkdir -p /u01/oradata/clone_base
rman target /
run {
configure controlfile autobackup off;
set nocfau;
backup as copy database format '/u01/oradata/clone_base/%U' ;
}
exit
Note: "nocfau" is "no controlfile auto backup“; also, may need "unset TWO_TASK" after
oraenv so running rman won’t give an error).
11
CoHEsion Summit
How Do We Set Up Direct NSF Locally?
How Do We Set Up Direct NSF
Locally?
Create a directory for the NFS shares (nfs_shares) while logged in as
root:
mkdir -p /u01/nfs_shares/clonedb
chown -R oracle:oinstall /u01/nfs_shares
13
Export the NFS shares directory:
Add the following line to /etc/exports:
/u01/nfs_shares/clonedb *(rw,sync,no_wdelay,insecure,
insecure_locks,no_root_squash)
exportfs -a
exportfs
Shows the clonedb directory exported to the entire world:
/u01/nfs_shares/clonedb
<world>
CoHEsion Summit
How Do We Set Up Direct NSF
Locally?
Create a directory that we are going to mount on (nfs_mount) on the same server:
mkdir -p /u01/nfs_mount/clonedb
chown -R oracle:oinstall /u01/nfs_mount
So, we are exporting /u01/nfs_shares/clonedb, and we are mounting
/u01/nfs_mount/clonedb, so, NFS is actually never leaving the system.
Add the NFS mount directory to /etc/fstab so that it does the mount again on server reboot
(specifying the localhost for nfs_shares):
myserver.myschool.edu:/u01/nfs_shares/clonedb /u01/nfs_mount/clonedb nfs
rw,bg,hard,nointr,tcp,nfsvers=3,timeo=400,rsize=32768,wsize=32768,actimeo=0 0 0
14
Mount the NFS mount directory (the mount command reads /etc/fstab):
mount -a
df -h
Shows:
myserver.myschool.edu:/u01/nfs_shares/clonedb
1.2T 816G 305G 73% /u01/nfs_mount/clonedb
CoHEsion Summit
How Do We Set Up Direct NSF
Locally?
Change the permissions for oradism (in the Oracle Home bin directory) to
eliminate “ORA-17513: dNFS package call failed” errors:
chmod 4755 /u01/app/oracle/product/11.2.0.3/dbhome_1/bin/oradism
Create oranfstab file in the Oracle Home dbs directory (logged in (su) as user
oracle) to declare the NFS servers you are going to use (this local NFS mount
loops back to the server itself (using IP address 127.0.0.1)):
su - oracle
. oraenv
TEST
cd $ORACLE_HOME/dbs
Add the following to the oranfstab file:
server: myserver.myschool.edu
path: 127.0.0.1
local: 127.0.0.1
export: /u01/nfs_shares/clonedb mount: /u01/nfs_mount/clonedb
15
CoHEsion Summit
How Do We Set Up Direct NSF
Locally?
Enable the Direct NFS client for the Oracle Home by replacing the current (non-NFS) Oracle Disk Manager (odm)
library with the NFS odm library:
cd ${ORACLE_HOME}/lib
ls -ltr *odm*
Shows:
-rw-r--r-- 1 oracle dba 7370 Sep 17 2011 libodm11.a
-rw-r--r-- 1 oracle dba 12259 Sep 17 2011 libodmd11.so
-rw-r--r-- 1 oracle dba 60431 Sep 17 2011 libnfsodm11.so
lrwxrwxrwx 1 oracle dba 12 May 25 2012 libodm11.so -> libodmd11.so
cp -p libodm11.so libodm11.so.save
cp -p libnfsodm11.so libodm11.so
(or, "ln –s libnfsodm11.so libodm11.so")
16
Now, we are NFS enabled (which will be seen in the alert log when the database is started, which shows a line like
“Oracle instance running with ODM: Oracle Direct NFS ODM Library Version 3.0”), with libodm11.so
containing libnfsodm11.so:
ls -ltr *odm*
Shows:
-rw-r--r-- 1 oracle dba 7370 Sep 17 2011 libodm11.a
-rw-r--r-- 1 oracle dba 60431 Sep 17 2011 libodmd11.so
-rw-r--r-- 1 oracle dba 12259 Sep 17 2011 libodm11.so.save
-rw-r--r-- 1 oracle dba 60431 Sep 17 2011 libnfsodm11.so
lrwxrwxrwx 1 oracle dba 12 May 25 2012 libodm11.so -> libodmd11.so
CoHEsion Summit
Let’s Make Our First Direct NFS Clone
Let’s Make Our First Direct NFS
Clone
Create the base parameter file, which will be customized by the clonedb.pl
script for every clone that is created from the RMAN base image copy:
sqlplus / as sysdba
create pfile='/u01/oradata/initbase.ora' from spfile;
exit
After the RMAN base image copy has been created, remove all files from that
clone_base directory (such as spfile and control file, if any), leaving just the
base image copies of the datafiles:
cd /u01/oradata/clone_base
ls -ltr
rm ... (i.e., remove the files without a tablespace name)
18
CoHEsion Summit
Let’s Make Our First Direct NFS
Clone
Create a directory in the nfs mount directory for the first clone - let's name it "CLONE1“
(note that this maps to /u01/nfs_shares/clonedb/CLONE1):
mkdir –p /u01/nfs_mount/clonedb/CLONE1
Add the CLONE1 entry to the /etc/oratab file (for ". oraenv" to use), specifying the correct
ORACLE_HOME directory for the clone:
CLONE1:/u01/app/oracle/product/11.2.0.3/dbhome_1:N
Switch to the CLONE1 environment variables (may need "unset TWO_TASK" after this so running
sqlplus won’t give an error):
. oraenv
CLONE1
19
Set the environment variables which will be used by Oracle's clonedb.pl script, specifying the
master copy (the base image copy), the clone's directory, and the clone's name (SID):
export MASTER_COPY_DIR=/u01/oradata/clone_base
export CLONE_FILE_CREATE_DEST=/u01/nfs_mount/clonedb/CLONE1
export CLONEDB_NAME=CLONE1
export ORACLE_SID=CLONE1
CoHEsion Summit
Let’s Make Our First Direct NFS
Clone
Create the control file creation script (crtdb.sql), the datafile rename script (dbren.sql which is used to map the clone's database-specific datafiles to the base image copy
datafiles), and the clone's parameter file using Oracle's clonedb.pl script, specifying the
base parameter file to model the clone's parameter file after, along with the two script
file names that are to be created:
cd /tmp
perl $ORACLE_HOME/rdbms/install/clonedb.pl /u01/oradata/initbase.ora crtdb.sql
dbren.sql
Note: The parameter file is put into /u01/nfs_mount/clonedb/CLONE1/initCLONE1.ora
(the CLONE_FILE_CREATE_DEST directory).
The clonedb.pl script makes the following parameter file changes for the clone:
control_files=/u01/nfs_mount/clonedb/CLONE1/CLONE1_ctl.dbf
db_name=CLONE1
db_create_file_dest=/u01/nfs_mount/clonedb/CLONE1
log_archive_dest=/u01/nfs_mount/clonedb/CLONE1
20
CoHEsion Summit
Let’s Make Our First Direct NFS
Clone
If this is an Oracle 11.2.0.3 database, add the following to the clone's init.ora
file (/u01/nfs_mount/clonedb/CLONE1/initCLONE1.ora):
clonedb=TRUE
Edit the crtdb.sql file to specify the TEST's character set:
In TEST: select value from nls_database_parameters
where parameter = 'NLS_CHARACTERSET';
shows: AL32UTF8
Edit crtdb.sql to contain that character set:
Change last line from:
CHARACTER SET WE8DEC;
to:
CHARACTER SET AL32UTF8;
21
CoHEsion Summit
Let’s Make Our First Direct NFS
Clone
Edit the dbren.sql file to specify the full create TEMP tablespace directives,
if needed, changing the two ending drop and create tablespace TEMP lines
to something like:
create temporary tablespace TEMP2
tempfile '/u01/nfs_mount/clonedb/CLONE1/temp_CLONE1_01.dbf'
size 400064K extent management local uniform size 640K;
alter database default temporary tablespace TEMP2;
drop tablespace TEMP;
alter tablespace TEMP2 rename to TEMP;
If you have SID-specific information in your oraenv script or your banenv
script, you will need to edit those scripts to include the
new clone's information.
22
CoHEsion Summit
Let’s Make Our First Direct NFS
Clone
Run the crtdb.sql (creates the control file) and dbren.sql (maps the
base to local datafiles) scripts in CLONE1, then, create the resulting
server parameter file (which is placed in $ORACLE_HOME/dbs) from
the parameter file:
sqlplus / as sysdba
@crtdb.sql
@dbren.sql
select name from v$datafiles;
create spfile from
pfile='/u01/nfs_mount/clonedb/CLONE1/initCLONE1.ora';
exit
23
CoHEsion Summit
Let’s Make Our First Direct NFS
Clone
24
If you get errors from dbren.sql like:
ORA-01152: file 1 was not restored from a sufficiently old backup
Do a recover database:
In CLONE1, "show parameter arch" shows:
log_archive_dest
string
/u01/nfs_mount/clonedb/CLONE1
In TEST, "show parameter arch" shows:
log_archive_dest
string
/u06/oradata/TEST/
See what archive logs are available in TEST (most recent backups since the base image copy was made):
ls /u06/oradata/TEST/archivelog/2013_10_13
o1_mf_1_1122_95o2vw75_.arc
ls /u06/oradata/TEST/archivelog/2013_10_14
o1_mf_1_1123_95r1x9q5_.arc
recover database using backup controlfile until cancel;
shows: ORA-00280: change 7392764100 for thread 1 is in sequence #1122, so enter 1122 file:
/u06/oradata/TEST/archivelog/2013_10_13/o1_mf_1_1122_95o2vw75_.arc
shows: ORA-00280: change 7392772306 for thread 1 is in sequence #1123, so enter 1123 file:
/u06/oradata/TEST/archivelog/2013_10_14/o1_mf_1_1123_95r1x9q5_.arc
shows: ORA-00280: change 7392815711 for thread 1 is in sequence #1124, N/A, so enter redo log:
/u06/oradata/TEST/redo_TEST_01b.rdo
Log applied.
Media recovery complete.
alter database open resetlogs;
(and, create the spfile and TEMP tablespace manually here)
CoHEsion Summit
Let’s Make Our First Direct NFS
Clone
Congratulations! We've just made our first clone based
on that base image copy of the TEST database that we
created at the beginning.
25
CoHEsion Summit
What about the Direct NFS Clone Space?
What about the Direct NFS Clone
Space?
Let's see the base image copy directory ("-lsh" shows the actual size and the apparent size of the
files):
cd /u01/oradata/clone_base
ls -lsh
Shows that the actual size and apparent size of the datafiles are the same, with a total of 136 Gig:
total 136G
51M -rw-r----- 1 oracle oinstall
51M Oct 14 16:19 data_D-TEST_I-2108832167_TS-BANAQ_FNO-4_13om9gjv
101M -rw-r----- 1 oracle oinstall 101M Oct 14 16:19 data_D-TEST_I-2108832167_TS-BANIAM_FNO-5_11om9gid
101M -rw-r----- 1 oracle oinstall 101M Oct 14 16:19 data_D-TEST_I-2108832167_TS-BANLOB_FNO-6_12om9gjg
11M -rw-r----- 1 oracle oinstall
11M Oct 14 16:19 data_D-TEST_I-2108832167_TS-CONVERSION_FNO-7_15om9gkd
23G -rw-r----- 1 oracle oinstall
23G Oct 14 16:19 data_D-TEST_I-2108832167_TS-DEVELOPMENT_FNO-10_0mom97s7
22G -rw-r----- 1 oracle oinstall
22G Oct 14 16:19 data_D-TEST_I-2108832167_TS-DEVELOPMENT_FNO-11_0nom99c9
21G -rw-r----- 1 oracle oinstall
21G Oct 14 16:19 data_D-TEST_I-2108832167_TS-DEVELOPMENT_FNO-12_0qom9dp4
21G -rw-r----- 1 oracle oinstall
21G Oct 14 16:19 data_D-TEST_I-2108832167_TS-DEVELOPMENT_FNO-8_0pom9cgv
21G -rw-r----- 1 oracle oinstall
21G Oct 14 16:19 data_D-TEST_I-2108832167_TS-DEVELOPMENT_FNO-9_0oom9ai8
1.3G -rw-r----- 1 oracle oinstall 1.3G Oct 14 16:22 data_D-TEST_I-2108832167_TS-SYSAUX_FNO-2_10om9gfe
1.5G -rw-r----- 1 oracle oinstall 1.5G Oct 14 16:22 data_D-TEST_I-2108832167_TS-SYSTEM_FNO-1_0vom9gcp
2.0G -rw-r----- 1 oracle oinstall 2.0G Oct 14 16:19 data_D-TEST_I-2108832167_TS-TESTMGR_FNO-13_0uom9g96
4.3G -rw-r----- 1 oracle oinstall 4.3G Oct 14 16:19 data_D-TEST_I-2108832167_TS-TOOLS_FNO-14_0tom9g24
6.7G -rw-r----- 1 oracle oinstall 6.7G Oct 14 16:21 data_D-TEST_I-2108832167_TS-UNDO_FNO-3_0som9fm3
27
15G -rw-r----- 1 oracle oinstall
15G Oct 14 16:19 data_D-TEST_I-2108832167_TS-USERS_FNO-15_0rom9ets
35M -rw-r----- 1 oracle oinstall
35M Oct 14 16:19 data_D-TEST_I-2108832167_TS-WORKFLOW_FNO-16_14om9gk6
CoHEsion Summit
What about the Direct NFS Clone
Space?
Let's see the clone datafile directory:
cd /u01/nfs_mount/clonedb/CLONE1
ls -lsh
Shows that the total size of the datafiles, etc., for CLONE1 is 372 Meg, versus the 136 Gig for the
base image copy database, with the actual size of datafiles being much smaller than apparent size
of those files (most of the directory size is taken up by the redo log files in CLONE1):
total 372M
8.0K drwxr-x--8.6M -rw-r----101M -rw-r----101M -rw-r----8.0K -rw-r--r-20K -rw-r----1.6M -rw-r----20K -rw-r----20K -rw-r----53M -rw-r----20K -rw-r----948K -rw-r----20K -rw-r----20K -rw-r----20K -rw-r----34M -rw-r-----
28
3
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
oracle
oracle
oracle
oracle
oracle
oracle
oracle
oracle
oracle
oracle
oracle
oracle
oracle
oracle
oracle
oracle
oinstall
oinstall
oinstall
oinstall
oinstall
oinstall
oinstall
oinstall
oinstall
oinstall
oinstall
oinstall
oinstall
oinstall
oinstall
oinstall
. . .
4.0K
8.6M
101M
101M
2.0K
51M
1.5G
2.0G
4.3G
6.7G
15G
35M
101M
101M
11M
23G
Oct
Oct
Oct
Oct
Oct
Oct
Oct
Oct
Oct
Oct
Oct
Oct
Oct
Oct
Oct
Oct
14
14
14
14
14
14
14
14
14
14
14
14
14
14
14
14
16:31
16:36
16:36
16:31
16:18
16:31
16:36
16:31
16:31
16:36
16:31
16:31
16:31
16:31
16:31
16:31
CLONE1
CLONE1_ctl.dbf
CLONE1_log1.log
CLONE1_log2.log
initCLONE1.ora
ora_data_CLONE10.dbf
ora_data_CLONE110.dbf
ora_data_CLONE111.dbf
ora_data_CLONE112.dbf
ora_data_CLONE113.dbf
ora_data_CLONE114.dbf
ora_data_CLONE115.dbf
ora_data_CLONE11.dbf
ora_data_CLONE12.dbf
ora_data_CLONE13.dbf
ora_data_CLONE14.dbf
CoHEsion Summit
Let’s Make Our Second Direct NFS Clone
Let’s Make Our Second Direct NFS
Clone
Wanna see it again?
OK. Get Your Stopwatches out! We’re going for
2 minutes here to create a clone!
30
CoHEsion Summit
Let’s Make Our Second Direct NFS
Clone
Create the directory where the new clone (CLONE2) will live:
mkdir -p /u01/nfs_mount/clonedb/CLONE2
Add the CLONE2 entry to the /etc/oratab file:
CLONE2:/u01/app/oracle/product/11.2.0.3/dbhome_1:N
Switch to the CLONE2 environment variables (may need "unset TWO_TASK" after this
so running sqlplus won’t give an error):
. oraenv
CLONE2
Set the environment variables for CLONE2 which will be used by the clonedb.pl script:
export MASTER_COPY_DIR=/u01/oradata/clone_base
export CLONE_FILE_CREATE_DEST=/u01/nfs_mount/clonedb/CLONE2
export CLONEDB_NAME=CLONE2
export ORACLE_SID=CLONE2
31
CoHEsion Summit
Let’s Make Our Second Direct NFS
Clone
Run the clonedb.pl script again for the second clone:
cd /tmp
perl $ORACLE_HOME/rdbms/install/clonedb.pl /u01/oradata/initbase.ora crtdb.sql
dbren.sql
If this is an Oracle 11.2.0.3 database, add the following to the clone's init.ora file
(/u01/nfs_mount/clonedb/CLONE2/initCLONE2.ora):
clonedb=TRUE
Edit the crtdb.sql file to specify the TEST's character set:
In TEST:
select value from nls_database_parameters
where parameter = 'NLS_CHARACTERSET';
shows: AL32UTF8
Edit crtdb.sql to contain that character set:
Change last line from:
CHARACTER SET WE8DEC;
to:
CHARACTER SET AL32UTF8;
32
CoHEsion Summit
Let’s Make Our Second Direct NFS
Clone
Edit the dbren.sql file to specify the full create TEMP tablespace directives, if needed,
changing the two ending drop and create tablespace TEMP lines to something like:
create temporary tablespace TEMP2
tempfile '/u01/nfs_mount/clonedb/CLONE2/temp_CLONE2_01.dbf'
size 400064K extent management local uniform size 640K;
alter database default temporary tablespace TEMP2;
drop tablespace TEMP;
alter tablespace TEMP2 rename to TEMP;
If you have SID-specific information in your oraenv script or your banenv script, you will
need to edit those scripts to include the new clone's information.
33
Run the crtdb.sql and dbren.sql scripts generated by the above clonedb.pl script in the
CLONE2 database, and create the spfile:
sqlplus / as sysdba
@crtdb.sql
@dbren.sql
create spfile from pfile='/u01/nfs_mount/clonedb/CLONE2/initCLONE2.ora';
exit
CoHEsion Summit
Let’s Make Our Second Direct NFS
Clone
And, BAM! We've just made our second clone in under 2
minutes! (Was anyone timing me?!)
34
CoHEsion Summit
Summary
Oracle’s Direct NFS capability allows you to
create instant database clones at the drop of
a hat occupying a very small footprint any
time your users need to test a new scenario.
Since disk space and creation time have
been minimized by DNFS clones, about the
only thing you may have to contend with is
memory (SGA, PGA, etc.) when having a
bunch of DNFS clones open.
35
CoHEsion Summit
Questions?
36
CoHEsion Summit
clonedb.pl
#
#
#
#
#
#
#
#
#
#
#
#
#
#
#
#
#
#
#
#
#
#
#
#
37
(from "My Oracle Support" Note 1210656.1)
clonedb.pl - This script generates two sql scripts that can be used to
create your test clones. Run this from your testdb Oracle Home env
Before running this script make sure the following env variables are set:
MASTER_COPY_DIR - env variable to point to the directory where the
backup/snapshot of your Master database are kept
CLONE_FILE_CREATE_DEST - env variable to point to the directory where
clonedb files will be created including datafiles,
log files, control files
CLONEDB_NAME
- Cloned database name
S7000_TARGET - Set if the nfs host providing the filesystem for the backup
and the clones is an S7000 series machine and we wish to
employ its cloning features.
perl clonedb.pl
Arg1 - Full path of the Master db init.ora file from your production env
Arg2 - sqlscript1
Arg3 - sqlscript2
CoHEsion Summit
clonedb.pl
#
#
#
#
#
#
#
#
#
#
#
#
#
#
#
This script copies the init.ora file from your master db env to your
clonedb env in CLONE_FILE_CREATE_DEST directory.
After running this script go through the test database parameter file to
make sure all parameters look correct
Go through crtdb.sql to make sure the log names are correct.
If all files look good do the following steps
sqlplus system/manager
@crtdb.sql
@dbren.sql
Now your test database should be available for use.
if ($#ARGV != 2) {
print "usage: perl clonedb.pl \n";
exit;
}
38
if (!$ENV{'MASTER_COPY_DIR'}) {
print "MASTER_COPY_DIR env not set. Set this and rerun it again \n";
exit;
}
CoHEsion Summit
clonedb.pl
if (!$ENV{'CLONE_FILE_CREATE_DEST'}) {
print "CLONE_FILE_CREATE_DEST env not set. Set this and rerun it again \n";
exit;
}
if (!$ENV{'CLONEDB_NAME'}) {
print "CLONEDB_NAME env not set. Set this and rerun it again \n";
exit;
}
($orafile)=$ARGV[0];
($sqlfile1)=$ARGV[1];
($sqlfile2)=$ARGV[2];
# Set env variable specific to your clone database
$neworafile ="$ENV{'CLONE_FILE_CREATE_DEST'}/init$ENV{'CLONEDB_NAME'}.ora";
$cldboh = "$ENV{'ORACLE_HOME'}";
$cldbosid = "$ENV{'ORACLE_SID'}";
$cldbname = "$ENV{'CLONEDB_NAME'}";
$cldbctlfl = "$ENV{'CLONE_FILE_CREATE_DEST'}/$ENV{'CLONEDB_NAME'}_ctl.dbf";
$mastercopydir = "$ENV{'MASTER_COPY_DIR'}";
$clonedbdir ="$ENV{'CLONE_FILE_CREATE_DEST'}";
$s7000 = $ENV{S7000_TARGET} ? 1 : 0 ;
39
CoHEsion Summit
clonedb.pl
# Check if the CLONE_FILE_CREATE_DEST exists
if (! open(CLONEDIR, $clonedbdir))
{
print("CLONE_FILE_CREATE_DEST directory does not exist.\n");
print("Create this directory and rerun the script \n");
exit;
}
close(CLONEDIR);
# Rename the parameters in the copied production init.ora and will open a new init.ora
with new values
open (INFILE,$orafile);
open (OUTFILE,">$neworafile");
@skipparam=("instance_name","service_names","diagnostic_dest");
@inparam=("db_name","control_files");
@outparm=($cldbname,$cldbctlfl);
$skiplen = @skipparam;
$inlen = @inparam;
for $ln (<INFILE>)
{
$newln = $ln;
40
CoHEsion Summit
clonedb.pl
#look for any include files and read their contents
if ($newln =~ "ifile")
{
@lnsp = split("=",$newln);
open(INCFILE, $lnsp[1]);
print OUTFILE "# Copy from $lnsp[1] \n";
for $ln (<INCFILE>)
{
$newln = $ln;
for ($i=0; $i<$skiplen; $i++){
if ($newln =~ /$skipparam[$i]/)
{
$newln="\n";
}
}
41
for ($i=0; $i<$inlen; $i++){
if ($newln =~ /$inparam[$i]/)
{
@lnsp = split("=",$newln);
$lnsp[1]=$outparm[$i];
$newln=$inparam[$i]."=".$lnsp[1]."\n";
}
}
CoHEsion Summit
clonedb.pl
print OUTFILE "$newln";
}
close INCFILE;
print OUTFILE "# End Copy";
}
else
{
for ($i=0; $i<$skiplen; $i++){
if ($newln =~ /$skipparam[$i]/)
{
$newln="\n";
}
}
for ($i=0; $i<$inlen; $i++){
if ($newln =~ /$inparam[$i]/)
{
@lnsp = split("=",$newln);
$lnsp[1]=$outparm[$i];
$newln=$inparam[$i]."=".$lnsp[1]."\n";
}
}
42
CoHEsion Summit
clonedb.pl
print OUTFILE "$newln";
}
}
# Add db_create_file_dest, log_arhive_dest parameter
print OUTFILE "db_create_file_dest=$clonedbdir\n";
print OUTFILE "log_archive_dest=$clonedbdir\n";
#print OUTFILE "clonedb=TRUE\n";
close INFILE;
close OUTFILE;
# Create clone db rename file sql
if (!$s7000)
{
$target=$mastercopydir;
} else {
$target=$clonedbdir;
}
# XXX Needs to be modified to just deal with datafiles.
system ("cd $target; ls -d $target/* >> dnfsa1axxx.log");
system ("cp $target/dnfsa1axxx.log .;rm $target/dnfsa1axxx.log");
43
CoHEsion Summit
clonedb.pl
open(INPFILE,"dnfsa1axxx.log");
open(INTFILE,">filenamexxx.txt");
open(OUTFILE1,">$sqlfile2");
open(OUTFILE,">dnfsa2axxx.log");
for $ln (<INPFILE>)
{
print INTFILE "$ln";
}
close INTFILE;
close INPFILE;
open(INTFILE,"filenamexxx.txt");
$refline=" ";
for $line (<INTFILE>)
{
$line =~ s/\s+$//;
if ($refline ne " ")
{
print OUTFILE "'"."$refline"."'".", \n";
}
$refline = $line;
}
44
CoHEsion Summit
clonedb.pl
if ($refline ne " ")
{
print OUTFILE "'"."$refline"."' \n";
}
close INTFILE;
if (!$s7000)
{
print OUTFILE1 "declare \n";
print OUTFILE1 "begin \n";
open(INTFILE,"filenamexxx.txt");
$i = 0;
for $lne (<INTFILE>)
{
$lne =~ s/\s+$//;
print OUTFILE1 "dbms_dnfs.clonedb_renamefile('$lne' ,
'$clonedbdir/\ora_data_$cldbname$i.dbf'); \n";
$i++;
}
print OUTFILE1 "end; \n";
print OUTFILE1 "/ \n";
print OUTFILE1 "show errors; \n";
}
45
CoHEsion Summit
clonedb.pl
print OUTFILE1 "alter database open resetlogs;\n";
#Add a default temp tablespace in teh clone env
print OUTFILE1 "drop tablespace TEMP;\n";
print OUTFILE1 "create temporary tablespace TEMP;";
close
close
close
close
OUTFILE;
OUTFILE1;
OUTFILE1;
OUTFILE1;
# Create the create controlfile script
open(INPFILE1,"dnfsa2axxx.log");
open(INPSQLFILE,">interm.sql");
open (OUTSQLFILE,">$sqlfile1");
print INPSQLFILE ("
SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
46
CoHEsion Summit
clonedb.pl
STARTUP NOMOUNT PFILE=$neworafile
CREATE CONTROLFILE REUSE SET DATABASE $cldbname RESETLOGS
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXINSTANCES 1
MAXLOGHISTORY 908
LOGFILE
GROUP 1 '$clonedbdir/$cldbname\_log1.log' SIZE 100M BLOCKSIZE 512,
GROUP 2 '$clonedbdir/$cldbname\_log2.log' SIZE 100M BLOCKSIZE 512
DATAFILE
CHARACTER SET WE8DEC; ");
close INPSQLFILE;
47
open(INPSQLFILE,"interm.sql");
for $ln (<INPSQLFILE>)
{
print OUTSQLFILE "$ln";
if ($ln =~ /DATAFILE/)
{
for $ln0 (<INPFILE1>)
{
print OUTSQLFILE "$ln0";
}
}
}
CoHEsion Summit
clonedb.pl
close OUTSQLFILE;
close INPFILE1;
close INPSQLFILE;
unlink("interm.sql");
unlink("dnfsa1axxx.log");
unlink("dnfsa2axxx.log");
unlink("filenamexxx.txt");
48
CoHEsion Summit
Thank You!
Stephen Rea
[email protected]
[email protected]
Please complete the session evaluation form
Course ID 243
49
CoHEsion Summit