RMAN Duplicate database from Active database
Posted by Mohammed Mehraj Hussain in Database, RMAN on March 1, 2013
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.
Beyond the Basics : File Permissions in Unix/Linux
Posted by Mohammed Mehraj Hussain in Database on November 11, 2010
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
Oracle 11g Virtual columns
Posted by Mohammed Mehraj Hussain in Database on April 6, 2010
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
PL/SQL Wrapper utility
Posted by Mohammed Mehraj Hussain in Database on March 24, 2010
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
Oracle 10g -Merge enhancements
Posted by Mohammed Mehraj Hussain in Database on March 15, 2010
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.
steps to Migrate ASM diskgroups from one SAN to another SAN
Posted by Mohammed Mehraj Hussain in Database on October 28, 2009
1) Backup all your databases and valid the backup .
2) Add the new path (new disks from the new storage) to your asm_disktring to be recognized by ASM:
Example:
SQL> alter system set asm_disktring = ‘/dev/dellpowerc*’ , ‘/dev/dellpowerh*’;
Where: ‘/dev/dellpowerc*’ are the current disks.
Where: ‘/dev/dellpowerh*’ are the new disks.
3) Confirm that the new disks are being detected by ASM:
SQL> select path from v$asm_disk;
4) Add the new disks to your desired diskgroup:
SQL> alter diskgroup <diskgroup name> add disk
‘<new disk 1>’,
‘<new disk 2>’,
‘<new disk 3>’,
‘<new disk 4>’,
.
.
.
‘<new disk N>’;
5) Then wait until the rebalance operation completes:
SQL> select * from v$asm_operation;
6) Finally, remove the old disks:
SQL> alter diskgroup <diskgroup name> drop disk
<disk name A>,
<disk name B>,
<disk name D>,
<disk name E>,
.
.
.
<disk name X>;
7) Then wait until the rebalance operation completes:
SQL> select * from v$asm_operation;
8) Done, your ASM diskgroups and database have been migrated to the new storage.
Note: Alternatively, we can execute add disk & drop disk statements in one operation, in that way only one rebalance operation will be started as follow:
SQL> alter diskgroup <diskgroup name>
add disk ‘<new device physical name 1>’, .., ‘<new device physical name N>’
drop disk <old disk logical name 1>, <old disk logical name 2>, ..,<old disk logical name N>
rebalance <#>;
This is more efficient than separated commands (add disk & drop disk statements).
Oracle Vs IBM
Posted by Mohammed Mehraj Hussain in Database on September 11, 2009
Wow …..
It will be a great challenge for Oracle to swipe IBM in Hardware Business.
And We can expect a Large improvements in Solaris and Sparc as Larry said.
