Active Database Cloning fails with RMAN-05001

I am trying to clone a Production database which is of 1.5 TB to our test server , I can even do this with the backup based duplication using RMAN , but there will some huge time which spends on my database backup and the network transfer of my backup file to my test server.

So i decided to use a cool feature of Active database duplication in 11g R2 . There main advantage of active db duplication is there is no need source database backup , RMAN will take care of coping the files from the source to the destination server .

Source DB Name = Prod
Destination DB name = Clone

I have copied the Password file of the source database to the destination server and

$ scp orapwProd oracle@hostname.localdomain.com:/opt/oracle/app/11.2.0.3/dbs/

I have created a pfile for my clone database as follows in the default location of /opt/oracle/app/11.2.0.3/dbs/

$ cat initclone.ora

*.control_files=’/opt/oracle/clone/control01.ctl’,’/opt/oracle/clone/control02.ctl’
*.db_block_size=8192
*.db_domain=”
*.db_name=’clone’
*.db_file_name_convert=’/opt/oracle/oradata/prd/datafile/’,’/opt/oracle/clone’
*.log_file_name_convert=’/opt/oracle/oradata/prd/onlinelog/’,’/opt/oracle/clone’
*.diagnostic_dest=’/opt/oracle/app’

Start the instance in the nomount stage

$ export ORACLE_SID=clone
$sqlplus / as sysdba

sql> startup nomount

sql> exit

Make sure you have configured the Listener.ora with the STATIC registration of the Cloned instance.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = clone)
(ORACLE_HOME = /opt/oracle/app/11.2.0.3)
(SID_NAME = clone)
)
)

Reload the Listener and verify the registration

$ lsnrctl reload

Configure the TNSNAMES connectivity for both the sides of source and destination server.

$ tnsping clone —– from the source and destination
$ tnsping prod —– from the source and destination

Connect to RMAN and start the duplication process.
Note: Iam using target control file instead of a recovery catalog

#rman target sys/orcl@prod
RMAN> connect auxiliary sys/orcl@clone

RMAN> DUPLICATE TARGET DATABASE TO ‘clone’ FROM ACTIVE DATABASE;

At some poing the duplication fails with the below error

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 01/30/2008 17:12:42
RMAN-05501: aborting duplication of target database
RMAN-05001: auxiliary file name /u01/oradata/prod/redo03.log conflicts with a file used by the target database
RMAN-05001: auxiliary file name /u01/oradata/prod/redo02.log conflicts with a file used by the target database
RMAN-05001: auxiliary file name /u01/oradata/prod/redo01.log conflicts with a file used by the target database

My source database is using the OMF files. So you cant able to use db_file_name_convert and log_file_name_convert parameter to clone the database when there source database files are Oracle Managed Files.

Quoting the official documentation:

If the source database files are in the Oracle Managed Files (OMF) format, then you cannot use the DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT initialization parameters or the fileNameConversionSpec clause to generate new OMF filenames for the duplicate database. OMF filenames are unique and generated by Oracle Database.

The only exception to this rule is when changing only an ASM disk group name. Assume that source datafiles and online redo log files are stored in ASM disk group +SOURCEDSK. You want to store the duplicate database files in ASM disk group +DUPDSK. In this case, you can set the initialization parameters as follows:

DB_FILE_NAME_CONVERT = (“+SOURCEDSK”,”+DUPDSK”)
LOG_FILE_NAME_CONVERT = (“+SOURCEDSK”,”+DUPDSK”)
RMAN uses DB_FILE_NAME_CONVERT or LOG_FILE_NAME_CONVERT to convert the disk group name, and then generates a new, valid filename based on the converted disk group name.

You have the following other supported options for naming datafiles when the source files are in the Oracle Managed Files format:

Use SET NEWNAME to specify names for individual datafiles.

Set DB_FILE_CREATE_DEST to make all datafiles of the new database Oracle-managed files, with the exception of the files for which SET NEWNAME is used. You should not set DB_FILE_NAME_CONVERT if you set DB_FILE_CREATE_DEST.

Supported options for naming online redo logs duplicated from Oracle-managed files are DB_CREATE_FILE_DEST, DB_RECOVERY_FILE_DEST, or DB_CREATE_ONLINE_LOG_DEST_n.

So shutdown the cloned instance and make sure you have removed the DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT parameterb from the pfile and set the DB_FILE_CREATE_DEST parameter ,
start the database in the nomount stage and proceed with the RMAN duplicate database creation

RMAN> connect auxiliary sys/orcl@clone

RMAN> DUPLICATE TARGET DATABASE TO ‘clone’ FROM ACTIVE DATABASE;

contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 28-FEB-14

Leave a comment

GSD is offline in 11gR2

After upgrading the Clusterware to 11gR2, the status of the GSD resources is offline and disabled.we can check the status of the clusterware resources with the below command.

crsctl stat res -t

ora.gsd
 OFFLINE OFFLINE node1 
OFFLINE OFFLINE node2
OFFLINE OFFLINE node3
OFFLINE OFFLINE node4

This displays  the status for the Local and the Cluster resources.You can find the GSD resources is offline on all the nodes in the cluster.Basically GSD resources is required to serve the request from the Oracle 9i RAC database.Sine we dont have any 9i Database ,this resource can be skipped for normal operation.

 

More information can be forund in the metalink note.

GSD Is Used Only if 9i RAC Database is Present (Doc ID 429966.1)

 

Leave a comment

RMAN Duplicate database from Active database

I am started writing this blog after a long time, in which I will discuss about one of the Oracle 11g New features in Recovery Manager (RMAN).

