OraPL

Monday, 25 June 2012

Oracle 11g Data Guard (Physical)

Implementing Oracle Data Guard in 11g release 2 Version:-

Oracle Data Guard is a feature provided by Oracle database, which makes production database available ,secure and accessible in the event of unplanned outage (Disaster caused production database un-available) or planned outage (Patch application, testing) etc.

The basic working of Data Guard is like, a process of Oracle Database sends the Archive log(s) to another location and another process at new location apply those Archive logs to new database which is a cloned copy of the database.

Sounds simple! J

In Data guard terminology the sender of Archive logs are called Primary database and Receiver database is Standby database.

There are types of Standby Databases in Data Guard configuration:

A:- Physical Standby

B:- Logical Standby

C:- SnapShot Standby

In this post we will talk about Physical Standby database. Oracle Documents defines Physical Standby database as block by block copy of Production database. Mean if we run a query and use ROWID column then result set will be identical. I checked this on Primary and Standby database by querying as follows:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select rowid,ename,sal from scott.emp;

ROWID ENAME SAL

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

AAAR3sAAEAAAACXAAA SMITH 800

AAAR3sAAEAAAACXAAB ALLEN 1600

AAAR3sAAEAAAACXAAC WARD 1250

SQL> conn sys/root123@STDBY as sysdba

Connected.

SQL> select rowid,ename,sal from scott.emp;

ROWID ENAME SAL

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

AAAR3sAAEAAAACXAAA SMITH 800

AAAR3sAAEAAAACXAAB ALLEN 1600

AAAR3sAAEAAAACXAAC WARD 1250

Methods to create Physical Standby database: We have two methods given by Oracle to configure Standby database.

1:- Manual 2:- RMAN

We will discuss RMAN method to create Standby database.

RMAN methodology to create Standby database have few steps that I am briefing here. Later we will discuss it in detail.

1:- Backup current control file for Standby Database

2:- Full backup of database with Archive logs

3:- Take database backup to destination server

4:- Make entries in Tnsnames.ora, Listener.ora file on both Servers

5:- Create Database , Add some Standby database specific parameter

5:- Duplicate target database as Standby to another Auxiliary database

Now! We discuss these steps in more detail.

Unlike Primary Database, Standby Database uses another type of Control file. Primary database has “CURRENT” Control file type and Standby database uses “Standby” Control file type.

To check another kind of Control file, Please use Oracle 11g Documentation.

Following is the procedure to take backup using RMAN, later to be used for Standby Database creation.

C:\Documents and Settings\Administrator>rman target sys/root123@TEST

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Jun 20 17:00:38 2012

connected to target database: TEST (DBID=2084526936)

RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT 'D:\SEED_CTL_FILE.CTL' TAG='CTL_FILE.BKP';

RMAN>backup check logical full as compressed backupset database plus archivelog;

Now! Take this backup to would be Standby server.

Get Pfile, orapw<SID Name>.ora file of Primary database to Standby Server. Make changes in pfile of Standby database as given below.

test.__db_cache_size=230686720

test.__java_pool_size=4194304

test.__large_pool_size=4194304

test.__oracle_base='C:\app\huser'#ORACLE_BASE set from environment

test.__pga_aggregate_target=247463936

test.__sga_target=314572800

test.__shared_io_pool_size=0

test.__shared_pool_size=272629760

test.__streams_pool_size=4194304

*.archive_lag_target=300 ß To force log switch after 5 mins.

*.audit_file_dest='C:\app\huser\admin\test\adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='C:\APP\HUSER\ORADATA\TEST\CONTROL01.CTL','C:\APP\HUSER\FLASH_RECOVERY_AREA\TEST\CONTROL02.CTL'#Restore Controlfile

*.db_block_size=8192

*.db_domain=''

*.db_name='test'

*.db_recovery_file_dest='C:\app\huser\flash_recovery_area'

*.db_recovery_file_dest_size=4039114752

