12c – Oracle Veda

Setup CloneDB

With the help of Oracle’s in-built copy-on-write technology, a consistent backup of a database can be used to create a clone. The clone database accesses the backup data files in read-only mode. A PL/SQL procedure is used to create pointers to the backup data files. When a clone changes a block, or creates a new one, an unshared copy of the block is created. Multiple clones can share a single source database backup.

To setup a clone database, an Oracle supplied perl script can be used to generate three scripts: a pfile for the clone, a create control file script, and a rename data file script. These scripts can also be created manually. After running these scripts, the clone DB is available for use.

1. Enable dNFS (optional)

Oracle direct NFS provides faster access to files stored on NFS. If your target backup, or clone data files, are on NFS and you want to avail of the faster access provided by dNFS, then use the following command to enable Oracle dNFS. Shut down all Oracle processes running from the Oracle home. (The documentation states that dNFS is required for CloneDB. However, as I have shown in Example 2 below, CloneDB works without dNFS.)

$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk dnfs_on

2. Create Backup

Create a consistent image backup of the target database. If you cannot shutdown the target database, you can make a hot backup and apply archive logs to roll it forward.

$ rman target /
RMAN> startup mount
RMAN> backup as copy database format '/uo1/fra/db1/rman/clone/db1_%U';

3. Generate Scripts

Setup environment variables and run perl script. Oracle home should point to the target database, and directories should be pre-created before runnning the perl script. The full path of the target database parameter file needs to be passed to the perl script (in the example below, it is /u01/oradata/clone1/initdb1.ora.) The MASTER_COPY_DIR should contain only backups of target database data files. You must remove all other files, including backups of control files and spfiles.

$ export MASTER_COPY_DIR='/uo1/fra/db1/rman/clone'
$ export CLONE_FILE_CREATE_DEST='/u01/oradata/clone1'
$ export CLONEDB_NAME=clone1

$ perl /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/install/clonedb.pl /u01/oradata/clone1/initdb1.ora crtdb.sql dbren.sql

4. Create Clone

The perl script create three files in the CLONE_FILE_CREATE_DEST directory. Edit these files as needed, and make sure all directories required have been created. Oracle home should now point to the clone db. The generated script dbren.sql has a bug which will cause errors while trying to drop/create the TEMP table space. You can get around this by adding a temp file, as shown below.

SQL> @crtdb
SQL> @dbren
SQL> alter tablespace temp add tempfile '/u01/oradata/clone1/clone1_temp_1.dbf' size 256M;

Demo

The examples below use a Linux guest on Oracle VBox. Both the target and clone DBs run on the same host, and use the same Oracle home. The target backups and clone data files are on the local file system. Both examples share the same target database backup.

Example 1

After creating a consistent image backup of target database db1, manually create the three files needed for cloning. Create clone database cp1 and verify space usage.

— log into target database and create a consistent image backup

$ . oraenv
ORACLE_SID = [db1] ? db1
The Oracle base remains unchanged with value /u01/app/oracle
oracle@d12c1:/home/oracle/gs [db1]
$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Sun Jun 10 04:27:19 2018

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

connected to target database (not started)

RMAN> startup mount

Oracle instance started
database mounted

Total System Global Area 578813952 bytes

Fixed Size 2926856 bytes
Variable Size 536872696 bytes
Database Buffers 33554432 bytes
Redo Buffers 5459968 bytes

RMAN> backup as copy database format '/u01/fra/db1/rman/clone/db1_%U';