From Oracle 11g, we create a duplicate database using the RMAN by two methods.

  • Active database duplication (Oracle 11g New Feature)
  • Backup-based duplication

Active database duplication copies the target database over the network to the destination and then creates the duplicate database. Only difference is you don’t need to have the pre-existing RMAN backups and copies. The duplication work is performed by an auxiliary channel. This channel corresponds to a server session on the auxiliary instance on the auxiliary host.

As part of the duplicating operation, RMAN automates the following steps:

  • Creates a control file for the duplicate database.
  • Restarts the auxiliary instance and mounts the duplicate control file.
  • Creates the duplicate data files and recovers them with incremental backups and archived redo logs.
  • Opens the duplicate database with the resetlogs option.

For the active database duplication, RMAN will copy the target database data files over the network to the auxiliary instance.

The Only Disadvantage is there will be a High traffic on your network connection between source and target database.

  • Creating initialization Parameter file for the Duplicate database

If you are using spfile then only parameter required for the duplicate database is DB_NAME. Rest other parameters can be set in the duplicate command itself. If you are not using the spfile , then you need to set initialization parameters in the pfile.

Required parameters:

DB_NAME
CONTROL_FILES
DB_BLOCK_SIZE
DB_FILE_NAME_CONVERT
LOG_FILE_NAME_CONVERT

We will create a pfile with the above parameters for the duplicate database.Create an Oracle Password File for the Duplicate database.

I will make the Duplicate database  name as  “DUP”

Password file is must for the Active database duplication where as it is not required for backup-based duplication. For Active database duplication it connects directly to the auxiliary instance using the password file with the same SYSDBA password as target database. In case you are using password file make sure to have same SYSDBA password as the target database. In this case, RMAN copies the source database password file to the destination host and overwrites any existing password file for the auxiliary instance.

cd $ORACLE_HOME/dbs

orapwd password=ORCL file=orapwDUP
  • Establish Oracle Net Connectivity to the Auxiliary Instance

Auxiliary instance must be available through Oracle Net if you are duplicating from an ACTIVE database.

Add following entries into listener.ora file.

Edit the listener.ora and add following lines:

SID_LIST_LISTENER =
 (SID_LIST =
 )
 (SID_DESC =
 (SID_NAME = dup)
 (ORACLE_HOME = =/home/oracle/app/oracle/product/11.2.0/dbhome_1)
 (GLOBAL_DBNAME = dup.localdomain)
 )
 )

Add the Following entry in the tnsnames.ora

prd =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = prd)
 )
 )

dup =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = dup)
 )
 )
  • Create the directories which are required for the duplicate database.
mkdir -p /home/oracle/app/oracle/oradata/dup
mkdir -p /home/oracle/app/oracle/fast_recovery_area/dup
mkdir -p /home/oracle/app/oracle/admin/dup/pfile
  • Create the parameter file (initdup.ora).
DB_NAME=dup
diagnostic_dest='/home/oracle/app/oracle'
DB_FILE_name_CONVERT=('/home/oracle/app/oracle/oradata/prd','/home/oracle/app/oracle/oradata/dup')
LOG_FILE_NAME_CONVERT=('/home/oracle/app/oracle/oradata/prd','/home/oracle/app/oracle/oradata/dup')
Memory_TARGET=262144000
CONTROL_FILES='/home/oracle/app/oracle/oradata/dup/control01.dbf'
COMPATIBLE= 11.2.0.0.0
  • Start the Auxiliary instance from Sqlplus

Use SQL*Plus to connect to the auxiliary instance using the above created pfile and start it in NOMOUNT mode.

