OraPL

Tuesday, 17 January 2012

Oracle Collections in SQL Part1

Well, We have heard a lot about advanced PL/SQL and particularly about Collection(s) of Oracle. Collection(s) comprise Nested Table, Varray and Index By Tables. Each has its own way of working. Most of time, these Collections are used in PL/SQL program. Eg Function, Procedure, Package.

It is not very often when we see any developer using these collections in SQL statement. Main reason could be because they themselves have not seen actually someone using it. Using Collections in SQL statements add a little more complexity in statement structure so many developer(s) try to skip this and use their old and proven methodology.

In this post , we will discuss exact method to use Nested Table in SQL statement and performance impact on queries using such tables. In first part of this post we will discuss actual working of this and in second post we will discuss performance impact.

Not all three collections could be used in SQL. We can use only Nested Table and Varray in SQL statement. For to be used in SQL statement these Collection / Types should be stored as an object in Database.

SQL> select banner from v$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

Step:- 1

Define a Object type and Map method. Following, I have created a type of Object Kind and defined a MAP method for comparison purpose.

SQL> CREATE TYPE SCOTT.COURSE_OBJ AS OBJECT

2 (

3 STU_ID NUMBER,

4 COURSE_ID NUMBER,

5 COURSE_NAME VARCHAR2(40),

6 MAP MEMBER FUNCTION MAP1 RETURN NUMBER);

7 /

1 CREATE TYPE BODY SCOTT.COURSE_OBJ AS

2 MAP MEMBER FUNCTION MAP1 RETURN NUMBER IS

3 BEGIN

4 RETURN STU_ID||COURSE_ID;

5 END MAP1;

6* END;

Step 2:- Created a TYPE of Nested Table type and used Course_Obj Object Type that we created in Step 1.

SQL> CREATE TYPE SCOTT.COURSE_NST_TB AS TABLE OF SCOTT.COURSE_OBJ;

2 /

Type created.

Step:- 3 Created a table and reference Nested Table (Scott.Course_Nst_Tb) as a column in table. See the STORE AS clause of the Table definition. We can use USER_TYPES dictionary table to check information

Of TYPES created by user.

SQL> CREATE TABLE SCOTT.STU_COURSE_INFO(STU_ID NUMBER,STU_NAME VARCHAR2(40),COURSE_INFO SCOTT.COURSE_NST_TB)

NESTED TABLE COURSE_INFO STORE AS COURSE_INFO;

Table created.

Elapsed: 00:00:00.29

Insert into Scott.Stu_Course_info

SQL> Insert Into Scott.Stu_Course_info Values (1,'ABC',scott.course_nst_tb(scott.course_OBJ(1,'Eelementry English')));

Insert into scott.Stu_course_info values (103,'SBX',scott.COURSE_NST_TB(scott.COURSE_OBJ(103,1001,'Elementry Physics')))

Insert into scott.Stu_course_info values (103,'SBX',scott.COURSE_NST_TB(scott.COURSE_OBJ(103,1002,'Elementry Science')))

Insert into scott.Stu_course_info values (103,'SBX',scott.COURSE_NST_TB(scott.COURSE_OBJ(103,1003,'Elementry Math')))

Insert into scott.Stu_course_info values (103,'SBX',scott.COURSE_NST_TB(scott.COURSE_OBJ(103,1004,'Elementry Chemistry')))

Insert into scott.Stu_course_info values (103,'SBX',scott.COURSE_NST_TB(scott.COURSE_OBJ(103,1005,'Elementry Pyscology')))

Insert into scott.Stu_course_info values (102,'ABC',scott.COURSE_NST_TB(scott.COURSE_OBJ(102,1001,'Elementry Physics')))

Insert into scott.Stu_course_info values (102,'ABC',scott.COURSE_NST_TB(scott.COURSE_OBJ(102,1002,'Elementry Science')))