Starting backup at 2018/06/10 04:28:40
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=124 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/oradata/db1/db1/example01.dbf
output file name=/u01/fra/db1/rman/clone/db1_data_D-DB1_I-1571084687_TS-EXAMPLE_FNO-5_13t52m7q tag=TAG20180610T042841 RECID=26 STAMP=978409730
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/oradata/db1/db1/system01.dbf
output file name=/u01/fra/db1/rman/clone/db1_data_D-DB1_I-1571084687_TS-SYSTEM_FNO-1_14t52m89 tag=TAG20180610T042841 RECID=27 STAMP=978409743
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/oradata/db1/db1/sysaux01.dbf
output file name=/u01/fra/db1/rman/clone/db1_data_D-DB1_I-1571084687_TS-SYSAUX_FNO-3_15t52m8g tag=TAG20180610T042841 RECID=28 STAMP=978409749
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/oradata/db1/db1/undotbs01.dbf
output file name=/u01/fra/db1/rman/clone/db1_data_D-DB1_I-1571084687_TS-UNDOTBS1_FNO-4_16t52m8o tag=TAG20180610T042841 RECID=29 STAMP=978409752
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/u01/fra/db1/rman/clone/db1_cf_D-DB1_id-1571084687_17t52m8p tag=TAG20180610T042841 RECID=30 STAMP=978409753
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/u01/oradata/db1/db1/users01.dbf
output file name=/u01/fra/db1/rman/clone/db1_data_D-DB1_I-1571084687_TS-USERS_FNO-6_18t52m8q tag=TAG20180610T042841 RECID=31 STAMP=978409754
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 2018/06/10 04:29:15
channel ORA_DISK_1: finished piece 1 at 2018/06/10 04:29:16
piece handle=/u01/fra/db1/rman/clone/db1_19t52m8r_1_1 tag=TAG20180610T042841 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2018/06/10 04:29:16

RMAN> exit

Recovery Manager complete.
oracle@d12c1:/home/oracle/gs [db1]

— These are the five datafile copies we need; the controlfile and spfile backups can be deleted.