*.diagnostic_dest='C:\app\huser'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'

*.fal_client='TEST'ß Gap Resolution,The client location for Fetch Archive log

*.fal_server='STDBY' ßGap Resolution, Server location

*.log_archive_config='DG_CONFIG=(TEST,STDBY)' ß All the database involoved in Data Guard configuration

*.log_archive_dest_1='LOCATION=F:\oradata\TEST\ MANDATORY ' ß Local Archiving

*.log_archive_format='TEST%S_%R_%T.ARC' Location

*.log_archive_dest_2='SERVICE=STDBY LGWR ASYNC VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STDBY'ßRedo Log shipping on Standby

1:- Server=STDBY (STDBY is TNSNAMES entry of Standby Database)

2:- LGWR process responsible of REDO shipping to Standby Site

3:- ASYNC Primary database w’nt wait of changes to be committed on Standby Site.

4:- VAILD_FOR=(ALL_LOGFILES,PRIMARY_ROLE):- The type of REDO log file valid for this Standby Server And The role in which Database can be opened.

5:- DB_Unique_Name=STDBY The DB_UNIQUE_NAME of the database. We need to give DB_UNIQUE_NAME here because the DB_NAME of both Primary and Standby remains same. Means if my Primary database has DB_NAME=TEST then DB_NAME of Standby Database will be TEST.

*.open_cursors=300

*.pga_aggregate_target=247463936

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_max_size=524288000

*.sga_target=524288000

*.standby_file_management='AUTO' What Structrual changes occur at primary goes automatically at Standby. Like Tblspace add,Rename DB file,Drop Tablespace

*.undo_tablespace='UNDOTBS1'

Now Check the Pfile of Standby Databse

test.__db_cache_size=230686720

test.__java_pool_size=4194304

test.__large_pool_size=4194304

test.__oracle_base='C:\app\huser'#ORACLE_BASE set from environment

test.__pga_aggregate_target=247463936

test.__sga_target=314572800

test.__shared_io_pool_size=0

test.__shared_pool_size=272629760

test.__streams_pool_size=4194304

*.archive_lag_target=3600 Automatic log switching

*.audit_file_dest='C:\app\huser\admin\test\adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.db_block_size=8192

*.db_domain=''

*.db_name='test' db Name

*.db_unique_name=’STDBY’ DB unique name in DG configuration

*.db_recovery_file_dest='C:\app\huser\flash_recovery_area'

*.db_recovery_file_dest_size=4039114752

*.diagnostic_dest='C:\app\huser'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'

*.fal_client='TEST' Client name for Archive Log Gap Sequence

*.fal_server='STDBY' Server name for Archive Log Gap Sequence

*.log_archive_config='DG_CONFIG=(TEST,STDBY)' DB taking part in DG configuration.

*.log_archive_dest_1='LOCATION=C:\APP\HUSER\ORADATA\STDBY\ARC\ MANDATORY '

*.log_archive_dest_2='SERVICE=STDBY LGWR ASYNC VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STDBY'

*.log_archive_format='TEST%S_%R_%T.ARC'

*.open_cursors=300

*.pga_aggregate_target=247463936

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_max_size=524288000

*.sga_target=524288000

*.standby_file_management='AUTO' Structural change automatically ocurrses

*.undo_tablespace='UNDOTBS1'

Reload Listener after adding service names of Primary and Standby Database.

C:\Documents and Settings\Administrator>lsnrctl services

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 25-JUN-2012 14:15:24

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=PUESws128.corp.hexaware.com)(PORT=1521)))

Services Summary...

Service "DEMOHR91" has 1 instance(s).

Instance "DEMOHR91", status UNKNOWN, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:0 refused:0

LOCAL SERVER

Service "PLSExtProc" has 1 instance(s).

Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:0 refused:0

LOCAL SERVER

Service "STDBY" has 2 instance(s).

Instance "STDBY", status UNKNOWN, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:765 refused:1

LOCAL SERVER