Insert into scott.Stu_course_info values (102,'ABC’,scott.COURSE_NST_TB(scott.COURSE_OBJ(102,1003,'Elementry Math')))

Insert into scott.Stu_course_info values (102,'ABC',scott.COURSE_NST_TB(scott.COURSE_OBJ(102,1004,'Elementry Chemistry')))

Insert into scott.Stu_course_info values (102,'ABC',scott.COURSE_NST_TB(scott.COURSE_OBJ(102,1005,'Elementry Pyscology')))

(101,'XYZ',scott.COURSE_NST_TB(scott.COURSE_OBJ(101,1001,'Elementry Physics')))

Insert into scott.Stu_course_info values (101,'XYZ',scott.COURSE_NST_TB(scott.COURSE_OBJ(101,1002,'Elementry Science')))

Insert into scott.Stu_course_info values (101,'XYZ’,scott.COURSE_NST_TB(scott.COURSE_OBJ(101,1003,'Elementry Math')))

Insert into scott.Stu_course_info values (101,'XYZ',scott.COURSE_NST_TB(scott.COURSE_OBJ(101,1004,'Elementry Chemistry')))

Insert into scott.Stu_course_info values (101,'XYZ',scott.COURSE_NST_TB(scott.COURSE_OBJ(101,1005,'Elementry Pyscology')))

Elapsed: 00:00:00.01

SQL> Commit ;

Commit complete.

Elapsed: 00:00:00.00

SELECT * FROM SCOTT.STU_COURSE_INFO where stu_id='101'

STU_ID STU_NAME COURSE_INFO

101 XYZ SCOTT.COURSE_NST_TB('SCOTT.COURSE_OBJ(101,1001,'Elementry Physics')')

101 XYZ SCOTT.COURSE_NST_TB('SCOTT.COURSE_OBJ(101,1002,'Elementry Science')')

101 XYZ SCOTT.COURSE_NST_TB('SCOTT.COURSE_OBJ(101,1003,'Elementry Math')')

101 XYZ SCOTT.COURSE_NST_TB('SCOTT.COURSE_OBJ(101,1004,'Elementry Chemistry')')

101 XYZ SCOTT.COURSE_NST_TB('SCOTT.COURSE_OBJ(101,1005,'Elementry Pyscology')')

Now! Here lies the little complexity. You cannot see the actual value of a NESTED type column by merely referencing column in SELECT clause.

If we fire any select statement having a column of Nested Type , The result set will be shown just like above.

The correct way to select any column defined as NESTED type follows:

Select sci.stu_id,sci.stu_name,x.* from scott.stu_course_info sci,table(sci.course_info)x

STU_ID STU_NAME STU_ID COURSE_ID COURSE_NAME

---------------------- ---------------------------------------- ---------------------- ---------------------- -------------

103 SBX 103 1001 Elementry Physics

103 SBX 103 1002 Elementry Science

103 SBX 103 1003 Elementry Math

103 SBX 103 1004 Elementry Chemistry

103 SBX 103 1005 Elementry Pyscology

101 XYZ 101 1001 Elementry Physics

101 XYZ 101 1002 Elementry Science

101 XYZ 101 1003 Elementry Math

101 XYZ 101 1004 Elementry Chemistry

101 XYZ 101 1005 Elementry Pyscology

102 ABC 102 1001 Elementry Physics

102 ABC 102 1002 Elementry Science

102 ABC 102 1003 Elementry Math

102 ABC 102 1004 Elementry Chemistry

102 ABC 102 1005 Elementry Pyscology

To select any particular value:

SELECT SCI.STU_ID,SCI.STU_NAME,X.COURSE_ID,X.COURSE_NAME FROM SCOTT.STU_COURSE_INFO SCI,TABLE(SCI.COURSE_INFO) X

STU_ID STU_NAME COURSE_ID COURSE_NAME

---------------------- ---------------------------------------- ---------------------- ---------------------- -------------

101 XYZ 1001 Elementry Physics

101 XYZ 1002 Elementry Science

101 XYZ 1003 Elementry Math

101 XYZ 1004 Elementry Chemistry

101 XYZ 1005 Elementry Pyscology

To select any particular value

SELECT SCI.STU_ID,SCI.STU_NAME,X.COURSE_ID,X.COURSE_NAME FROM SCOTT.STU_COURSE_INFO SCI,TABLE(SCI.COURSE_INFO) X

WHERE X.STU_ID=SCI.STU_ID

AND X.STU_ID=101

update

To update a table having a column defined as NESTED type, we need to define a MAP method of Object.

1 Update scott.stu_course_info sci

2 set sci.course_info=Scott.Course_Nst_tb(Scott.Course_obj(101,1003,'elementry math'))

3* where course_info=scott.course_nst_tb(scott.course_obj(101,1003,'Elementry Math'))

SQL> /

1 row updated.

SELECT SCI.STU_ID,SCI.STU_NAME,X.COURSE_ID,X.COURSE_NAME FROM SCOTT.STU_COURSE_INFO SCI,TABLE(SCI.COURSE_INFO) X

WHERE X.STU_ID=SCI.STU_ID

AND X.STU_ID=101

and x.course_name like '%ath%'

STU_ID STU_NAME COURSE_ID COURSE_NAME

---------------------- ---------------------------------------- ---------------------- ---------------------- -------------

101 XYZ 1003 elementry math

Delete

delete from scott.stu_course_info sci

where course_info=scott.course_nst_tb(scott.course_obj(101,1003,'Elementry Math'))

commit

SELECT SCI.STU_ID,SCI.STU_NAME,X.COURSE_ID,X.COURSE_NAME FROM SCOTT.STU_COURSE_INFO SCI,TABLE(SCI.COURSE_INFO) X

WHERE X.STU_ID=SCI.STU_ID

AND X.STU_ID=101

order by 1,3

STU_ID STU_NAME COURSE_ID COURSE_NAME

---------------------- ---------------------------------------- ---------------------- ---------------------- -------------

101 RAJ 1002 Elementry Science

101 RAJ 1004 Elementry Chemistry

101 RAJ 1005 Elementry Pyscology

As we can see, that it is really not that complicated or difficult to use NESTED type or Varry Type using in SQL Statement. We can use is pretty easily once we start using it. In part 2 we will discuss the performance impact of using NESTED types as columns..

Any suggestion, correction, comments will be appreciated.

Monday, 2 January 2012

Database creation Using RMAN backup in Oracle 10g

It is quite possible that we fall in a situation where we have to re-create a Database using the Backup (RMAN backup). It is not so rare scenario. The new Database can reside on new Server or existing server. Basic thing is,

We need to have a new database in Nomount state and Name of database should be Same. Means DB_NAME

Parameter should be like it was in old database. However, We can have different DB_UNIQUE_NAME for new database.

Another thing is, we cannot re-recreate a database on Windows machine using RMAN backup taken on Linux/Solaris OS and vice versa. Cross platform thing does not work here.

Following are the steps to recreate database from RMAN Backup.

SQL> select banner from v$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 – Production

1: In this database name will be same. but Instance name will be different.

2:- C:\Documents and Settings\20001>oradim -new -sid findb -intpwd seed -startmode m

3:- Create pfile. either copy any existing or create new one.

keep db_name as FINDB <-- found in init.ora file of OLD database

4:-create directory structure on OS level.

5:-follow steps

C:\Documents and Settings\20001>set oracle_sid=findb

C:\Documents and Settings\20001>sqlplus sys as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 2 14:47:37 2012

Enter password:

Connected to an idle instance.

SQL> startup nomount pfile=%oracle_home%\database\initFINDB.ora

ORACLE instance started.

Total System Global Area 612368384 bytes

Fixed Size 1250428 bytes

Variable Size 239078276 bytes

Database Buffers 364904448 bytes

Redo Buffers 7135232 bytes

SQL> create spfile from pfile;

File created.

6: Put the RMAN backup to the new server on which you want to re-create the database.

7: Connect to RMAN

C:\Documents and Settings\20001>Set Oracle_sid=FINDB

C:\Documents and Settings\20001>rman target sys/seed

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Jan 2 14:50:33 2012

connected to target database: FINDB (not mounted)

As at this stage, we can see that show all shows all the vanilla setting of RMAN configuration.

RMAN> show all;

using target database control file instead of recovery catalog

RMAN configuration parameters are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

RMAN> Set DBID=496261842

executing command: SET DBID

Our next step is to re-store the controlfile(s) from the BACKUPSET. Command to restore Control file dependes weather we are going to use AUTOBACKUP FORMAT or restoring from a particular backup set. Here we have little complexity. If we have the same directory structure on new server like we had on original server from where we had taken backup.

Eg. Following commands show the location (E:\RMAN_BKUP\FINDB\FINDB_CTLFILE_C-496261842-20111220-00) of control file backup in backupset and on new server I do not have this location. So RESTORE FROM AUTOBACKUP would not work. So I will use RESTORE FROM statement.

RMAN> list backup of controlfile;

List of Backup Sets

===================

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

2 Full 13.80M DISK 00:00:02 20-DEC-11

BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20111220T144031

Piece Name: E:\RMAN_BKUP\FINDB\FINDB_CTLFILE_C-496261842-20111220-00

Control File Included: Ckp SCN: 9376006 Ckp time: 20-DEC-11

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

4 Full 13.80M DISK 00:00:03 20-DEC-11

BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20111220T144425

Piece Name: E:\RMAN_BKUP\FINDB\FINDB_CTLFILE_C-496261842-20111220-01

Control File Included: Ckp SCN: 9376116 Ckp time: 20-DEC-11

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

6 Full 13.80M DISK 00:00:02 20-DEC-11

BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20111220T144814

Piece Name: E:\RMAN_BKUP\FINDB\FINDB_CTLFILE_C-496261842-20111220-02

Control File Included: Ckp SCN: 9376236 Ckp time: 20-DEC-11

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

10 Full 13.80M DISK 00:00:02 20-DEC-11

BP Key: 10 Status: AVAILABLE Compressed: NO Tag: TAG20111220T151429

Piece Name: E:\RMAN_BKUP\FINDB\FINDB_CTLFILE_C-496261842-20111220-03

Control File Included: Ckp SCN: 9377372 Ckp time: 20-DEC-11

RMAN> Run

2> {

3> Set Controlfile Autobackup Format For Device Type Disk to 'F:\Rman_Bkup\FINDB_851_91\FINDB_CTL_%F';

4> RESTORE CONTROLFILE FROM 'F:\Rman_Bkup\FINDB_851_91\FINDB_CTLFILE_C-496261842-20111222-00';

5> }

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Starting restore at 02-JAN-12

using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:05

output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\FINDB\CONTROL01.CTL

output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\FINDB\CONTROL02.CTL

output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\FINDB\CONTROL03.CTL

Finished restore at 02-JAN-12

Setting DBID is very important before we start recovery process. If we do not set DBID and try to use above run block than we get following error

RMAN> run

2> {

3> Set Controlfile Autobackup Format For Device Type Disk to 'F:\BKUP\Seed_Bkup\SEED_CTL_%F';

4> Restore Controlfile from Autobackup;

5> }

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Starting restore at 27-DEC-11

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=157 devtype=DISK

recovery area destination: D:\oracle\product\10.2.0/flash_recovery_area

database name (or database unique name) used for search: ORCL

channel ORA_DISK_1: no autobackups found in the recovery area

autobackup search outside recovery area not attempted because DBID was not set

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 12/27/2011 14:30:26

RMAN-06172: no autobackup found or specified handle is not a valid copy or piece

9:- Alter database mount;

RMAN> Alter database Mount;

database mounted

released channel: ORA_DISK_1

10:- if Database backup set expired then crosscheck backup once.

RMAN> list backup of datafile 3;

List of Backup Sets

===================

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

30 Full 35.00M DISK 00:00:18 21-DEC-11

BP Key: 33 Status: EXPIRED Compressed: YES Tag: FULL_DB_BKP

Piece Name: D:\BKUP\SEED_BKUP\SEED_11MUP6LQ_1_1.BKP

List of Datafiles in backup set 30

File LV Type Ckp SCN Ckp Time Name

---- -- ---- ---------- --------- ----

3 Full 16375812 21-DEC-11 D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\SYSAUX01.DBF

RMAN> Crosscheck Backup

RMAN> list backup of datafile 4;

List of Backup Sets

===================

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

31 Full 32.96M DISK 00:00:12 21-DEC-11

BP Key: 34 Status: AVAILABLE Compressed: YES Tag: FULL_DB_BKP

Piece Name: D:\BKUP\SEED_BKUP\SEED_12MUP6MJ_1_1.BKP

List of Datafiles in backup set 31

File LV Type Ckp SCN Ckp Time Name

---- -- ---- ---------- --------- ----

4 Full 16375848 21-DEC-11 D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\USERS01.DBF

11:- Restore database; <-- of backup is at its default location;

Now! Restore of database also has some of complexity. After Mounting the database, we check the backup sets and using “List Backup” command , we can see the location ,time and every other detail of backup set but the status of every backup set will be ‘EXPIRED’ because BACKUPSET are not on their default location. So we need to use catalog log command to update the BACKUPSET information in control file so that it actually reads our backup set. One easy method of doing so is

RMAN> CATALOG START WITH 'F:\Rman_Bkup\FINDB_851_91\FINDB_BKP_0' NOPROMPT;

searching for all files that match the pattern F:\Rman_Bkup\FINDB_851_91\FINDB_BKP_0

List of Files Unknown to the Database

=====================================

File Name: F:\RMAN_BKUP\FINDB_851_91\FINDB_BKP_07MUMK7R_1_1.BKP

File Name: F:\RMAN_BKUP\FINDB_851_91\FINDB_BKP_08MUMKJE_1_1.BKP

File Name: F:\RMAN_BKUP\FINDB_851_91\FINDB_BKP_09MUMKQD_1_1.BKP

File Name: F:\RMAN_BKUP\FINDB_851_91\FINDB_BKP_0BMUS65U_1_1.BKP

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: F:\RMAN_BKUP\FINDB_851_91\FINDB_BKP_07MUMK7R_1_1.BKP

File Name: F:\RMAN_BKUP\FINDB_851_91\FINDB_BKP_08MUMKJE_1_1.BKP

File Name: F:\RMAN_BKUP\FINDB_851_91\FINDB_BKP_09MUMKQD_1_1.BKP

File Name: F:\RMAN_BKUP\FINDB_851_91\FINDB_BKP_0BMUS65U_1_1.BKP

Now, RMAN can use, newly cataloged backup set.

RMAN> Run

2> {

3> Allocate Channel ch1 device type disk format 'F:\Rman_Bkup\FINDB_851_91\FINDB_BKP_%U.BKP';

4> SET NEWNAME FOR DATAFILE 'E:\ORACLE\ORADATA\FINDB\PSINDEX2.DBF' TO 'F:\oracle\findb\PSINDEX2.DBF';

5> SET NEWNAME FOR DATAFILE 'E:\ORACLE\ORADATA\FINDB\PSINDEX.DBF' TO 'F:\oracle\findb\PSINDEX.DBF';

6> RESTORE DATABASE;

7> SWITCH DATAFILE ALL;

8> RESTORE DATABASE;

9> ALTER DATABASE OPEN RESETLOGS;

10> }

Any suggestion,query,correction will be highly appriciated.

Thanks and Regards
Jai