$ ls -ltr /u01/fra/db1/rman/clone/*_TS-*
-rw-r-----. 1 oracle oinstall 1304174592 Jun 10 04:28 /u01/fra/db1/rman/clone/db1_data_D-DB1_I-1571084687_TS-EXAMPLE_FNO-5_13t52m7q
-rw-r-----. 1 oracle oinstall 838868992 Jun 10 04:28 /u01/fra/db1/rman/clone/db1_data_D-DB1_I-1571084687_TS-SYSTEM_FNO-1_14t52m89
-rw-r-----. 1 oracle oinstall 660611072 Jun 10 04:29 /u01/fra/db1/rman/clone/db1_data_D-DB1_I-1571084687_TS-SYSAUX_FNO-3_15t52m8g
-rw-r-----. 1 oracle oinstall 83894272 Jun 10 04:29 /u01/fra/db1/rman/clone/db1_data_D-DB1_I-1571084687_TS-UNDOTBS1_FNO-4_16t52m8o
-rw-r-----. 1 oracle oinstall 5251072 Jun 10 04:29 /u01/fra/db1/rman/clone/db1_data_D-DB1_I-1571084687_TS-USERS_FNO-6_18t52m8q
oracle@d12c1:/home/oracle/gs [db1]
$

— Create audit dump and datafile directories.

$ mkdir -p /u01/app/oracle/admin/cp1/adump
$ mkdir -p /u01/oradata/cp1

— Review three scripts required to create the clone; verify backup file names are correctly entered in the create controlfile and rename datafile statements.

$ ls -ltr
total 12
-rw-r--r--. 1 oracle oinstall 225 Jun 9 18:13 initcp1.ora
-rw-r--r--. 1 oracle oinstall 653 Jun 10 04:47 crtdb.sql
-rw-r--r--. 1 oracle oinstall 927 Jun 10 04:53 dbren.sql
oracle@d12c1.localdomain:/u01/oradata/cp1 [db1]
$ cat initcp1.ora
control_files=/u01/oradata/cp1/cp1_ctl.dbf
clonedb=TRUE
compatible='12.1.0.2.0'
db_name=cp1
undo_tablespace='UNDOTBS1'
db_cache_size=32m
java_pool_size=32m
large_pool_size=96m
pga_aggregate_target=256m
shared_pool_size=384m

$ cat crtdb.sql
STARTUP NOMOUNT PFILE=/u01/oradata/cp1/initcp1.ora
CREATE CONTROLFILE REUSE SET DATABASE cp1 RESETLOGS
LOGFILE
GROUP 1 '/u01/oradata/cp1/cp1_log1.log' SIZE 50M,
GROUP 2 '/u01/oradata/cp1/cp1_log2.log' SIZE 50M
DATAFILE
'/u01/fra/db1/rman/clone/db1_data_D-DB1_I-1571084687_TS-SYSTEM_FNO-1_14t52m89',
'/u01/fra/db1/rman/clone/db1_data_D-DB1_I-1571084687_TS-SYSAUX_FNO-3_15t52m8g',
'/u01/fra/db1/rman/clone/db1_data_D-DB1_I-1571084687_TS-UNDOTBS1_FNO-4_16t52m8o',
'/u01/fra/db1/rman/clone/db1_data_D-DB1_I-1571084687_TS-USERS_FNO-6_18t52m8q',
'/u01/fra/db1/rman/clone/db1_data_D-DB1_I-1571084687_TS-EXAMPLE_FNO-5_13t52m7q'
CHARACTER SET WE8MSWIN1252;

$ cat dbren.sql
declare
begin
dbms_dnfs.clonedb_renamefile('/u01/fra/db1/rman/clone/db1_data_D-DB1_I-1571084687_TS-SYSTEM_FNO-1_14t52m89', '/u01/oradata/cp1/system01.dbf');
dbms_dnfs.clonedb_renamefile('/u01/fra/db1/rman/clone/db1_data_D-DB1_I-1571084687_TS-SYSAUX_FNO-3_15t52m8g', '/u01/oradata/cp1/sysaux01.dbf');
dbms_dnfs.clonedb_renamefile('/u01/fra/db1/rman/clone/db1_data_D-DB1_I-1571084687_TS-UNDOTBS1_FNO-4_16t52m8o', '/u01/oradata/cp1/undotbs01.dbf');
dbms_dnfs.clonedb_renamefile('/u01/fra/db1/rman/clone/db1_data_D-DB1_I-1571084687_TS-USERS_FNO-6_18t52m8q', '/u01/oradata/cp1/users01.dbf');
dbms_dnfs.clonedb_renamefile('/u01/fra/db1/rman/clone/db1_data_D-DB1_I-1571084687_TS-EXAMPLE_FNO-5_13t52m7q', '/u01/oradata/cp1/example01.dbf');
end;
/
show errors;
-- if there are no errors, run these commands manually
/*
alter database open resetlogs;
alter tablespace temp add tempfile '/u01/oradata/cp1/temp01.dbf' size 256M;
*/

oracle@d12c1.localdomain:/u01/oradata/cp1 [db1]
$

— Create the clone

$ . oraenv
ORACLE_SID = [db1] ? cp1
The Oracle base remains unchanged with value /u01/app/oracle
oracle@d12c1.localdomain:/u01/oradata/cp1 [cp1]
$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jun 10 04:57:08 2018

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to an idle instance.

SQL> @crtdb
ORACLE instance started.

Total System Global Area 578813952 bytes
Fixed Size 2926856 bytes
Variable Size 536872696 bytes
Database Buffers 33554432 bytes
Redo Buffers 5459968 bytes

Control file created.

SQL> @dbren

PL/SQL procedure successfully completed.

No errors.
SQL> alter database open resetlogs;

Database altered.

SQL> alter tablespace temp add tempfile '/u01/oradata/cp1/temp01.dbf' size 256M;

Tablespace altered.

SQL>

-- verify clone files are just pointers and use less space than the target