Instance "stdby", status READY, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:10 refused:0 state:ready

LOCAL SERVER

Service "TEST" has 2 instance(s).

Instance "TEST", status UNKNOWN, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:258 refused:8

LOCAL SERVER

Instance "test", status READY, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:1 refused:0 state:ready

LOCAL SERVER

Service "stdbyXDB" has 1 instance(s).

Instance "stdby", status READY, has 1 handler(s) for this service...

Handler(s):

"D000" established:0 refused:0 current:0 max:1022 state:ready

DISPATCHER <machine: PUESWS128, pid: 5484>

(ADDRESS=(PROTOCOL=tcp)(HOST=PUESws128.corp.hexaware.com)(PORT=2902))

The command completed successfully

Now our basic structure of Data Guard is ready. We need to do following further:

1:- Take backup of Primary Database to Standby Server

rman target sys/root123@test

----------Controlfile for Standby creation

RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT 'D:\SEED_CTL_FILE.CTL' TAG='CTL_FILE.BKP';

------------DBF(s) backup for Database

RMAN>backup check logical full as compressed backupset database plus archivelog;

2:- Put Standby Database (STDBY) in Nomount stage using SPFILE

C:\Documents and Settings\Administrator>oradim -new -sid stdby -startmode m

Instance created.

C:\Documents and Settings\Administrator>set oracle_sid=STDBY

C:\Documents and Settings\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 20 16:22:38 2012

Connected to an idle instance.

SQL> startup pfile=%ORACLE_HOME%\database\initSTDBY.ora nomount

ORACLE instance started.

Total System Global Area 740724736 bytes

Fixed Size 1377164 bytes

Variable Size 255855732 bytes

Database Buffers 478150656 bytes

Redo Buffers 5341184 bytes

SQL> create spfile from pfile;

File created.

SQL> shutdown immedaite;

SP2-0717: illegal SHUTDOWN option

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 740724736 bytes

Fixed Size 1377164 bytes

Variable Size 255855732 bytes

Database Buffers 478150656 bytes

Redo Buffers 5341184 bytes

2:- Connect to target Database using Catalog db or not and with Auxiliary Database and clone the target database for Standby

C:\Documents and Settings\Administrator>rman target sys/root123@TEST auxiliary sys/root123@STDBY

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Jun 20 17:00:38 2012

connected to target database: TEST (DBID=2084526936)

connected to auxiliary database: TEST (not mounted)

RMAN> list backup of datafile 1;

using target database control file instead of recovery catalog

RMAN> run

2> {

3> Allocate Auxiliary Channel ch1 Device TYpe Disk;

4> Duplicate Target database for Standby NoFileNameCheck DoRecover;

5> }

allocated channel: ch1

channel ch1: SID=10 device type=DISK

Starting Duplicate Db at 20-JUN-12

contents of Memory Script:

{

set until scn 1170271;

sql clone "alter system set control_files =

''C:\APP\HUSER\FLASH_RECOVERY_AREA\STDBY\CONTROLFILE\O1_MF_7Y3F58SC_.CTL'' comment=

''Set by RMAN'' scope=spfile";

restore clone standby controlfile;

}

executing Memory Script

executing command: SET until clause

sql statement: alter system set control_files = ''C:\APP\HUSER\FLASH_RECOVERY_AREA\STDBY\CONTROLFILE\O1_MF_7Y3F58SC_.CTL'' comment= ''Se

e=spfile

Starting restore at 20-JUN-12

channel ch1: starting datafile backup set restore

channel ch1: restoring control file

channel ch1: reading from backup piece F:\ORADATA\TEST\RMAN_BKP\C-2084526936-20120620-01

channel ch1: piece handle=F:\ORADATA\TEST\RMAN_BKP\C-2084526936-20120620-01 tag=TAG20120620T161613

channel ch1: restored backup piece 1

channel ch1: restore complete, elapsed time: 00:00:02