[oracle@localhost admin]$ export ORACLE_SID=dup
[oracle@localhost admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Mar 1 12:40:21 2013

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

Connected to an idle instance.

SQL> startup nomount pfile=/home/oracle/app/oracle/admin/dup/pfile/initdup.ora
ORACLE instance started.

Total System Global Area 372449280 bytes
Fixed Size 1345044 bytes
Variable Size 234883564 bytes
Database Buffers 130023424 bytes
Redo Buffers 6197248 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 -
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
  • Test connectivity to auxiliary and target instance from the Both Sides.
sqlplus sys/ORCL@PRD as sysdba

sqlplus sys/ORCL@DUP as sysdba
  • Using RMAN, Connect to the Database Instances
[oracle@localhost admin]$ rman target sys/ORCL@prd catalog rman/rman@prd

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Mar 1 12:41:24 2013

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

connected to target database: PRD (DBID=1867953957)
connected to recovery catalog database

RMAN> connect auxiliary sys/ORCL@DUP

connected to auxiliary database: DUP (not mounted)

RMAN> DUPLICATE TARGET DATABASE TO 'DUP' FROM ACTIVE DATABASE
2> DB_FILE_NAME_CONVERT '/home/oracle/app/oracle/oradata/prd','/home/oracle/app/oracle/oradata/dup';

Starting Duplicate Db at 01-MAR-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK

contents of Memory Script:
{
 sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
 shutdown clone immediate;
 startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 372449280 bytes

Fixed Size 1345044 bytes
Variable Size 239077868 bytes
Database Buffers 125829120 bytes
Redo Buffers 6197248 bytes

contents of Memory Script:
{
 sql clone "alter system set db_name =
 ''PRD'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
 sql clone "alter system set db_unique_name =
 ''DUP'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
 shutdown clone immediate;
 startup clone force nomount
 backup as copy current controlfile auxiliary format
'/home/oracle/app/oracle/oradata/dup/control01.ctl';
 alter clone database mount;
}
executing Memory Script

sql statement: alter system set db_name = ''PRD'' comment= ''Modified by
RMAN duplicate'' scope=spfile

sql statement: alter system set db_unique_name = ''DUP'' comment= ''Modified
by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 372449280 bytes

Fixed Size 1345044 bytes
Variable Size 239077868 bytes
Database Buffers 125829120 bytes
Redo Buffers 6197248 bytes

Starting backup at 01-MAR-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file
name=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_prd.f
tag=TAG20130301T124259 RECID=9 STAMP=808922591
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26
Finished backup at 01-MAR-13

database mounted

contents of Memory Script:
{
 set newname for datafile 1 to
 "/home/oracle/app/oracle/oradata/dup/system01.dbf";
 set newname for datafile 2 to
 "/home/oracle/app/oracle/oradata/dup/sysaux01.dbf";
 set newname for datafile 3 to
 "/home/oracle/app/oracle/oradata/dup/undotbs01.dbf";
 set newname for datafile 4 to
 "/home/oracle/app/oracle/oradata/dup/users01.dbf";
 set newname for datafile 5 to
 "/home/oracle/app/oracle/oradata/dup/example01.dbf";
 set newname for datafile 6 to
 "/home/oracle/app/oracle/oradata/dup/rmantbs01.dbf";
 backup as copy reuse
 datafile 1 auxiliary format
 "/home/oracle/app/oracle/oradata/dup/system01.dbf" datafile
 2 auxiliary format
 "/home/oracle/app/oracle/oradata/dup/sysaux01.dbf" datafile
 3 auxiliary format
 "/home/oracle/app/oracle/oradata/dup/undotbs01.dbf" datafile
 4 auxiliary format
 "/home/oracle/app/oracle/oradata/dup/users01.dbf" datafile
 5 auxiliary format
 "/home/oracle/app/oracle/oradata/dup/example01.dbf" datafile
 6 auxiliary format
 "/home/oracle/app/oracle/oradata/dup/rmantbs01.dbf" ;
 sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 01-MAR-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001
name=/home/oracle/app/oracle/oradata/prd/system01.dbf
output file name=/home/oracle/app/oracle/oradata/dup/system01.dbf
tag=TAG20130301T124336
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:06:38
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002
name=/home/oracle/app/oracle/oradata/prd/sysaux01.dbf
output file name=/home/oracle/app/oracle/oradata/dup/sysaux01.dbf
tag=TAG20130301T124336
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:06:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005
name=/home/oracle/app/oracle/oradata/prd/example01.dbf
output file name=/home/oracle/app/oracle/oradata/dup/example01.dbf
tag=TAG20130301T124336
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:05:18
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003
name=/home/oracle/app/oracle/oradata/prd/undotbs01.dbf
output file name=/home/oracle/app/oracle/oradata/dup/undotbs01.dbf
tag=TAG20130301T124336
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006
name=/home/oracle/app/oracle/oradata/prd/rmantbs01.dbf
output file name=/home/oracle/app/oracle/oradata/dup/rmantbs01.dbf
tag=TAG20130301T124336
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004
name=/home/oracle/app/oracle/oradata/prd/users01.dbf
output file name=/home/oracle/app/oracle/oradata/dup/users01.dbf
tag=TAG20130301T124336
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 01-MAR-13

sql statement: alter system archive log current

contents of Memory Script:
{
 backup as copy reuse
 archivelog like
"/home/oracle/app/oracle/fast_recovery_area/PRD/archivelog/2013_03_01/o1_mf_1_26_8m0pf583_.arc"
auxiliary format
 "/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_26_804290346.dbf"
;
 catalog clone archivelog
"/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_26_804290346.dbf";
 switch clone datafile all;
}
executing Memory Script

Starting backup at 01-MAR-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=26 RECID=26 STAMP=808923777
output file
name=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_26_804290346.dbf
RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:07
Finished backup at 01-MAR-13

cataloged archived log
archived log file
name=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_26_804290346.dbf
RECID=26 STAMP=808923794

datafile 1 switched to datafile copy
input datafile copy RECID=9 STAMP=808923795 file
name=/home/oracle/app/oracle/oradata/dup/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=10 STAMP=808923795 file
name=/home/oracle/app/oracle/oradata/dup/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=11 STAMP=808923795 file
name=/home/oracle/app/oracle/oradata/dup/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=12 STAMP=808923795 file
name=/home/oracle/app/oracle/oradata/dup/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=13 STAMP=808923795 file
name=/home/oracle/app/oracle/oradata/dup/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=14 STAMP=808923795 file
name=/home/oracle/app/oracle/oradata/dup/rmantbs01.dbf

contents of Memory Script:
{
 set until scn 1134180;
 recover
 clone database
 delete archivelog
 ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 01-MAR-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK

starting media recovery

archived log for thread 1 with sequence 26 is already on disk as file
/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_26_804290346.dbf
archived log file
name=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_26_804290346.dbf
thread=1 sequence=26
media recovery complete, elapsed time: 00:00:02
Finished recover at 01-MAR-13
Oracle instance started

Total System Global Area 372449280 bytes

Fixed Size 1345044 bytes
Variable Size 239077868 bytes
Database Buffers 125829120 bytes
Redo Buffers 6197248 bytes

contents of Memory Script:
{
 sql clone "alter system set db_name =
 ''DUP'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
 sql clone "alter system reset db_unique_name scope=spfile";
 shutdown clone immediate;
 startup clone nomount;
}
executing Memory Script

sql statement: alter system set db_name = ''DUP'' comment= ''Reset to
original value by RMAN'' scope=spfile

sql statement: alter system reset db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 372449280 bytes

Fixed Size 1345044 bytes
Variable Size 239077868 bytes
Database Buffers 125829120 bytes
Redo Buffers 6197248 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUP" RESETLOGS
ARCHIVELOG
 MAXLOGFILES 16
 MAXLOGMEMBERS 3
 MAXDATAFILES 100
 MAXINSTANCES 8
 MAXLOGHISTORY 292
 LOGFILE
 GROUP 1 ( '/home/oracle/app/oracle/oradata/dup/redo01.log' ) SIZE 50 M
REUSE,
 GROUP 2 ( '/home/oracle/app/oracle/oradata/dup/redo02.log' ) SIZE 50 M
REUSE,
 GROUP 3 ( '/home/oracle/app/oracle/oradata/dup/redo03.log' ) SIZE 50 M
REUSE
 DATAFILE
 '/home/oracle/app/oracle/oradata/dup/system01.dbf'
 CHARACTER SET AL32UTF8
contents of Memory Script:
{
 set newname for tempfile 1 to
 "/home/oracle/app/oracle/oradata/dup/temp01.dbf";
 switch clone tempfile all;
 catalog clone datafilecopy
"/home/oracle/app/oracle/oradata/dup/sysaux01.dbf",
 "/home/oracle/app/oracle/oradata/dup/undotbs01.dbf",
 "/home/oracle/app/oracle/oradata/dup/users01.dbf",
 "/home/oracle/app/oracle/oradata/dup/example01.dbf",
 "/home/oracle/app/oracle/oradata/dup/rmantbs01.dbf";
 switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /home/oracle/app/oracle/oradata/dup/temp01.dbf in
control file

cataloged datafile copy
datafile copy file name=/home/oracle/app/oracle/oradata/dup/sysaux01.dbf
RECID=1 STAMP=808923861
cataloged datafile copy
datafile copy file name=/home/oracle/app/oracle/oradata/dup/undotbs01.dbf
RECID=2 STAMP=808923861
cataloged datafile copy
datafile copy file name=/home/oracle/app/oracle/oradata/dup/users01.dbf
RECID=3 STAMP=808923861
cataloged datafile copy
datafile copy file name=/home/oracle/app/oracle/oradata/dup/example01.dbf
RECID=4 STAMP=808923861
cataloged datafile copy
datafile copy file name=/home/oracle/app/oracle/oradata/dup/rmantbs01.dbf
RECID=5 STAMP=808923861

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=808923861 file
name=/home/oracle/app/oracle/oradata/dup/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=808923861 file
name=/home/oracle/app/oracle/oradata/dup/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=808923861 file
name=/home/oracle/app/oracle/oradata/dup/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=808923861 file
name=/home/oracle/app/oracle/oradata/dup/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=808923861 file
name=/home/oracle/app/oracle/oradata/dup/rmantbs01.dbf

contents of Memory Script:
{
 Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 01-MAR-13

Duplicate database is successfully created.

Leave a comment

Beyond the Basics : File Permissions in Unix/Linux

A user’s ability to read from or write to files on a UNIX system depends on the permissions that have been granted for that file by the owner of the file or directory.

The user who creates a file is the owner of that file. Every file and directory comes with three types of permissions:
• Read: Lets you view the contents of the file only.
• Write: Lets you change the contents of the file. Write permission on a directory will let you create, modify, or delete files in that directory.
• Execute: Lets you execute (run) the file if the file contains an executable program (script).
Read permission is the most basic permission. Having the execute permission without the read permission is of no use,you can’t execute a file if you can’t read it in the first place.

Use the ls -al command to list the file permissions along with the filenames in a directory. For example, look at the (partial) output of the following command:

$ ls -al
-rwxrwxrwx 1 oracle dba 320 Jan 23 09:00 test.ksh
-rw-r—r- 1 oracle dba 152 Jul 18 13:38 updown.ksh
-rw-r—r- 1 oracle dba 70 Nov 22 01:30 tokill.ksh

You’ll notice that at the beginning of each line, each file has a combination of ten different letters and the blank sign (-).
The first letter could be a blank or the letter d. If it is the letter d, then it’s a directory. If it’s a blank, it’s a regular file.
The next nine spaces are grouped into three sets of the letters rwx. The rwx group refers to the read, write, and execute permissions on that file. The first set of rwx indicates the permissions assigned to the owner of the file. The second set lists the permissions assigned to the group the user belongs to. The last set lists the permissions on that file granted to all the other users of the system.

For example, consider the access permissions on the following file:
$ -rwxr-x–x 1 oracle dba Nov 11 2001 test.ksh

Because the first character is a hyphen (-), this is a file, not a directory. The next three characters, rwx, indicate that the owner of the file test.ksh has all three permissions (read, write, and execute) on the file. The next three characters, r-x, show that all the users who are in the same group as the owner have read and execute permissions, but not write permissions. In other words, they cannot change the contents of the file. The last set of characters, –x, indicates that all other users on the system can execute the file, but they cannot modify it.

Any file that you create will first have the permissions set to -rw-r–r–. That is, everybody has read permissions, and no user has permission to execute the file. If you put an executable program inside the file, you’ll want to grant someone permission to execute the file. You can set the permissions on the file by using the chmod command in one of two ways.
First, you can use the symbolic notation, with the letter o standing for owner, g for group, and u for other users on the system. You grant a group or users specific permissions by first specifying the entity along with a plus sign (+) followed by the appropriate symbol for the permission.

In the following example, the notation go+x means that both the group and others are assigned the execute (x) permission on the test.ksh shell script:

$ chmod go+x test.ksh

The next example shows how you can use symbolic notation to remove read and write permissions on a file from the group:

$ chmod g-rw test.ksh

Second, you can use the octal numbers method to change file permissions. Each permission carries different numeric “weights”: read carries a weight of 4, write a weight of 2, and execute a weight of 1. To determine a permission setting, just add the weights for the permissions you want to assign. The highest number that can be associated with each of the three different entities—owner,group, and all others—is 7, which is the same as having read, write, and execute permissions on the file.

For example, consider the following:

$ chmod 777 test.txt
$ ls
$ -rwxrwxrwx 1 oracle dba 102 Nov 11 15:20 test.txt

The file test.txt initially had its file permissions set to 644 (rw, r, r.) The command chmod 777 assigned full permissions (read, write, and execute) to all three entities: owner, group, and all others.
If you want to change this so that only the owner has complete rights and the others have no permissions at all, set the octal number to 700 (read, write, and execute permissions for the owner, and no permissions at all for the group or others) and use the chmod command as follows:

$ chmod 700 test.txt
$ ls -altr test.txt
-rwx—— 1 oracle dba 0 Mar 28 11:23 test.txt

 


Leave a comment

Oracle 11g Virtual columns

I read an article regarding the virtual columns in oracle 11g . i wish to share this article in  this blog.

Thanks to Arup Nanda for his excellent presentation.

Acme’s database contains a table called SALES, as you saw earlier. The table has the following structure:

SALES_ID NUMBER
CUST_ID NUMBER
SALES_AMT NUMBER

Some users want to add a column called SALE_CATEGORY, which identifies the type of the sale: LOW, MEDIUM, HIGH and ULTRA, depending on the amount of sale and the customer in question. This column will help them identify the records for appropriate action and routing to the concerned employee for handling. Here are the logic for values in the column:

If sale_amt is more than: And sale_amt is less than or equal to: Then sale_category is:
0 1000 LOW
10001 100000 MEDIUM
100001 1000000 HIGH
1000001 Unlimited ULTRA

Although this column is a crucial business requirement, the development team does not want to change the code to create the necessary logic. Of course, you could add a new column in the table called sale_category, and write a trigger to populate the column using the logic shown above—a fairly trivial exercise. But performance issues would arise due to context switching from and into the trigger code.

In Oracle Database 11g, you do not need to write a single line of code in any trigger. All you have to do instead is add a virtual column. Virtual columns offer the flexibility to add columns that convey business sense without adding any complexity or performance impact.

Here’s how you would create this table:

SQL> create table sales
  2  (
  3     sales_id      number,
  4     cust_id       number,
  5     sales_amt     number,
  6     sale_category varchar2(6)
  7     generated always as
  8     (
  9        case
 10           when sales_amt <= 10000 then 'LOW'
 11           when sales_amt > 10000 and sales_amt <= 100000 then 'MEDIUM'
 12           when sales_amt > 100000 and sales_amt <= 1000000 then 'HIGH'
 13           else 'ULTRA'
 14        end
 15      ) virtual
 16  );

Note lines 6-7; the column is specified as “generated always as”, meaning the column values are generated at runtime, not stored as part of the table. That clause is followed by how the value is calculated in the elaborate CASE statement. Finally, in line 15,”virtual” is specified to reinforce the fact that this is a virtual column. Now, if you insert some records:

SQL> insert into sales (sales_id, cust_id, sales_amt) values (1,1,100);

1 row created.

SQL> insert into sales (sales_id, cust_id, sales_amt) values (2,102,1500);

1 row created.

SQL>insert into sales (sales_id, cust_id, sales_amt) values (3,102,100000);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from sales;

  SALES_ID    CUST_ID  SALES_AMT SALE_C
---------- ---------- ---------- ------
         1          1        100 LOW
         2        102       1500 LOW
         3        102     100000 MEDIUM

3 rows selected.

the virtual column values are all populated as usual. Even though this column is not stored, you can refer to it as any other column in the table. You can even create indexes on it.

SQL> create index in_sales_cat on sales (sale_category);

Index created.

The result will be a function-based index.

SQL> select index_type
  2  from user_indexes
  3  where index_name = 'IN_SALES_CAT';

INDEX_TYPE
---------------------------
FUNCTION-BASED NORMAL

SQL>  select column_expression
  2  from user_ind_expressions
  3  where index_name = 'IN_SALES_CAT';

COLUMN_EXPRESSION
--------------------------------------------------------------------------------
CASE  WHEN "SALES_AMT"<=10000 THEN 'LOW' WHEN ("SALES_AMT">10000 AND "SALES_AMT"
<=100000) THEN CASE  WHEN "CUST_ID"<101 THEN 'LOW' WHEN ("CUST_ID">=101 AND "CUS
T_ID"<=200) THEN 'MEDIUM' ELSE 'MEDIUM' END  WHEN ("SALES_AMT">100000 AND "SALES
_AMT"<=1000000) THEN CASE  WHEN "CUST_ID"<101 THEN 'MEDIUM' WHEN ("CUST_ID">=101
 AND "CUST_ID"<=200) THEN 'HIGH' ELSE 'ULTRA' END  ELSE 'ULTRA' END

You can even partition on this column, as you saw in the Partitioning installment of this series. You can’t, however, enter a value for this column. If you try to, you won’t get far:

insert into sales values (5,100,300,'HIGH','XX')
            *
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns

Leave a comment

PL/SQL Wrapper utility

As a application developer  we do not always want our code displayed to the outside world
when writing an application. In some cases, the features are proprietary and we
need to protect the intellectual capital that is invested in them. In other cases, we
simply want to prevent the code from being modified by the user in order to avoid
problems .

Oracle provides a way to hide code with the PL/SQL Wrapper utility. When
source code has been wrapped, not only is the file unreadable, but also when it
is loaded into the database, the code cannot be read in the data dictionary.

The wrapper utility does not encrypt the code. Instead, it converts it to
hexadecimal digits so that it cannot be read or edited.

example:

SQL> CREATE TABLE lecturer (
2       id               NUMBER(5) PRIMARY KEY,
3       first_name       VARCHAR2(20),
4       last_name        VARCHAR2(20),
5       major            VARCHAR2(30),
6       current_credits  NUMBER(3)
7       );

Table created.

SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2   VALUES (10001, ‘Scott’, ‘Lawson’,’Computer Science’, 11);

1 row created.
SQL>  INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2   VALUES (10002, ‘Mar’, ‘Wells’,’History’, 4);

1 row created.
SQL>  INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2   VALUES (10003, ‘Jone’, ‘Bliss’,’Computer Science’, 8);

1 row created.
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2  VALUES (10004, ‘Man’, ‘Kyte’,’Economics’, 8);

1 row created.
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2  VALUES (10005, ‘Pat’, ‘Poll’,’History’, 4);

1 row created.
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2  VALUES (10006, ‘Tim’, ‘Viper’,’History’, 4);

1 row created.
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2  VALUES (10007, ‘Barbara’, ‘Blues’,’Economics’, 7);

1 row created.
SQL>  INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2   VALUES (10008, ‘David’, ‘Large’,’Music’, 4);

1 row created.
SQL>  INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2   VALUES (10009, ‘Chris’, ‘Elegant’,’Nutrition’, 8);

1 row created.
SQL>  INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2   VALUES (10010, ‘Rose’, ‘Bond’,’Music’, 7);

1 row created.
SQL>  INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2   VALUES (10011, ‘Rita’, ‘Johnson’,’Nutrition’, 8);

1 row created.
SQL>  INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2   VALUES (10012, ‘Sharon’, ‘Clear’,’Computer Science’, 3);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from lecturer;

ID FIRST_NAME           LAST_NAME            MAJOR                          CURRENT_CREDITS
———- ——————– ——————– —————————— —————
10001 Scott                Lawson               Computer Science                            11
10002 Mar                  Wells                History                                      4
10003 Jone                 Bliss                Computer Science                             8
10004 Man                  Kyte                 Economics                                    8
10005 Pat                  Poll                 History                                      4
10006 Tim                  Viper                History                                      4
10007 Barbara              Blues                Economics                                    7
10008 David                Large                Music                                        4
10009 Chris                Elegant              Nutrition                                    8
10010 Rose                 Bond                 Music                                        7
10011 Rita                 Johnson              Nutrition                                    8

ID FIRST_NAME           LAST_NAME            MAJOR                          CURRENT_CREDITS
———- ——————– ——————– —————————— —————
10012 Sharon               Clear                Computer Science                             3

12 rows selected.

i have a file named newrec which has the following stored procedure,

D:\>type newrec.sql
CREATE OR REPLACE PROCEDURE AddNewStudent (
p_FirstName  lecturer.first_name%TYPE,
p_LastName   lecturer.last_name%TYPE,
p_Major      lecturer.major%TYPE) AS
BEGIN
INSERT INTO lecturer (ID, first_name, last_name,
major, current_credits)
VALUES (1, p_FirstName, p_LastName,p_Major, 0);
END AddNewStudent;
/

Now i am going to wrap this file by using the pl/sql wrapper utility.

D:\>wrap iname=newrec.sql oname=newrec_wrap.plb

PL/SQL Wrapper: Release 10.2.0.4.0- Production on Wed Mar 24 15:11:28 2010

Copyright (c) 1993, 2004, Oracle.  All rights reserved.

Processing newrec.sql to newrec_wrap.plb

Now the warped file looks like

D:\>type newrec_wrap.plb
CREATE OR REPLACE PROCEDURE AddNewStudent wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
13c 10b
Ud07xFlyvsFfWzUuUo/ECDS1uwMwgzLQf5kVfC8CkE7VSPOjV6LB3C2tbCWH2WzMbkWgCl6V
nSCroGKsIM/Pz7vijegUtkZn/gAolngi+O5/5ugOzdwrbhdMtmfpY9CJigmxLkM8mroV9ArS
H7pTJvivJxDeNgnJVjK9+jXaRIB7uiunpPRb2bxYzIXYNVSL84v0EbW3GoAW1uctu/KoC+3Z
PVzqN3aRW3Gd9AoPpceWFSKWUU+0qd9AKYvPfMwrfhnBkGlD

/

Now this wrapper file will get compiled without any errors.

SQL> @D:\newrec_wrap.plb

Procedure created.

Even in the data dictionary only your wrapped code exists.
SQL> select text from user_source where NAME=’ADDNEWSTUDENT';

TEXT
——————————————————————————–
PROCEDURE AddNewStudent wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd

TEXT
——————————————————————————–
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
13c 10b
Ud07xFlyvsFfWzUuUo/ECDS1uwMwgzLQf5kVfC8CkE7VSPOjV6LB3C2tbCWH2WzMbkWgCl6V
nSCroGKsIM/Pz7vijegUtkZn/gAolngi+O5/5ugOzdwrbhdMtmfpY9CJigmxLkM8mroV9ArS

TEXT
——————————————————————————–
H7pTJvivJxDeNgnJVjK9+jXaRIB7uiunpPRb2bxYzIXYNVSL84v0EbW3GoAW1uctu/KoC+3Z
PVzqN3aRW3Gd9AoPpceWFSKWUU+0qd9AKYvPfMwrfhnBkGlD

2 Comments

Oracle 10g -Merge enhancements

Today , i have learned about the enhancements in the oracle 10g merge statement  from oracle-developer.net website.Thanks to Adrian Billington , Oracle developer site moderator for sharing this information with us.

The MERGE statement was introduced in Oracle 9i as part of Oracle’s ETL-enabling technology. It enables us to conditionally update or insert data into a target table and in many cases is more flexible and efficient than previous hand-coded “UPSERT” solutions. With the release of 10g, Oracle has added many enhancements to MERGE, making it a considerably more flexible and powerful statement than its 9i predecessor. For example, MERGE can now UPDATE, DELETE and INSERT with separate conditions for each. It also supports UPDATE-only or INSERT-only operations. We’ll examine such improvements in this article.

setup

Throughout this article, we’ll be using a source and target table to perform the MERGE examples. The target table will have a subset of the source table’s data, meaning that with any MERGE, there will be an “80-20″ mix of UPDATEs and INSERTs. Note that the target table is modified to enable NULL in all columns. This will enable us to keep the column lists short for the INSERT operations in the examples.

SQL> CREATE TABLE src AS SELECT * FROM all_objects WHERE ROWNUM <= 100;
Table created.
SQL> CREATE TABLE tgt AS SELECT * FROM src WHERE ROWNUM <= 80;
Table created.
SQL> ALTER TABLE tgt MODIFY
  2  ( owner         NULL
  3  , object_name   NULL
  4  , object_id     NULL
  5  , created       NULL
  6  , last_ddl_time NULL
  7  );
Table altered.

recap: merge in oracle 9i

We’ll begin by demonstrating a straight MERGE supported by Oracle 9i. We’ll pick an arbitrary operation for UPDATE (in this case setting the OBJECT_NAME to lower case). The DML operations in this example are of little consequence; rather it is the syntax that is important.

SQL> MERGE
  2     INTO  tgt
  3     USING src
  4     ON   (src.object_id = tgt.object_id)
  5  WHEN MATCHED
  6  THEN
  7     UPDATE
  8     SET tgt.object_name = LOWER(src.object_name)
  9  WHEN NOT MATCHED
 10  THEN
 11     INSERT (tgt.object_id, tgt.object_name)
 12     VALUES (src.object_id, src.object_name);

100 rows merged.

This tells us that we’ve merged 100 records. From the setup of the data, we’d expect there to be 80 lower-cased object names in the target table. We can check this with a simple regular expression as follows.

SQL> SELECT COUNT(*)
  2  FROM   tgt
  3  WHERE  REGEXP_LIKE(object_name,'[a-z]');
  COUNT(*)
----------
        80

1 row selected.

Now we’ve revisited the MERGE statement as supported by Oracle 9i, we can move onto the 10g new features and enhancements.

optional insert or update in 10g

In Oracle 10g, either the INSERT or UPDATE operations are optional (you must have one, but not necessarily both, unlike in 9i). This is a critical enhancement and one which makes the MERGE statement much more flexible. In particular, this new feature could make updates with correlated sub-queries a thing of the past as MERGE is easier to code, easier to understand, more flexible, less error-prone and faster. In the following example, we’ll MERGE source into target but ignore new rows by leaving out the INSERT operation. Note how much easier this is to code and read than a “double-correlated UPDATE”.

SQL> MERGE
  2     INTO  tgt
  3     USING src
  4     ON   (src.object_id = tgt.object_id)
  5  WHEN MATCHED
  6  THEN
  7     UPDATE
  8     SET tgt.object_name = LOWER(src.object_name);
80 rows merged.

The following example demonstrates the opposite by leaving out the UPDATE operation. This is the logical equivalent of a conditional INSERT..SELECT statement.

SQL> MERGE
  2     INTO  tgt
  3     USING src
  4     ON   (src.object_id = tgt.object_id)
  5  WHEN NOT MATCHED
  6  THEN
  7     INSERT (tgt.object_id, tgt.object_name)
  8     VALUES (src.object_id, src.object_name);
20 rows merged.

In addition to the conditional INSERT..SELECT above, MERGE also supports unconditional INSERT..SELECT by way of the 1=0 condition which Oracle calls a constant filter predicate. The purpose of this is to avoid a join between source and target. Whether this will draw developers away from coding a straightforward INSERT..SELECT statement will, of course, be a matter of personal preference!

SQL> MERGE
  2     INTO  tgt
  3     USING src
  4     ON   (1 = 0)    --constant filter predicate to avoid join
  5  WHEN NOT MATCHED
  6  THEN
  7     INSERT (tgt.object_id, tgt.object_name)
  8     VALUES (src.object_id, src.object_name);

100 rows merged.

We can see that the unconditional INSERT..SELECT that this example implies has loaded all records from source to target. The lack of keys or constraints on the demonstration tables has allowed this to happen, though in a real system, this would be likely to fail.

conditional dml

With Oracle 10g, we can can now apply additional conditions to the UPDATE or INSERT operation within a MERGE. This is extremely useful if we have different rules for when a record is updated or inserted but we do not wish to restrict the ON condition that joins source and target together. To explain this another way, any filters added to the ON clause, other than the primary join conditions, could increase the likelihood that a record flips over to the “NOT MATCHED” bucket. This would in all probability be wrong and considered a bug.

We can see this in the following example. We MERGE source to target but only wish to UDPATE or INSERT specific matched records. We simply add a suitable predicate (WHERE clause) to the UPDATE and INSERT statements within the MERGE. This does not affect the join between the source and target data in any way so does not change the nature of the MERGE.

SQL> MERGE
  2     INTO  tgt
  3     USING src
  4     ON   (src.object_id = tgt.object_id)
  5  WHEN MATCHED
  6  THEN
  7     UPDATE
  8     SET tgt.object_name = LOWER(src.object_name)
  9     WHERE src.object_type = 'PACKAGE'
 10  WHEN NOT MATCHED
 11  THEN
 12     INSERT (tgt.object_id, tgt.object_name)
 13     VALUES (src.object_id, src.object_name)
 14     WHERE src.created > TRUNC(SYSDATE) - 365;
20 rows merged.

We can see that these predicates have restricted the number of records merged.

deleting during a merge

Possibly the most unexpected 10g enhancement to MERGE is the ability to conditionally DELETE rows from the target dataset during an UPDATE operation. There are a couple of important points to note regarding this. First, the DELETE works against conditions on the target data, not the source. Second, the DELETE works only on rows that have been updated as a result of the MERGE. Any rows in the target table that are not touched by the MERGE are not deleted, even if they satisfy the DELETE criteria.

We’ll begin with a simple example of the DELETE operation. We’ll remove any records that are in both the source and target data (i.e. WHEN MATCHED) that are of OBJECT_TYPE = ‘TYPE’.

SQL> MERGE
  2     INTO  tgt
  3     USING src
  4     ON   (src.object_id = tgt.object_id)
  5  WHEN MATCHED
  6  THEN
  7     UPDATE
  8     SET tgt.object_name = LOWER(src.object_name)
  9     DELETE
 10     WHERE tgt.object_type = 'TYPE'
 11  WHEN NOT MATCHED
 12  THEN
 13     INSERT (tgt.object_id, tgt.object_name)
 14     VALUES (src.object_id, src.object_name);

100 rows merged.

We can now look a bit more closely at the MERGE-UPDATE-DELETE operation and what data it affects. First, we’ll populate the target table with new data and add some of the same records to the source data to ensure we have matches.

SQL> TRUNCATE TABLE tgt;
Table truncated.
SQL> TRUNCATE TABLE src;
Table truncated.
SQL> INSERT ALL
  2     WHEN object_type IN ( 'PACKAGE', 'PACKAGE BODY' )
  3     THEN INTO tgt
  4     WHEN object_type IN ( 'PACKAGE', 'PACKAGE BODY' )
  5     AND  object_name LIKE 'DBMS%'
  6     THEN INTO src
  7  SELECT *
  8  FROM   all_objects;
2320 rows created.
SQL> SELECT object_type
  2  ,      COUNT(*)
  3  FROM   src
  4  GROUP  BY
  5         object_type;
OBJECT_TYPE           COUNT(*)
------------------- ----------
PACKAGE                    374
PACKAGE BODY               367

2 rows selected.
SQL> SELECT object_type
  2  ,      COUNT(*)
  3  FROM   tgt
  4  GROUP  BY
  5         object_type;
OBJECT_TYPE           COUNT(*)
------------------- ----------
PACKAGE                    816
PACKAGE BODY               763

2 rows selected.

Using ALL_OBJECTS data, we’ve setup the example such that the source and target tables contain data relating only to packages. The source table has a subset of the target data and only contains packages that begin with ‘DBMS’. The target table contains data for all packages. Using this data, we can easily demonstrate the effect of a conditional DELETE. In the following example, we’ll MERGE the source table into the target table but include a DELETE of any matched records that have an OBJECT_TYPE = ‘PACKAGE’. For simplicity, we’ll use an update-only MERGE.

SQL> MERGE
  2     INTO  tgt
  3     USING src
  4     ON   (src.object_id = tgt.object_id)
  5  WHEN MATCHED
  6  THEN
  7     UPDATE
  8     SET tgt.object_name = LOWER(src.object_name)
  9     DELETE
 10     WHERE tgt.object_type = 'PACKAGE';

741 rows merged.

Remember that the DELETE condition should only be tested against records that match. In this case, the only records that should match are those that begin with ‘DBMS’ (these were the only packages in the source table). Following the MERGE, therefore, we should have no packages in our target table that begin with ‘DBMS’. The records in the target table that did not have a matching source record should remain. We can test this by counting both the number of packages and just those that begin with ‘DBMS’ as below.

SQL> SELECT SUM( CASE object_type
  2                 WHEN 'PACKAGE'
  3                 THEN 1
  4                 ELSE 0
  5              END ) AS package_records
  6  ,      SUM( CASE
  7                 WHEN object_type = 'PACKAGE'
  8                 AND  object_name LIKE 'DBMS%'
  9                 THEN 1
 10                 ELSE 0
 11              END ) AS dbms_package_records
 12  FROM   tgt;
PACKAGE_RECORDS DBMS_PACKAGE_RECORDS
--------------- --------------------
            442                    0

1 row selected.

As we can see, despite requesting a DELETE for OBJECT_TYPE = ‘PACKAGE’, we still have 442 records of this type in the target table. As stated, this data does not have a match in the source data and therefore is not subjected to the DELETE.

To demonstrate that we did get some updates, we can look at the data that would have matched during the MERGE but would not have satisfied the DELETE criteria. This time, we expect this to be package body records that begin with ‘DBMS’. For this data, we would expect the OBJECT_NAME to be lower-cased as a result of the MERGE. We can look at a small sample as below.

SQL> SELECT object_name
  2  FROM   tgt
  3  WHERE  object_type = 'PACKAGE BODY'
  4  AND    object_name LIKE 'dbms%'
  5  AND    ROWNUM <= 3;
OBJECT_NAME
------------------------------
dbms_aq_exp_queues
dbms_prvtaqip
dbms_prvtaqis

3 rows selected.

Leave a comment

Follow

Get every new post delivered to your Inbox.