SQL> !du -sk /u01/oradata/cp1/*
7920 /u01/oradata/cp1/cp1_ctl.dbf
51204 /u01/oradata/cp1/cp1_log1.log
51204 /u01/oradata/cp1/cp1_log2.log
4 /u01/oradata/cp1/crtdb.sql
4 /u01/oradata/cp1/dbren.sql
16 /u01/oradata/cp1/example01.dbf
4 /u01/oradata/cp1/initcp1.ora
604 /u01/oradata/cp1/sysaux01.dbf
372 /u01/oradata/cp1/system01.dbf
1024 /u01/oradata/cp1/temp01.dbf
500 /u01/oradata/cp1/undotbs01.dbf
16 /u01/oradata/cp1/users01.dbf




SQL> !du -sk /u01/fra/db1/rman/clone/*
1273612 /u01/fra/db1/rman/clone/db1_data_D-DB1_I-1571084687_TS-EXAMPLE_FNO-5_13t52m7q
645132 /u01/fra/db1/rman/clone/db1_data_D-DB1_I-1571084687_TS-SYSAUX_FNO-3_15t52m8g
819212 /u01/fra/db1/rman/clone/db1_data_D-DB1_I-1571084687_TS-SYSTEM_FNO-1_14t52m89
81928 /u01/fra/db1/rman/clone/db1_data_D-DB1_I-1571084687_TS-UNDOTBS1_FNO-4_16t52m8o
5128 /u01/fra/db1/rman/clone/db1_data_D-DB1_I-1571084687_TS-USERS_FNO-6_18t52m8q

SQL>

Example 2

Use the backup of target database db1 created in the previous example. Generate the required clone scripts by running the Oracle supplied perl script. Create clone database cp2 and verify space used.

— These are the consistent image backups of all five datafiles in the target database; there are no other files in this directory.

$ ls -ltr /u01/fra/db1/rman/clone/
total 2825012
-rw-r-----. 1 oracle oinstall 1304174592 Jun 10 04:28 db1_data_D-DB1_I-1571084687_TS-EXAMPLE_FNO-5_13t52m7q
-rw-r-----. 1 oracle oinstall 838868992 Jun 10 04:28 db1_data_D-DB1_I-1571084687_TS-SYSTEM_FNO-1_14t52m89
-rw-r-----. 1 oracle oinstall 660611072 Jun 10 04:29 db1_data_D-DB1_I-1571084687_TS-SYSAUX_FNO-3_15t52m8g
-rw-r-----. 1 oracle oinstall 83894272 Jun 10 04:29 db1_data_D-DB1_I-1571084687_TS-UNDOTBS1_FNO-4_16t52m8o
-rw-r-----. 1 oracle oinstall 5251072 Jun 10 04:29 db1_data_D-DB1_I-1571084687_TS-USERS_FNO-6_18t52m8q
oracle@d12c1:/home/oracle/gs [db1]
$

— Disable Oracle direct NFS for the purpose of demonstrating that clonedb can work without it. Both target and clone use the same Oracle home.

$ cd $ORACLE_HOME/rdbms/lib
oracle@d12c1:/u01/app/oracle/product/12.1.0/dbhome_1/rdbms/lib [db1]
$ make -f ins_rdbms.mk dnfs_off
rm -f /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/odm/libnfsodm12.so
oracle@d12c1:/u01/app/oracle/product/12.1.0/dbhome_1/rdbms/lib [db1]

— Create a pfile of the target database; it will be used by the perl script

$ . oraenv
ORACLE_SID = [db1] ? db1
The Oracle base remains unchanged with value /u01/app/oracle
oracle@d12c1:/home/oracle/gs [db1]
$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jun 10 05:38:13 2018

Connected to an idle instance.

SQL> create pfile=’/u01/oradata/cp2/initdb1.ora’ from spfile;

File created.

SQL> exit
Disconnected

— Create directories for clone cp2

$ mkdir -p /u01/app/oracle/admin/cp2/adump
$ mkdir -p /u01/oradata/cp2

— Set up variables for perl script

$ . oraenv
ORACLE_SID = [cp1] ? cp2
The Oracle base remains unchanged with value /u01/app/oracle
oracle@d12c1.localdomain:/u01/oradata/cp2 [cp2]

$ export MASTER_COPY_DIR='/u01/fra/db1/rman/clone'
$ export CLONE_FILE_CREATE_DEST='/u01/oradata/cp2'
$ export CLONEDB_NAME=cp2

— Run the perl script and review generated files

$ cd /u01/oradata/cp2
$ perl /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/install/clonedb.pl /u01/oradata/cp2/initdb1.ora crtdb.sql dbren.sql
$ ls -ltr
total 16
-rw-r--r--. 1 oracle oinstall 643 Jun 10 05:38 initdb1.ora
-rw-r--r--. 1 oracle oinstall 643 Jun 10 05:49 initcp2.ora
-rw-r--r--. 1 oracle oinstall 882 Jun 10 05:49 dbren.sql
-rw-r--r--. 1 oracle oinstall 868 Jun 10 05:49 crtdb.sql
$

— Edit initcp2.ora; comment out four lines, as shown; replace ‘db1’ with ‘cp2’ in audit_file_dest and dispatchers. here is the final version.

oracle@d12c1.localdomain:/u01/oradata/cp2 [cp2]
$ cat initcp2.ora
##db1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
*.audit_file_dest='/u01/app/oracle/admin/cp2/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
control_files=/u01/oradata/cp2/cp2_ctl.dbf
*.db_block_size=8192
*.db_cache_size=32m
*.db_domain=''
db_name=cp2
*.dispatchers='(PROTOCOL=TCP) (SERVICE=cp2XDB)'
*.java_pool_size=32m
*.large_pool_size=96m
##*.local_listener='LISTENER_DB1'
*.open_cursors=300
*.pga_aggregate_target=256m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=384m
*.undo_tablespace='UNDOTBS1'
##db_create_file_dest=/u01/oradata/cp2/
##log_archive_dest=/u01/oradata/cp2/
clonedb=TRUE

— Create the clone using crtdb.sql and dbren.sql, as is.

oracle@d12c1.localdomain:/u01/oradata/cp2 [cp2]
$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jun 10 06:01:51 2018

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to an idle instance.

SQL> @crtdb
SQL> SET FEEDBACK 1
SQL> SET NUMWIDTH 10
SQL> SET LINESIZE 80
SQL> SET TRIMSPOOL ON
SQL> SET TAB OFF
SQL> SET PAGESIZE 100
SQL>
SQL> STARTUP NOMOUNT PFILE=/u01/oradata/cp2/initcp2.ora
ORACLE instance started.

Total System Global Area 578813952 bytes
Fixed Size 2926856 bytes
Variable Size 536872696 bytes
Database Buffers 33554432 bytes
Redo Buffers 5459968 bytes
SQL> CREATE CONTROLFILE REUSE SET DATABASE cp2 RESETLOGS
2 MAXLOGFILES 32
3 MAXLOGMEMBERS 2
4 MAXINSTANCES 1
5 MAXLOGHISTORY 908
6 LOGFILE
7 GROUP 1 '/u01/oradata/cp2/cp2_log1.log' SIZE 100M BLOCKSIZE 512,
8 GROUP 2 '/u01/oradata/cp2/cp2_log2.log' SIZE 100M BLOCKSIZE 512
9 DATAFILE
10 '/u01/fra/db1/rman/clone/db1_data_D-DB1_I-1571084687_TS-EXAMPLE_FNO-5_13t52m7q',
11 '/u01/fra/db1/rman/clone/db1_data_D-DB1_I-1571084687_TS-SYSAUX_FNO-3_15t52m8g',
12 '/u01/fra/db1/rman/clone/db1_data_D-DB1_I-1571084687_TS-SYSTEM_FNO-1_14t52m89',
13 '/u01/fra/db1/rman/clone/db1_data_D-DB1_I-1571084687_TS-UNDOTBS1_FNO-4_16t52m8o',
14 '/u01/fra/db1/rman/clone/db1_data_D-DB1_I-1571084687_TS-USERS_FNO-6_18t52m8q'
15 CHARACTER SET WE8DEC;

Control file created.

SQL> @dbren
SQL> declare
2 begin
3 dbms_dnfs.clonedb_renamefile('/u01/fra/db1/rman/clone/db1_data_D-DB1_I-1571084687_TS-EXAMPLE_FNO-5_13t52m7q' , '/u01/oradata/cp2//ora_data_cp20.dbf');
4 dbms_dnfs.clonedb_renamefile('/u01/fra/db1/rman/clone/db1_data_D-DB1_I-1571084687_TS-SYSAUX_FNO-3_15t52m8g' , '/u01/oradata/cp2//ora_data_cp21.dbf');
5 dbms_dnfs.clonedb_renamefile('/u01/fra/db1/rman/clone/db1_data_D-DB1_I-1571084687_TS-SYSTEM_FNO-1_14t52m89' , '/u01/oradata/cp2//ora_data_cp22.dbf');
6 dbms_dnfs.clonedb_renamefile('/u01/fra/db1/rman/clone/db1_data_D-DB1_I-1571084687_TS-UNDOTBS1_FNO-4_16t52m8o' , '/u01/oradata/cp2//ora_data_cp23.dbf');
7 dbms_dnfs.clonedb_renamefile('/u01/fra/db1/rman/clone/db1_data_D-DB1_I-1571084687_TS-USERS_FNO-6_18t52m8q' , '/u01/oradata/cp2//ora_data_cp24.dbf');
8 end;
9 /

PL/SQL procedure successfully completed.

SQL> show errors;
No errors.
SQL> alter database open resetlogs;

Database altered.

SQL> drop tablespace TEMP;
drop tablespace TEMP
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace

SQL> create temporary tablespace TEMP;
create temporary tablespace TEMP
*
ERROR at line 1:
ORA-02199: missing DATAFILE/TEMPFILE clause

— Ignore TEMP errors, and add temp file

SQL> alter tablespace temp add tempfile ‘/u01/oradata/cp2/temp01.dbf’ size 256M;

Tablespace altered.

— Verify clone space usage

SQL> !du -sk /u01/oradata/cp2/*
9072 /u01/oradata/cp2/cp2_ctl.dbf
102404 /u01/oradata/cp2/cp2_log1.log
102404 /u01/oradata/cp2/cp2_log2.log
4 /u01/oradata/cp2/crtdb.sql
4 /u01/oradata/cp2/dbren.sql
4 /u01/oradata/cp2/initcp2.ora
4 /u01/oradata/cp2/initdb1.ora
16 /u01/oradata/cp2/ora_data_cp20.dbf
620 /u01/oradata/cp2/ora_data_cp21.dbf
404 /u01/oradata/cp2/ora_data_cp22.dbf
548 /u01/oradata/cp2/ora_data_cp23.dbf
16 /u01/oradata/cp2/ora_data_cp24.dbf
1024 /u01/oradata/cp2/temp01.dbf

SQL>

SQL> !ls -ltr /u01/oradata/cp2/*
-rw-r–r–. 1 oracle oinstall 643 Jun 10 05:38 /u01/oradata/cp2/initdb1.ora
-rw-r–r–. 1 oracle oinstall 882 Jun 10 05:49 /u01/oradata/cp2/dbren.sql
-rw-r–r–. 1 oracle oinstall 868 Jun 10 05:49 /u01/oradata/cp2/crtdb.sql
-rw-r–r–. 1 oracle oinstall 650 Jun 10 05:58 /u01/oradata/cp2/initcp2.ora
-rw-r—–. 1 oracle oinstall 104858112 Jun 10 06:02 /u01/oradata/cp2/cp2_log2.log
-rw-r—–. 1 oracle oinstall 1304174592 Jun 10 06:02 /u01/oradata/cp2/ora_data_cp20.dbf
-rw-r—–. 1 oracle oinstall 5251072 Jun 10 06:02 /u01/oradata/cp2/ora_data_cp24.dbf
-rw-r—–. 1 oracle oinstall 268443648 Jun 10 06:05 /u01/oradata/cp2/temp01.dbf
-rw-r—–. 1 oracle oinstall 838868992 Jun 10 06:07 /u01/oradata/cp2/ora_data_cp22.dbf
-rw-r—–. 1 oracle oinstall 660611072 Jun 10 06:07 /u01/oradata/cp2/ora_data_cp21.dbf
-rw-r—–. 1 oracle oinstall 83894272 Jun 10 06:07 /u01/oradata/cp2/ora_data_cp23.dbf
-rw-r—–. 1 oracle oinstall 104858112 Jun 10 06:07 /u01/oradata/cp2/cp2_log1.log
-rw-r—–. 1 oracle oinstall 9289728 Jun 10 06:07 /u01/oradata/cp2/cp2_ctl.dbf

SQL>

Gopal June 10, 2018 Oracle DBA11g, 12c, clone database, clonedb, oracle Read more >

Terminate Datapump Job

A data pump job runs on the server. It cannot be terminated by killing the client session which started it. Follow these steps to terminate a data pump export job.

1. Open a separate terminal window and start SQL*Plus. Find the name of the running job.

SQL> select owner_name, job_name from dba_datapump_jobs;

2. Start new datapump session and attach to job name retrieved in previous step.

$ expdp system/mypwd attach=<job_name>

3. Terminate the job

Export> kill_job

Demo

The examples below use a Linux guest on Oracle VBox. The first one runs a data pump export in the background, and attaches to it using the job name. The second one runs the export in the foreground and then terminates the client process using CTL-C; the server process is then killed from data pump.

Example 1

Start a DP export job in the background. Query the dictionary to find it’s name. Attach to it and issue the kill job command.

— Start data pump export in the background.

$ nohup expdp system/blogtest directory=dpump full=y dumpfile=full.dmp logfile=full.log &

— Log into SQLPLUS and find job name.

$ sqlplus system/blogtest

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jun 3 10:30:04 201

SQL> select job_name,state from user_datapump_jobs;

JOB_NAME              STATE
--------------------- ------------------------------
SYS_EXPORT_FULL_01    EXECUTING

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

— Attach to the data pump job and kill it.

$ expdp system/blogtest attach=SYS_EXPORT_FULL_01

Export: Release 12.1.0.2.0 - Production on Sun Jun 3 10:30:48 2018

Job: SYS_EXPORT_FULL_01
Owner: SYSTEM
Operation: EXPORT
Creator Privs: TRUE
GUID: 6DBEA56196A71FBCE0536338A8C0DC89
Start Time: Sunday, 03 June, 2018 10:29:36
Mode: FULL
Instance: ocp12c
Max Parallelism: 1
Timezone: +00:00
Timezone version: 18
Endianness: LITTLE
NLS character set: AL32UTF8
NLS NCHAR character set: AL16UTF16
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND system/******** directory=dpump full=y dumpfile=full.dmp logfile=full.log
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /u01/fra/db1/dp/full.dmp
bytes written: 4,096

Worker 1 Status:
Instance ID: 1
Instance name: ocp12c
Host name: d12c1.localdomain
Process Name: DW00
State: EXECUTING
Object Schema: SYS
Object Name: AUD$Job: SYS_EXPORT_FULL_01
Owner: SYSTEM
Operation: EXPORT
Creator Privs: TRUE
GUID: 6DBEA56196A71FBCE0536338A8C0DC89
Start Time: Sunday, 03 June, 2018 10:29:36
Mode: FULL
Instance: ocp12c
Max Parallelism: 1
Timezone: +00:00
Timezone version: 18
Endianness: LITTLE
NLS character set: AL32UTF8
NLS NCHAR character set: AL16UTF16
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND system/******** directory=dpump full=y dumpfile=full.dmp logfile=full.log
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /u01/fra/db1/dp/full.dmp
bytes written: 4,096
Object Type: DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
Completed Objects: 60
Worker Parallelism: 1

Export> kill_job
Are you sure you wish to stop this job ([yes]/no): yes

— Verify job has been terminated.

$ sqlplus system/blogtest

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jun 3 10:31:07 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Sun Jun 03 2018 10:30:48 -04:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select job_name,state from user_datapump_jobs;

no rows selected

Example 2

Start a DP export job, and press CTL-C. Issue kill job command to terminate it.

— Start data pump export job in the foreground and press CTL-C to terminate the client session.

$ expdp system/blogtest directory=dpump full=y dumpfile=full.dmp logfile=full.log

Export: Release 12.1.0.2.0 – Production on Sun Jun 3 10:47:39 2018

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Starting “SYSTEM”.”SYS_EXPORT_FULL_01″: system/******** directory=dpump full=y dumpfile=full.dmp logfile=full.log
Estimate in progress using BLOCKS method…
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 5.578 MB
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
^C

-- Pressing CTL-C while the job is running brings up inter-active command mode. Type HELP to see the options available.

Export> help
------------------------------------------------------------------------------

The following commands are valid while in interactive mode.
Note: abbreviations are allowed.

ADD_FILE
Add dumpfile to dumpfile set.

CONTINUE_CLIENT
Return to logging mode. Job will be restarted if idle.

EXIT_CLIENT
Quit client session and leave job running.

FILESIZE
Default filesize (bytes) for subsequent ADD_FILE commands.

HELP
Summarize interactive commands.

KILL_JOB
Detach and delete job.

PARALLEL
Change the number of active workers for current job.

REUSE_DUMPFILES
Overwrite destination dump file if it exists [NO].

START_JOB
Start or resume current job.
Valid keyword values are: SKIP_CURRENT.

STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.

STOP_JOB
Orderly shutdown of job execution and exits the client.
Valid keyword values are: IMMEDIATE.

Export> status

Job: SYS_EXPORT_FULL_01
Operation: EXPORT
Mode: FULL
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /u01/fra/db1/dp/full.dmp
bytes written: 4,096

Worker 1 Status:
Instance ID: 1
Instance name: ocp12c
Host name: d12c1.localdomain
Process Name: DW00
State: EXECUTING
Object Name: +*
Object Type: DATABASE_EXPORT/TRUSTED_DB_LINK
Completed Objects: 1
Total Objects: 1
Worker Parallelism: 1

— issue the kill job command

Export> kill_job
Are you sure you wish to stop this job ([yes]/no): yes

— verify job is not running anymore.

$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jun 3 10:48:34 2018

SQL> select owner_name, job_name,state from dba_datapump_jobs;

no rows selected

— check the export job log.

$ cd /u01/fra/db1/dp

$ cat full.log

Export: Release 12.1.0.2.0 - Production on Sun Jun 3 10:47:39 2018

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** directory=dpump full=y dumpfile=full.dmp logfile=full.log
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 5.578 MB
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/RADM_FPTM
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
;;; Export> help
;;; Export> status
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
;;; Export> kill_job
Job "SYSTEM"."SYS_EXPORT_FULL_01" stopped due to fatal error at Sun Jun 3 10:48:19 2018 elapsed 0 00:00:38

$

Gopal May 31, 2018 Oracle DBA10g, 11g, 12c, abort, data pump, datapump, expdp, export, impdp, import, job, kill, oracle, terminate Read more >

Tag Archive: 12c

Setup CloneDB

1. Enable dNFS (optional)

2. Create Backup

3. Generate Scripts

4. Create Clone

Demo

Example 1

Example 2

Terminate Datapump Job

Demo

Example 1

Example 2