output file name=C:\APP\HUSER\FLASH_RECOVERY_AREA\STDBY\CONTROLFILE\O1_MF_7Y3F5B1X_.CTL

Finished restore at 20-JUN-12

contents of Memory Script:

{

sql clone 'alter database mount standby database';

}

executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:

{

set until scn 1170271;

set newname for tempfile 1 to

"C:\APP\HUSER\ORADATA\STDBY\TEMP01.DBF";

switch clone tempfile all;

set newname for datafile 1 to

"C:\APP\HUSER\ORADATA\STDBY\SYSTEM01.DBF";

set newname for datafile 2 to

"C:\APP\HUSER\ORADATA\STDBY\SYSAUX01.DBF";

set newname for datafile 3 to

"C:\APP\HUSER\ORADATA\STDBY\UNDOTBS01.DBF";

set newname for datafile 4 to

"C:\APP\HUSER\ORADATA\STDBY\USERS01.DBF";

set newname for datafile 5 to

"C:\APP\HUSER\ORADATA\STDBY\EXAMPLE01.DBF";

restore

clone database

;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

renamed tempfile 1 to C:\APP\HUSER\ORADATA\STDBY\TEMP01.DBF in control file

executing command: SET NEWNAME

Starting restore at 20-JUN-12

channel ch1: starting datafile backup set restore

channel ch1: specifying datafile(s) to restore from backup set

channel ch1: restoring datafile 00001 to C:\APP\HUSER\ORADATA\STDBY\SYSTEM01.DBF

channel ch1: restoring datafile 00002 to C:\APP\HUSER\ORADATA\STDBY\SYSAUX01.DBF

channel ch1: restoring datafile 00003 to C:\APP\HUSER\ORADATA\STDBY\UNDOTBS01.DBF

channel ch1: restoring datafile 00004 to C:\APP\HUSER\ORADATA\STDBY\USERS01.DBF

channel ch1: restoring datafile 00005 to C:\APP\HUSER\ORADATA\STDBY\EXAMPLE01.DBF

channel ch1: reading from backup piece F:\ORADATA\TEST\RMAN_BKP\TEST_0BNE16BD_1_1.BKP

channel ch1: piece handle=F:\ORADATA\TEST\RMAN_BKP\TEST_0BNE16BD_1_1.BKP tag=BACKUP_FOR_STANDBY

channel ch1: restored backup piece 1

channel ch1: restore complete, elapsed time: 00:02:15

Finished restore at 20-JUN-12

contents of Memory Script:

{

switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=2 STAMP=786474261 file name=C:\APP\HUSER\ORADATA\STDBY\SYSTEM01.DBF

datafile 2 switched to datafile copy

input datafile copy RECID=3 STAMP=786474261 file name=C:\APP\HUSER\ORADATA\STDBY\SYSAUX01.DBF

datafile 3 switched to datafile copy

input datafile copy RECID=4 STAMP=786474261 file name=C:\APP\HUSER\ORADATA\STDBY\UNDOTBS01.DBF

datafile 4 switched to datafile copy

input datafile copy RECID=5 STAMP=786474262 file name=C:\APP\HUSER\ORADATA\STDBY\USERS01.DBF

datafile 5 switched to datafile copy

input datafile copy RECID=6 STAMP=786474262 file name=C:\APP\HUSER\ORADATA\STDBY\EXAMPLE01.DBF

contents of Memory Script:

{

set until scn 1170271;

recover

standby

clone database

delete archivelog

;

}

executing Memory Script

executing command: SET until clause

Starting recover at 20-JUN-12

starting media recovery

archived log for thread 1 with sequence 68 is already on disk as file F:\ORADATA\TEST\TEST0000000068_0786391138_0001.ARC

archived log file name=F:\ORADATA\TEST\TEST0000000068_0786391138_0001.ARC thread=1 sequence=68

media recovery complete, elapsed time: 00:00:03

Finished recover at 20-JUN-12

Finished Duplicate Db at 20-JUN-12

released channel: ch1

RMAN>

Now! RMAN work is done.

The first query that I run on Standby Database is:

Select Name,controlfile_type,database_Role,open_mode,protection_mode,protection_leve from v$database

NAME CONTROLFILE_TYPE DATABASE_ROLE OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL ----------------- --------- ---------------- ---------------- -------------------- -------------------- -------------------- ---------------

TEST STANDBY PHYSICAL STANDBY MOUNT MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

1 rows selected

Now! We need to start MRP process by default its not started. So we need to write something like follow:

SQL> Alter Database Recover Managed Standby Database Using Current Logfile Disconnect From Session;

Database altered.

After this a Back ground process (MRP01) will be started that will apply REDO logs to Standby Database and keep Standby and Primary in Sync.

To see transfer of REDO logs from Primary to Standby Site you following same query on both Database(s).

On Primary: Select sequence# ,applied,name from v$archived_log order by 1 desc

Seq# App Name

197 YES STDBY

197 NO F:\ORADATA\TEST\TEST0000000197_0786391138_0001.ARC

196 YES STDBY

196 NO F:\ORADATA\TEST\TEST0000000196_0786391138_0001.ARC

195 NO F:\ORADATA\TEST\TEST0000000195_0786391138_0001.ARC

195 YES STDBY

194 NO F:\ORADATA\TEST\TEST0000000194_0786391138_0001.ARC

194 YES STDBY

193 NO F:\ORADATA\TEST\TEST0000000193_0786391138_0001.ARC

193 YES STDBY

192 NO F:\ORADATA\TEST\TEST0000000192_0786391138_0001.ARC

192 YES STDBY

On Standby: Select sequence#,applied,name from v$archived_log order by 1 desc

SEQUENCE# APPLIED NAME

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

197 YES C:\APP\HUSER\ORADATA\STDBY\ARC\ARC0000000197_0786391138.0001

196 YES C:\APP\HUSER\ORADATA\STDBY\ARC\ARC0000000196_0786391138.0001

195 YES C:\APP\HUSER\ORADATA\STDBY\ARC\ARC0000000195_0786391138.0001

194 YES C:\APP\HUSER\ORADATA\STDBY\ARC\ARC0000000194_0786391138.0001

193 YES C:\APP\HUSER\ORADATA\STDBY\ARC\ARC0000000193_0786391138.0001

192 YES C:\APP\HUSER\ORADATA\STDBY\ARC\ARC0000000192_0786391138.0001

Starting with Oracle 11g, You can open your Standby database in READ-ONLY mode and MRP process keeps applying the Archived log in Back end.

For trouble shooting you can check v$archive_dest,v$archive_dest_status

Select Dest_name,Status,Type,Database_mode,recovery_mode,destination from $archive_dest_status

LOG_ARCHIVE_DEST_1 VALID LOCAL OPEN IDLE F:\oradata\TEST\

LOG_ARCHIVE_DEST_2 VALID PHYSICAL OPEN_READ-ONLY MANAGED REAL TIME APPLY STDBY

Select Dest_Name,Status,Destination,target,error from v$archive_dest

LOG_ARCHIVE_DEST_1 VALID F:\oradata\TEST\ PRIMARY

LOG_ARCHIVE_DEST_2 VALID STDBY STANDBY

Views to trouble shoot the Data Guard

V$ARCHIVE_GAP,v$ARCHIVE_PROCESSES,V$DATABASE, V$ARCHIVE,V$PROXY_ARCHIVEDLOG, V$ARCHIVED_LOG,V$ARCHIVE_GAP, V$ARCHIVE_PROCESSES, V$ARCHIVE_DEST ,V$ARCHIVE_DEST_STATUS,V$PROXY_ARCHIVELOG_DETAILS,V$BACKUP_ARCHIVELOG_DETAILS,V$BACKUP_ARCHIVELOG_SUMMARY, V$PROXY_ARCHIVELOG_SUMMARY

In continuation, We will discuss new method of Oracle 11g to create Standby which saves from sending Backup to Standby Server.

For further knowledge refer following documents:

http://www.oracle.com/pls/db112/homepage

https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=ADVISOR&id=1396735.2

https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=HOWTO&id=316740.1

https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=HOWTO&id=1429223.1

https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=HOWTO&id=1338669.1

https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=HOWTO&id=1304939.1

https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=HOWTO&id=800212.1

https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=ADVISOR&id=1396735.2

https://communities.oracle.com/portal/server.pt/community/high_availability_data_guard/302

Any comment, suggestion, correction will be welcome.

Sunday, 20 May 2012

Cursor sharing Part 1

By default it is Cursor_Sharing is EXACT it means ONLY THOSE STATEMENT ARE SHAREABLE WHICH ARE IDENTICAL IN TEXT. Means in such case, if someone wants a query shareable then they have to fix the TEXT of the query and have to run same text over and over again, which I think not possible in the real production environment.

I opened two session on Toad 10.2 using following database

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

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

And run following query which gave me one row

To do our test, I prepared on table, created an index, Updated that table and gathered statistics.

Following are the exact statement that I executed.

1:- CREATE TABLE SCOTT.OBJ_TAB PCTFREE 90 PCTUSED 10

AS SELECT * FROM SYS.DBA_OBJECTS WHERE 1=2;

2:- INSERT INTO SCOTT.OBJ_TAB SELECT * FROM DBA_OBJECTS;

3:- create index scott.obj2 on scott.obj_tab(owner) compute statistics;

4:- UPDATE SCOTT.OBJ_TAB SET OWNER='SYS' WHERE OWNER NOT IN ('PM')

5:- begin

dbms_stats.gather_table_stats(ownname=>'scott',tabname=>'obj_tab',estimate_percent=>100,method_opt=>'for all columns SIZE AUTO',cascade=>true);

end;

SQL> Show parameter Cursor_sharing

NAME TYPE VALUE

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

cursor_sharing string EXACT

Select /*Exact*/ * from scott.DBA_OBJECTS where ='SMITH'

While executing this query, I used same ‘SMITH’ literal value in two different session and it turns out that this query is shareable. On v$sqlarea, v$sql it gave one unique SQL_ID. However, when I changed the literal value to ‘ALLEN’ then, one additional row was added into v$SQLAREA and v$SQL.

Every time any session changes the value of literal, a new row is added into v$SQLAREA and just because of this same query is parsed again. It means if query text remains same then only query will be shared otherwise it is not shareable.

Disadvantage: Non-Reusability, Load on SHARED_POOL, More Hard parsing

Advantage: Correct cardinality feeding to Optimizer, right execution path and correct index picking.

After this test I changed my CURSOR_SHARING parameter to FORCE;

SQL> alter system set cursor_sharing=force scope=memory;

System altered.

CURSOR_SHARING to FORCE means it Forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement. (courtesy oracle docs)

After this I ran following query :

select /*force*/ * from scott.obj_tab where owner='PM’

This query gave me 52 rows and one thing I noticed, when I queried V$SQLAREA using following query:

SELECT * FROM V$SQLAREA WHERE SQL_TEXT LIKE '%force%'

The SQL_TEXT column showed following value

select /*force*/ * from scott.obj_tab where owner=:"SYS_B_0" a automatically generated bind variable of character type. This is the trick, after changing CURSOR_SHARING parameter, lateral values are automatically replaced by BIND variable(s) to make query shareable.

However, this behavior has a limitation. If we remember what oracle documents says about FORCE value of CURSOR_SHARING, “, unless the literals affect the meaning of the statement”. So It means

Monday, 13 February 2012

ASM configuration on Oracle 10g R2

ASM Instance configuration...

Automatic Storage Management (ASM) is a new feature included in Oracle Database 10g that helps busy DBA's from manually managing and tuning disks used by Oracle databases. ASM provides the DBA with a file system and volume manager that makes use of an Oracle instance (referred to as an ASM instance) and can be managed using either SQL or Oracle Enterprise Manager.

Only one ASM instance is required per node. The same ASM instance can manage ASM storage for all 10g databases running on the node.

When the DBA installs the Oracle Database 10g software and creates a new database, creating an ASM instance is a snap. The DBCA provides a simple check box and an easy wizard to create an ASM instance as well as an Oracle database that makes use of the new ASM instance for ASM storage. But, what happens when the DBA is migrating to Oracle Database 10g or didn't opt to use ASM when a 10g database was first created. The DBA will need to know how to manually create an ASM instance and that is what this article provides

We can implement ASM using following two methods:

ASM with ASMLib I/O: This method creates all Oracle database files on raw block devices managed by ASM using ASMLib calls. RAW devices are not required with this method as ASMLib works with block devices.

ASM with Standard Linux I/O: This method creates all Oracle database files on raw character devices managed by ASM using standard Linux I/O system calls. You will be required to create RAW devices for all disk partitions used by ASM

However, for this post we will use ASM Lib

1:- Install ASMLib RPM,s of your machine/OS architecture. You can download those from following location:

http://www.oracle.com/technetwork/server-storage/linux/downloads/rhel5-084877.html

1:- Oracle-support-<kernal>.rpm is oracleasm-support-2.1.7-1.el5.i386.rpm

2:- Oracleasmlib-<kernal>.rpm is oracleasmlib-2.0.4-1.el5.i386.rpm

3:- Oracleasm-<kernal>.rpm is oracleasm-2.6.18-8.el5-2.0.4-1.el5.i686.rpm

To install these, RPMS, Please use following command

rpm -Uvh \oracleasm-support-<kernal>.rpm \oracleasmlib-<kernal> \oracleasm-<kernal>

2:- Now,Second, we need to run CSSD. Using Oracle 10g Release 2 we can achive this by running

Automatic Storage Management (ASM) requires the use of Oracle Cluster Synchronization Services (CSS), and as such, CSS must be configured and running before attempting to use ASM. The CSS service is required to enable synchronization between an ASM instance and the database instances that rely on it for database file storage.

In a non-RAC environment, the Oracle Universal Installer will configure and start a single-node version of the CSS service. For Oracle Real Application Clusters (RAC) installations, the CSS service is installed with Oracle Cluster Ready Services (CRS) in a separate Oracle home directory (also called the CRS home directory). For single-node installations, the CSS service is installed in and runs from the same Oracle home as the Oracle database.

$ORACLE_HOME/bin/localconfig all

/etc/oracle does not exist. Creating it now.

Successfully accumulated necessary OCR keys.

Creating OCR keys for user 'root', privgrp 'root'..

Operation successful.

Configuration for local CSS has been initialized

Adding to inittab

Startup will be queued to init within 90 seconds.

Checking the status of new Oracle init process...

Expecting the CRS daemons to be up within 600 seconds.

CSS is active on these nodes.

linux4

CSS is active on all nodes.

Oracle CSS service is installed and running under init(1M)

to stop CSS, We can use following commaind on Linux

/etc/ini.d/init.cssd stop

and to restart it we can use either of command

/etc/ini.d/init.cssd start

$ORACLE_HOME/bin/oracleasm reset

Now! We are ready to use ASM utility of Oracle. Unlike traditional Database(s) that use disk drives to store Oracle related files, any Database trying to use ASM facility should have access to up and running ASM instance.ASM is just like any another instance but with a very limited parameters. A typical pfile of ASM contains following parameters only:

[oracle@PSLinux dbs]$ cat init+ASM.ora

+ASM.asm_diskgroups='DISK1'#Manual Mount

*.asm_diskgroups='DISK1'

*.background_dump_dest='/Soft/oracle/admin/+ASM/bdump'

*.core_dump_dest='/Soft/oracle/admin/+ASM/cdump'

*.instance_type='asm'

*.large_pool_size=12M

*.remote_login_passwordfile='SHARED'

*.user_dump_dest='/Soft/oracle/admin/+ASM/udump'

[oracle@PSLinux dbs]

But before we proceed with ASM instance creation, we need to do some home work. ASM instance uses Disk(s) (On Linux Un-Mounted partitioned). To use, First we need to configure ASM Library drivers

# /etc/init.d/oracleasm configure

Configuring the Oracle ASM library driver.

Default user to own the driver interface []: oracle

Default group to own the driver interface []: oracle

Start Oracle ASM library driver on boot (y/n) [n]: y

Fix permissions of Oracle ASM disks on boot (y/n) [y]: y

Writing Oracle ASM library driver configuration: [ OK ]

Creating /dev/oracleasm mount point: [ OK ]

Loading module "oracleasm": [ OK ]

Mounting ASMlib driver filesystem: [ OK ]

Scanning system for ASM disks: [ OK ]

# /etc/init.d/oracleasm createdisk VOL1 /dev/ASM2

Marking disk "/dev/ASM2" as an ASM disk: [ OK ]

# /etc/init.d/oracleasm listdisks

VOL1

Now! After this you need to run DBCA and configure ASM instance. The steps are easy to follow and easy to understand.

Once ASM instance is ready, all we need is to opt for “Automatic Storage Management” radio button and provide password of ASM instance in step 6 of DBCA.

Rest is absolutely same.

Any comment ,suggestion are welcome.

Monday, 23 January 2012

Oracle Collection in SQL part 2

In Part1, We explored Nested Table and saw its usage in SQL statement. In this post, We will check impact of SQLs referencing Nested table. We will use the same structure that we created in post 1.

Along with that I created additional tables with same number of rows. We checked explain plan of both of queries. One using Nested table second without using Nested table. Before running our SQL(s), I gather Optimizer Statistics using DBMS_STATS package.

1 begin

2 dbms_stats.gather_schema_stats(ownname=>'scott',estimate_percent=>100,Method_Opt=>'for all columns size 1',cascade=>true);

3* end;

SQL> /

1:- Query without using Nested Table.

SQL> set autotrace traceonly explain stat

1 SELECT SCI2.*,CI.* FROM SCOTT.STU_COURSE_INFO2 SCI2,SCOTT.COURSE_INFO2 CI

2 WHERE SCI2.STU_ID=CI.STU_ID

3 AND SCI2.COURSE_ID=CI.COURSE_ID

4* ORDER BY SCI2.STU_ID,CI.COURSE_ID

As we can see the Explain Plan, Statistics, Access and filter of the query. Now we check the same of query using Nested Table.

Query 2:-

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

2 where x.stu_id=sci.stu_id

3* ORDER BY 1,3

SQL> /

begin

dbms_stats.set_table_stats(ownname=>'scott',tabname=>'course_info2',numrows=>1000000,numblks=>100000,avgrlen=>350);

end;

begin

dbms_stats.set_table_stats(ownname=>'scott',tabname=>'stu_course_info',numrows=>1000000,numblks=>100000,avgrlen=>350);

end;

After STATS setting on table:

Query 1:-

1 SELECT SCI2.*,CI.* FROM SCOTT.STU_COURSE_INFO2 SCI2,SCOTT.COURSE_INFO2 CI

2 WHERE SCI2.STU_ID=CI.STU_ID

3 AND SCI2.COURSE_ID=CI.COURSE_ID

4* ORDER BY SCI2.STU_ID,CI.COURSE_ID

Query 2:-

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

ORDER BY 1,3

As we can see, Although Nested tables helped us by “no Need to have” another table to store Course information but has hindered performance of our query. I have tried to confirm this by setting Statistics of all the participating tables.

Not only, Query using Nested tables have taken more time to execute, but it has also did more Physical I/O and more sorts in memory.

So while using, Nested tables in query, Developer should consider pros and cons of this approach.

Any comment, suggestion or correction is welcome.