Apun's Weblog

dbms_metadata.get_ddl & ORA-31603

2011-12-01T06:24:00.002+05:30

I wanted to allow a database user/schema called TEST (other than user with SYSDBA privileges) to have privileges to run dbms_metadata.get_ddl for any object in the database. Basically for other schemas in the database. Even though I provided execute privileges on the package and even DBA privilege to the user it still gave:


16:37:39 SQL> select dbms_metadata.get_ddl('PACKAGE','PACK1','SCOTT') from dual;
ERROR:
ORA-31603: object "PACK1" of type PACKAGE not found in schema "SCOTT"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 3912
ORA-06512: at "SYS.DBMS_METADATA", line 5678
ORA-06512: at line 1

The privileges required to allow a user/schema (TEST) to be able to run the package are:


grant SELECT_CATALOG_ROLE to TEST;
The most important steps is:
ALTER USER TEST DEFAULT ROLE SELECT_CATALOG_ROLE;

Just by granting SELECT_CATALOG_ROLE to the user/schema you would still get ORA-31603.
So we must alter the user and make SELECT_CATALOG_ROLE as its default role.

How to Move OCR and Voting Disk to ASM Diskgroup in 11gR2

2011-09-01T02:49:00.000+05:30

Case: Moving to Redhat Linux 5/Oracle Enterprise Linux 5, which does not support Raw filesytem.Steps: (these steps can be performanced on 11gR2)1.) Check the current location of ocr file:


[root@host1 bin]# ocrcheck
Status of Oracle Cluster Registry is as follows :
	 Version                  :          3
	 Total space (kbytes)     :     262120
	 Used space (kbytes)      :       3004
	 Available space (kbytes) :     259116
	 ID                       :  798462606
	 Device/File Name         : /mnt/cludata/ocrfile
                                    Device/File integrity check succeeded
                                    Device/File not configured
                                    Device/File not configured
                                    Device/File not configured
                                    Device/File not configured
	 Cluster registry integrity check succeeded
	 Logical corruption check succeeded

2.) Add a Mirror OCR location else you might encounter error :PROT-28: Cannot delete or replace the only configured Oracle Cluster Registry location
i.) Create the file (as root)


cp /dev/null /mnt/cludata/ocrmirror 
chown root:dba /mnt/cludata/ocrmirror 
chmod 640 /mnt/cludata/ocrmirror
[root@host1 cludata]# pwd
/rmanbkp1/cludata
[root@host1 cludata]# ls -lrt
total 23784
-rw-r----- 1 root   dba 272756736 Aug 31 12:37 ocrfile
-rw-r----- 1 root   dba         0 Aug 31 13:12 ocrmirror
-rw-r----- 1 oracle dba  21004288 Aug 31 13:12 cssfile

ii.) Add the mirror OCR file


[root@host1 cludata]# ocrconfig -add /rmanbkp1/cludata/ocrmirror
[root@host1 cludata]# ocrcheck
Status of Oracle Cluster Registry is as follows :
	 Version                  :          3
	 Total space (kbytes)     :     262120
	 Used space (kbytes)      :       3004
	 Available space (kbytes) :     259116
	 ID                       :  798462606
	 Device/File Name         : /mnt/cludata/ocrfile
                                    Device/File integrity check succeeded
	 Device/File Name         : /mnt/cludata/ocrmirror
                                    Device/File integrity check succeeded
                                    Device/File not configured
                                    Device/File not configured
                                    Device/File not configured
	 Cluster registry integrity check succeeded
	 Logical corruption check succeeded

3.) Check ASM Diskgroup compatibility (login as sqlplus / as sysasm)


col name format a10
col COMPATIBILITY format a20
col DATABASE_COMPATIBILITY format a20
select name,COMPATIBILITY,DATABASE_COMPATIBILITY from v$asm_diskgroup;

NAME	   COMPATIBILITY	DATABASE_COMPATIBILI
---------- -------------------- --------------------
DG_ARCH01  10.1.0.0.0		10.1.0.0.0
DG_SYS01   10.1.0.0.0		10.1.0.0.0
DG_TEMP01  10.1.0.0.0		10.1.0.0.0
DG_DATA01 10.1.0.0.0		10.1.0.0.0

4.) Upgrade the compatibility to 11.2compatible.asm


SQL> alter diskgroup DG_ARCH01 set attribute 'compatible.asm'='11.2';
Diskgroup altered.
SQL> alter diskgroup DG_SYS01 set attribute 'compatible.asm'='11.2';
Diskgroup altered.
SQL> alter diskgroup DG_TEMP01 set attribute 'compatible.asm'='11.2';
Diskgroup altered.
SQL> alter diskgroup DG_DATA01 set attribute 'compatible.asm'='11.2';
Diskgroup altered.

SQL> select name,COMPATIBILITY,DATABASE_COMPATIBILITY from v$asm_diskgroup;
NAME	   COMPATIBILITY	DATABASE_COMPATIBILI
---------- -------------------- --------------------
DG_ARCH01  11.2.0.0.0		10.1.0.0.0
DG_SYS01   11.2.0.0.0		10.1.0.0.0
DG_TEMP01  11.2.0.0.0		10.1.0.0.0
DG_DATA01 11.2.0.0.0		10.1.0.0.0

Update compatible.rdbms


SQL> alter diskgroup DG_ARCH01 set attribute 'compatible.rdbms'='11.2';
Diskgroup altered.
SQL> alter diskgroup DG_SYS01 set attribute 'compatible.rdbms'='11.2';
Diskgroup altered.
SQL> alter diskgroup DG_TEMP01 set attribute 'compatible.rdbms'='11.2';
Diskgroup altered.
SQL> alter diskgroup DG_DATA01 set attribute 'compatible.rdbms'='11.2';
Diskgroup altered.

SQL> select name,COMPATIBILITY,DATABASE_COMPATIBILITY from v$asm_diskgroup;
NAME	   COMPATIBILITY	DATABASE_COMPATIBILI
---------- -------------------- --------------------
DG_ARCH01  11.2.0.0.0		11.2.0.0.0
DG_SYS01   11.2.0.0.0		11.2.0.0.0
DG_TEMP01  11.2.0.0.0		11.2.0.0.0
DG_DATA01 11.2.0.0.0		11.2.0.0.0

5.) Move OCR file to ASM:


ocrconfig -replace /rmanbkp1/cludata/ocrfile -replacement +DG_ARCH01
ocrconfig -replace /rmanbkp1/cludata/ocrmirror -replacement +DG_SYS01

6.) Check OCR:


[root@host1 cludata]# ocrcheck
Status of Oracle Cluster Registry is as follows :
	 Version                  :          3
	 Total space (kbytes)     :     262120
	 Used space (kbytes)      :       3004
	 Available space (kbytes) :     259116
	 ID                       :  798462606
	 Device/File Name         : +DG_ARCH01
                                    Device/File integrity check succeeded
	 Device/File Name         :  +DG_SYS01
                                    Device/File integrity check succeeded
                                    Device/File not configured
                                    Device/File not configured
                                    Device/File not configured
	 Cluster registry integrity check succeeded
	 Logical corruption check succeeded

7.) Restart CRS to check if it comes back fine.
NOTE: You will not be able to stop just ASM now, as the OCR file is in ASM
8.) Check OCR:( as oracle user )


[oracle@host1]~% /home/oragrid/product/11.2/bin/cluvfy comp ocr -n all -verbose

Verifying OCR integrity 

Checking OCR integrity...

Checking the absence of a non-clustered configuration...
All nodes free of non-clustered, local-only configurations

ASM Running check passed. ASM is running on all cluster nodes

Checking OCR config file "/etc/oracle/ocr.loc"...

OCR config file "/etc/oracle/ocr.loc" check successful

Disk group for ocr location "+DG_SYS01" available on all the nodes

Disk group for ocr location "+DG_ARCH01" available on all the nodes

Checking size of the OCR location "+DG_SYS01" ...

Size check for OCR location "+DG_SYS01" successful...
Size check for OCR location "+DG_SYS01" successful...
Size check for OCR location "+DG_SYS01" successful...
Size check for OCR location "+DG_SYS01" successful...

Checking size of the OCR location "+DG_ARCH01" ...

Size check for OCR location "+DG_ARCH01" successful...
Size check for OCR location "+DG_ARCH01" successful...
Size check for OCR location "+DG_ARCH01" successful...
Size check for OCR location "+DG_ARCH01" successful...

WARNING: 
This check does not verify the integrity of the OCR contents. Execute 'ocrcheck' as a privileged user to verify the contents of OCR.

OCR integrity check passed

Verification of OCR integrity was successful.

9.) Move Voting disk now


[root@host1 cludata]# crsctl replace votedisk +DG_ARCH01
CRS-4256: Updating the profile
Successful addition of voting disk aa5d2a1024384faebf3f2c108e6b203a                                                                                                                                                                                                                                .
Successful deletion of voting disk 253472991d324fb9bfab1f1301385697.
Successfully replaced voting disk group with +DG_ARCH01.
CRS-4256: Updating the profile
CRS-4266: Voting file(s) successfully replaced

10.) Check Voting Disk:


[root@host1 cludata]# crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   aa5d2a1024384faebf3f2c108e6b203a (ORCL:DISK2) [DG_ARCH01]
Located 1 voting disk(s).
[root@host1 cludata]# crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online

11.) Check contents of ocr.loc file


[root@host1 cludata]# more /etc/oracle/ocr.loc
#Device/file /mnt/cludata/ocrmirror getting replaced by device +DG_SYS01 
ocrconfig_loc=+DG_ARCH01
ocrmirrorconfig_loc=+DG_SYS01
local_only=false

ALL LOOKS GOOD NOW !!!

Oracle: Rename single/RAC Database

2011-07-21T07:07:00.003+05:30

Method 1: Recreate control file with the new DB name, the old tried and tested method.
Method 2: Use new utility called NID
Steps to rename Databases using the new NID utility:
1.) Stop database


[oracle@testing1]~% srvctl status database -d test
Instance test1 is running on node testing1
Instance test2 is running on node testing2

srvctl stop database -d test

2.) startup mount the database:


[oracle@testing1]~% sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Jul 21 00:50:44 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1.2527E+10 bytes
Fixed Size      2238104 bytes
Variable Size   8120174952 bytes
Database Buffers  4294967296 bytes
Redo Buffers    109346816 bytes
Database mounted.

3.) Exit and run the "nid" utility

nid sys/oracle@test DBNAME=new


[oracle@testing1]~% nid sys/m4gent4 DBNAME=new   

DBNEWID: Release 11.2.0.2.0 - Production on Thu Jul 21 00:55:12 2011

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

NID-00002: Parse error: LRM-00108: invalid positional parameter value 'sys/m4gent4'

Change of database ID failed during validation - database is intact.
DBNEWID - Completed with validation errors.

Correct the syntax:
nid target=sys/oracle@test DBNAME=new


[oracle@testing1]~% nid target=sys/m4gent4 DBNAME=new

DBNEWID: Release 11.2.0.2.0 - Production on Thu Jul 21 00:56:04 2011

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

Connected to database test (DBID=3952648861)

NID-00120: Database should be mounted exclusively


Change of database name failed during validation - database is intact.
DBNEWID - Completed with validation errors.

Incase of RAC, we need to set cluster_database=FALSE to change DB name. Then stop and mount the DB again.


SQL> alter system set cluster_database=FALSE scope=spfile;

System altered.

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1.2527E+10 bytes
Fixed Size      2238104 bytes
Variable Size   8120174952 bytes
Database Buffers  4294967296 bytes
Redo Buffers    109346816 bytes
Database mounted.

nid target=sys/oracle@test DBNAME=new


[oracle@testing1]~% nid target=sys/m4gent4 DBNAME=new

DBNEWID: Release 11.2.0.2.0 - Production on Thu Jul 21 00:59:54 2011

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

Connected to database test (DBID=3952648861)

Connected to server version 11.2.0

Control Files in database:
    +DG_SYS01/test/control01.ctl
    +DG_DATA01/test/control02.ctl

Change database ID and database name test to NEW? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 3952648861 to 388598347
Changing database name from test to NEW
    Control File +DG_SYS01/test/control01.ctl - modified
    Control File +DG_DATA01/test/control02.ctl - modified
    Datafile +DG_SYS01/test/datafile/system.261.75517911 - dbid changed, wrote new name
    Datafile +DG_SYS01/test/datafile/sysaux.262.75517911 - dbid changed, wrote new name
    Datafile +DG_DATA01/test/datafile/undotbs1.258.75518469 - dbid changed, wrote new name
    Datafile +DG_DATA01/test/datafile/undotbs2.260.75518577 - dbid changed, wrote new name
    Datafile +DG_DATA01/test/datafile/users.257.75518565 - dbid changed, wrote new name
    Control File +DG_SYS01/test/control01.ctl - dbid changed, wrote new name
    Control File +DG_DATA01/test/control02.ctl - dbid changed, wrote new name
    Instance shut down

Database name changed to NEW.
Modify parameter file and generate a new password file before restarting.
Database ID for database NEW changed to 388598347.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

4.) Copy the old inittest1.ora as initnew1.ora, update the DB_NAME, instance_name parameter to reflect the new name.
Then startup mount


SQL> startup mount pfile='/home/oracle/software/dbcreate/test/inittest1.ora' 
ORACLE instance started.

Total System Global Area 1.2527E+10 bytes
Fixed Size      2238104 bytes
Variable Size   8120174952 bytes
Database Buffers  4294967296 bytes
Redo Buffers    109346816 bytes
Database mounted.

5.) Resetlog open the database.


SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

6.) Create spfile


SQL> create spfile='+DG_SYS01/NEW/spfileNEW.ora' from pfile='/home/oracle/software/dbcreate/test/inittest1.ora';

File created.

NOTE: For single node DB, steps are over. Step 7 for RAC only.

7.) Add DB to the cluster


[oracle@testing1]~/software/dbcreate/test% srvctl add database -d NEW -o /home/oracle/product/11.2 -r primary -s OPEN -p +DG_SYS01/NEW/spfileNEW.ora
[oracle@testing1]~/software/dbcreate/test% srvctl add instance -d NEW -i NEW1 -n testing1
[oracle@testing1]~/software/dbcreate/test% srvctl add instance -d NEW -i NEW2 -n testing2
[oracle@testing1]~/software/dbcreate/test% srvctl config database -d NEW -a
Database unique name: NEW
Database name: 
Oracle home: /home/oracle/product/11.2
Oracle user: oracle
Spfile: +DG_SYS01/NEW/spfileNEW.ora
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: NEW
Database instances: NEW1,NEW2
Disk Groups: 
Mount point paths: 
Services: 
Type: RAC
Database is enabled
Database is administrator managed
[oracle@testing1]~/software/dbcreate/test% vi inittest1.ora 
[oracle@testing1]~/software/dbcreate/test% srvctl start database -d NEW
[oracle@testing1]~/software/dbcreate/test% srvctl status database -d NEW
Instance NEW1 is running on node testing1
Instance NEW2 is running on node testing2

 INST_ID INSTANCE_NAM      HOST_NAME  STATUS     STARTED     VERSION
-------- ------------------------- ----------------------------------- ---------- 
   1     new1         testing1   OPEN       20 Jul 18:25:20    11.2.0.2.0
   2     new2         testing2   OPEN       20 Jul 18:25:22    11.2.0.2.0

IMPDP Stuck with wait event - "wait for unread message on broadcast channel"

2011-05-19T05:13:00.001+05:30

I was trying to run data import using impdp and the job was stuck with wait event "wait for unread message on broadcast channel".
This is an idle wait event as per oracle, that means oracle is waiting for something to happen for the session to proceed.

I googled and checked metalink but couldn't really find a solution and my job was stuck indefinitely.
From one of the forums online I figured out an old defunct job in the DB can cause new impdp sessions to be stuck.

Hence I queried dba_datapump_jobs and found out that I have an old job in "NOT RUNNING" state:


SQL> select * from dba_datapump_jobs;

OWNER_NAME   JOB_NAME                       OPERATION    JOB_MODE     STATE                     DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
------------ ------------------------------ ------------ ------------ ------------------- ------------ ----------------- -----------------
SYSTEM       NEW1                           IMPORT       TABLE        EXECUTING                      4                 1                 3
SYSTEM       SYS_IMPORT_FULL_01             IMPORT       FULL         NOT RUNNING                    0                 0                 0

Checked to find the underlying object:


SQL> SELECT a.object_id, a.object_type, a.owner, a.object_name, a.status
  FROM dba_objects a, dba_datapump_jobs j
 WHERE a.owner=j.owner_name AND a.object_name=j.job_name
 and j.job_name='SYS_IMPORT_FULL_01';

    OBJECT_ID OBJECT_TYPE         OWNER                OBJECT_NAME            STATUS
------------ ------------------- -------------------- ---------------------- -------
     984119  TABLE               SYSTEM               SYS_IMPORT_FULL_01      VALID

15:02:47 SQL> select owner,object_name,subobject_name, object_type,last_ddl_time from dba_objects where object_id=984119
/

OWNER                OBJECT_NAME                              SUBOBJECT_NAME                 OBJECT_TYPE         LAST_DDL_TIME
-------------------- ---------------------------------------- ------------------------------ ------------------- -------------------
SYSTEM               SYS_IMPORT_FULL_01                                                      TABLE               13-08-2010 19:59:50

Dropped the datapump job table:


15:02:59 SQL> drop table SYSTEM.SYS_IMPORT_FULL_01;

Table dropped.

And then my impdp job "NEW1" started progressing:


15:04:03 SQL> select * from dba_datapump_jobs;

OWNER_NAME   JOB_NAME                       OPERATION    JOB_MODE     STATE                     DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
------------ ------------------------------ ------------ ------------ ------------------- ------------ ----------------- -----------------
SYSTEM       NEW1                           IMPORT       TABLE        EXECUTING                      4                 1                 6

NOTE: Please make sure that the old job is NOT RUNNING.
Secondly, if its a known job you can also try to:
impdp username/password attach=JOB_NAME
and then at the IMPDP prompt do a kill_job. This should also clear the jobs status from dba_datapump_jobs.

Drop Materialized View takes a long time

2011-01-27T00:47:00.007+05:30

Recently I had to drop a couple of large Materialized View.
And dropping them was taking a long time, as it tries to drop the data in both source and destination DB. In Source DB it tries to purge the mview log and at destination mview itself.
To accelerate the process I tried truncating the mview tables at destination and also the mview log table at source.

At destination (mview site):


truncate table mview_to_drop;

At source (mview log site):


select master,log_table from dba_mview_logs where master='MVIEW_TO_DROP';
LOG_OWNER     MASTER                    LOG_TABLE
------------ ------------------------------ ------------------------------
SCOTT         MVIEW_TO_DROP             MLOG$_MVIEW_TO_DROP

truncate table SCOTT.MLOG$_MVIEW_TO_DROP;

Now back at destination site:


drop materialized view SCOTT.MVIEW_TO_DROP;

Materialized view dropped.

This is the fastest way I could find, please let me know if anyone else has any ideas.

How to Setup Resource Manager to Control DB Resources

2011-01-14T04:41:00.002+05:30

Step By Step Setup:

Step 1: Create a work area for creation/modification of resource manager objects.


SQL> exec dbms_resource_manager.create_pending_area();

Step 2: Create Resource Manager PLAN

 
SQL> exec dbms_resource_manager.create_plan(plan => 'PLAN_NAME', comment => 'Comments abt the plan');  
e.g.
SQL> exec dbms_resource_manager.create_plan( plan => 'ETL_PLAN', comment => 'PLAN for ETL processes');

Step 3: Create a consumer group which to which the PLAN details would be assigned

 
SQL> exec dbms_resource_manager.create_consumer_group ( consumer_group => 'COUNSUMER_GROUP_NAME' , comment =>'Comments abt the consumer_group'); 

e.g.
SQL> exec dbms_resource_manager.create_consumer_group ( consumer_group => 'ETL_GROUP' , comment =>'Check Max Degree of parallelism for ETL process');

Step 4: Assign the Consumer group to the Resource Plan created and define the resource limits

 
SQL> exec dbms_resource_manager.create_plan_directive( plan => 'ETL_PLAN',      
              group_or_subplan => 'ETL_GROUP',
              comment => '',
              cpu_p1 => NULL,
              cpu_p2 => NULL,
              cpu_p3 => NULL,
              cpu_p4 => NULL,
              cpu_p5 => NULL,
              cpu_p6 => NULL,
              cpu_p7 => NULL,
              cpu_p8 => NULL,
              parallel_degree_limit_p1 => 1,
              active_sess_pool_p1 => NULL,
              queueing_p1 => NULL,
              switch_group => NULL,
              switch_time => NULL,
              switch_estimate => false,
              max_est_exec_time => NULL,
              undo_pool => NULL,
              max_idle_time => NULL,
              max_idle_blocker_time => NULL,
              switch_time_in_call => NULL);

Examples:
1.) Limit max parallelism

 
SQL> exec dbms_resource_manager.create_plan_directive ( plan => 'ETL_PLAN', 
              group_or_subplan =>'ETL_GROUP',
              comment => 'Limit CPU resource', 
              parallel_degree_limit_p1 => 4 );

2.) Disconnect Idle Users after a Predetermined time

 
SQL> exec dbms_resource_manager.create_plan_directive(plan => 'ETL_PLAN', 
           group_or_subplan => 'ETL_GROUP', 
           comment => 'Limit idle time', 
           max_idle_time => 300);

3.) Control CPU Resources

 
SQL> exec dbms_resource_manager.create_plan_directive ( plan => 'ETL_PLAN', 
              group_or_subplan =>'ETL_GROUP',
              comment => 'Limit CPU resource', 
              cpu_p1 => 80 );

4.) Limit max number of concurrently active sessions

 
SQL> exec DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE ( plan => 'ETL_PLAN',
              group_or_subplan =>'ETL_GROUP', 
              comment => 'Max 10 Active Sessions with 20 in queue, rest to timeout', 
              active_sess_pool_p1=>10, 
              queueing_p1=>20);

Step 5: Validate the PLAN setting.

 
SQL> exec dbms_resource_manager.validate_pending_area();

You will encounter the below error:

 
ERROR at line 1:
ORA-29382: validation of pending area failed
ORA-29377: consumer group OTHER_GROUPS is not part of top-plan ETL_PLAN
ORA-06512: at "SYS.DBMS_RMIN", line 437
ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 798
ORA-06512: at line 2

Basically for each plan we need to define the plan directives for the OTHER_GROUPS also, what that means is determine limits for all other sessions other than those bound by the consumer group ETL_GROUP.

 
SQL> exec dbms_resource_manager.create_plan_directive ( plan => 'ETL_PLAN', 
              group_or_subplan =>'OTHER_GROUPS',
              comment => 'Limit CPU resource', 
              parallel_degree_limit_p1 => 2);   

SQL> exec dbms_resource_manager.validate_pending_area();

Step 6: Submit the changes/pending area that was created

 
SQL> exec dbms_resource_manager.submit_pending_area();

Step 7: Switch the consumer group of the schema/user

 
SQL> exec dbms_resource_manager.create_pending_area();  
SQL> exec dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name=>'SCOTT',consumer_group=>'ETL_GROUP',grant_option=>FALSE); 

Make is the default/initial consumer group for the user.
SQL> exec dbms_resource_manager.set_initial_consumer_group(user => 'SCOTT',consumer_group =>'ETL_GROUP'); 

SQL> exec dbms_resource_manager.submit_pending_area();

Others:
Modify Consumer Group:

 
exec dbms_resource_manager.UPDATE_PLAN_DIRECTIVE( plan => 'ETL_PLAN', group_or_subplan => 'ETL_GROUP', new_comment => 'ETL_GROUP Change the DOP to 8', new_parallel_degree_limit_p1 => 8);

How to refresh MVIEW in parallel

2010-11-30T07:33:00.002+05:30

Refresh TYPES are COMPLETE/FAST/ON COMMIT/ON DEMAND
Couple of things you should do to make a mview to refresh in parallel:
1.) Alter the master table to make sure it allows parallelism:


alter table  parallel (degree 4);
To check
select degree from dba_tables where table_name='';

2.) When you create the mview log (incase of FAST refresh mviews), create it with a parallel clause:


create materialized view log on  parallel (degree 4);
OR
Incase of existing mviews:
alter materialized view log on  parallel (degree 4);

3.) When you create the mview, create it with a parallel clause:


create materialized view  REFRESH COMPLETE ON DEMAND  as select /*+ PARALLEL (table_name,4) */ * from @dblink;
OR
create materialized view  REFRESH COMPLETE ON DEMAND  parallel 4 as select * from @dblink;
Or
Incase of existing mviews:
alter table  parallel (degree 4);
alter materialized view  parallel (degree 4);

Lastly while executing the refresh:


EXECUTE DBMS_MVIEW.REFRESH(LIST=>'',PARALLELISM=>4);

This should make your mview refresh in parallel. With the above example the mview should run with parallel degree 4.

PS: Make sure that parallel is enabled for your database, check parameters:

PARALLEL_MAX_SERVERS
PARALLEL_EXECUTION_MESSAGE_SIZE
PARALLEL_ADAPTIVE_MULTI_USER
PARALLEL_AUTOMATIC_TUNING

Make sure that the above parameter are set to allow any parallelism to work.
Sample values for these parameters:


SQL> show parameter PARALLEL_MAX_SERVERS
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers                 integer     120
SQL> show parameter PARALLEL_EXECUTION_MESSAGE_SIZE
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_execution_message_size      integer     16384
SQL> show parameter PARALLEL_ADAPTIVE_MULTI_USER
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_adaptive_multi_user         boolean     TRUE
SQL>  show parameter PARALLEL_AUTOMATIC_TUNING
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_automatic_tuning            boolean     FALSE

Note:
1.) Replace with the table name.
2.) Replace with materialized view name.
3.) Replacedblink with the name of the database link.

Import Error: IMP-00032: SQL statement exceeded buffer length

2010-11-17T00:06:00.004+05:30

While trying to import a big table with 1000s of partitions I got error:


IMP-00032: SQL statement exceeded buffer length
IMP-00008: unrecognized statement in the export file:
  MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TEST_PART_1" NOLOGGING COMPRESS, PARTITION "TEST_NOV_1" VALUES LESS THAN (2010112806)  PCTFREE 0 PCTUSED 40...

The table was exported simiply with:


exp system/oracle file=tab.dmp tables=table_part

While run import:


imp system/oracle file=tab.dmp fromuser=scott touser=tiger

I got the above error.
I tried various combination of parameter buffer i.e.


imp system/oracle file=tab.dmp fromuser=scott touser=tiger buffer=1000000 commit=y

etc, but still got the error. So I wanted to know whats the value required for buffer.

Solution:
In the source database we need to run:


SQL> select dbms_lob.getlength (dbms_metadata.get_ddl('TABLE','table_part','SCOTT')) "SQL_LENGTH" from dual;

SQL_LENGTH
----------
   3886502

The above output gives the value of buffer we need to have atleast to run the import, so I used:


imp system/o1ympic file=tabs.dmp log=tabs.log fromuser=SCOTT touser=TIGER  commit=y ignore=Y buffer=44886502

This solved the problem.
Metalink Note: 846397.1

Oracle 11gr2 Grid: root.sh fails on node2, asmlib issue

2010-09-09T06:05:00.003+05:30

A very interesting problem which took me quite a while to resolve.
Issue while running root.sh on node 2 during 11gr2 grid software installation i was receiving the following error:
Error in root.sh:


DiskGroup DG_SYS01 creation failed with the following message:
ORA-15018: diskgroup cannot be created
ORA-15031: disk specification 'ORCL:DISK0' matches no disks


Configuration of ASM failed, see logs for details
Did not succssfully configure and start ASM
CRS-2500: Cannot stop resource 'ora.crsd' as it is not running
CRS-4000: Command Stop failed, or completed with errors.
Command return code of 1 (256) from command: /oragrid/product/11.2/bin/crsctl stop resource ora.crsd -init
Stop of resource "ora.crsd -init" failed
Failed to stop CRSD

Error in ASM alert log:


ORA-15183: ASMLIB initialization error [driver/agent not installed]
WARNING: FAILED to load library: /opt/oracle/extapi/64/asm/orcl/1/libasm.so
ERROR: diskgroup DG_SYS01 was not mounted
NOTE: cache deleting context for group DG_SYS01 1/-239075992
WARNING: Disk Group DG_SYS01 containing configured OCR is not mounted
ORA-15032: not all alterations performed
ORA-15017: diskgroup "DG_SYS01" cannot be mounted
ORA-15063: ASM discovered an insufficient number of disks for diskgroup "DG_SYS01"
ERROR: ALTER DISKGROUP ALL MOUNT
Wed Sep 08 22:09:42 2010
SQL> CREATE DISKGROUP DG_SYS01 EXTERNAL REDUNDANCY  DISK 'ORCL:DISK0' ATTRIBUTE 'compatible.asm'='11.2.0.0.0' /* ASMCA */
ORA-15018: diskgroup cannot be created
ORA-15031: disk specification 'ORCL:DISK0' matches no disks
ERROR: CREATE DISKGROUP DG_SYS01 EXTERNAL REDUNDANCY  DISK 'ORCL:DISK0' ATTRIBUTE 'compatible.asm'='11.2.0.0.0' /* ASMCA */
kfdp_dismount(): 3
kfdp_dismountBg(): 3
ERROR: diskgroup DG_SYS01 was not created

What was puzzling was that why is node2 trying to run CREATE diskgroup, while node1 has run successfully and created the diskgroup.
Other errors in cssd.log


2010-09-08 20:06:14.856: [   SKGFD][1151920448]ERROR: -14(asmlib /opt/oracle/extapi/64/asm/orcl/1/libasm.so version failed with 2)
...
2010-09-08 20:06:14.856: [   SKGFD][1151920448]Discovery skipping bad asmlib :ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:

2010-09-08 20:06:14.856: [    CSSD][1151920448]clssnmvDiskVerify: Successful discovery of 0 disks
2010-09-08 20:06:14.856: [    CSSD][1151920448]clssnmCompleteInitVFDiscovery: Completing initial voting file discovery
2010-09-08 20:06:14.857: [    CSSD][1151920448]clssnmvFindInitialConfigs: No voting files found

This showed that it looked like an issue with the oracleasm library.
But I was able to execute all /etc/init.d/oracleasm commands without any problems.
e.g /etc/init.d/oracleasm listdisks --> this showed all the disk correctly.

I tried rerunning /etc/init.d/oracleasm configure. But still continued to get the error.

I used steps mentioned in http://jarneil.wordpress.com/2008/07/07/asmlib-troubleshooting/ to make sure the libraries were installed properly. This note was really helpful, I would like to thank the author.
The libraries were installed properly.

I had actually reinstalled the oracleasm libraries also:


[oracle@node2]/% rpm -qa |grep oracleasm
oracleasm-support-2.1.3-1.el5
oracleasm-2.6.18-128.el5debug-2.0.5-1.el5
oracleasm-2.6.18-128.el5-2.0.5-1.el5
oracleasm-2.6.18-128.el5xen-2.0.5-1.el5
oracleasm-2.6.18-128.el5-debuginfo-2.0.5-1.el5
oracleasmlib-2.0.4-1.el5

Finally after a lot of searching (google, metalink..etc...etc).
I found metalink note "FAQ ASMLIB CONFIGURE,VERIFY, TROUBLESHOOT [ID 359266.1]" on metalink and started with all the checks mentioned.
Finally I figured out that there was problem with my /etc/sysconfig/oracleasm file. This file on other servers is a sym link:


[oracle@node1]/% ls -lrt /etc/sysconfig/oracle*
-rw-r--r-- 1 root root 774 Sep  8 23:32 /etc/sysconfig/oracleasm-_dev_oracleasm
lrwxrwxrwx 1 root root  24 Sep  8 23:36 /etc/sysconfig/oracleasm -> oracleasm-_dev_oracleasm

But in my case it was:


[root@node2 sysconfig]# ls -lrt oracle*
-rw-r--r-- 1 root root 574 Mar 18  2009 oracleasm
lrwxrwxrwx 1 root root  24 Sep  7 23:30 oracleasm.rpmsave -> oracleasm-_dev_oracleasm
-rw-r--r-- 1 root root 774 Sep  8 23:32 oracleasm-_dev_oracleasm

And all the parameters were blank inside oracleasm. What I understood is that libasm.so used oracleasm file.
So I:


[root@node2 sysconfig]# ln -s oracleasm-_dev_oracleasm oracleasm
[root@node2 sysconfig]# ls -lrt oracle*
lrwxrwxrwx 1 root root  24 Sep  7 23:30 oracleasm.rpmsave -> oracleasm-_dev_oracleasm
-rw-r--r-- 1 root root 774 Sep  8 23:32 oracleasm-_dev_oracleasm
lrwxrwxrwx 1 root root  24 Sep  8 23:36 oracleasm -> oracleasm-_dev_oracleasm
[root@node2 sysconfig]# rm oracleasm.rpmsave
rm: remove symbolic link `oracleasm.rpmsave'? y

Now as I had already run root.sh and it had failed, i was unable to run it again:


[root@node2 11.2]# ./root.sh
Running Oracle 11g root.sh script...

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /oragrid/product/11.2

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file "dbhome" already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]: y
   Copying dbhome to /usr/local/bin ...
The file "oraenv" already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]: y
   Copying oraenv to /usr/local/bin ...
The file "coraenv" already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]: y
   Copying coraenv to /usr/local/bin ...

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
2010-09-08 23:37:12: Parsing the host name
2010-09-08 23:37:12: Checking for super user privileges
2010-09-08 23:37:12: User has super user privileges
Using configuration parameter file: /oragrid/product/11.2/crs/install/crsconfig_params
CRS is already configured on this node for crshome=0
Cannot configure two CRS instances on the same cluster.
Please deconfigure before proceeding with the configuration of new home.

So first we have to deconfigure the previous run of root.sh:


[root@node2 11.2]# crs/install/rootcrs.pl -verbose -deconfig -force
2010-09-08 23:37:48: Parsing the host name
2010-09-08 23:37:48: Checking for super user privileges
2010-09-08 23:37:48: User has super user privileges
Using configuration parameter file: crs/install/crsconfig_params
PRCR-1035 : Failed to look up CRS resource ora.cluster_vip.type for 1
PRCR-1068 : Failed to query resources
Cannot communicate with crsd
PRCR-1070 : Failed to check if resource ora.gsd is registered
Cannot communicate with crsd
PRCR-1070 : Failed to check if resource ora.ons is registered
Cannot communicate with crsd
PRCR-1070 : Failed to check if resource ora.eons is registered
Cannot communicate with crsd

ACFS-9200: Supported
CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4000: Command Stop failed, or completed with errors.
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'node2'
CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'node2'
CRS-2677: Stop of 'ora.drivers.acfs' on 'node2' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'node2' has completed
CRS-4133: Oracle High Availability Services has been stopped.
error: package cvuqdisk is not installed
Successfully deconfigured Oracle clusterware stack on this node

After this I restart oracleasm:


[root@node2 11.2]# /etc/init.d/oracleasm stop
Dropping Oracle ASMLib disks:                              [  OK  ]
Shutting down the Oracle ASMLib driver:                    [  OK  ]
[root@node2 11.2]# /etc/init.d/oracleasm start
Initializing the Oracle ASMLib driver:                     [  OK  ]
Scanning the system for Oracle ASMLib disks:               [  OK  ]

Check that the sym link is also in place, basically to see if restarting oracleasm changed anything:


lrwxrwxrwx 1 root root   24 Sep  8 23:36 oracleasm -> oracleasm-_dev_oracleasm

All looking fine i reran root.sh and it went through fine.

Some command that I ran in the pursuit to find the problem:


/etc/init.d/oracleasm listdisks
/etc/init.d/oracleasm start
/etc/init.d/oracleasm status
ls -rlt /dev/oracleasm/disks/
/etc/init.d/oracleasm querydisk DISK0
rpm -ql oracleasm-support
df -ha |grep asm
rpm -ql oracleasmlib
/usr/sbin/oracleasm-discover
/usr/sbin/oracleasm-discover 'ORCL:*'
/usr/sbin/oracleasm-discover 'ORCL:*'

Oracle 11gR2 Grid Infrastructure Installation Steps

2010-08-28T05:57:00.005+05:30

Step By Step Installation 11gR2 Non ASM
========================================
Connect as root user:
1.) groupadd dba
2.) useradd -d /home/oracle -g dba -G dba oracle
3.) Create directories for oracle grid installation, the directory path should not be same as $ORACLE_BASE. Assuming $ORACLE_BASE=/oracle.
mkdir /oragrid
Make sure the directory as atleast 5GB free space.
4.) Change the owner of the directory to oracle:dba
chown -R oracle:dba /oragrid
5.) RPM requirements (From Metalink note# 880989.1:


        1.) binutils-2.17.50.0.6-6.el5 (x86_64) 
        2.) compat-libstdc++-33-3.2.3-61 (x86_64) <<< both ARCH's are required. See next line. 
        3.) compat-libstdc++-33-3.2.3-61 (i386) <<< both ARCH's are required. See previous line. 
        4.) elfutils-libelf-0.125-3.el5 (x86_64) 
        5.) glibc-2.5-24 (x86_64) <<< both ARCH's are required. See next line. 
        6.) glibc-2.5-24 (i686) <<< both ARCH's are required. See previous line. 
        7.) glibc-common-2.5-24 (x86_64) 
        8.) ksh-20060214-1.7 (x86_64) 
        9.) libaio-0.3.106-3.2 (x86_64) <<< both ARCH's are required. See next line. 
        10.) libaio-0.3.106-3.2 (i386) <<< both ARCH's are required. See previous line. 
        11.) libgcc-4.1.2-42.el5 (i386) <<< both ARCH's are required. See next line. 
        12.) libgcc-4.1.2-42.el5 (x86_64) <<< both ARCH's are required. See previous line. 
        13.) libstdc++-4.1.2-42.el5 (x86_64) <<< both ARCH's are required. See next line. 
        14.) libstdc++-4.1.2-42.el5 (i386) <<< both ARCH's are required. See previous line. 
        15.) make-3.81-3.el5 (x86_64) 
     16.) elfutils-libelf-devel-0.125-3.el5.x86_64.rpm 
            a.) requires elfutils-libelf-devel-static-0.125-3.el5.x86_64.rpm as a prerequisite, as listed below. 
            b.) elfutils-libelf-devel and elfutils-libelf-devel-static each depend upon the other. Therefore, they must be installed together, in one (1) "rpm -ivh" command as follows: 
                 rpm -ivh elfutils-libelf-devel-0.125-3.el5.x86_64.rpm elfutils-libelf-devel-static-0.125-3.el5.x86_64.rpm 
        17.) glibc-headers-2.5-24.x86_64.rpm 
            a.) requires kernel-headers-2.6.18-92.el5.x86_64.rpm as a prerequisite, as listed below 
        18.) glibc-devel-2.5-24.x86_64.rpm <<< both ARCH's are required. See next item. 
        19.) glibc-devel-2.5-24.i386.rpm <<< both ARCH's are required. See previous item. 
        20.) gcc-4.1.2-42.el5.x86_64.rpm 
            a.) requires libgomp-4.1.2-42.el5.x86_64.rpm as a prerequisite, as listed below 
        21.) libstdc++-devel-4.1.2-42.el5.x86_64.rpm 
        22.) gcc-c++-4.1.2-42.el5.x86_64.rpm 
        23.) libaio-devel-0.3.106-3.2.x86_64.rpm <<< both ARCH's are required. See next item 
        24.) libaio-devel-0.3.106-3.2.i386.rpm <<< both ARCH's are required. See previous item. 
        25.) sysstat-7.0.2-1.el5.x86_64.rpm 
        26.) unixODBC-2.2.11-7.1.x86_64.rpm <<< both ARCH's are required. See next item 
        27.) unixODBC-2.2.11-7.1.i386.rpm <<< both ARCH's are required. See previous item. 
        28.) unixODBC-devel-2.2.11-7.1.x86_64.rpm <<< both ARCH's are required. See next item 
        29.) unixODBC-devel-2.2.11-7.1.i386.rpm <<< both ARCH's are required. See previous item.

Command to check:


 rpm -qa |grep -E "binutils|compat-libstdc++|elfutils-libelf-|glibc-|glibc-common-|ksh-|libaio-|libgcc-|libstdc++|make|gcc|sysstat|unixODBC"

6.) Values for /etc/sysctl.conf (these are suggested, these values should be calculated with respect to the memory/cpu/processes of the server.)

 
    kernel.shmall = physical RAM size / pagesize For most systems
    kernel.shmmax = 1/2 of physical RAM eg. for 32g ram it should be 17179869184.
    kernel.shmmni = 4096 
    kernel.sem = 250 32000 100 128 
    fs.file-max = 409200
    fs.aio-max-nr = 1048576
    net.ipv4.ip_local_port_range = 9000 65500 
    net.core.rmem_default = 262144 
    net.core.rmem_max = 4194304 
    net.core.wmem_default = 262144 
    net.core.wmem_max = 1048576

7.) Make sure u have configured a scan IP, for more details on what is scan IP please refer to: http://www.oracle.com/technetwork/database/clustering/overview/scan-129069.pdf
Make sure your /etc/hosts file has entries or hostname, host IP address, Interconnect name, Interconnect IP address, virtual hostname, virtual host IP address for all RAC nodes and on all RAC nodes.

8a.) Since we are using Network Time Protocol (NTP) for synchronization of time across all the servers in the cluster, a mandatory requirement with 11gR2 is to enable the slewing option by adding ‘-x’ argument in the ntp configuration file as seen below: -
# vi /etc/sysconfig/ntpd


# Drop root to id 'ntp:ntp' by default.
OPTIONS="-x -u ntp:ntp -p /var/run/ntpd.pid" #add -x in the options string.
# Set to 'yes' to sync hw clock after successful ntpdate
SYNC_HWCLOCK=no
# Additional options for ntpdate
NTPDATE_OPTIONS=""

Restart ntpd daemon.
[root@node1 oracle]# /etc/init.d/ntpd stop
Shutting down ntpd: [ OK ]
[root@node1 oracle]# /etc/init.d/ntpd start
ntpd: Synchronizing with time server: [ OK ]
Starting ntpd: [ OK ]

8b.) Update /etc/security/limits.conf with:


         oracle soft nproc 2047 
         oracle hard nproc 16384 
         oracle soft nofile 1024 
         oracle hard nofile 65536

9.) Add following line in the /etc/pam.d/login file:


         session required pam_limits.so

10.) Add the following lines to /etc/profile:


         if [ $USER = "oracle" ]; then 
            if [ $SHELL = "/bin/ksh" ]; then 
                ulimit -u 16384 
                ulimit -n 65536 
            else 
                ulimit -u 16384 -n 65536 
            fi 
         fi

11.) Setup user equivalence between the oracle user of the rac nodes.
12.) Run Cluverify (change directory where you have copied and unzipped the oracle 11gr2 grid software)
#./runcluvfy.sh stage -pre crsinst -n node1,node2 -verbose

--Fix any error you see.

13.) Start ./runInstaller
Screenshots:

Welcome Screen

14.)

Select Advanced Installation

15.)

Select all required languages, I have selected only English

16.)

Specify scan name, cluster name, and scan listener port.

17.)

Provide RAC node information, along with virtual name.

18.)

Review is the the interfaces, subnet mask are correct and select the public/private accordingly.(private is for interconnect).

19.)

Oracle will do some validation checks

20.)

Select ASM/shared file system, depending on what u are using. I am using shared file system.

21.)

Select location for OCR.

22.)

Select location of voting disks.

23.)

Failure Support, I dont plan to use this.

24.)

Select the OS groups.

25.)

Warning message, ignore it

26.)

Specify oracle_base and grid installation folder.

27.)

Specify Oracle Inventory Location.

28.)

Oracle perform pre-checks. I am ignoring swap space issue.

29.)

Final summary page!!

30.)

Ignore this warning

31.)

Shows installation Progress

32.)

Run the scripts

33.) Run /oracle/oraInventory/orainstRoot.sh


[root@node1 oraInventory]# ./orainstRoot.sh
Changing permissions of /oracle/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.

Changing groupname of /oracle/oraInventory to dba.
The execution of the script is complete.

34.) Run $GRID_HOME/root.sh


[root@node1 11.2]# ./root.sh
Running Oracle 11g root.sh script...

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /oragrid/product/11.2

Enter the full pathname of the local bin directory: [/usr/local/bin]:
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   Copying coraenv to /usr/local/bin ...


Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
2010-08-27 23:25:19: Parsing the host name
2010-08-27 23:25:19: Checking for super user privileges
2010-08-27 23:25:19: User has super user privileges
Using configuration parameter file: /oragrid/product/11.2/crs/install/crsconfig_params
Creating trace directory
LOCAL ADD MODE
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
  root wallet
  root wallet cert
  root cert export
  peer wallet
  profile reader wallet
  pa wallet
  peer wallet keys
  pa wallet keys
  peer cert request
  pa cert request
  peer cert
  pa cert
  peer root cert TP
  profile reader root cert TP
  pa root cert TP
  peer pa cert TP
  pa peer cert TP
  profile reader pa cert TP
  profile reader peer cert TP
  peer user cert
  pa user cert
Adding daemon to inittab
CRS-4123: Oracle High Availability Services has been started.
ohasd is starting
CRS-2672: Attempting to start 'ora.gipcd' on 'node1'
CRS-2672: Attempting to start 'ora.mdnsd' on 'node1'
CRS-2676: Start of 'ora.gipcd' on 'node1' succeeded
CRS-2676: Start of 'ora.mdnsd' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'node1'
CRS-2676: Start of 'ora.gpnpd' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'node1'
CRS-2676: Start of 'ora.cssdmonitor' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'node1'
CRS-2672: Attempting to start 'ora.diskmon' on 'node1'
CRS-2676: Start of 'ora.diskmon' on 'node1' succeeded
CRS-2676: Start of 'ora.cssd' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.ctssd' on 'node1'
CRS-2676: Start of 'ora.ctssd' on 'node1' succeeded
clscfg: -install mode specified
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
CRS-2672: Attempting to start 'ora.crsd' on 'node1'
CRS-2676: Start of 'ora.crsd' on 'node1' succeeded
Now formatting voting disk: /u05/cludata/votedisk1.
Now formatting voting disk: /u01/oradata/orcl/cludata/votedisk2.
Now formatting voting disk: /u02/oradata/orcl/cludata/votedisk3.
CRS-4603: Successful addition of voting disk /u05/cludata/votedisk1.
CRS-4603: Successful addition of voting disk /u01/oradata/orcl/cludata/votedisk2.
CRS-4603: Successful addition of voting disk /u02/oradata/orcl/cludata/votedisk3.
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   a893117617e74ffebfd7a379396f281d (/u05/cludata/votedisk1) []
 2. ONLINE   018eafc00dfd4fa9bf568fa245581dcf (/u01/oradata/orcl/cludata/votedisk2) []
 3. ONLINE   0460f0a8560d4f0abf624c47870654fd (/u02/oradata/orcl/cludata/votedisk3) []
Located 3 voting disk(s).
CRS-2673: Attempting to stop 'ora.crsd' on 'node1'
CRS-2677: Stop of 'ora.crsd' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.ctssd' on 'node1'
CRS-2677: Stop of 'ora.ctssd' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.cssdmonitor' on 'node1'
CRS-2677: Stop of 'ora.cssdmonitor' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'node1'
CRS-2677: Stop of 'ora.cssd' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on 'node1'
CRS-2677: Stop of 'ora.gpnpd' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'node1'
CRS-2677: Stop of 'ora.gipcd' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.mdnsd' on 'node1'
CRS-2677: Stop of 'ora.mdnsd' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.mdnsd' on 'node1'
CRS-2676: Start of 'ora.mdnsd' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.gipcd' on 'node1'
CRS-2676: Start of 'ora.gipcd' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'node1'
CRS-2676: Start of 'ora.gpnpd' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'node1'
CRS-2676: Start of 'ora.cssdmonitor' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'node1'
CRS-2672: Attempting to start 'ora.diskmon' on 'node1'
CRS-2676: Start of 'ora.diskmon' on 'node1' succeeded
CRS-2676: Start of 'ora.cssd' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.ctssd' on 'node1'
CRS-2676: Start of 'ora.ctssd' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.crsd' on 'node1'
CRS-2676: Start of 'ora.crsd' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.evmd' on 'node1'
CRS-2676: Start of 'ora.evmd' on 'node1' succeeded

node1     2010/08/27 23:36:53     /oragrid/product/11.2/cdata/node1/backup_20100827_233653.olr
Preparing packages for installation...
cvuqdisk-1.0.7-1
Configure Oracle Grid Infrastructure for a Cluster ... succeeded
Updating inventory properties for clusterware
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 12287 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /oracle/oraInventory
'UpdateNodeList' was successful.

Run the same on node2


[root@node2 11.2]# ./root.sh
Running Oracle 11g root.sh script...

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /oragrid/product/11.2

Enter the full pathname of the local bin directory: [/usr/local/bin]:
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   Copying coraenv to /usr/local/bin ...


Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
2010-08-27 23:44:15: Parsing the host name
2010-08-27 23:44:15: Checking for super user privileges
2010-08-27 23:44:15: User has super user privileges
Using configuration parameter file: /oragrid/product/11.2/crs/install/crsconfig_params
Creating trace directory
LOCAL ADD MODE
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
Adding daemon to inittab
CRS-4123: Oracle High Availability Services has been started.
ohasd is starting
CRS-4402: The CSS daemon was started in exclusive mode but found an active CSS daemon on node node1, number 1, and is terminating
An active cluster was found during exclusive startup, restarting to join the cluster
CRS-2672: Attempting to start 'ora.mdnsd' on 'node2'
CRS-2676: Start of 'ora.mdnsd' on 'node2' succeeded
CRS-2672: Attempting to start 'ora.gipcd' on 'node2'
CRS-2676: Start of 'ora.gipcd' on 'node2' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'node2'
CRS-2676: Start of 'ora.gpnpd' on 'node2' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'node2'
CRS-2676: Start of 'ora.cssdmonitor' on 'node2' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'node2'
CRS-2672: Attempting to start 'ora.diskmon' on 'node2'
CRS-2676: Start of 'ora.diskmon' on 'node2' succeeded
CRS-2676: Start of 'ora.cssd' on 'node2' succeeded
CRS-2672: Attempting to start 'ora.ctssd' on 'node2'
CRS-2676: Start of 'ora.ctssd' on 'node2' succeeded
CRS-2672: Attempting to start 'ora.crsd' on 'node2'
CRS-2676: Start of 'ora.crsd' on 'node2' succeeded
CRS-2672: Attempting to start 'ora.evmd' on 'node2'
CRS-2676: Start of 'ora.evmd' on 'node2' succeeded

node2     2010/08/27 23:47:32     /oragrid/product/11.2/cdata/node2/backup_20100827_234732.olr
Preparing packages for installation...
cvuqdisk-1.0.7-1
Configure Oracle Grid Infrastructure for a Cluster ... succeeded
Updating inventory properties for clusterware
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 12287 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /oracle/oraInventory
'UpdateNodeList' was successful.

Press "OK" after running all the scripts on all the nodes.

35.)

I received the above error, this can be fixed later. Refer to the post
Oracle: Failed to initialize GPnP

Pres "OK" and then press "Skip" on the original screen.

36.)

Oracle 11gr2 Grid Infrastructure for a 2 Node RAC has completed successfully.

Oracle: Failed to initialize GPnP

2010-08-28T05:44:00.006+05:30

Received error: Failed to initialize GPnP, during Oracle 11gR2 Grid installation.
This failure occurred in the step after running the root.sh, "Oracle Private Interconnect Configuration Assistant".
This error occurs when oracle tries to execute "oifcfg setif".
Error Stack:


INFO: Started Plugin named: Oracle Private Interconnect Configuration Assistant
INFO: Found associated job
INFO: Starting 'Oracle Private Interconnect Configuration Assistant'
INFO: Starting 'Oracle Private Interconnect Configuration Assistant'
INFO:  Failed to initialize GPnP
WARNING:
INFO:
INFO: Completed Plugin named: Oracle Private Interconnect Configuration Assistant
INFO: Oracle Private Interconnect Configuration Assistant failed.
INFO: Oracle Private Interconnect Configuration Assistant failed.

The issue occurs when we start installation with a wrong ORA_NLS10 environment parameter. Best is to unset the variable before installation or change it to point to $GRID_HOME/nls/data:


unset ORA_NLS10
or 
export ORA_NLS10=$GRID_HOME/nls/data

After doing that run the command:
[root@node1 oracle]# oifcfg setif -global eth1/10.100.3.0:cluster_interconnect
[root@node2 oracle]# oifcfg setif -global eth1/10.100.3.0:cluster_interconnect

Here eth1 is interconnect interface, 10.100.3.0 is the subnet for the interconnect IP address.

Syntax details for oifcfg command:


Name:
        oifcfg - Oracle Interface Configuration Tool.

Usage:  oifcfg iflist [-p [-n]]
        oifcfg setif {-node  | -global} {/:}...
        oifcfg getif [-node  | -global] [ -if [/] [-type ] ]
        oifcfg delif [{-node  | -global} [[/]]]
        oifcfg [-help]

         - name of the host, as known to a communications network
          - name by which the interface is configured in the system
           - subnet address of the interface
          - type of the interface { cluster_interconnect | public }

ORA-12060: During Mview Creation

2010-08-18T05:05:00.009+05:30

Well I had racked my brains a lot to create an mview on a large table across geographies, and it failed a few times.
Plus to increase my problem the character set was also different between the two DBs.:


Master Site:
SQL> select * from nls_database_parameters@link1 where PARAMETER='NLS_CHARACTERSET';
PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_CHARACTERSET               WE8MSWIN1252

Mview Site:
SQL> select * from nls_database_parameters where PARAMETER='NLS_CHARACTERSET';
PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_CHARACTERSET               AL32UTF8

I started by creating the mview directly:


create materialized view user.test1
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "users"
PARALLEL 4
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
WITH PRIMARY KEY
START WITH sysdate+0 NEXT SYSDATE+6/24
USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE
AS  select  * from user.test1@link1;

This ran for ever and finally failed with ORA-01555, as expected, as it ran for almost a day.
To give a background the size of the table is 1G.

Then I finally decided to use the PREBUILT Table option:
So I took an export backup of that table in the master site and imported it in mview site.
And then tried to recreate the mview with PREBUILT TABLE option:


create materialized view user.test1 ON PREBUILT TABLE
refresh fast with primary key for update
as
select col1,col2 from user.test1@link1;

Which failed with:
Col1,
*
ERROR at line 6:
ORA-12060: shape of prebuilt table does not match definition query

Well I checked the table structure and realized that the col size for char data type had become 2 folds as during import, due to character set conversion this happens.

Then I searched metalink, etc, and found an options called "WITH REDUCED PRECISION", to be used when there is col mis-match.


create materialized view user.test1 ON PREBUILT TABLE WITH REDUCED PRECISION
refresh fast with primary key for update
as
select col1,col2 from user.test1@link1;

But it still failed:

create materialized view user.test1 ON PREBUILT TABLE WITH REDUCED PRECISION
*
ERROR at line 1:
ORA-12060: shape of prebuilt table does not match definition query

What resolved the problem is removing "with primary key for update"


create materialized view user.test1 ON PREBUILT TABLE WITH REDUCED PRECISION
refresh fast on demand
as
select col1,col2 from user.test1@link1;

Materialized view created.

Configuring and starting ons daemon in non RAC DB

2009-09-23T16:43:00.000+05:30

Mostly used in case of Data Guard.

1.) Change directory to $ORACLE_HOME/opmn/conf

[oracle@TEST conf]$ pwd
/opt/app/oracle/10.2/opmn/conf
[oracle@TEST conf]$ ls -lrt
total 12
-rw-r--r-- 1 oracle oinstall 71 Feb 21 2006 ons.config.tmp
-rw------- 1 oracle oinstall 44 Oct 8 2008 ons.config.backup.10203
-rw------- 1 oracle oinstall 44 Oct 8 2008 ons.config

2.) Update the ons configuration file. (ons.config)

[oracle@TEST conf]$ more ons.config
localport=6100
remoteport=4200
loglevel=3
nodes=10.224.57.68:6200,10.224.56.38:6200 --> Mention the primary and standby servers with their ons ports
walletfile=$ORACLE_HOME/opmn/conf/ssl.wlt/default --> To enable SSL access between the ons services.

---The nodes parameter points to the primary and standby hosts followed by the remote port for the ONS daemon running on that port.
---The walletfile parameters point to the walletfile name. A wallet file is used by the Oracle Secure Sockets Layer (SSL) to store SSL certificates. If a wallet file is specified to ONS, it will use SSL when communicating with other ONS instances and require SSL certificate authentication from all ONS instances that try to connect to it. This means that if you want to turn on SSL for one ONS instance, then you must turn it on for all instances that are connected. Oracle recommends using SSL for all ONS communications.

3.) Start ONS

[oracle@TEST conf]$ onsctl start
onsctl: ons started

Configure and start ONS on both primary and standby.

How To: Configure Data Guard Fast-Start Failover Step By Step

2009-09-19T20:46:00.003+05:30

Note: Physcial Standby should be working fine and Data Guard Broker has already been configured and working fine.

How to Setup Data Guard
How To: Configure Data Guard Broker

1.) Set Local_Listener (Primary):

For the database to register with the data guard listener we need to get the local_listener parameter.
SQL> show parameter local_listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string

SQL> alter system set local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = 10.224.56.38)(PORT = 1521))';
System altered.

SQL> alter system register;
System altered.

SQL> show parameter local_listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (ADDRESS = (PROTOCOL = TCP)(HOST = 10.224.56.38)(PORT = 1521))

2.) Verify force logging:

SQL> select force_logging from v$database;
FOR
---
YES

Should be YES.

3.) Should use spfile:
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /opt/app/oracle/10.2/dbs/spfileorcl.ora

4.) Should use a password file:

SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP
----------------------------------------
SYS TRUE TRUE

5.) Remote login should be enabled:

SQL> show parameter remote_login_passwordfile
NAME TYPE VALUE
-------------------------------------------------
remote_login_passwordfile string EXCLUSIVE

6.) DB_UNIQUE_NAME should be set:

SQL> show parameter db_unique_name
NAME TYPE VALUE
-------------------------------------------------
db_unique_name string orcl

7.) Flash Recovery Ares should be set:

SQL> show parameter db_recovery_file
NAME TYPE VALUE
-----------------------------------------------------------------------------
db_recovery_file_dest string /opt/app/oracle/flash_recovery_area
db_recovery_file_dest_size big integer 2G

To set it, use the following commands:

alter system set db_recovery_file_dest_size = 2g scope=both;
alter system set db_recovery_file_dest = '' scope=both;

8.) Standby file management should be AUTO:

SQL> show parameter standby_file_management
NAME TYPE VALUE
--------------------------------------------------
standby_file_management string MANUAL

SQL> alter system set standby_file_management=auto;
System altered.

SQL> show parameter standby_file_management
NAME TYPE VALUE
-------------------------------------------------------
standby_file_management string AUTO

9.) log_archive_config should be set:

SQL> show parameter log_archive_config
NAME TYPE VALUE
-----------------------------------------------------------------------------
log_archive_config string DG_CONFIG=(orcl,orcl1)

10.) Standby Redo logs should be created:

SQL> select group#, type, member from v$logfile where type = 'STANDBY';
GROUP# TYPE MEMBER
-------------------------------------------------------------------------------------
3 STANDBY /opt/app/oracle/flash_recovery_area/orcl/onlinelog/o1_mf_3_5bvzkzgs_.log
4 STANDBY /opt/app/oracle/flash_recovery_area/orcl/onlinelog/o1_mf_4_5bvzl8hf_.log

10.) Enable flash back database:

alter database flashback on
*
ERROR at line 1:
ORA-38759: Database must be mounted by only one instance and not open.

Well need to mount the database to run this command.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 2684354560 bytes
Fixed Size 2086352 bytes
Variable Size 570427952 bytes
Database Buffers 2097152000 bytes
Redo Buffers 14688256 bytes
Database mounted.
SQL> alter database flashback on;
Database altered.

SQL> alter system set db_flashback_retention_target = 60 scope=both;
System altered.

SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES

SQL> show parameter db_flashback_retention_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 60

SQL> alter database set standby database to maximize availability;
Database altered.

SQL> select protection_mode from v$database;
PROTECTION_MODE
--------------------
MAXIMUM AVAILABILITY

Enable Flashback DB on Standby also.
SQL> alter database recover managed standby database cancel;
Database altered.

SQL> alter database flashback on;
Database altered.

SQL> alter system set db_flashback_retention_target = 60 scope=both;
System altered.

11.) Muliplex the data broker configuration files in 2 different disk, for protection.

SQL> show parameter dg_broker_config_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1 string /opt/app/oracle/10.2/dbs/dr1orcl.dat
dg_broker_config_file2 string /opt/app/oracle/10.2/dbs/dr2orcl.dat

SQL> alter system set dg_broker_config_file2='/opt/app/oracle/oradata/orcl/dr2orcl.dat';
alter system set dg_broker_config_file2='/opt/app/oracle/oradata/orcl/dr2orcl.dat'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16573: attempt to change configuration file for an enabled broker configuration

Well looks like the above problem is not easy to resolve. We need to disable the configuration, and also stop DMON process by doing alter system set dg_broker_start='false';

And then we can change the location of these files. Well I am not doing that right now. As I saw a bug filed on metalink that there are errors in restarting DMON after change in the file location. (BUG:5955795).

Well now doing the change of location.
SQL> ALTER SYSTEM SET DG_BROKER_START=FALSE;

System altered.
[oracle@APP3 dbs]$ mv dr2orcl.dat old.dr2orcl.dat
[oracle@APP3 dbs]$ mv dr1orcl.dat old.dr1orcl.dat

SQL> alter system set dg_broker_config_file2='/opt/app/oracle/oradata/orcl/dr2orcl.dat';
System altered.

SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE;
System altered.

SQL> show parameter dg_broker_config_file
NAME TYPE VALUE
-----------------------------------------------------------------------------
dg_broker_config_file1 string /opt/app/oracle/10.2/dbs/dr1regdb.dat
dg_broker_config_file2 string /opt/app/oracle/oradata/orcl/dr2orcl.dat

DGMGRL> create configuration 'DBTEST' as primary database is 'orcl' connect identifier is 'orcl';
Configuration "DBTEST" created with primary database "orcl"
DGMGRL> show configuration

Configuration
Name: DBTEST
Enabled: NO
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
orcl - Primary database

Current status for "DBTEST":
DISABLED

DGMGRL> ALTER CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY ;
Succeeded.
DGMGRL> show configuration

Configuration
Name: DBTEST
Enabled: NO
Protection Mode: MaxAvailability
Fast-Start Failover: DISABLED
Databases:
orcl - Primary database

Current status for "DBTEST":
DISABLED

FYI very useful metalink 808783.1 (Step By Step How to Recreate Dataguard Broker Configuration)

12.) Edit database setting in data guard like

DGMGRL> edit database orcl set property LogXptMode='SYNC';
Property "logxptmode" updated
DGMGRL> edit database orcl set property NetTimeout=10;
Property "nettimeout" updated
DGMGRL> edit database orcl1 set property NetTimeout=10;
Property "nettimeout" updated
DGMGRL> enable configuration
Error: ORA-16627: operation disallowed since no standby databases would remain to support protection mode

Enable the LogXptMode to SYNC for standby also.
DGMGRL> edit database orcl1 set property LogXptMode='SYNC';
Property "logxptmode" updated

DGMGRL> enable configuration
Operation requires shutdown of instance "orcl" on database "orcl"
Shutting down instance "orcl"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "orcl" on database "orcl"

Startup the DB manually.

We I had some more errors:
Warning: ORA-16608: one or more databases have warnings
Error: ORA-16795: database resource guard detects that database re-creation is required

i did
DGMGRL> enable resource orcl1
Enabled.

Well did not help. I problem was grave, I needed standby redo log files in the standby DB also.
SQL> alter database recover managed standby database cancel;
Database altered.

SQL> alter database add standby logfile group 3 size 50M;
Database altered.

SQL> alter database add standby logfile group 4 size 50M;
Database altered.

GROUP# STATUS TYPE MEMBER IS_
--------------------------------------------------------------------------------- ---
1 ONLINE /home/oracle/oradata/orcl/redo01.log NO
2 ONLINE /home/oracle/oradata/orcl/redo02.log NO
3 STANDBY /home/oracle/oradata/flash_recovery_area/orcl/orcl1/onlinelog/o1_mf_3_5c3kdj01_.log YES
4 STANDBY /home/oracle/oradata/flash_recovery_area/orcl/orcl1/onlinelog/o1_mf_4_5c3kdw63_.log YES

I decide to start redo apply !!
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE disconnect;

Database altered.

Well some more errors: in data guard log.
RSM0: HEALTH CHECK WARNING: ORA-16826: apply service state is inconsistent with the DelayMins property
DGMGRL> show site verbose orcl
Site
Name: 'orcl'
Hostname: 'APP3'
Instance name: 'orcl'
Service Name: 'orcl'
Standby Type: 'physical'
Enabled: 'yes'
Required: 'yes'
Default state: 'PRIMARY'
Intended state: 'PRIMARY'
PFILE: ''
Number of resources: 1
Resources:
Name: orcl (default) (verbose name='orcl')
Current status for "orcl":
Warning: ORA-16570: operation requires restart of database "orcl"

Need to restart the primary DB.
Whenever you see any error in the "show configuration" output. Check "show site verbose " to find which site has what error.
Well after the restart of the primary database:
DGMGRL> show configuration

Configuration
Name: DBTEST
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: ENABLED
Databases:
orcl - Primary database
orcl1 - Physical standby database
- Fast-Start Failover target

Current status for "DBTEST":
SUCCESS

13.) Enable Fast-start failover:

DGMGRL> enable fast_start failover;
Enabled.

14.) Start Observer:
Idealy observer should be started on another host, to observe the primary, as if the host goes down it can monitor the outage and do specify actions.
DGMGRL> start observer;
Observer started
The session will seem to hang. You can do a control C. Or create and run the script as nohup.
eg.
#!/bin/sh

dgmgrl sys/oracle <start observer;
!
Another Way:
CentOS4.4-64:wrpws:oracle > nohup dgmgrl sys/oracle@orcl "start observer file='$ORACLE_HOME/dbs/fsfo.dat'" -logfile $HOME/observer.log &

And another way to start:
[oracle@APP3 scripts]$ dgmgrl -logfile $HOME/observer.log sys/oracle@orcl "start observer" &

15. Lets test the configuration:
SQL> select FS_FAILOVER_STATUS,FS_FAILOVER_CURRENT_TARGET,FS_FAILOVER_THRESHOLD,FS_FAILOVER_OBSERVER_PRESENT,FS_FAILOVER_OBSERVER_HOST from v$database;

FS_FAILOVER_STATUS FS_FAILOVER_CURRENT_TARGET FS_FAILOVER_THRESHOLD FS_FAIL FS_FAILOVER_OBSERVER_HOST
--------------------- ------------------------------ --------------------- ------- ------------------------------
SYNCHRONIZED orcl1 30 YES wrpws

Switchover in both directions:
DGMGRL for Linux: Version 10.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show configuration

Configuration
Name: DBTEST
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: ENABLED
Databases:
orcl - Primary database
orcl1 - Physical standby database
- Fast-Start Failover target

Current status for "DBTEST":
SUCCESS

DGMGRL> switchover to orcl1;
Performing switchover NOW, please wait...
Operation requires shutdown of instance "orcl" on database "orcl"
Shutting down instance "orcl"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "orcl" on database "orcl1"
Shutting down instance "orcl"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "orcl" on database "orcl"
Starting instance "orcl"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.
You are no longer connected to ORACLE
Please connect again.
Unable to start instance "orcl"
You must start instance "orcl" manually
Operation requires startup of instance "orcl" on database "orcl1"
You must start instance "orcl" manually
Switchover succeeded, new primary is "orcl1"

I had to manually start up the databases to see if they switched well. They did reverse the roles both sides.
Checked metalink note: 308943.1 to resolve the above problem.
The listener.ora entries:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /home/oracle/10.2)
(SID_NAME = orcl)
(SERVICE_NAME = orcl1)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl1_DGMGRL) ##--->Should be db_unique_name_DGMGRL.db_domain
(ORACLE_HOME = /home/oracle/10.2)
(SID_NAME = orcl)
(SERVICE_NAME = orcl1)
)
)

After fixing the listener issue, the error was rectified and the switchover was working seemlessly.
DGMGRL> switchover to orcl1;
Performing switchover NOW, please wait...
Operation requires shutdown of instance "orcl" on database "orcl"
Shutting down instance "orcl"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "orcl" on database "orcl1"
Shutting down instance "orcl"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "orcl" on database "orcl"
Starting instance "orcl"...
ORACLE instance started.
Database mounted.
Operation requires startup of instance "orcl" on database "orcl1"
Starting instance "orcl"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "orcl1"
DGMGRL> show configuration

Configuration
Name: DBTEST
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: ENABLED
Databases:
orcl - Physical standby database
- Fast-Start Failover target
orcl1 - Primary database

Current status for "DBTEST":
SUCCESS

Switchover Back to the Primary:

DGMGRL> switchover to orcl;
Performing switchover NOW, please wait...
Operation requires shutdown of instance "orcl" on database "orcl1"
Shutting down instance "orcl"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "orcl" on database "orcl"
Shutting down instance "orcl"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "orcl" on database "orcl1"
Starting instance "orcl"...
ORACLE instance started.
Database mounted.
Operation requires startup of instance "orcl" on database "orcl"
Starting instance "orcl"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "orcl"
DGMGRL>

This completes the testing for switchover.
Its takes about 2.5 mins to switchover.

Now the testing for Failover:

Check that there is enough flashback data available in the primary database, atleast 30-40 mins.

SQL> select (sysdate - oldest_flashback_time)*24*60 as history from v$flashback_database_log;

HISTORY
----------
486.133333

Lets create a table and put some data to see if there is any data loss in the failover.

SQL> create table x as select * from all_objects;

Table created.

SQL> select count(*) from x;

COUNT(*)
----------
9915

SQL> shutdown abort;
ORACLE instance shut down.
SQL> exit

Observer Log:

[W000 09/17 09:53:56.79] Failed to start the Observer.
Error: ORA-16647: could not start more than one observer

Failed.

[W000 09/17 10:02:22.38] Failed to start the Observer.
Observer started
[W000 09/17 10:02:53.82] Observer started.
Observer stopped
Observer started
[W000 09/17 10:15:42.88] Observer started.

15:14:04.26 Friday, September 18, 2009
Initiating fast-start failover to database "orcl1"...
Performing failover NOW, please wait...
Failover succeeded, new primary is "orcl1"
15:14:13.79 Friday, September 18, 2009

Status from dgmgrl on the new primary:
CentOS4.4-64:wrpws:oracle > dgmgrl sys/oracle
DGMGRL for Linux: Version 10.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show configuration

Configuration
Name: DBTEST
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: ENABLED
Databases:
orcl - Physical standby database (disabled)
- Fast-Start Failover target
orcl1 - Primary database

Current status for "DBTEST":
Warning: ORA-16608: one or more databases have warnings

Record count from the new primary:
CentOS4.4-64:wrpws:oracle > sqlplus sys as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Sep 18 15:17:32 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(*) from x;

COUNT(*)
----------
9915

Now to reinstate the old primary as standby:
I started the old primary in mount mode, after about 30 sec I see activity in the observer.log
15:25:27.74 Friday, September 18, 2009
Initiating reinstatement for database "orcl"...
Reinstating database "orcl", please wait...
Operation requires shutdown of instance "orcl" on database "orcl"
Shutting down instance "orcl"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "orcl" on database "orcl"
Starting instance "orcl"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "orcl" ...
Reinstatement of database "orcl" succeeded
15:26:27.32 Friday, September 18, 2009

When you do show configuration in dgmgrl you will get the following error:
Warning: ORA-16817: unsynchronized Fast-Start Failover configuration

Just switch a couple fo logfile (alter system switch logfile;) in sqlplus and then check the output of dgmgrl, it should be fine:
CentOS4.4-64:wrpws:oracle > dgmgrl sys/oracle
DGMGRL for Linux: Version 10.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show configuration

Configuration
Name: DBTEST
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: ENABLED
Databases:
orcl - Physical standby database
- Fast-Start Failover target
orcl1 - Primary database

Current status for "DBTEST":
SUCCESS

DGMGRL>

Well this completes my testing and implementation of Fast-Start Failover!!!
I hope this helps.

How To: Configure Data Guard Broker

2009-09-19T20:34:00.003+05:30

Configuring Data Guard Broker:
NOTE: You should have your physical standby already setup.
How to Setup Data Guard

1.) Check parameter DG_BROKER_START on primary:
SQL> show parameter DG_BROKER_START

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start boolean FALSE

2.) Set DG_BROKER_START to true on primary:
SQL> alter system set DG_BROKER_START=true scope=both;

System altered.

3.)Check DG_BROKER_START on standby side:
SQL> show parameter DG_BROKER_START

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start boolean FALSE

4.) Set DG_BROKER_START to true on standby:
SQL> alter system set DG_BROKER_START=true scope=both;

System altered.

5.) Define a service for the listener that includes the db_unique_name_DGMGRL.db_domain values for the GLOBAL_DBNAME:
Update the listener.ora file on primary and standby
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl.abc.com)
(ORACLE_HOME = /opt/app/oracle/10.2)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl_DGMGRL.abc.com) -->_DGMGRL.
(ORACLE_HOME = /opt/app/oracle/10.2)
(SID_NAME = orcl)
)
)

6.) Now to create the data guard broker configurations:

[oracle@APP3 admin]$ dgmgrl
DGMGRL for Linux: Version 10.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle
Connected.
DGMGRL> create configuration 'DBTEST'
> as primary database is 'orcl'
> connect identifier is orcl;
Configuration "DBTEST" created with primary database "orcl"
DGMGRL>

DGMGRL> show configuration

Configuration
Name: DBTEST
Enabled: NO
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
orcl - Primary database

Current status for "DBTEST":
DISABLED

DGMGRL>

7.) Now add standby DB to the data broker configuration:

DGMGRL> add database 'orcl1' as
> connect identifier is orcl1
> maintained as physical;
Database "orcl1" added
DGMGRL>
DGMGRL> show configuration

Configuration
Name: DBTEST
Enabled: NO
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
orcl - Primary database
orcl1 - Physical standby database

Current status for "DBTEST":
DISABLED

8.) Now enable the configurations:
DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration

Configuration
Name: DBTEST
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
orcl - Primary database
orcl1 - Physical standby database

Current status for "DBTEST":
Warning: ORA-16607: one or more databases have failed

DGMGRL> show database verbose orcl1

Database
Name: orcl1
Role: PHYSICAL STANDBY
Enabled: YES
Intended State: ONLINE
Instance(s):
orcl

Properties:
InitialConnectIdentifier = 'orcl1'
ObserverConnectIdentifier = ''
LogXptMode = 'ARCH'
Dependency = ''
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '180'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'MANUAL'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '3'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '/opt/app/oracle/oradata/orcl, /home/oracle/oradata/orcl'
LogFileNameConvert = '/opt/app/oracle/oradata/orcl, /home/oracle/oradata/orcl, /opt/app/oracle/flash_recovery_area/orcl/onlinelog, /home/oracle/oradata/flash_recovery_area/orcl'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'wrpws'
SidName = 'orcl'
LocalListenerAddress = '(ADDRESS=(PROTOCOL=tcp)(HOST=wrpws)(PORT=1521))'
StandbyArchiveLocation = '/home/oracle/oradata/orcl/archive'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.arc'
LatestLog = '(monitor)'
TopWaitEvents = '(monitor)'

Current status for "orcl1":
Error: ORA-12545: Connect failed because target host or object does not exist

DGMGRL>

Well the problem in my case was that the hostname was not getting resolved. I added the hostname to the host file and it started working.

After this error I got error:
Current status for "orcl1":
Error: ORA-16664: unable to receive the result from a remote database

Well same problem, communication between the 2 database. Make sure that the /etc/hosts files have entries of all other servers, with name and IP address.
Always review the log_archive_dest_2 parameter, to have the right entries and service is accessible.

DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration

Configuration
Name: DBTEST
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
orcl - Primary database
orcl1 - Physical standby database

Current status for "DBTEST":
SUCCESS

DGMGRL>

This should setup Data Guard Broker.

COMMANDS Available in DGMGRL:

DGMGRL> help

The following commands are available:

add Add a standby database to the broker configuration
connect Connect to an Oracle instance
create Create a broker configuration
disable Disable a configuration, a database, or Fast-Start Failover
edit Edit a configuration, database, or instance
enable Enable a configuration, a database, or Fast-Start Failover
exit Exit the program
failover Change a standby database to be the primary database
help Display description and syntax for a command
quit Exit the program
reinstate Change a disabled database into a viable standby database
rem Comment to be ignored by DGMGRL
remove Remove a configuration, database, or instance
show Display information about a configuration, database, or instance
shutdown Shutdown a currently running Oracle instance
start Start Fast-Start Failover observer
startup Start an Oracle database instance
stop Stop Fast-Start Failover observer
switchover Switch roles between the primary database and a standby database

Use "help " to see syntax for individual commands

Step by Step Data Guard Setup for Oracle 10g

2009-09-15T15:54:00.003+05:30

Steps:
1.) Make sure archive log mode is enabled on your database:
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/app/oracle/oradata/orcl/archive
Oldest online log sequence 108
Next log sequence to archive 109
Current log sequence 109

SQL> select name, log_mode from v$database;

NAME LOG_MODE
--------- ------------
ORCL ARCHIVELOG

If archive log mode is not enabled. Please enable it using the following link.
How to enable archivelog mode in Oracle 11g database

2.) Enable force logging on the database, so that there is no problems with no logging operations in the future.

SQL> alter database force logging;

Database altered.

3.) Create password file, if you do not have one already.

[oracle@APP3 dbs]$ cd $ORACLE_HOME/dbs
[oracle@APP3 dbs]$ orapwd file=orapworcl password=oracle force=y
[oracle@APP3 dbs]$ ls -lrt orapworcl
-rw-r----- 1 oracle oinstall 1536 Sep 14 08:21 orapworcl

SQL> select * from v$pwfile_users;

USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE

4.) Create Standby Redo Logfiles on primary DB.

Current logfile:
SQL> col member a40
SQL> select a.group#,a.status,a.member,b.bytes/1024/1024 from v$logfile a,v$log b
2 where a.group#=b.group#;

GROUP# STATUS MEMBER B.BYTES/1024/1024
---------- ------- ---------------------------------------- -----------------
1 /opt/app/oracle/oradata/orcl/redo01.log 50
2 /opt/app/oracle/oradata/orcl/redo02.log 50

Add standby redo log groups:

SQL> alter database add standby logfile group 3 size 50M;

Database altered.

SQL> alter database add standby logfile group 4 size 50M;

Database altered.

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- --------------------------------------------------------------------------- ---
1 ONLINE /opt/app/oracle/oradata/ORCL/redo01.log NO
2 ONLINE /opt/app/oracle/oradata/ORCL/redo02.log NO
3 STANDBY /opt/app/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_3_5bvzkzgs_.log YES
4 STANDBY /opt/app/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_4_5bvzl8hf_.log YES

SQL> select * from v$standby_log;

GROUP# DBID THREAD# SEQUENCE# BYTES USED ARC STATUS FIRST_CHANGE# FIRST_TIM LAST_CHANGE# LAST_TIME
----------------------------------------------------------------------------------------------------------------------
3 UNASSIGNED 0 0 52428800 512 YES UNASSIGNED 0 0
4 UNASSIGNED 0 0 52428800 512 YES UNASSIGNED 0 0

5.) Check parameter db_unique_name
SQL> show parameters unique

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string orcl

6.) Add standby related entried to Primary database:
SQL> create pfile='/home/oracle/initprim.ora' from spfile;

Sample init.ora from Primary:
orcl.__db_cache_size=2097152000
orcl.__java_pool_size=16777216
orcl.__large_pool_size=16777216
orcl.__shared_pool_size=536870912
orcl.__streams_pool_size=0
*.audit_file_dest='/opt/app/oracle/admin/orcl/adump'
*.background_dump_dest='/opt/app/oracle/admin/orcl/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/opt/app/oracle/oradata/orcl/control01.ctl','/opt/app/oracle/oradata/orcl/control02.ctl','/opt/app/oracle/oradata/orcl/control03.ctl'
*.core_dump_dest='/opt/app/oracle/admin/orcl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.db_recovery_file_dest='/opt/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.db_unique_name='orcl'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='location=/opt/app/oracle/oradata/orcl/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'
*.open_cursors=300
*.pga_aggregate_target=823132160
*.processes=500
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2684354560
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/opt/app/oracle/admin/orcl/udump'
db_unique_name=orcl
LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcl1)'
*.LOG_ARCHIVE_DEST_2='SERVICE=ORCL1 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl1'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=3
DB_FILE_NAME_CONVERT='/u01/oradata/orcl','/opt/app/oracle/oradata/orcl'
LOG_FILE_NAME_CONVERT='/u01/oradata/orcl/archive',/opt/app/oracle/oradata/orcl/archive','/u01/oradata/flash_recovery_area/orcl','/opt/app/oracle/flash_recovery_area/orcl/onlinelog'
FAL_SERVER=orcl1
FAL_CLIENT=orcl

Copy the init.ora and make necessary changes to the file to be used at standby side. Changes like location of various files, FAL_SERVER, FAL_CLIENT etc.

Sample init.ora in Standby DB:
orcl.__db_cache_size=2097152000
orcl.__java_pool_size=16777216
orcl.__large_pool_size=16777216
orcl.__shared_pool_size=536870912
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/oradata/orcl/adump'
*.background_dump_dest='/u01/oradata/orcl/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/u01/oradata/orcl/control01.ctl','/u01/oradata/orcl/control02.ctl','/u01/oradata/orcl/control03.ctl'
*.core_dump_dest='/u01/oradata/orcl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.db_recovery_file_dest='/u01/oradata/flash_recovery_area/orcl'
*.db_recovery_file_dest_size=2147483648
*.db_unique_name='orcl1'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='location=/u01/oradata/orcl/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'
*.open_cursors=300
*.pga_aggregate_target=823132160
*.processes=500
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2684354560
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/oradata/orcl/udump'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcl1)'
*.LOG_ARCHIVE_DEST_2='SERVICE=ORCL LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=3
DB_FILE_NAME_CONVERT='/opt/app/oracle/oradata/orcl','/u01/oradata/orcl'
LOG_FILE_NAME_CONVERT='/opt/app/oracle/oradata/orcl/archive','/u01/oradata/orcl/archive','/opt/app/oracle/flash_recovery_area/orcl/onlinelog','/u01/oradata/flash_recovery_area/orcl'
FAL_SERVER=orcl
FAL_CLIENT=orcl1

7.) Shutdown the primary database. Use the newly created pfile to startup nomount the database. Then create a spfile for the database. Mount the database and create a standby controlfile.
Shutdown the database and take a cold back of the database, all files including the redo log files. You can also create a standby DB from hot backup.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup nomount pfile='/home/oracle/pfileorcl.ora'
ORACLE instance started.

Total System Global Area 2684354560 bytes
Fixed Size 2086352 bytes
Variable Size 570427952 bytes
Database Buffers 2097152000 bytes
Redo Buffers 14688256 bytes
SQL> create spfile from pfile='/home/oracle/pfileorcl.ora';

File created.
Meanwhile I also received the error:
create spfile from pfile='/home/oracle/pfileregdb.ora'
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kspsetpao1], [1753], [1700], [*],
[user_dump_dest], [33], [], []

Note: This error usually comes when the syntax of the pfile is wrong somewhere, please fix the pfile and try again. It worked for me.

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2684354560 bytes
Fixed Size 2086352 bytes
Variable Size 570427952 bytes
Database Buffers 2097152000 bytes
Redo Buffers 14688256 bytes
Database mounted.
Database opened.

8.) Shutdown the database again and take a cold backup of all files.

9.) Create standby control file.
SQL> startup mount
ORACLE instance started.
Total System Global Area 2684354560 bytes
Fixed Size 2086352 bytes
Variable Size 570427952 bytes
Database Buffers 2097152000 bytes
Redo Buffers 14688256 bytes
Database mounted.

--Then mount and create a standby controlfile.
SQL> alter database create standby controlfile as 'standby.ctl';
Database altered.

-- Open the primary read write.
SQL> alter database open;
Database altered.

10.) Transfer all the file from the cold backup from Primary to Standby server. Also copy the password file from primary to standby.
Also copy the standby controlfile created in step 9 and copy if with the right name and location on standby server.
I use SFTP for transferring the files.

11.) Add entries for the primary db and standby DB in both primary and standby servers. i.e. primary server should have its own (orcl) and standby server (orcl1) tns entry.

12.) Copy the pfile from step 6 for standby DB.
Now try to nomount the standby database with the new pfile.

[oracle@dbtest dbs]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 15 04:57:32 2009

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

Connected to an idle instance.

SQL> startup nomount pfile='/home/oracle/oracle/product/10.2.0/db_1/dbs/pfilestbregdb.ora';
ORACLE instance started.

Total System Global Area 1694498816 bytes
Fixed Size 1219784 bytes
Variable Size 402654008 bytes
Database Buffers 1275068416 bytes
Redo Buffers 15556608 bytes

13.) Create spfile from pfile.
SQL> create spfile from pfile='/home/oracle/oracle/product/10.2.0/db_1/dbs/pfilestbregdb.ora';

File created.

14.) Shutdown the DB and do a startup mount.
SQL>startup mount;

15.) Start REDO apply process:
SQL> alter database recover managed standby database disconnect from session;
OR
SQL> alter database recover managed standby database nodelay disconnect parallel 8;

16.) Verification.
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

To check archive gap:
SELECT thread#, low_sequence#, high_sequence# from V$archive_gap;
for RAC
SELECT thread#, low_sequence#, high_sequence# from gv$archive_gap;

To stop redo apply:
alter database recover managed standby database cancel;

17.) Check alert log files and verify that you did not receive any error.

18.) Switch some logfiles on the Primary and check if the same are getting applied to the standby.
on Primary:
SQL> alter system switch logfile;

on standby:
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

Thanks should be it, your Physical Standby DB should be working fine.

Country ISO Code with Calling Code Data

2009-08-12T11:15:00.003+05:30

Recently was required to populate Country Name, Country ISO Code and Country Calling Code data in a table in the database.
Compiled data from various sources and finally came to the below data. Please use it for reference, this data is correct to best of my knowledge.
Incase you find that there is any problem please let me know.
Incase you need insert statement for database, please ask for them I will send them to you.

I hope this is useful!!

Insert/Update XML data stored in CLOB Column

2009-08-04T16:17:00.008+05:30

Recently I had a requirement to insert/update XML data stored directly in CLOB column of a table by the application.
There was a requirement to update the XML node values directly by-passing the application. I had no experience with using XML function provided by oracle, plus the column is a normal CLOB column, as part of the many columns the table has. Let me give you an example for such a table:

Columns Name Data Type
--------------------------------------------------------------------------------
ID Number
First_Name VARCHAR2(128)
Last_Name VARCHAR2(128)
DETAILS CLOB ---> This column stores the XML information.

The process that we need to follow:

Convert the CLOB column to XMLTYPE using XMLTYPE.createXML(column_name)
e.g. XMLTYPE.createXML(details)
The important thing is to know the XML pattern in the column. eg.


<details>

<profile>

  <websiteurl><![CDATA[www.yahoo.com]]></websiteurl>

  <companyname><![CDATA[Google]]></companyname>

  <jobtitle><![CDATA[DBA]]></jobtitle>

  <addresses>

     <address find="addressType">

     <addresstype>Address1</addresstype>

     <streetline1><![CDATA[123 XYZ 1111]]></streetline1>

     </address>

     <address find="addressType">

     <addresstype>Address2</addresstype>

     <streetline1><![CDATA[890 abc 2222]]></streetline1>

     </address>

  </addresses>

  <phonenumbers>

     <phonenumber find="phoneType">

     <phonetype>Phone1</phonetype>

     <number><![CDATA[+1-111-111-1111]]></number>

     </phonenumber>

     <phonenumber find="phoneType">

     <phonetype>Fax1</phonetype>

     <number><![CDATA[222-222-2222]]></number>

     </phonenumber>

     <phonenumber find="phoneType">

     <phonetype>Mobile1</phonetype>

     <number><![CDATA[+1-333-333-3333]]></number>

     </phonenumber>

  </phonenumbers>

  <emails>

     <email find="emailType">

     <emailtype>Email1</emailtype>

     <email><![CDATA[hero@abc.com]]></email>

     </email>

     <email find="emailType">

     <emailtype>Email2</emailtype>

     <email><![CDATA[hero1@abc.com]]></email>

     </email>

  </emails>

</profile>

</details>

Now queries to SELECT data from the XML column:

SQL> SELECT EXTRACTVALUE(XMLTYPE.createXML(details),'/details/profile/companyName/text()') XMLSrc FROM usera WHERE id='1';
XMLSRC
--------------------------------------------------------------------------------
Google
The above query will give you the extact value of the company.

SQL> SELECT EXTRACT(XMLTYPE.createXML(details),'/details/profile/companyName/text()') XMLSrc FROM usera WHERE id='1';
XMLSRC
--------------------------------------------------------------------------------

The above query returns the complete of the node.

SQL> SELECT EXTRACTVALUE(XMLTYPE.createXML(details),'/details/profile/addresses/address[addressType=''Address1'']/streetLine1/text()') XMLSrc FROM usera WHERE id='1';
XMLSRC
--------------------------------------------------------------------------------
123 XYZ 1111

The above example shows how to query data inside a nested node. For me it was easy to query any node directly, but I had to really dig in to find out how to query a node which is for a particular type of a parent node.

Now to Update some data:
SQL> UPDATE usera set details = UPDATEXML(XMLTYPE.createXML(details),'/details/profile/addresses/address[addressType=''Address1'']/streetLine1/text()','The World is Great').getClobVal() WHERE id=1;

1 row updated.

SQL> SELECT EXTRACTVALUE(XMLTYPE.createXML(details),'/details/profile/addresses/address[addressType=''Address1'']/streetLine1/text()') XMLSrc FROM usera WHERE id='1';
XMLSRC
--------------------------------------------------------------------------------
The World is Great

If you look at the above update, the details column was first converted into XMLTYPE and then the node updated and then returned back as CLOB using getClobVal() function as the column is CLOB type not XMLTYPE. If you do not use the getClobVal() function you would get the following error:

SQL> UPDATE usera set details = UPDATEXML(XMLTYPE.createXML(details),'/details/profile/addresses/address[addressType=''Address1'']/streetLine1/text()','The World is Great') WHERE id=1;
UPDATE usera set details = UPDATEXML(XMLTYPE.createXML(details),'/details/profile/addresses/address[
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected CLOB got -

Now to add data into the XML:

SQL> UPDATE usera set details = APPENDCHILDXML(XMLTYPE.createXML(details),'/details/profile/addresses/address[addressType=''Address1'']',XMLTYPE ('xyz garbage')).getClobVal() WHERE id=1;

1 row updated.

SQL> SELECT EXTRACTVALUE(XMLTYPE.createXML(details),'/details/profile/addresses/address[addressType=''Address1'']/streetLine2/text()') XMLSrc FROM usera WHERE id='1';
XMLSRC
--------------------------------------------------------------------------------
xyz garbage

In the append data section, we need to provide the path and then the XML value to add.

A small test procedure to check if the value exists or not and then append.
declare
var1 clob;
begin
SELECT EXTRACTVALUE(XMLTYPE.createXML(details),'/details/profile/addresses/address[addressType=''Address1'']/streetLine2/text()') into var1 FROM usera WHERE id=1;
IF var1 is NULL then
UPDATE usera set details = APPENDCHILDXML(XMLTYPE.createXML(details),'/details/profile/addresses/address[addressType=''Address1'']',XMLTYPE ('xyz garbage')).getClobVal() WHERE id=1;
END IF;
end;

To look for more XML functions please refer to page Oracle XML Functions

How To: Setup up of Oracle Streams Replication

2009-07-20T15:34:00.002+05:30

Note: I have started using TOAD now, so you wont see the statement complication output.

Step 1: Create stream administration user in both databases.

create user streamadmin identified by streamadmin default tablespace users;

Step 2: Required grants to the user streamadmin.

grant dba,select_catalog_role to streamadmin;
exec dbms_streams_auth.grant_admin_privilege('streamadmin',true);

Step 3: We will use default SCOTT schema for seting up this replication.

Step 4: Check database paramters required for setting up stream replication

For our example:
DB 1:
Name: TEST1
Global Name should be true
SQL> show parameter global_name
NAME TYPE VALUE
------------------------------------ ----------- --------------------------
global_names boolean TRUE

SQL> select * from global_name;
GLOBAL_NAME
-----------------------------------------------
TEST1.REGRESS.RDBMS.DEV.US.ORACLE.COM

SQL> show parameter job_queue

NAME TYPE VALUE
------------------------------------ ----------- ------
job_queue_processes integer 10

SQL> show parameter db_recovery_file_dest_size
NAME TYPE VALUE
------------------------------------ ----------- -----------
db_recovery_file_dest_size big integer 2G

SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE
------------------------------------ ----------- -------------------------------------
db_recovery_file_dest string D:\oracle\product/flash_recovery_area

DB 2:
Name: TEST
Global Name should be true.
SQL> show parameter global_name
NAME TYPE VALUE
------------------------------------ ----------- -------
global_names boolean TRUE

SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------
TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM

SQL> show parameter job_queue
NAME TYPE VALUE
------------------------------------ ----------- ------
job_queue_processes integer 10

SQL> show parameter db_recovery_file_dest_size

NAME TYPE VALUE
------------------------------------ ----------- --------
db_recovery_file_dest_size big integer 2G

SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string D:\oracle\product\10.2.0/flash_recovery_area

Step 5: Enable supplemental logging on the tables of the scott user in both the databases:

Oracle has two types of supplimental logging options:
1.) Unconditional Supplemental Logging: Logs the before images of specified columns any time the table is updated, regardless of whether the update affected any of the specified columns. This is sometimes referred to as an ALWAYS log group.
2.) Conditional Supplemental Log Groups: Logs the before images of all specified columns only if at least one of the columns in the log group is updated.
(From Oracle Documentation)

Login to the SCOTT schema to start conditional supplemental logging:
SQL> alter table emp add supplemental log group supp_log_emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO);
Table altered.
SQL> alter table dept add supplemental log group supp_log_dept (deptno,dname,loc);
Table altered.
SQL> alter table bonus add supplemental log group supp_log_bonus (ename,job,sal,comm);
Table altered.
SQL> alter table salgrade add supplemental log group supp_log_sal (grade,losal,hisal);
Table altered.

The same needs to be done on the other database also.

SQL> select log_group_name, table_name from dba_log_groups where owner='SCOTT';
LOG_GROUP_NAME TABLE_NAME
------------------------------ ------------------------------
SUPP_LOG_EMP EMP
SUPP_LOG_DEPT DEPT
SUPP_LOG_BONUS BONUS
SUPP_LOG_SAL SALGRADE

Step 6: Create Database Links between the stream administrator users in the 2 databases.
SQL> CREATE DATABASE LINK TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM CONNECT TO STREAMADMIN IDENTIFIED BY streamadmin USING 'TEST_REP2';
Database link created.

SQL> CREATE DATABASE LINK TEST1.REGRESS.RDBMS.DEV.US.ORACLE.COM CONNECT TO STREAMADMIN IDENTIFIED BY streamadmin USING 'TEST_REP1';
Database link created.

Step 7: Create Stream Queues under the streamadmin user to apply and capture the database changes to be replicated.
This also needs to run on both the databases as streamadmin.

SQL> show user
USER is "STREAMADMIN"
SQL> exec dbms_streams_adm.set_up_queue(queue_table => 'apply_q', queue_name => 'apply_q', queue_user => 'streamadmin');
PL/SQL procedure successfully completed.

SQL> exec dbms_streams_adm.set_up_queue(queue_table => 'capture_q',queue_name => 'capture_q',queue_user => 'streamadmin');
PL/SQL procedure successfully completed.

Step 8: Setup data capture on both the databases:
Procedure dbms_streams_adm is the administration package for setting up capture process when using streams, like dbms_repcat when using normal replication.
http://www.psoug.org/reference/dbms_streams_adm.html for details reference of this package.

Views sys.streams$_process_params, sys.streams$_capture_process

SQL> BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES (
SCHEMA_NAME => 'SCOTT',
STREAMS_TYPE => 'CAPTURE',
STREAMS_NAME => 'CAPTURE_STREAM',
QUEUE_NAME => 'CAPTURE_Q',
INCLUDE_DML => TRUE,
INCLUDE_DDL => TRUE,
INCLUSION_RULE => TRUE);
END;
/
PL/SQL procedure successfully completed.

The above procedure need to run on both the databases.

Step 9: Setup data apply on both the databases:
SQL> BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES (
SCHEMA_NAME => 'SCOTT',
STREAMS_TYPE => 'APPLY',
STREAMS_NAME => 'APPLY_STREAM',
QUEUE_NAME => 'APPLY_Q',
INCLUDE_DML => TRUE,
INCLUDE_DDL => TRUE,
SOURCE_DATABASE => 'TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM');
END;
/
PL/SQL procedure successfully completed.

SQL> BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES (
SCHEMA_NAME => 'SCOTT',
STREAMS_TYPE => 'APPLY',
STREAMS_NAME => 'APPLY_STREAM',
QUEUE_NAME => 'APPLY_Q',
INCLUDE_DML => TRUE,
INCLUDE_DDL => TRUE,
SOURCE_DATABASE => 'TEST1.REGRESS.RDBMS.DEV.US.ORACLE.COM');
END;
/
PL/SQL procedure successfully completed.

Step 10: Setup propogation process on both the databases:
Its basically setting up related between the capture process on one database and apply process on the other database.
Thes need to run as streamadmin user.
DB 1:

SQL> BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES (
SCHEMA_NAME => 'SCOTT',
STREAMS_NAME => 'TEST1_TO_TEST',
SOURCE_QUEUE_NAME => 'CAPTURE_Q',
DESTINATION_QUEUE_NAME => 'APPLY_Q@TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM',
INCLUDE_DML => TRUE,
INCLUDE_DDL => TRUE,
SOURCE_DATABASE => 'TEST1.REGRESS.RDBMS.DEV.US.ORACLE.COM');
END;
/
PL/SQL procedure successfully completed.

DB 2:
SQL> BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES (
SCHEMA_NAME => 'SCOTT',
STREAMS_NAME => 'TEST_TO_TEST1',
SOURCE_QUEUE_NAME => 'CAPTURE_Q',
DESTINATION_QUEUE_NAME => 'APPLY_Q@TEST1.REGRESS.RDBMS.DEV.US.ORACLE.COM',
INCLUDE_DML => TRUE,
INCLUDE_DDL => TRUE,
SOURCE_DATABASE => 'TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM');
END;
/
PL/SQL procedure successfully completed.

Step 11: Setup schema instantiation SCN on DB 2 (TEST) & DB 1 (TEST1):

SQL> DECLARE
ISCN NUMBER;
BEGIN
ISCN := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM (
SOURCE_SCHEMA_NAME => 'SCOTT',
SOURCE_DATABASE_NAME => 'TEST1.REGRESS.RDBMS.DEV.US.ORACLE.COM',
INSTANTIATION_SCN => ISCN,
RECURSIVE => TRUE);
END;
/
PL/SQL procedure successfully completed.

DECLARE
ISCN NUMBER;
BEGIN
ISCN := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@TEST1.REGRESS.RDBMS.DEV.US.ORACLE.COM (
SOURCE_SCHEMA_NAME => 'SCOTT',
SOURCE_DATABASE_NAME => 'TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM',
INSTANTIATION_SCN => ISCN,
RECURSIVE => TRUE);
END;
/
PL/SQL procedure successfully completed.

Step 12: Start capture and apply process:
Setting the disable_on_error parameter to ‘N’ allows the apply process to continue applying row LCRs even when it encounters errors. The default value is ‘Y’ which disables the apply process automatically on the first error encountered.
DB 1 (TEST1):

SQL> EXEC DBMS_APPLY_ADM.SET_PARAMETER (APPLY_NAME => 'APPLY_STREAM', PARAMETER => 'DISABLE_ON_ERROR', VALUE => 'N');
PL/SQL procedure successfully completed.

SQL> EXEC DBMS_APPLY_ADM.START_APPLY (APPLY_NAME => 'APPLY_STREAM');
PL/SQL procedure successfully completed.

SQL> EXEC DBMS_CAPTURE_ADM.START_CAPTURE (CAPTURE_NAME => 'CAPTURE_STREAM');
PL/SQL procedure successfully completed.

Same steps for the other DB also.

Steps 13: Test the setup. I just realized that its "Step 13" for testing, not good not good, not number 13.
Actually I faced a few problems with the test.
1.) The AQ_TM_PROCESSES parameter in my case was 0, but the data was not getting transferred.
2.) I had not set the instantiation SCN for the second DB.

I found the above errors from the below query:
SELECT APPLY_NAME,
SOURCE_DATABASE,
LOCAL_TRANSACTION_ID,
ERROR_NUMBER,
ERROR_MESSAGE,
MESSAGE_COUNT
FROM DBA_APPLY_ERROR;

After rectifying the error, to apply the failed transaction:
BEGIN
DBMS_APPLY_ADM.EXECUTE_ERROR(
local_transaction_id => '4.30.434',
execute_as_user => false);
END;
You ge the local_transaction_id from the above query.

Anyway lets test the setup once again:
Insert test:
------------
SQL> connect scott/tiger@test_rep1
Connected.
SQL> insert into dept values (50,'RESEARCH','MEMPHIS');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from dept@TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM;

DEPTNO DNAME LOC
---------- -------------- -------------
50 RESEARCH MEMPHIS
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

Update Test:
------------
SQL> connect scott/tiger@test_rep2
Connected.
SQL> update dept set loc='DELHI' where deptno=50;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from dept@TEST1.REGRESS.RDBMS.DEV.US.ORACLE.COM;

DEPTNO DNAME LOC
---------- -------------- -------------
50 RESEARCH DELHI
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

Finally u can see that basic steps of replication is working.

How TO: Check for Charater Set Conversion Problems & Fix Them

2009-07-20T15:28:00.005+05:30

Character set conversion was done without using the csscan to verify if its possible.
ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-553: character set name is not recognized
Character set was converted from WE8ISO8859P1 to AL32UTF8.
Helpful metalink note: 286964.1

Below query is used to see if there is a mismatch in character set in the database.
SQL> select distinct(nls_charset_name(charsetid)) CHARACTERSET,
decode(type#, 1, decode(charsetform, 1, 'VARCHAR2', 2, 'NVARCHAR2','UNKOWN'),
9, decode(charsetform, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKOWN'),
96, decode(charsetform, 1, 'CHAR', 2, 'NCHAR', 'UNKOWN'),
112, decode(charsetform, 1, 'CLOB', 2, 'NCLOB', 'UNKOWN')) TYPES_USED_IN
from sys.col$ where charsetform in (1,2) and type# in (1, 9, 96, 112) order by CHARACTERSET;

CHARACTERSET TYPES_USED_IN
---------------------------------------- -------------
AL16UTF16 NCHAR
AL16UTF16 NCLOB
AL16UTF16 NVARCHAR2
AL32UTF8 CLOB
AL32UTF8 VARCHAR2
WE8ISO8859P1 CHAR
WE8ISO8859P1 CLOB
WE8ISO8859P1 VARCHAR2

So we can see that the above query also returns the old character set along with the new character set. It should ideally not show any record of the old character.
Here you can see that there are 2 different character sets returned for VARCHAR2, CLOB & CHAR data, which indicates a mixup in the database character set setup, which is the cause for this PLS-553 error.

I had to work on this problem as in a new development database, someone accidently changed the charaterset and then we started getting all sorts of issues. Now lets find out which tables & columns are having problems:
For CHAR,VARCHAR2 and CLOB columns: I have mentioned the old or problem character set in the below query, you are replace it with the one you are facing problem with.
SQL> set lines 199
SQL> col owner format a10
SQL> col table_name format a20
SQL> col column_name format a20
SQL> select OWNER, TABLE_NAME, COLUMN_NAME from DBA_TAB_COLS where COLUMN_ID in (select unique COL#
from sys.col$ where CHARSETID=NLS_CHARSET_ID('WE8ISO8859P1') and charsetform ='1')
order by OWNER, TABLE_NAME, COLUMN_NAME; 2 3

OWNER TABLE_NAME COLUMN_NAME
---------- -------------------- --------------------
TEST TEST_TABLE1 CODE
TEST TEST_TABLE1 CREATETIME
TEST TEST_TABLE1 LASTMODIFIEDTIME
TEST TEST_TABLE1 TYPEID
TEST TEST_TABLE2 ACTIONTYPE
.
.
.
49692 rows selected.

In my case almost all schemas in the database are affected including sys and system.

For Nchar, Nvarchar2 and Nclob:
SQL> set lines 199
SQL> col owner format a10
SQL> col table_name format a20
SQL> col column_name format a20
SQL> select OWNER, TABLE_NAME, COLUMN_NAME from DBA_TAB_COLS where COLUMN_ID in (select unique COL#
from sys.col$ where CHARSETID=NLS_CHARSET_ID('WE8ISO8859P1') and charsetform ='2')
order by OWNER, TABLE_NAME, COLUMN_NAME;

no rows selected

Oracle note suggests that if the character set conversion has happened between a 7/8 bit character set like WE8ISO8859P1, US7ASCII etc to a mutibyte character set like UTF8, AL32UTF8 etc, then there will be data loss for clob columns which display the old character set. So it is best to take a full back of the database, preferably using the tradional export utility.

Check the following things:
SQL> sho parameter CLUSTER_DATABASE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean FALSE
cluster_database_instances integer 1
SQL> sho parameter PARALLEL_SERVER
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_server boolean FALSE
parallel_server_instances integer 1

The parameters CLUSTER_DATABASE & parallel_server should be false.

Save the values for;
SQL> show parameter JOB_QUEUE_PROCESSES
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 10
SQL> show parameter AQ_TM_PROCESSES
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 2

To start the fix:
Shutdown listener, make sure there are not connections to the database.
SQL> shutdown immediate;
SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area 926941184 bytes
Fixed Size 1222648 bytes
Variable Size 276826120 bytes
Database Buffers 641728512 bytes
Redo Buffers 7163904 bytes
Database mounted.
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
System altered.

SQL> ALTER DATABASE OPEN;
Database altered.
SQL> COL VALUE NEW_VALUE CHARSET
SQL> SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';
VALUE
--------------------------------------------------------------------------------
AL32UTF8

SQL> COL VALUE NEW_VALUE NCHARSET
SQL> SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_NCHAR_CHARACTERSET';
VALUE
--------------------------------------------------------------------------------
AL16UTF16

SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE &CHARSET;
old 1: ALTER DATABASE CHARACTER SET INTERNAL_USE &CHARSET
new 1: ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8
Database altered.
SQL> ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE &NCHARSET;
old 1: ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE &NCHARSET
new 1: ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE AL16UTF16
Database altered.
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP;
ORACLE instance started.
Total System Global Area 926941184 bytes
Fixed Size 1222648 bytes
Variable Size 276826120 bytes
Database Buffers 641728512 bytes
Redo Buffers 7163904 bytes
Database mounted.
Database opened.
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 926941184 bytes
Fixed Size 1222648 bytes
Variable Size 276826120 bytes
Database Buffers 641728512 bytes
Redo Buffers 7163904 bytes
Database mounted.
Database opened.

Need to restart the database twice.

Check the database to see if there are any problems:
SQL> SQL> SQL> select distinct(nls_charset_name(charsetid)) CHARACTERSET,
decode(type#, 1, decode(charsetform, 1, 'VARCHAR2', 2, 'NVARCHAR2','UNKOWN'),
9, decode(charsetform, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKOWN'),
96, decode(charsetform, 1, 'CHAR', 2, 'NCHAR', 'UNKOWN'),
112, decode(charsetform, 1, 'CLOB', 2, 'NCLOB', 'UNKOWN')) TYPES_USED_IN
from sys.col$ where charsetform in (1,2) and type# in (1, 9, 96, 112);

CHARACTERSET TYPES_USED_IN
---------------------------------------- -------------
AL32UTF8 VARCHAR2
AL16UTF16 NCLOB
AL16UTF16 NVARCHAR2
AL32UTF8 CHAR
AL32UTF8 CLOB
AL16UTF16 NCHAR

6 rows selected.

SQL> select OWNER, TABLE_NAME, COLUMN_NAME from DBA_TAB_COLS where COLUMN_ID in (select unique COL#
from sys.col$ where CHARSETID=NLS_CHARSET_ID('WE8ISO8859P1') and charsetform ='1')
order by OWNER, TABLE_NAME, COLUMN_NAME;

no rows selected

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=10;
System altered.

SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=2;
System altered.

This procedure doesn't fix any data, it cannot get back the lost data due to character set changes. That will need to be restored from backup.
All it does is fixes the references the tables are having to the old character set. Hence this is just the solution to fix the data dictionary not the data, or the loss that has happened.

Oracle Partitioning VIII - Partition Indexes

2009-05-14T14:18:00.005+05:30

Indexing on Partitoned Tables:
Local Indexes:
A local index on a partition table will have the same partition schema as the table. Each partition in a local index will have similar entries like the corresponding data in the table partition.
eg.:
Creating a table first:
SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
data_size NUMBER,
state VARCHAR2(20))
PARTITION BY RANGE (load_date)
SUBPARTITION BY LIST (state)
SUBPARTITION TEMPLATE (
SUBPARTITION NORTH_01 VALUES ('DELHI', 'JAMMU KASHMIR', 'PUNJAB', 'HARYANA') TABLESPACE DATA_01,
SUBPARTITION WEST_01 VALUES ('RAJASTHAN', 'GUJRAT', 'MAHARASTRA') TABLESPACE DATA_02,
SUBPARTITION SOUTH_01 VALUES ('TAMIL NADU', 'KERELA', 'KARNATAKA') TABLESPACE DATA_03,
SUBPARTITION EAST_01 VALUES ('ASSAM', 'WEST BENGAL', 'NAGALAND') TABLESPACE DATA_04,
SUBPARTITION NO_REGION VALUES (NULL) TABLESPACE USERS,
SUBPARTITION OTHER VALUES (DEFAULT) TABLESPACE USERS
)
( PARTITION data_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','DD-MON-YYYY')) ,
PARTITION data_2007 VALUES LESS THAN (TO_DATE('01-JAN-2008','DD-MON-YYYY')) ,
PARTITION data_2008 VALUES LESS THAN (TO_DATE('01-JAN-2009','DD-MON-YYYY')) ,
PARTITION data_2009 VALUES LESS THAN (TO_DATE('01-JAN-2010','DD-MON-YYYY'))
);
Table created.

SQL> create index DATA_LOAD_LOCAL1 on DATA_LOAD (load_date) LOCAL;
Index created.

SQL> insert into DATA_LOAD values (1,1,sysdate-1200,10,'DELHI');
1 row created.
SQL> insert into DATA_LOAD values (2,2,sysdate-1100,20,'RAJASTHAN');
1 row created.
SQL> insert into DATA_LOAD values (3,3,sysdate-1000,30,'KERELA');
1 row created.
SQL> insert into DATA_LOAD values (4,4,sysdate-1300,40,'ASSAM');
1 row created.
SQL> insert into DATA_LOAD values (5,5,sysdate-900,50,'NAGALAND');
1 row created.
SQL> insert into DATA_LOAD values (6,6,sysdate-300,60,'PUNJAB');
1 row created.
SQL> insert into DATA_LOAD values (7,7,sysdate,70,'HARYANA');
1 row created.
SQL> commit;
Commit complete.
SQL> analyze table DATA_LOAD compute statistics;
Table analyzed.

SQL> analyze index DATA_LOAD_LOCAL1 compute statistics ;
Index analyzed.

SQL> select INDEX_NAME,COMPOSITE,PARTITION_NAME,SUBPARTITION_COUNT,NUM_ROWS from user_ind_partitions where index_name='DATA_LOAD_LOCAL1';

INDEX_NAME COM PARTITION_NAME SUBPARTITION_COUNT NUM_ROWS
------------------------------ --- --------------- ------------------ ----------
DATA_LOAD_LOCAL1 YES DATA_2006 6 5
DATA_LOAD_LOCAL1 YES DATA_2007 6 0
DATA_LOAD_LOCAL1 YES DATA_2008 6 1
DATA_LOAD_LOCAL1 YES DATA_2009 6 1

SO we can see the index partitions were created the same way as the table partition, same subpartitions also.

Prefixed LOCAL Index:
A prefixed local index is an index in which the left most column used in the index definition is the same as the table partitioning key. The above example is a local prefixed index.

LOCAL NON-Prefixed Index:
As the name suggests its opposite to the prefixed local index. In this index the left most key is not the partition key column.

SQL> create index DATA_LOAD_LOCAL2 on DATA_LOAD (source_id) LOCAL;
Index created.

SQL> analyze index DATA_LOAD_LOCAL2 compute statistics;
Index analyzed.

SQL> select INDEX_NAME,COMPOSITE,PARTITION_NAME,SUBPARTITION_COUNT,NUM_ROWS from user_ind_partitions where index_name='DATA_LOAD_LOCAL2';
INDEX_NAME COM PARTITION_NAME SUBPARTITION_COUNT NUM_ROWS
------------------------------ --- --------------- ------------------ ----------
DATA_LOAD_LOCAL2 YES DATA_2006 6 5
DATA_LOAD_LOCAL2 YES DATA_2007 6 0
DATA_LOAD_LOCAL2 YES DATA_2008 6 1
DATA_LOAD_LOCAL2 YES DATA_2009 6 1

We can see that even in the NON-Prefixed index has the same partitioning scheme as the partition table.
Each partition of the NON-Prefixed Local index will contain the whole range of possible partition key values, so this might we inefficient at time if we are not using parallel option while selecting. As here the partitions for the index are created like the that of the table using the partition key, but the index key is something else.

GLOBAL INDEXES:
Basically Global Indexes are indexes on which we can define the parition scheme, we define how many partitions we want of the index using which ever table column we would like to use. A global index can be created with the table partition key as the leftmost column or with any other column and in the create statement we can define the partition values for the key.

SQL> CREATE INDEX DATA_LOAD_GLOBAL ON DATA_LOAD (load_date,data_size)
GLOBAL PARTITION BY RANGE (load_date)
(PARTITION p1 VALUES LESS THAN (TO_DATE('01-JAN-2008','DD-MON-YYYY')),
PARTITION p2 VALUES LESS THAN (MAXVALUE)
);
Index created.

SQL> select INDEX_NAME,COMPOSITE,PARTITION_NAME,SUBPARTITION_COUNT,NUM_ROWS from user_ind_partitions where index_name='DATA_LOAD_GLOBAL';
INDEX_NAME COM PARTITION_NAME SUBPARTITION_COUNT NUM_ROWS
------------------------------ --- --------------- ------------------ ----------
DATA_LOAD_GLOBAL NO P1 0 5
DATA_LOAD_GLOBAL NO P2 0 2

We can see that the partition definition of the index is different from the table.

Actually the Global indexes are more useful when using other column for indexing the table rather than the partition key column of the table. As in our case the partitions are done on the basic on load_date, what if we use the column data_size more often to query than the load_date. In this case if we create a local index on data_size (non-prefixed local index) than its partition defination doesn't help the query as it is partitions are based on the table partitioning.
In our case:
Every partition based on load_date can have all the values of data_size. Say the data size column has values from 1 to 100.
Then DATA_2006 can have values 1 to 100, DATA_2007 can have values 1 to 100 and so on. So this does not help quering this index so much.

Instead we can create a global index on this column and define the correct partition scheme for the index using data_size as the partition key for the index.
SQL> CREATE INDEX DATA_LOAD_GLOBAL2 on DATA_LOAD (data_size)
GLOBAL PARTITION BY RANGE (data_size)
(PARTITION p1 VALUES LESS THAN (30),
PARTITION p2 VALUES LESS THAN (50),
PARTITION p3 VALUES LESS THAN (80),
PARTITION p4 VALUES LESS THAN (MAXVALUE)
);
Index created.

SQL> select INDEX_NAME,COMPOSITE,PARTITION_NAME,SUBPARTITION_COUNT,NUM_ROWS from user_ind_partitions where index_name='DATA_LOAD_GLOBAL2';
INDEX_NAME COM PARTITION_NAME SUBPARTITION_COUNT NUM_ROWS
------------------------------ --- --------------- ------------------ ----------
DATA_LOAD_GLOBAL2 NO P2 0 2
DATA_LOAD_GLOBAL2 NO P3 0 3
DATA_LOAD_GLOBAL2 NO P4 0 0
DATA_LOAD_GLOBAL2 NO P1 0 2

Note: Global indexed should have same left most index key and the index partition key, they cannot be different.
Else we will receive the below error. They are different from the non-prefixed local partition in this context.

SQL> CREATE INDEX DATA_LOAD_GLOBAL2 on DATA_LOAD (data_size)
GLOBAL PARTITION BY RANGE (source_id)
(PARTITION p1 VALUES LESS THAN (30),
PARTITION p2 VALUES LESS THAN (50),
PARTITION p3 VALUES LESS THAN (80),
PARTITION p4 VALUES LESS THAN (MAXVALUE)
); 2 3 4 5 6 7
GLOBAL PARTITION BY RANGE (source_id)
*
ERROR at line 2:
ORA-14038: GLOBAL partitioned index must be prefixed

Similarly we can have hash and list partition Global indexes:

SQL> CREATE INDEX DATA_LOAD_GLOBAL3 on DATA_LOAD (data_size) GLOBAL PARTITION BY HASH (data_size) PARTITIONS 4;
CREATE INDEX DATA_LOAD_GLOBAL3 on DATA_LOAD (data_size) GLOBAL PARTITION BY HASH (data_size) PARTITIONS 4
*
ERROR at line 1:
ORA-01408: such column list already indexed
We received the above error as we already create an index with the above column.

SQL> CREATE INDEX DATA_LOAD_GLOBAL3 on DATA_LOAD (data_size,source_id) GLOBAL PARTITION BY HASH (data_size) PARTITIONS 4;
Index created.

And so on....

Details about Oracle Partition Methods/Types

Oracle Partitioning - Exchange Partition

2009-05-14T07:58:00.007+05:30

The need and use of exchange partitions:

I have used exchange partition to archive old data from an existing partition table. As we have a lot of very large partition tables which store data for past years, we exchange older partitions which don't need to be part of the existing table to a new table and then export it and save it on to a tape device as archive data. This way we are able to store the data out of the database and keep it on tapes for any future use. We can import this data whenever we need. We see that exchange partition is widely used in data warehousing environments to keep the tables in a manageable size, achieve adequate performance apart from data archiving. As using exchange partition we can restore this data back into the old table whenever we need it.
We can use exchange partition to convert a non partitioned table into a partitioned table also.
Syntax and example:

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
data_size NUMBER,
state VARCHAR2(20))
PARTITION BY RANGE (load_date)
( PARTITION data_2006 VALUES LESS THAN (TO_DATE('01-Jan-2007','DD-MON-YYYY')) TABLESPACE DATA_01,
PARTITION data_2007 VALUES LESS THAN (TO_DATE('01-Jan-2008','DD-MON-YYYY')) TABLESPACE DATA_02,
PARTITION data_2008 VALUES LESS THAN (TO_DATE('01-Jan-2009','DD-MON-YYYY')) TABLESPACE DATA_03,
PARTITION data_2009 VALUES LESS THAN (TO_DATE('01-Jan-2010','DD-MON-YYYY')) TABLESPACE DATA_04
);
Table created.

SQL> insert into DATA_LOAD values (1,1,sysdate-1200,1,'CA');
1 row created.
SQL> insert into DATA_LOAD values (2,2,sysdate-1100,1,'LA');
1 row created.
SQL> insert into DATA_LOAD values (3,3,sysdate-1000,1,'FA');
1 row created.
SQL> insert into DATA_LOAD values (4,4,sysdate-1300,1,'GA');
1 row created.
SQL> insert into DATA_LOAD values (5,5,sysdate-900,1,'BA');
1 row created.
SQL> insert into DATA_LOAD values (6,6,sysdate-300,1,'RA');
1 row created.
SQL> insert into DATA_LOAD values (7,7,sysdate,1,'TA');
1 row created.
SQL> commit;
Commit complete.

SQL> set lines 300
SQL> select * from DATA_LOAD;
DATA_ID SOURCE_ID LOAD_DATE DATA_SIZE STATE
---------- ---------- --------- ---------- --------------------
1 1 30-JAN-06 1 CA
2 2 10-MAY-06 1 LA
3 3 18-AUG-06 1 FA
4 4 22-OCT-05 1 GA
5 5 26-NOV-06 1 BA
6 6 18-JUL-08 1 RA
7 7 14-MAY-09 1 TA
7 rows selected.

SQL> analyze table DATA_LOAD compute statistics;
Table analyzed.

SQL> select TABLE_NAME,PARTITION_NAME,num_rows from user_tab_partitions where TABLE_NAME='DATA_LOAD';
TABLE_NAME PARTITION_NAME NUM_ROWS
--------------- --------------- ----------
DATA_LOAD DATA_2006 5
DATA_LOAD DATA_2007 0
DATA_LOAD DATA_2008 1
DATA_LOAD DATA_2009 1

Now lets try and move the DATA_2006 to a new table, lets create the table to exchange the partition with:
SQL> create table DATA_2006 as select * from DATA_LOAD where 1=2;
Table created.

SQL> select TABLE_NAME,PARTITION_NAME,num_rows from user_tab_partitions where TABLE_NAME='DATA_2006';
no rows selected

SQL> alter table DATA_LOAD EXCHANGE PARTITION DATA_2006 with table DATA_2006 without validation update global indexes;
Table altered.

SQL> select * from DATA_2006;
DATA_ID SOURCE_ID LOAD_DATE DATA_SIZE STATE
---------- ---------- --------- ---------- --------------------
1 1 30-JAN-06 1 CA
2 2 10-MAY-06 1 LA
3 3 18-AUG-06 1 FA
4 4 22-OCT-05 1 GA
5 5 26-NOV-06 1 BA

SQL> select * from DATA_LOAD;
DATA_ID SOURCE_ID LOAD_DATE DATA_SIZE STATE
---------- ---------- --------- ---------- --------------------
6 6 18-JUL-08 1 RA
7 7 14-MAY-09 1 TA

This was an example of single level partition, lets try a table with composite partitions:

SQL> drop table DATA_LOAD;
Table dropped.
SQL> drop table DATA_2006;
Table dropped.

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
data_size NUMBER,
state VARCHAR2(20))
PARTITION BY RANGE (load_date)
SUBPARTITION BY LIST (state)
SUBPARTITION TEMPLATE (
SUBPARTITION NORTH_01 VALUES ('DELHI', 'JAMMU KASHMIR', 'PUNJAB', 'HARYANA') TABLESPACE DATA_01,
SUBPARTITION WEST_01 VALUES ('RAJASTHAN', 'GUJRAT', 'MAHARASTRA') TABLESPACE DATA_02,
SUBPARTITION SOUTH_01 VALUES ('TAMIL NADU', 'KERELA', 'KARNATAKA') TABLESPACE DATA_03,
SUBPARTITION EAST_01 VALUES ('ASSAM', 'WEST BENGAL', 'NAGALAND') TABLESPACE DATA_04,
SUBPARTITION NO_REGION VALUES (NULL) TABLESPACE USERS,
SUBPARTITION OTHER VALUES (DEFAULT) TABLESPACE USERS
)
( PARTITION data_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','DD-MON-YYYY')) ,
PARTITION data_2007 VALUES LESS THAN (TO_DATE('01-JAN-2008','DD-MON-YYYY')) ,
PARTITION data_2008 VALUES LESS THAN (TO_DATE('01-JAN-2009','DD-MON-YYYY')) ,
PARTITION data_2009 VALUES LESS THAN (TO_DATE('01-JAN-2010','DD-MON-YYYY'))
);
Table created.

SQL> insert into DATA_LOAD values (1,1,sysdate-1200,1,'DELHI');
1 row created.
SQL> insert into DATA_LOAD values (2,2,sysdate-1100,1,'RAJASTHAN');
1 row created.
SQL> insert into DATA_LOAD values (3,3,sysdate-1000,1,'KERELA');
1 row created.
SQL> insert into DATA_LOAD values (4,4,sysdate-1300,1,'ASSAM');
1 row created.
SQL> insert into DATA_LOAD values (5,5,sysdate-900,1,'NAGALAND');
1 row created.
SQL> insert into DATA_LOAD values (6,6,sysdate-300,1,'PUNJAB');
1 row created.
SQL> insert into DATA_LOAD values (7,7,sysdate,1,'HARYANA');
1 row created.
SQL> commit;
Commit complete.
SQL> analyze table DATA_LOAD compute statistics;
Table analyzed.

SQL> select TABLE_NAME,PARTITION_NAME,num_rows,SUBPARTITION_COUNT from user_tab_partitions where TABLE_NAME='DATA_LOAD';
TABLE_NAME PARTITION_NAME NUM_ROWS SUBPARTITION_COUNT
--------------- --------------- ---------- ------------------
DATA_LOAD DATA_2006 5 6
DATA_LOAD DATA_2007 0 6
DATA_LOAD DATA_2008 1 6
DATA_LOAD DATA_2009 1 6

SQL> select TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,num_rows from user_tab_subpartitions where TABLE_NAME='DATA_LOAD';
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME NUM_ROWS
--------------- --------------- ------------------------------ ----------
DATA_LOAD DATA_2006 DATA_2006_OTHER 0
DATA_LOAD DATA_2006 DATA_2006_NO_REGION 0
DATA_LOAD DATA_2006 DATA_2006_EAST_01 2
DATA_LOAD DATA_2006 DATA_2006_SOUTH_01 1
DATA_LOAD DATA_2006 DATA_2006_WEST_01 1
DATA_LOAD DATA_2006 DATA_2006_NORTH_01 1
DATA_LOAD DATA_2007 DATA_2007_OTHER 0
DATA_LOAD DATA_2007 DATA_2007_NO_REGION 0
DATA_LOAD DATA_2007 DATA_2007_EAST_01 0
DATA_LOAD DATA_2007 DATA_2007_SOUTH_01 0
DATA_LOAD DATA_2007 DATA_2007_WEST_01 0
DATA_LOAD DATA_2007 DATA_2007_NORTH_01 0
DATA_LOAD DATA_2008 DATA_2008_OTHER 0
DATA_LOAD DATA_2008 DATA_2008_NO_REGION 0
DATA_LOAD DATA_2008 DATA_2008_EAST_01 0
DATA_LOAD DATA_2008 DATA_2008_SOUTH_01 0
DATA_LOAD DATA_2008 DATA_2008_WEST_01 0
DATA_LOAD DATA_2008 DATA_2008_NORTH_01 1
DATA_LOAD DATA_2009 DATA_2009_OTHER 0
DATA_LOAD DATA_2009 DATA_2009_NO_REGION 0
DATA_LOAD DATA_2009 DATA_2009_EAST_01 0
DATA_LOAD DATA_2009 DATA_2009_SOUTH_01 0
DATA_LOAD DATA_2009 DATA_2009_WEST_01 0
DATA_LOAD DATA_2009 DATA_2009_NORTH_01 1
24 rows selected.

Now lets create the table to exchange the partition with:

SQL> create table DATA_2006 as select * from DATA_LOAD where 1=2;
Table created.
Now to exchange the partition DATA_2006, with the table DATA_2006:

SQL> alter table DATA_LOAD EXCHANGE PARTITION DATA_2006 with table DATA_2006 without validation update global indexes;
alter table DATA_LOAD EXCHANGE PARTITION DATA_2006 with table DATA_2006 without validation update global indexes
*
ERROR at line 1:
ORA-14291: cannot EXCHANGE a composite partition with a non-partitioned table

SQL> !oerr ora 14291
14291, 00000, "cannot EXCHANGE a composite partition with a non-partitioned table"
// *Cause: A composite partition can only be exchanged with a partitioned
// table.
// *Action: Ensure that the table being exchanged is partitioned or that
// that the partition being exchanged is non-composite.

This should that we cannot exchange partition with a normal table like we did the last time, now lets create the correct table:

SQL> drop table DATA_2006;
Table dropped.

We need to create a table with the partition scheme as the subpartition scheme of the main/big table, to be able to exchange its partition with a new table:

SQL> CREATE TABLE DATA_2006
2 ( DATA_id NUMBER(6),
3 source_id NUMBER,
4 load_date DATE,
5 data_size NUMBER,
6 state VARCHAR2(20))
7 PARTITION BY LIST (state)
8 (PARTITION NORTH_01 VALUES ('DELHI', 'JAMMU KASHMIR', 'PUNJAB', 'HARYANA') TABLESPACE DATA_01,
9 PARTITION WEST_01 VALUES ('RAJASTHAN', 'GUJRAT', 'MAHARASTRA') TABLESPACE DATA_02,
10 PARTITION SOUTH_01 VALUES ('TAMIL NADU', 'KERELA', 'KARNATAKA') TABLESPACE DATA_03,
11 PARTITION EAST_01 VALUES ('ASSAM', 'WEST BENGAL', 'NAGALAND') TABLESPACE DATA_04,
12 PARTITION NO_REGION VALUES (NULL) TABLESPACE USERS,
13 PARTITION OTHER VALUES (DEFAULT) TABLESPACE USERS
14 );
Table created.

SQL> alter table DATA_LOAD EXCHANGE PARTITION DATA_2006 with table DATA_2006 without validation update global indexes;
Table altered.

Just to check how the partitions are laid out on the new table. You can see that the subpartition data has moved to the respective partitions of the exchanged table.

SQL> select * from DATA_2006;
DATA_ID SOURCE_ID LOAD_DATE DATA_SIZE STATE
---------- ---------- --------- ---------- --------------------
1 1 30-JAN-06 1 DELHI
2 2 10-MAY-06 1 RAJASTHAN
3 3 18-AUG-06 1 KERELA
4 4 22-OCT-05 1 ASSAM
5 5 26-NOV-06 1 NAGALAND

SQL> select * from DATA_LOAD;
DATA_ID SOURCE_ID LOAD_DATE DATA_SIZE STATE
---------- ---------- --------- ---------- --------------------
6 6 18-JUL-08 1 PUNJAB
7 7 14-MAY-09 1 HARYANA

SQL> select TABLE_NAME,PARTITION_NAME,num_rows,SUBPARTITION_COUNT from user_tab_partitions where TABLE_NAME='DATA_2006';
TABLE_NAME PARTITION_NAME NUM_ROWS SUBPARTITION_COUNT
--------------- --------------- ---------- ------------------
DATA_2006 NORTH_01 1 0
DATA_2006 WEST_01 1 0
DATA_2006 SOUTH_01 1 0
DATA_2006 EAST_01 2 0
DATA_2006 NO_REGION 0 0
DATA_2006 OTHER 0 0
6 rows selected.

The main/big table will still have the partition defination but with no rows after we analyze the table. Now we can either leave this empty partition or drop the same.

SQL> select TABLE_NAME,PARTITION_NAME,num_rows,SUBPARTITION_COUNT from user_tab_partitions where TABLE_NAME='DATA_LOAD';
TABLE_NAME PARTITION_NAME NUM_ROWS SUBPARTITION_COUNT
--------------- --------------- ---------- ------------------
DATA_LOAD DATA_2006 0 6
DATA_LOAD DATA_2007 0 6
DATA_LOAD DATA_2008 1 6
DATA_LOAD DATA_2009 1 6

We can restore the data back into the main table also. We can exchange the this newly created table with the empty partition in the big/main table back, incase we ever need the data back into the main table. Hence if we archive data by exchange partition we can restore them back into the main table if we ever need it. Another thing is if we might need to restore we should not drop the empty partitions as its easier to restore the data, else we would need to do a split partition to create the partitions again.

SQL> alter table DATA_LOAD EXCHANGE PARTITION DATA_2006 with table DATA_2006 without validation update global indexes;
Table altered.

SQL> select * from DATA_2006;
no rows selected

SQL> select * from DATA_LOAD;
DATA_ID SOURCE_ID LOAD_DATE DATA_SIZE STATE
---------- ---------- --------- ---------- --------------------
1 1 30-JAN-06 1 DELHI
2 2 10-MAY-06 1 RAJASTHAN
3 3 18-AUG-06 1 KERELA
4 4 22-OCT-05 1 ASSAM
5 5 26-NOV-06 1 NAGALAND
6 6 18-JUL-08 1 PUNJAB
7 7 14-MAY-09 1 HARYANA
7 rows selected.

SQL> select TABLE_NAME,PARTITION_NAME,num_rows,SUBPARTITION_COUNT from user_tab_partitions where TABLE_NAME='DATA_LOAD';
TABLE_NAME PARTITION_NAME NUM_ROWS SUBPARTITION_COUNT
--------------- --------------- ---------- ------------------
DATA_LOAD DATA_2006 5 6
DATA_LOAD DATA_2007 0 6
DATA_LOAD DATA_2008 1 6
DATA_LOAD DATA_2009 1 6

Useful metalink notes:
Exchange Partitions - Common Problems, Doc ID: 198120.1

SCRIPT: To Automate the Composite-Partitioned Table Exchange, Doc ID: 100701.1

Exchange Partition Having Global Index with Non - Partitioned Table, Doc ID: 419505.1

Note: When we do exchange partition the local indexes become invalid, hence we would need to rebuild the same. If we provide the update global indexes, the global indexes don't become invalid and hence no need to rebuild them.

Oracle Partitioning - Merge Partition

2009-05-13T11:11:00.001+05:30

Oracle allows us to merge 2 partitions into one new partition.
SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
data_size NUMBER,
state VARCHAR2(20))
PARTITION BY RANGE (data_size)
( PARTITION data_01_2009 VALUES LESS THAN (10) TABLESPACE DATA_01,
PARTITION data_02_2009 VALUES LESS THAN (20) TABLESPACE DATA_02,
PARTITION data_03_2009 VALUES LESS THAN (30) TABLESPACE DATA_03,
PARTITION data_04_2009 VALUES LESS THAN (40) TABLESPACE DATA_04
);
Table created.

SQL> alter table DATA_LOAD merge partitions data_01_2009,data_02_2009 into partition data_01_02_2009 TABLESPACE DATA_02;
Table altered.

Merging Subpartitions:

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
target_date DATE,
data_size NUMBER,
state VARCHAR2(20)
)
PARTITION BY RANGE (load_date)
SUBPARTITION BY RANGE (target_date)
( PARTITION data_01_2009 VALUES LESS THAN (TO_DATE('01-FEB-2009','DD-MON-YYYY')) TABLESPACE DATA_01
(SUBPARTITION data_01_2009_1 VALUES LESS THAN (TO_DATE('15-JAN-2009','DD-MON-YYYY')),
SUBPARTITION data_01_2009_2 VALUES LESS THAN (TO_DATE('25-JAN-2009','DD-MON-YYYY'))
) ,
PARTITION data_02_2009 VALUES LESS THAN (TO_DATE('01-MAR-2009','DD-MON-YYYY')) TABLESPACE DATA_02
(SUBPARTITION data_02_2009_1 VALUES LESS THAN (TO_DATE('15-FEB-2009','DD-MON-YYYY')),
SUBPARTITION data_02_2009_2 VALUES LESS THAN (TO_DATE('25-FEB-2009','DD-MON-YYYY'))
) ,
PARTITION data_03_2009 VALUES LESS THAN (TO_DATE('01-APR-2009','DD-MON-YYYY')) TABLESPACE DATA_03
(SUBPARTITION data_03_2009_1 VALUES LESS THAN (TO_DATE('15-MAR-2009','DD-MON-YYYY')),
SUBPARTITION data_03_2009_2 VALUES LESS THAN (TO_DATE('25-MAR-2009','DD-MON-YYYY')),
SUBPARTITION data_03_2009_3 VALUES LESS THAN (MAXVALUE)
) ,
PARTITION data_04_2009 VALUES LESS THAN (TO_DATE('01-MAY-2009','DD-MON-YYYY')) TABLESPACE DATA_04
(SUBPARTITION data_04_2009_1 VALUES LESS THAN (TO_DATE('15-APR-2009','DD-MON-YYYY')),
SUBPARTITION data_04_2009_2 VALUES LESS THAN (TO_DATE('25-APR-2009','DD-MON-YYYY')),
SUBPARTITION data_04_2009_3 VALUES LESS THAN (MAXVALUE)
)
);

SQL> alter table DATA_LOAD merge subpartitions data_03_2009_1,data_03_2009_2 into subpartition data_03_2009_1_2;
Table altered.

Details about Oracle Partition Methods/Types

Oracle Partitioning - Add Partition

2009-05-13T06:58:00.003+05:30

We can also add partitions to an already created partition table. Creating a partition table.
SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
data_size NUMBER,
state VARCHAR2(20))
PARTITION BY RANGE (data_size)
( PARTITION data_01_2009 VALUES LESS THAN (10) TABLESPACE DATA_01,
PARTITION data_02_2009 VALUES LESS THAN (20) TABLESPACE DATA_02,
PARTITION data_03_2009 VALUES LESS THAN (30) TABLESPACE DATA_03,
PARTITION data_04_2009 VALUES LESS THAN (40) TABLESPACE DATA_04
);
Table created.

Note: The table should not have a max value partition, else you will need to use split partition instead of add partition. Partition can be added only in the end not in between.

SQL> insert into DATA_LOAD values (1,1,sysdate-20,9,'DL');
1 row created.
SQL> insert into DATA_LOAD values (2,2,sysdate-9,20,'RJ');
1 row created.
SQL> insert into DATA_LOAD values (2,2,sysdate-9,35,'LL');
1 row created.
SQL> commit;
Commit complete.
SQL> insert into DATA_LOAD values (2,2,sysdate-3,50,'LL');
insert into DATA_LOAD values (2,2,sysdate-3,50,'LL')
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

Now when we tried to insert a value higher than the partition bound value we get this error.
Now lets add partition to accomodate this insert statement.

SQL> alter table DATA_LOAD add partition data_05_2009 VALUES LESS THAN (50) TABLESPACE DATA_01;
Table altered.

SQL> alter table DATA_LOAD add partition data_06_2009 VALUES LESS THAN (60) TABLESPACE DATA_02;
Table altered.

SQL> insert into DATA_LOAD values (2,2,sysdate-3,50,'LL');
1 row created.

Same way the partitions can be added for list or hash partition:
Hash partition: SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
data_size NUMBER,
state VARCHAR2(20))
PARTITION BY HASH (source_id)
( PARTITION data_01_2009 TABLESPACE DATA_01,
PARTITION data_02_2009 TABLESPACE DATA_02,
PARTITION data_03_2009 TABLESPACE DATA_03,
PARTITION data_04_2009 TABLESPACE DATA_04
);
Table created.

SQL> alter table DATA_LOAD add partition data_05_2009 TABLESPACE DATA_01;
Table altered.

List Partition: SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
data_size NUMBER,
state VARCHAR2(20))
PARTITION BY LIST (state)
(PARTITION NORTH_01 VALUES ('DELHI', 'JAMMU KASHMIR', 'PUNJAB', 'HARYANA'),
PARTITION WEST_01 VALUES ('RAJASTHAN', 'GUJRAT', 'MAHARASTRA'),
PARTITION SOUTH_01 VALUES ('TAMIL NADU', 'KERELA', 'KARNATAKA'),
PARTITION EAST_01 VALUES ('ASSAM', 'WEST BENGAL', 'NAGALAND'),
PARTITION NO_REGION VALUES (NULL),
PARTITION OTHER VALUES (DEFAULT)
);
Table created.

Its important that the table does not have a DEFAULT value partition.

SQL> alter table DATA_LOAD add partition NORTH_EAST_01 VALUES ('ARUNACHAL PRADESH','MIZORAM','TRIPURA');
alter table DATA_LOAD add partition NORTH_EAST_01 VALUES ('ARUNACHAL PRADESH','MIZORAM','TRIPURA')
*
ERROR at line 1:
ORA-14323: cannot add partition when DEFAULT partition exists

SQL> drop table DATA_LOAD
2 /
Table dropped.
Alternately, you cn also drop the partition, make sure that the partition does not have any data, rows:

SQL> alter table DATA_LOAD drop partition OTHER;
Table altered.

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
data_size NUMBER,
state VARCHAR2(20))
PARTITION BY LIST (state)
(PARTITION NORTH_01 VALUES ('DELHI', 'JAMMU KASHMIR', 'PUNJAB', 'HARYANA'),
PARTITION WEST_01 VALUES ('RAJASTHAN', 'GUJRAT', 'MAHARASTRA'),
PARTITION SOUTH_01 VALUES ('TAMIL NADU', 'KERELA', 'KARNATAKA'),
PARTITION EAST_01 VALUES ('ASSAM', 'WEST BENGAL', 'NAGALAND'),
PARTITION NO_REGION VALUES (NULL)
);
Table created.

SQL> alter table DATA_LOAD add partition NORTH_EAST_01 VALUES ('ARUNACHAL PRADESH','MIZORAM','TRIPURA');
Table altered.

Add subpartition:

Create table first:
SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
target_date DATE,
data_size NUMBER,
state VARCHAR2(20)
)
PARTITION BY RANGE (load_date)
SUBPARTITION BY RANGE (target_date)
( PARTITION data_01_2009 VALUES LESS THAN (TO_DATE('01-FEB-2009','DD-MON-YYYY')) TABLESPACE DATA_01
(SUBPARTITION data_01_2009_1 VALUES LESS THAN (TO_DATE('15-JAN-2009','DD-MON-YYYY')),
SUBPARTITION data_01_2009_2 VALUES LESS THAN (TO_DATE('25-JAN-2009','DD-MON-YYYY'))
) ,
PARTITION data_02_2009 VALUES LESS THAN (TO_DATE('01-MAR-2009','DD-MON-YYYY')) TABLESPACE DATA_02
(SUBPARTITION data_02_2009_1 VALUES LESS THAN (TO_DATE('15-FEB-2009','DD-MON-YYYY')),
SUBPARTITION data_02_2009_2 VALUES LESS THAN (TO_DATE('25-FEB-2009','DD-MON-YYYY'))
) ,
PARTITION data_03_2009 VALUES LESS THAN (TO_DATE('01-APR-2009','DD-MON-YYYY')) TABLESPACE DATA_03
(SUBPARTITION data_03_2009_1 VALUES LESS THAN (TO_DATE('15-MAR-2009','DD-MON-YYYY')),
SUBPARTITION data_03_2009_2 VALUES LESS THAN (TO_DATE('25-MAR-2009','DD-MON-YYYY')),
SUBPARTITION data_03_2009_3 VALUES LESS THAN (MAXVALUE)
) ,
PARTITION data_04_2009 VALUES LESS THAN (TO_DATE('01-MAY-2009','DD-MON-YYYY')) TABLESPACE DATA_04
(SUBPARTITION data_04_2009_1 VALUES LESS THAN (TO_DATE('15-APR-2009','DD-MON-YYYY')),
SUBPARTITION data_04_2009_2 VALUES LESS THAN (TO_DATE('25-APR-2009','DD-MON-YYYY')),
SUBPARTITION data_04_2009_3 VALUES LESS THAN (MAXVALUE)
)
);
Table created.

SQL> alter table DATA_LOAD modify PARTITION data_01_2009 add SUBPARTITION data_01_2009_3 VALUES LESS THAN (TO_DATE('28-JAN-2009','DD-MON-YYYY'));
Table altered.

SQL> alter table DATA_LOAD modify PARTITION data_03_2009 add SUBPARTITION data_03_2009_4 VALUES LESS THAN (TO_DATE('28-MAR-2009','DD-MON-YYYY'));
alter table DATA_LOAD modify PARTITION data_03_2009 add SUBPARTITION data_03_2009_4 VALUES LESS THAN (TO_DATE('28-MAR-2009','DD-MON-YYYY'))
*
ERROR at line 1:
ORA-14211: subpartition bound must collate higher than that of the last subpartition

Cant add partition if there is a maxvalye partition. Please make sure there is no rows before dropping.

SQL> alter table DATA_LOAD drop SUBPARTITION data_03_2009_3;
Table altered.

SQL> alter table DATA_LOAD modify PARTITION data_03_2009 add SUBPARTITION data_03_2009_4 VALUES LESS THAN (TO_DATE('28-MAR-2009','DD-MON-YYYY'));
Table altered.

Details about Oracle Partition Methods/Types

Oracle Partitioning - Split Partition

2009-05-13T06:25:00.002+05:30

At times when we initially create partitions we make them with a certain data load in mind. But after sometime/years we realize that the partition scheme is becoming a problem and there are certain partitions with more data and this can create performance problems. Here is how we can split the partitions to create smaller partitions or to say create partitions between already existing partitions.
Lets create a partition table first:

SQL> CREATE TABLE DATA_LOAD
2 ( DATA_id NUMBER(6),
3 source_id NUMBER,
4 load_date DATE,
5 data_size NUMBER,
6 state VARCHAR2(20))
7 PARTITION BY RANGE (data_size)
8 ( PARTITION data_01_2009 VALUES LESS THAN (10) TABLESPACE DATA_01,
9 PARTITION data_02_2009 VALUES LESS THAN (20) TABLESPACE DATA_02,
10 PARTITION data_03_2009 VALUES LESS THAN (30) TABLESPACE DATA_03,
11 PARTITION data_04_2009 VALUES LESS THAN (MAXVALUE) TABLESPACE DATA_04
12 );
Table created.

Inserting some data into the table:

SQL> insert into DATA_LOAD values (1,1,sysdate-20,9,'DL');
1 row created.
SQL> insert into DATA_LOAD values (2,2,sysdate-9,35,'RJ');
1 row created.
SQL> insert into DATA_LOAD values (3,3,sysdate-8,45,'LL');
1 row created.
SQL> insert into DATA_LOAD values (4,4,sysdate-7,100,'KK');
1 row created.
SQL> insert into DATA_LOAD values (5,5,sysdate-6,200,'JJ');
1 row created.
SQL> commit;
Commit complete.
SQL> analyze table DATA_LOAD compute statistics;
Table analyzed.
SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,num_rows from user_tab_partitions where TABLE_NAME='DATA_LOAD';
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
--------------- --------------- --------------- ----------
DATA_LOAD DATA_01_2009 10 1
DATA_LOAD DATA_02_2009 20 0
DATA_LOAD DATA_03_2009 30 0
DATA_LOAD DATA_04_2009 MAXVALUE 4

Now we can see that the 4th partition has more rows than the the others, so lets try and split the partition in to 2. And lets make a partition from value 30 to 100.

SQL> alter table DATA_LOAD split partition data_04_2009 at (100) into (PARTITION data_04_2009_a, PARTITION data_04_2009_b);
Table altered.

SQL> analyze table DATA_LOAD compute statistics;
Table analyzed.

SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,num_rows from user_tab_partitions where TABLE_NAME='DATA_LOAD';
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
--------------- --------------- --------------- ----------
DATA_LOAD DATA_01_2009 10 1
DATA_LOAD DATA_02_2009 20 0
DATA_LOAD DATA_03_2009 30 0
DATA_LOAD DATA_04_2009_A 100 2
DATA_LOAD DATA_04_2009_B MAXVALUE 2

So you can see the last partition got split into 2 parts.

Some important things to keep in mind before doing a split partition:

1.) SPLIT PARTITION cannot be used for hash partitions or subpartitions.
2.) Partition cannot be split along the already specified high boundry of the partition you are trying to split.

A good metalink note: 199623.1

Details about Oracle Partition Methods/Types

Oracle Partitioning Types - VII (Virtual Column Based Partitioning)

2009-05-08T08:47:00.003+05:30

Virtual Column Based Partitioning
As the name suggest oracle allows us to define partitions based on a virtual column of a table. All types of partitioning methods are allowed for virtual columns. The only thing that is not allowed is if the virtual column uses a PL\SQL function in its definition.

SQL> drop table DATA_LOAD;
Table dropped.

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
target_date DATE,
data_size NUMBER,
state VARCHAR2(20),
diff_date NUMBER GENERATED ALWAYS AS (target_date-load_date) VIRTUAL
)
PARTITION BY RANGE (load_date)
SUBPARTITION BY RANGE (diff_date)
( PARTITION data_01_2009 VALUES LESS THAN (TO_DATE('01-FEB-2009','DD-MON-YYYY')) TABLESPACE DATA_01
(SUBPARTITION data_01_2009_1 VALUES LESS THAN (10),
SUBPARTITION data_01_2009_2 VALUES LESS THAN (20),
SUBPARTITION data_01_2009_3 VALUES LESS THAN (MAXVALUE)
) ,
PARTITION data_02_2009 VALUES LESS THAN (TO_DATE('01-MAR-2009','DD-MON-YYYY')) TABLESPACE DATA_02
(SUBPARTITION data_02_2009_1 VALUES LESS THAN (10),
SUBPARTITION data_02_2009_2 VALUES LESS THAN (20),
SUBPARTITION data_02_2009_3 VALUES LESS THAN (MAXVALUE)
) ,
PARTITION data_03_2009 VALUES LESS THAN (TO_DATE('01-APR-2009','DD-MON-YYYY')) TABLESPACE DATA_03
(SUBPARTITION data_03_2009_1 VALUES LESS THAN (10),
SUBPARTITION data_03_2009_2 VALUES LESS THAN (20),
SUBPARTITION data_03_2009_3 VALUES LESS THAN (MAXVALUE)
) ,
PARTITION data_04_2009 VALUES LESS THAN (TO_DATE('01-MAY-2009','DD-MON-YYYY')) TABLESPACE DATA_04
(SUBPARTITION data_04_2009_1 VALUES LESS THAN (10),
SUBPARTITION data_04_2009_2 VALUES LESS THAN (20),
SUBPARTITION data_04_2009_3 VALUES LESS THAN (MAXVALUE)
)
);
Table created.

SQL> insert into DATA_LOAD values (1,2,sysdate-60,sysdate-50,1,'DELHI',DEFAULT);
1 row created.

SQL> insert into DATA_LOAD values (2,3,sysdate-50,sysdate-35,2,'RAJASTHAN',DEFAULT);
1 row created.

SQL> select * from DATA_LOAD;
DATA_ID SOURCE_ID LOAD_DATE TARGET_DA DATA_SIZE STATE DIFF_DATE
---------- ---------- --------- --------- ---------- -------------------- ----------
1 2 09-MAR-09 19-MAR-09 1 DELHI 10
2 3 19-MAR-09 03-APR-09 2 RAJASTHAN 15
3 4 08-APR-09 13-APR-09 3 RAJASTHAN 5
SQL> select TABLE_NAME,COMPOSITE,PARTITION_NAME,NUM_ROWS,subpartition_count from user_tab_partitions where TABLE_NAME='DATA_LOAD';

TABLE_NAME COM PARTITION_NAME NUM_ROWS SUBPARTITION_COUNT
--------------- --- --------------- ---------- ------------------
DATA_LOAD YES DATA_01_2009 0 3
DATA_LOAD YES DATA_02_2009 0 3
DATA_LOAD YES DATA_03_2009 2 3
DATA_LOAD YES DATA_04_2009 1 3

SQL> select TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,NUM_ROWS from USER_TAB_SUBPARTITIONS where TABLE_NAME='DATA_LOAD';
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME NUM_ROWS
--------------- --------------- ------------------------------ ----------
DATA_LOAD DATA_01_2009 DATA_01_2009_3 0
DATA_LOAD DATA_01_2009 DATA_01_2009_2 0
DATA_LOAD DATA_01_2009 DATA_01_2009_1 0
DATA_LOAD DATA_02_2009 DATA_02_2009_3 0
DATA_LOAD DATA_02_2009 DATA_02_2009_2 0
DATA_LOAD DATA_02_2009 DATA_02_2009_1 0
DATA_LOAD DATA_03_2009 DATA_03_2009_3 0
DATA_LOAD DATA_03_2009 DATA_03_2009_2 2
DATA_LOAD DATA_03_2009 DATA_03_2009_1 0
DATA_LOAD DATA_04_2009 DATA_04_2009_3 0
DATA_LOAD DATA_04_2009 DATA_04_2009_2 0
DATA_LOAD DATA_04_2009 DATA_04_2009_1 1

So we can see that the data has been sub partitioned based on virtual column.

Another example of normal range partition using the virtual column:
SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
target_date DATE,
data_size NUMBER,
state VARCHAR2(20),
diff_date NUMBER GENERATED ALWAYS AS (target_date-load_date) VIRTUAL)
PARTITION BY RANGE (diff_date)
( PARTITION data_01_2009 VALUES LESS THAN (10) TABLESPACE DATA_01,
PARTITION data_02_2009 VALUES LESS THAN (20) TABLESPACE DATA_02,
PARTITION data_03_2009 VALUES LESS THAN (30) TABLESPACE DATA_03,
PARTITION data_04_2009 VALUES LESS THAN (MAXVALUE) TABLESPACE DATA_04
);
Table created.

To see what error we get while using PL\SQL function while creating virtual column and using it as paritioning key:

The Funtion:
SQL> create or replace function add_test (a in number,b in number)
return number
is
c number;
begin
c:=a+b;
RETURN c;
end;
/
Function created.

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
target_date DATE,
data_size NUMBER,
state VARCHAR2(20),
sum_up NUMBER GENERATED ALWAYS AS (add_test(source_id,data_size)) VIRTUAL)
PARTITION BY RANGE (sum_up)
( PARTITION data_01_2009 VALUES LESS THAN (10) TABLESPACE DATA_01,
PARTITION data_02_2009 VALUES LESS THAN (20) TABLESPACE DATA_02,
PARTITION data_03_2009 VALUES LESS THAN (30) TABLESPACE DATA_03,
PARTITION data_04_2009 VALUES LESS THAN (MAXVALUE) TABLESPACE DATA_04
);
sum_up NUMBER GENERATED ALWAYS AS (add_test(source_id,data_size)) VIRTUAL)
*
ERROR at line 8:
ORA-54021: Cannot use PL/SQL expressions in partitioning or subpartitioning columns

SQL> !oerr ora 54021
54021, 0000, "Cannot use PL/SQL expressions in partitioning or subpartitioning columns"
// *Cause: Attempted to partition a table on a virtual column that contained
// PL/SQL expressions.
// *Action: This is not supported.

Part I
Part II
Part III
Part IV
Part V
Part VI

Oracle Partitioning Types - VI (Multiple Column Partition)

2009-05-07T06:52:00.002+05:30

Multiple Column Partitioning
As the name suggests we can define the partition key using multiple column in the table.
So basically if there are two columns which you would like to use to define partitioning to facilitate related data to be stored in the same partition. There are be more than 2 columns for defining column keys. The idea is if using the first column key oracle is not able to ascertain which partition it has to go into, it used the second key and like-wise. The reason why oracle is not able to define the correct partition could be because the partition bound values are overlapping, between partitions.

SQL> CREATE TABLE DATA_LOAD
2 ( DATA_id NUMBER(6),
3 source_id NUMBER,
4 load_date DATE,
5 data_size NUMBER,
6 state VARCHAR2(20))
7 PARTITION BY RANGE (data_id,source_id)
8 ( PARTITION data_01_2009 VALUES LESS THAN (10,10) TABLESPACE DATA_01,
9 PARTITION data_02_2009 VALUES LESS THAN (10,20) TABLESPACE DATA_02,
10 PARTITION data_03_2009 VALUES LESS THAN (20,30) TABLESPACE DATA_03,
11 PARTITION data_04_2009 VALUES LESS THAN (30,40) TABLESPACE DATA_04
12 );
Table created.

SQL> select TABLE_NAME,COMPOSITE,PARTITION_NAME,SUBPARTITION_COUNT from user_tab_partitions where TABLE_NAME='DATA_LOAD';
TABLE_NAME COM PARTITION_NAME SUBPARTITION_COUNT
--------------- --- --------------- ------------------
DATA_LOAD NO DATA_01_2009 0
DATA_LOAD NO DATA_02_2009 0
DATA_LOAD NO DATA_03_2009 0
DATA_LOAD NO DATA_04_2009 0

SQL> insert into DATA_LOAD values (1,1,sysdate-20,10,'DELHI');
1 row created.

SQL> insert into DATA_LOAD values (1,15,sysdate-10,11,'KERELA');
1 row created.

SQL> insert into DATA_LOAD values (15,19,sysdate-10,11,'KERELA');
1 row created.

SQL> insert into DATA_LOAD values (25,25,sysdate-20,10,'DELHI');
1 row created.

SQL> insert into DATA_LOAD values (29,36,sysdate-20,10,'DELHI');
1 row created.

SQL> insert into DATA_LOAD values (10,12,sysdate-10,11,'KERELA');
1 row created.

SQL> commit;
Commit complete.

SQL> analyze table DATA_LOAD compute statistics;
Table analyzed.

SQL> select TABLE_NAME,COMPOSITE,PARTITION_NAME,NUM_ROWS from user_tab_partitions where TABLE_NAME='DATA_LOAD';
TABLE_NAME COM PARTITION_NAME NUM_ROWS
--------------- --- --------------- ----------
DATA_LOAD NO DATA_01_2009 2
DATA_LOAD NO DATA_02_2009 1
DATA_LOAD NO DATA_03_2009 1
DATA_LOAD NO DATA_04_2009 2

So you can see that the insert statement no. 2, had the second value as 15, but it went into partition 1, as it was table to decide using the first value. But the last insert statement had the first value as 10, which is the partition bound value for 1st and 2nd partition, so the row used the second value i.e. 12, and inserted the row in partition 2.

Part I
Part II
Part III
Part IV
Part V
Part VII

Oracle Partitioning Types - V (Reference Partition)

2009-05-06T10:41:00.004+05:30

Reference Partitioning:

As the name suggests, this has got to do with parent child relations between tables or rather primary key and foreign key relations between two tables. The table is partitioned by reference when the partition key value in a foreign key constraint of that table. The table with the primary key should be partition table, single level or composite partitioned. Fo example we will first use a single level partition table.

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
data_size NUMBER,
state VARCHAR2(20),
CONSTRAINT data_pk PRIMARY KEY(DATA_id))
PARTITION BY RANGE (load_date)
( PARTITION data_01_2009 VALUES LESS THAN (TO_DATE('01-FEB-2009','DD-MON-YYYY')) TABLESPACE DATA_01,
PARTITION data_02_2009 VALUES LESS THAN (TO_DATE('01-MAR-2009','DD-MON-YYYY')) TABLESPACE DATA_02,
PARTITION data_03_2009 VALUES LESS THAN (TO_DATE('01-APR-2009','DD-MON-YYYY')) TABLESPACE DATA_03,
PARTITION data_04_2009 VALUES LESS THAN (TO_DATE('01-MAY-2009','DD-MON-YYYY')) TABLESPACE DATA_04
);
Table created.

Now we will create a child table with for our DATA_LOAD table.

SQL> CREATE TABLE DATA_LOAD_FK
( DATA_id NUMBER(6),
source_name VARCHAR2(20),
timestamp DATE,
data_size NUMBER,
state VARCHAR2(20),
CONSTRAINT data_load_fk FOREIGN KEY(DATA_id) REFERENCES DATA_LOAD(DATA_id)
) PARTITION BY REFERENCE(data_load_fk);
) PARTITION BY REFERENCE(data_load_fk)
*
ERROR at line 8:
ORA-14652: reference partitioning foreign key is not supported

SQL> !oerr ora 14652
14652, 00000, "reference partitioning foreign key is not supported"
// *Cause: The specified partitioning foreign key was not supported
// for reference-partitioned tables. All columns of the
// partitioning foreign key must be constrained NOT NULL with
// enabled, validated, and not deferrable constraints. Furthermore,
// a virtual column cannot be part of the partitioning foreign key.
//* Action: Correct the statement to specify a supported
// partitioning foreign key.

I realsized that the referencing column should be not null as its referencing a primary key column. The error can we misleading.

SQL> CREATE TABLE DATA_LOAD_FK
( DATA_id NUMBER(6) NOT NULL,
source_name VARCHAR2(20),
timestamp DATE,
data_size NUMBER,
state VARCHAR2(20),
CONSTRAINT data_load_fk FOREIGN KEY(DATA_id) REFERENCES DATA_LOAD(DATA_id)
) PARTITION BY REFERENCE(data_load_fk);
Table created.

Lets check the partitions for this referenced partition table.

SQL> col TABLE_NAME format a15
SQL> col PARTITION_NAME format a15
SQL> col HIGH_VALUE format a80
SQL> set lines 300
SQL> select TABLE_NAME,COMPOSITE,PARTITION_NAME,HIGH_VALUE from user_tab_partitions where TABLE_NAME='DATA_LOAD';

TABLE_NAME COM PARTITION_NAME HIGH_VALUE
--------------- --- --------------- --------------------------------------------------------------------------------
DATA_LOAD NO DATA_01_2009 TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO DATA_02_2009 TO_DATE(' 2009-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO DATA_03_2009 TO_DATE(' 2009-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO DATA_04_2009 TO_DATE(' 2009-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SQL> select TABLE_NAME,COMPOSITE,PARTITION_NAME,HIGH_VALUE from user_tab_partitions where TABLE_NAME='DATA_LOAD_FK';

TABLE_NAME COM PARTITION_NAME HIGH_VALUE
--------------- --- --------------- --------------------------------------------------------------------------------
DATA_LOAD_FK NO DATA_01_2009
DATA_LOAD_FK NO DATA_02_2009
DATA_LOAD_FK NO DATA_03_2009
DATA_LOAD_FK NO DATA_04_2009

So we can see that four partitions were created with the same name as the primary table. And no high value is available as the data will be stored on the basis on the data stored in the primary table.
Lets test that also, by adding a row.

SQL> insert into DATA_LOAD values (1,1,sysdate-90,1,'DELHI');
1 row created.

SQL> commit;
Commit complete.

SQL> insert into DATA_LOAD_FK values (1,'TEST',sysdate-90,1,'DELHI');
1 row created.

SQL> commit;
Commit complete.

SQL> analyze table DATA_LOAD compute statistics;
Table analyzed.

SQL> analyze table DATA_LOAD_FK compute statistics;
Table analyzed.

SQL> select TABLE_NAME,COMPOSITE,PARTITION_NAME,NUM_ROWS from user_tab_partitions where TABLE_NAME='DATA_LOAD';
TABLE_NAME COM PARTITION_NAME NUM_ROWS
--------------- --- --------------- ----------
DATA_LOAD NO DATA_01_2009 0
DATA_LOAD NO DATA_02_2009 1
DATA_LOAD NO DATA_03_2009 0
DATA_LOAD NO DATA_04_2009 0

SQL> select TABLE_NAME,COMPOSITE,PARTITION_NAME,NUM_ROWS from user_tab_partitions where TABLE_NAME='DATA_LOAD_FK';
TABLE_NAME COM PARTITION_NAME NUM_ROWS
--------------- --- --------------- ----------
DATA_LOAD_FK NO DATA_01_2009 0
DATA_LOAD_FK NO DATA_02_2009 1
DATA_LOAD_FK NO DATA_03_2009 0
DATA_LOAD_FK NO DATA_04_2009 0

So we can see that the partition in which the row goes in the reference partitioned table is the same as the primary table.

Now let us see the affect of composite partition table as the primary table.

SQL> drop table DATA_LOAD_FK;
Table dropped.

SQL> drop table DATA_LOAD;
Table dropped.

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
target_date DATE,
data_size NUMBER,
state VARCHAR2(20),
CONSTRAINT data_pk PRIMARY KEY(DATA_id)
)
PARTITION BY RANGE (load_date)
SUBPARTITION BY RANGE (target_date)
( PARTITION data_01_2009 VALUES LESS THAN (TO_DATE('01-FEB-2009','DD-MON-YYYY')) TABLESPACE DATA_01
(SUBPARTITION data_01_2009_1 VALUES LESS THAN (TO_DATE('15-JAN-2009','DD-MON-YYYY')),
SUBPARTITION data_01_2009_2 VALUES LESS THAN (TO_DATE('25-JAN-2009','DD-MON-YYYY')),
SUBPARTITION data_01_2009_3 VALUES LESS THAN (MAXVALUE)
) ,
PARTITION data_02_2009 VALUES LESS THAN (TO_DATE('01-MAR-2009','DD-MON-YYYY')) TABLESPACE DATA_02
(SUBPARTITION data_02_2009_1 VALUES LESS THAN (TO_DATE('15-FEB-2009','DD-MON-YYYY')),
SUBPARTITION data_02_2009_2 VALUES LESS THAN (TO_DATE('25-FEB-2009','DD-MON-YYYY')),
SUBPARTITION data_02_2009_3 VALUES LESS THAN (MAXVALUE)
) ,
PARTITION data_03_2009 VALUES LESS THAN (TO_DATE('01-APR-2009','DD-MON-YYYY')) TABLESPACE DATA_03
(SUBPARTITION data_03_2009_1 VALUES LESS THAN (TO_DATE('15-MAR-2009','DD-MON-YYYY')),
SUBPARTITION data_03_2009_2 VALUES LESS THAN (TO_DATE('25-MAR-2009','DD-MON-YYYY')),
SUBPARTITION data_03_2009_3 VALUES LESS THAN (MAXVALUE)
) ,
PARTITION data_04_2009 VALUES LESS THAN (TO_DATE('01-MAY-2009','DD-MON-YYYY')) TABLESPACE DATA_04
(SUBPARTITION data_04_2009_1 VALUES LESS THAN (TO_DATE('15-APR-2009','DD-MON-YYYY')),
SUBPARTITION data_04_2009_2 VALUES LESS THAN (TO_DATE('25-APR-2009','DD-MON-YYYY')),
SUBPARTITION data_04_2009_3 VALUES LESS THAN (MAXVALUE)
)
);
Table created.

SQL> CREATE TABLE DATA_LOAD_FK
( DATA_id NUMBER(6) NOT NULL,
source_name VARCHAR2(20),
timestamp DATE,
data_size NUMBER,
state VARCHAR2(20),
CONSTRAINT data_load_fk FOREIGN KEY(DATA_id) REFERENCES DATA_LOAD(DATA_id)
) PARTITION BY REFERENCE(data_load_fk);
Table created.

SQL> select TABLE_NAME,COMPOSITE,PARTITION_NAME,SUBPARTITION_COUNT from user_tab_partitions where TABLE_NAME='DATA_LOAD';
TABLE_NAME COM PARTITION_NAME SUBPARTITION_COUNT
--------------- --- --------------- ------------------
DATA_LOAD YES DATA_01_2009 3
DATA_LOAD YES DATA_02_2009 3
DATA_LOAD YES DATA_03_2009 3
DATA_LOAD YES DATA_04_2009 3

SQL> select TABLE_NAME,COMPOSITE,PARTITION_NAME,SUBPARTITION_COUNT from user_tab_partitions where TABLE_NAME='DATA_LOAD_FK';
TABLE_NAME COM PARTITION_NAME SUBPARTITION_COUNT
--------------- --- --------------- ------------------
DATA_LOAD_FK NO DATA_03_2009_2 0
DATA_LOAD_FK NO DATA_03_2009_3 0
DATA_LOAD_FK NO DATA_04_2009_1 0
DATA_LOAD_FK NO DATA_04_2009_2 0
DATA_LOAD_FK NO DATA_01_2009_1 0
DATA_LOAD_FK NO DATA_01_2009_2 0
DATA_LOAD_FK NO DATA_01_2009_3 0
DATA_LOAD_FK NO DATA_02_2009_1 0
DATA_LOAD_FK NO DATA_02_2009_2 0
DATA_LOAD_FK NO DATA_02_2009_3 0
DATA_LOAD_FK NO DATA_03_2009_1 0

TABLE_NAME COM PARTITION_NAME SUBPARTITION_COUNT
--------------- --- --------------- ------------------
DATA_LOAD_FK NO DATA_04_2009_3 0
12 rows selected.

So you can see that the subpartitions have become individual partitions in the reference partition table.

Partitions limits cannot be specified in a reference partition table.

The partitions take the same name as the parent table partitions.

Part I
Part II
Part III
Part IV
Part VI
Part VII

Oracle Partitioning Types - IV (Interval Partition)

2009-05-05T13:35:00.007+05:30

1.) Interval-Partitioned Tables (11g)

Extract from Oracle Documentation
-----------------------------------------------------------------------------------------
The INTERVAL clause of the CREATE TABLE statement establishes interval partitioning for the table. You must specify at least one range partition using the PARTITION clause. The range partitioning key value determines the high value of the range partitions, which is called the transition point, and the database automatically creates interval partitions for data beyond that transition point. The lower boundary of every interval partition is the non-inclusive upper boundary of the previous range or interval partition.
-----------------------------------------------------------------------------------------

SQL> DROP TABLE DATA_LOAD;
Table dropped.

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
data_size NUMBER,
state VARCHAR2(20))
PARTITION BY RANGE (load_date)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
( PARTITION data_01_2006 VALUES LESS THAN (TO_DATE('01-01-2006','DD-MM-YYYY')) TABLESPACE DATA_01,
PARTITION data_01_2007 VALUES LESS THAN (TO_DATE('01-01-2007','DD-MM-YYYY')) TABLESPACE DATA_02,
PARTITION data_01_2008 VALUES LESS THAN (TO_DATE('01-01-2008','DD-MM-YYYY')) TABLESPACE DATA_03,
PARTITION data_01_2009 VALUES LESS THAN (TO_DATE('01-01-2009','DD-MM-YYYY')) TABLESPACE DATA_04
);

Table created.
Before data insert:
SQL> col TABLE_NAME format a15
SQL> col PARTITION_NAME format a15
SQL> col HIGH_VALUE format a80
SQL> set lines 300
SQL> select TABLE_NAME,COMPOSITE,PARTITION_NAME,HIGH_VALUE from user_tab_partitions where TABLE_NAME='DATA_LOAD';
TABLE_NAME COM PARTITION_NAME HIGH_VALUE
------------------------------------------------------------
DATA_LOAD NO DATA_01_2006 TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO DATA_01_2007 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO DATA_01_2008 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO DATA_01_2009 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

Now we insert data in this table:

insert into DATA_LOAD values (1,1,sysdate,1,'DELHI');
insert into DATA_LOAD values (2,2,sysdate-30,2,'KERELA');
insert into DATA_LOAD values (3,3,sysdate-300,3,'RAJASTHAN');
insert into DATA_LOAD values (4,4,sysdate-600,4,'PUNJAB');
insert into DATA_LOAD values (5,5,sysdate-60,5,'HARYANA');

SQL> select TABLE_NAME,COMPOSITE,PARTITION_NAME,HIGH_VALUE from user_tab_partitions where TABLE_NAME='DATA_LOAD';

TABLE_NAME COM PARTITION_NAME HIGH_VALUE
--------------- --- --------------- --------------------------------------------------------------------------------
DATA_LOAD NO DATA_01_2006 TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO DATA_01_2007 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO DATA_01_2008 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO DATA_01_2009 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO SYS_P81 TO_DATE(' 2009-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO SYS_P82 TO_DATE(' 2009-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO SYS_P83 TO_DATE(' 2009-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

Adding future rows:

SQL> insert into DATA_LOAD values (6,6,sysdate+300,6,'HARYANA');

1 row created.

SQL> commit;

Commit complete.

SQL> select TABLE_NAME,COMPOSITE,PARTITION_NAME,HIGH_VALUE from user_tab_partitions where TABLE_NAME='DATA_LOAD';

TABLE_NAME COM PARTITION_NAME HIGH_VALUE
--------------- --- --------------- --------------------------------------------------------------------------------
DATA_LOAD NO DATA_01_2006 TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO DATA_01_2007 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO DATA_01_2008 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO DATA_01_2009 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO SYS_P81 TO_DATE(' 2009-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO SYS_P82 TO_DATE(' 2009-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO SYS_P83 TO_DATE(' 2009-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO SYS_P84 TO_DATE(' 2010-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

8 rows selected.

So we see that the new INTERVAL PARTITIONS are being created for future dates and dates beyond the high value of the last range partition.

Part I
Part II
Part III
Part V
Part VI
Part VII

Oracle Partitioning Types - III (Composite Partitions)

2009-05-05T12:38:00.009+05:30

Composite Partitions:
1.) Composite Range-Range Partitioning
(Available in 11g. Oracle 10g would give error: ORA-14151)

SQL> DROP TABLE DATA_LOAD;
Table dropped.

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
target_date DATE,
data_size NUMBER,
state VARCHAR2(20)
)
PARTITION BY RANGE (load_date)
SUBPARTITION BY RANGE (target_date)
( PARTITION data_01_2009 VALUES LESS THAN (TO_DATE('01-FEB-2009','DD-MON-YYYY')) TABLESPACE DATA_01
(SUBPARTITION data_01_2009_1 VALUES LESS THAN (TO_DATE('15-JAN-2009','DD-MON-YYYY')),
SUBPARTITION data_01_2009_2 VALUES LESS THAN (TO_DATE('25-JAN-2009','DD-MON-YYYY')),
SUBPARTITION data_01_2009_3 VALUES LESS THAN (MAXVALUE)
) ,
PARTITION data_02_2009 VALUES LESS THAN (TO_DATE('01-MAR-2009','DD-MON-YYYY')) TABLESPACE DATA_02
(SUBPARTITION data_02_2009_1 VALUES LESS THAN (TO_DATE('15-FEB-2009','DD-MON-YYYY')),
SUBPARTITION data_02_2009_2 VALUES LESS THAN (TO_DATE('25-FEB-2009','DD-MON-YYYY')),
SUBPARTITION data_02_2009_3 VALUES LESS THAN (MAXVALUE)
) ,
PARTITION data_03_2009 VALUES LESS THAN (TO_DATE('01-APR-2009','DD-MON-YYYY')) TABLESPACE DATA_03
(SUBPARTITION data_03_2009_1 VALUES LESS THAN (TO_DATE('15-MAR-2009','DD-MON-YYYY')),
SUBPARTITION data_03_2009_2 VALUES LESS THAN (TO_DATE('25-MAR-2009','DD-MON-YYYY')),
SUBPARTITION data_03_2009_3 VALUES LESS THAN (MAXVALUE)
) ,
PARTITION data_04_2009 VALUES LESS THAN (TO_DATE('01-MAY-2009','DD-MON-YYYY')) TABLESPACE DATA_04
(SUBPARTITION data_04_2009_1 VALUES LESS THAN (TO_DATE('15-APR-2009','DD-MON-YYYY')),
SUBPARTITION data_04_2009_2 VALUES LESS THAN (TO_DATE('25-APR-2009','DD-MON-YYYY')),
SUBPARTITION data_04_2009_3 VALUES LESS THAN (MAXVALUE)
)
);
Table created.

2.) Composite Range-Hash Partitioning

SQL> DROP TABLE DATA_LOAD;
Table dropped.

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
target_date DATE,
data_size NUMBER,
state VARCHAR2(20))
PARTITION BY RANGE (load_date)
SUBPARTITION BY HASH (source_id)
SUBPARTITIONS 4 STORE IN (DATA_01, DATA_02, DATA_03, DATA_04)
( PARTITION data_01_2009 VALUES LESS THAN (TO_DATE('01-FEB-2009','DD-MON-YYYY')) TABLESPACE DATA_01,
PARTITION data_02_2009 VALUES LESS THAN (TO_DATE('01-MAR-2009','DD-MON-YYYY')) TABLESPACE DATA_02,
PARTITION data_03_2009 VALUES LESS THAN (TO_DATE('01-APR-2009','DD-MON-YYYY')) TABLESPACE DATA_03,
PARTITION data_04_2009 VALUES LESS THAN (TO_DATE('01-MAY-2009','DD-MON-YYYY')) TABLESPACE DATA_04
);
Table created.

3.) Composite Range-List Partitioning

SQL> DROP TABLE DATA_LOAD;
Table dropped.

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
target_date DATE,
data_size NUMBER,
state VARCHAR2(20))
PARTITION BY RANGE (load_date)
SUBPARTITION BY LIST (state)
SUBPARTITION TEMPLATE (
SUBPARTITION NORTH_01 VALUES ('DELHI', 'JAMMU KASHMIR', 'PUNJAB', 'HARYANA') TABLESPACE DATA_01,
SUBPARTITION WEST_01 VALUES ('RAJASTHAN', 'GUJRAT', 'MAHARASTRA') TABLESPACE DATA_02,
SUBPARTITION SOUTH_01 VALUES ('TAMIL NADU', 'KERELA', 'KARNATAKA') TABLESPACE DATA_03,
SUBPARTITION EAST_01 VALUES ('ASSAM', 'WEST BENGAL', 'NAGALAND') TABLESPACE DATA_04,
SUBPARTITION NO_REGION VALUES (NULL) TABLESPACE USERS,
SUBPARTITION OTHER VALUES (DEFAULT) TABLESPACE USERS
)
( PARTITION data_01_2009 VALUES LESS THAN (TO_DATE('01-FEB-2009','DD-MON-YYYY')) ,
PARTITION data_02_2009 VALUES LESS THAN (TO_DATE('01-MAR-2009','DD-MON-YYYY')) ,
PARTITION data_03_2009 VALUES LESS THAN (TO_DATE('01-APR-2009','DD-MON-YYYY')) ,
PARTITION data_04_2009 VALUES LESS THAN (TO_DATE('01-MAY-2009','DD-MON-YYYY'))
);
Table created.

4.) Composite List-Range Partitioning

SQL> DROP TABLE DATA_LOAD;
Table dropped.

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
target_date DATE,
data_size NUMBER,
state VARCHAR2(20))
PARTITION BY LIST (state)
SUBPARTITION BY RANGE (load_date)
(
PARTITION NORTH_01 VALUES ('DELHI', 'JAMMU KASHMIR', 'PUNJAB', 'HARYANA')
( SUBPARTITION n_data_01_2009 VALUES LESS THAN (TO_DATE('01-FEB-2009','DD-MON-YYYY')) TABLESPACE DATA_01,
SUBPARTITION n_data_02_2009 VALUES LESS THAN (TO_DATE('01-MAR-2009','DD-MON-YYYY')) TABLESPACE DATA_02,
SUBPARTITION n_data_03_2009 VALUES LESS THAN (TO_DATE('01-APR-2009','DD-MON-YYYY')) TABLESPACE DATA_03,
SUBPARTITION n_data_04_2009 VALUES LESS THAN (TO_DATE('01-MAY-2009','DD-MON-YYYY')) TABLESPACE DATA_04
),
PARTITION WEST_01 VALUES ('RAJASTHAN', 'GUJRAT', 'MAHARASTRA')
( SUBPARTITION w_data_01_2009 VALUES LESS THAN (TO_DATE('01-FEB-2009','DD-MON-YYYY')) TABLESPACE DATA_01,
SUBPARTITION w_data_02_2009 VALUES LESS THAN (TO_DATE('01-MAR-2009','DD-MON-YYYY')) TABLESPACE DATA_02,
SUBPARTITION w_data_03_2009 VALUES LESS THAN (TO_DATE('01-APR-2009','DD-MON-YYYY')) TABLESPACE DATA_03,
SUBPARTITION w_data_04_2009 VALUES LESS THAN (TO_DATE('01-MAY-2009','DD-MON-YYYY')) TABLESPACE DATA_04
),
PARTITION SOUTH_01 VALUES ('TAMIL NADU', 'KERELA', 'KARNATAKA')
( SUBPARTITION s_data_01_2009 VALUES LESS THAN (TO_DATE('01-FEB-2009','DD-MON-YYYY')) TABLESPACE DATA_01,
SUBPARTITION s_data_02_2009 VALUES LESS THAN (TO_DATE('01-MAR-2009','DD-MON-YYYY')) TABLESPACE DATA_02,
SUBPARTITION s_data_03_2009 VALUES LESS THAN (TO_DATE('01-APR-2009','DD-MON-YYYY')) TABLESPACE DATA_03,
SUBPARTITION s_data_04_2009 VALUES LESS THAN (TO_DATE('01-MAY-2009','DD-MON-YYYY')) TABLESPACE DATA_04
),
PARTITION EAST_01 VALUES ('ASSAM', 'WEST BENGAL', 'NAGALAND')
( SUBPARTITION e_data_01_2009 VALUES LESS THAN (TO_DATE('01-FEB-2009','DD-MON-YYYY')) TABLESPACE DATA_01,
SUBPARTITION e_data_02_2009 VALUES LESS THAN (TO_DATE('01-MAR-2009','DD-MON-YYYY')) TABLESPACE DATA_02,
SUBPARTITION e_data_03_2009 VALUES LESS THAN (TO_DATE('01-APR-2009','DD-MON-YYYY')) TABLESPACE DATA_03,
SUBPARTITION e_data_04_2009 VALUES LESS THAN (TO_DATE('01-MAY-2009','DD-MON-YYYY')) TABLESPACE DATA_04
),
PARTITION NO_REGION VALUES (NULL)
( SUBPARTITION no_data_01_2009 VALUES LESS THAN (TO_DATE('01-FEB-2009','DD-MON-YYYY')) TABLESPACE DATA_01,
SUBPARTITION no_data_02_2009 VALUES LESS THAN (TO_DATE('01-MAR-2009','DD-MON-YYYY')) TABLESPACE DATA_02,
SUBPARTITION no_data_03_2009 VALUES LESS THAN (TO_DATE('01-APR-2009','DD-MON-YYYY')) TABLESPACE DATA_03,
SUBPARTITION no_data_04_2009 VALUES LESS THAN (TO_DATE('01-MAY-2009','DD-MON-YYYY')) TABLESPACE DATA_04
),
PARTITION OTHER VALUES (DEFAULT)
( SUBPARTITION o_data_01_2009 VALUES LESS THAN (TO_DATE('01-FEB-2009','DD-MON-YYYY')) TABLESPACE DATA_01,
SUBPARTITION o_data_02_2009 VALUES LESS THAN (TO_DATE('01-MAR-2009','DD-MON-YYYY')) TABLESPACE DATA_02,
SUBPARTITION o_data_03_2009 VALUES LESS THAN (TO_DATE('01-APR-2009','DD-MON-YYYY')) TABLESPACE DATA_03,
SUBPARTITION o_data_04_2009 VALUES LESS THAN (TO_DATE('01-MAY-2009','DD-MON-YYYY')) TABLESPACE DATA_04
)
);
Table created.

5.) Composite List-Hash Partitioning

SQL> DROP TABLE DATA_LOAD;
Table dropped.

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
target_date DATE,
data_size NUMBER,
state VARCHAR2(20))
PARTITION BY LIST (state)
SUBPARTITION BY HASH (load_date) SUBPARTITIONS 4
(PARTITION NORTH_01 VALUES ('DELHI', 'JAMMU KASHMIR', 'PUNJAB', 'HARYANA'),
PARTITION WEST_01 VALUES ('RAJASTHAN', 'GUJRAT', 'MAHARASTRA'),
PARTITION SOUTH_01 VALUES ('TAMIL NADU', 'KERELA', 'KARNATAKA'),
PARTITION EAST_01 VALUES ('ASSAM', 'WEST BENGAL', 'NAGALAND'),
PARTITION NO_REGION VALUES (NULL),
PARTITION OTHER VALUES (DEFAULT)
);
Table created.

6.) Composite List-List Partitioning

SQL> DROP TABLE DATA_LOAD;
Table dropped.

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
target_date DATE,
data_size NUMBER,
status VARCHAR2(1),
state VARCHAR2(20))
PARTITION BY LIST (state)
SUBPARTITION BY LIST (status)
(
PARTITION NORTH_01 VALUES ('DELHI', 'JAMMU KASHMIR', 'PUNJAB', 'HARYANA')
(SUBPARTITION a_NORTH_01 VALUES ('A'),
SUBPARTITION b_NORTH_01 VALUES ('B'),
SUBPARTITION c_NORTH_01 VALUES ('C')
),
PARTITION WEST_01 VALUES ('RAJASTHAN', 'GUJRAT', 'MAHARASTRA')
(SUBPARTITION a_WEST_01 VALUES ('A'),
SUBPARTITION b_WEST_01 VALUES ('B'),
SUBPARTITION c_WEST_01 VALUES ('C')
),
PARTITION SOUTH_01 VALUES ('TAMIL NADU', 'KERELA', 'KARNATAKA')
(SUBPARTITION a_SOUTH_01 VALUES ('A'),
SUBPARTITION b_SOUTH_01 VALUES ('B'),
SUBPARTITION c_SOUTH_01 VALUES ('C')
),
PARTITION EAST_01 VALUES ('ASSAM', 'WEST BENGAL', 'NAGALAND')
(SUBPARTITION a_EAST_01 VALUES ('A'),
SUBPARTITION b_EAST_01 VALUES ('B'),
SUBPARTITION c_EAST_01 VALUES ('C')
),
PARTITION NO_REGION VALUES (NULL)
(SUBPARTITION a_NO_REGION_01 VALUES ('A'),
SUBPARTITION b_NO_REGION_01 VALUES ('B'),
SUBPARTITION c_NO_REGION_01 VALUES ('C')
),
PARTITION OTHER VALUES (DEFAULT)
(SUBPARTITION a_OTHER_01 VALUES ('A'),
SUBPARTITION b_OTHER_01 VALUES ('B'),
SUBPARTITION c_OTHER_01 VALUES ('C')
)
);
Table created.

Part I
Part II
Part IV
Part V
Part VI
Part VII

Oracle Partitioning Types - II (Single Level Partitions)

2009-05-05T12:26:00.009+05:30

Syntax examples on how to create partition tables:
1.) Range Partitioning:

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
data_size NUMBER,
state VARCHAR2(20))
PARTITION BY RANGE (load_date)
( PARTITION data_01_2009 VALUES LESS THAN (TO_DATE('01-FEB-2009','DD-MON-YYYY')) TABLESPACE DATA_01,
PARTITION data_02_2009 VALUES LESS THAN (TO_DATE('01-MAR-2009','DD-MON-YYYY')) TABLESPACE DATA_02,
PARTITION data_03_2009 VALUES LESS THAN (TO_DATE('01-APR-2009','DD-MON-YYYY')) TABLESPACE DATA_03,
PARTITION data_04_2009 VALUES LESS THAN (TO_DATE('01-MAY-2009','DD-MON-YYYY')) TABLESPACE DATA_04
);

Table created.

This create Date range based partition each for the month of JAN, FEB, MAR & APR 2009.

2.) Hash Partitioning

Oracle apply's a hashing algorithm to the column selected on partitions, we can provide the number of partitions we want or also the names of partitions.

SQL> DROP TABLE DATA_LOAD;
Table dropped.

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
data_size NUMBER,
state VARCHAR2(20))
PARTITION BY HASH (source_id)
( PARTITION data_01_2009 TABLESPACE DATA_01,
PARTITION data_02_2009 TABLESPACE DATA_02,
PARTITION data_03_2009 TABLESPACE DATA_03,
PARTITION data_04_2009 TABLESPACE DATA_04
);
Table created.

Alternatively,
SQL> DROP TABLE DATA_LOAD;
Table dropped.

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
data_size NUMBER,
state VARCHAR2(20))
PARTITION BY HASH (source_id) PARTITIONS 4;
Table created.

3.) List Partitioning

SQL> DROP TABLE DATA_LOAD;
Table dropped.

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
data_size NUMBER,
state VARCHAR2(20))
PARTITION BY LIST (state)
(PARTITION NORTH_01 VALUES ('DELHI', 'JAMMU KASHMIR', 'PUNJAB', 'HARYANA'),
PARTITION WEST_01 VALUES ('RAJASTHAN', 'GUJRAT', 'MAHARASTRA'),
PARTITION SOUTH_01 VALUES ('TAMIL NADU', 'KERELA', 'KARNATAKA'),
PARTITION EAST_01 VALUES ('ASSAM', 'WEST BENGAL', 'NAGALAND'),
PARTITION NO_REGION VALUES (NULL),
PARTITION OTHER VALUES (DEFAULT)
);
Table created.

Part I
Part III
Part IV
Part V
Part VI
Part VII

Oracle Partitioning Types - I (Index)

2009-05-05T12:23:00.019+05:30

Types of Oracle partitions:

Single Level Partitions:

Range Partitioning

Hash Partitioning

List Partitioning

Composite Partitions:

Composite Range-Range Partitioning

Composite Range-Hash Partitioning

Composite Range-List Partitioning

Composite List-Range Partitioning

Composite List-Hash Partitioning

Composite List-List Partitioning

Enhancements in 11g:

Interval Partitioning

Reference Partitioning

Multiple Column Partitioning

Virtual column-based Partitioning

Partition Advisor

Reasons for Partitioning od Data:
1.) Large amount of data in a table, oracle suggests that table size of over 2G are good candidates for partitioning.
2.) Historical data and current data reside in the table at the same time.
3.) When the disk space is limited and you want to span a table across different storage types.
4.) Partition is good for inproving performance, as the data related to different regions, dates etc can be stored seperately and can be queries faster. For example, data for last 2-3 yrs reside in the same table, older data is moreover read-only and the current data is queries more than the other, always good to have partitions like monthly etc. another example would be data of different states of a country reside in the same table, they can be seperated stored state-wise.

For details on the syntax of these partitioning types:
Single Level Partitions, Part II
Composite Partitions, Part III
Interval Partitions, Part IV
Reference Partitions, Part V
Multicolumn Partitioning, Part VI
Virtual Column Based Partitioning, Part VII

How TO: Setup Multi-Master\Advanced Replication with Oracle 10g, 11g

2009-04-15T12:02:00.009+05:30

As per the oracle documentation there are 3 main processes involved in replication between masters:
1.) Replication Administration: Create replication groups, defines replication objects and other replication administrative functions.
2.) Replication Propagation: Makes sure that the changes are sent from the database in which they have happened to all the other masters.
3.) Replication Receiver: Applies all the received changes to the destination master databases.

These three functions can be assigned to 3 different schema in the oracle database, or can be done by one schema.
I am going to use the same schema to perform all the above functions. The name of the schema, used most popularly is REPADMIN.

Init parameter JOB_QUEUE_PROCESSES should have a non zero value.

Enable GLOBAL_NAMES
SQL> show parameter global_names
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean FALSE
SQL> alter system set global_names=TRUE;

(Master 1)
SQL> select * from global_name;
GLOBAL_NAME
-----------------
TEST.ORACLE.COM

(Master 2)
SQL> select * from global_name;
GLOBAL_NAME
----------------
ORCL.ORACLE.COM

Incase you want to change the global name:
alter database rename global_name to ;

In my case the name of the database are test (Linux) and orcl (windows) and the setup is replication between 2 databases, one is a Linux installation and the other is windows.

In a multi-master replication setup, one DB acts as the master definition database. This database is used for running administration and maintenance tasks. In our case we will use TEST DB as the master definition database.

Step 1:
Create REPADMIN schema in all the master databases.

SQL> CREATE USER REPADMIN IDENTIFIED BY REPADMIN DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;

User created.

Step 2:
Granting admin permissions to REPADMIN schema. DBMS_REPCAT_ADMIN package is used to perform any administrative activity related to replication.

SQL> EXEC DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA('REPADMIN');

PL/SQL procedure successfully completed.

Permission to allow REPADMIN to propogate changes.

SQL> EXEC DBMS_DEFER_SYS.REGISTER_PROPAGATOR('REPADMIN');

PL/SQL procedure successfully completed.

Permission to allow REPADMIN to be a receiver.

SQL> EXEC DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (USERNAME => 'REPADMIN', PRIVILEGE_TYPE => 'RECEIVER', LIST_OF_GNAMES => NULL);

PL/SQL procedure successfully completed.

Step 3:
Create database links between the various master sites. The name of the DB Link should be same as the global name of the target database.
Need to create a public database link first:

(Master 1)
SQL> CREATE PUBLIC DATABASE LINK ORCL.ORACLE.COM USING 'ORCL';

Database link created.
(Master 2)
SQL> CREATE PUBLIC DATABASE LINK TEST.ORACLE.COM USING 'TEST';

Database link created.

Now create a private database link in REPADMIN schema.

(Master 1)
SQL> conn repadmin/repadmin
Connected.
SQL> CREATE DATABASE LINK ORCL.ORACLE.COM CONNECT TO REPADMIN IDENTIFIED BY repadmin USING 'ORCL';

Database link created.

(Master 2)
SQL> conn repadmin/repadmin
Connected.
SQL> CREATE DATABASE LINK TEST.ORACLE.COM CONNECT TO REPADMIN IDENTIFIED BY repadmin USING 'TEST';

Database link created.

Step 4:
Defining or creating replication objects.
I am using the HR schema and the tables present under it for replication purpose.
SQL> SELECT TABLE_NAME FROM USER_TABLES;

TABLE_NAME
------------------------------
LOCATIONS
BLOB_TEST
EMPLOYEES
REGIONS
JOBS
JOB_HISTORY
DEPARTMENTS

7 rows selected.

Step 5:
Creation of replication group.
We need to create this group in the master definition DB or test DB in our case as REPADMIN.

SQL> CONN REPADMIN/REPADMIN
Connected.
SQL> EXEC DBMS_REPCAT.CREATE_MASTER_REPGROUP('REP2');

PL/SQL procedure successfully completed.

You can create multiple replication groups depending on our requirement, for different objects or set of objects.

Step 6:
Add objects/tables to the replication group REP2. Need to be executed only at the master definition DB (test).

SQL> EXEC DBMS_REPCAT.CREATE_MASTER_REPOBJECT('HR', 'BLOB_TEST', 'TABLE', GNAME=>'REP2');
EXEC DBMS_REPCAT.CREATE_MASTER_REPOBJECT('HR', 'DEPARTMENTS', 'TABLE', GNAME=>'REP2');
EXEC DBMS_REPCAT.CREATE_MASTER_REPOBJECT('HR', 'EMPLOYEES', 'TABLE', GNAME=>'REP2');
EXEC DBMS_REPCAT.CREATE_MASTER_REPOBJECT('HR', 'JOBS', 'TABLE', GNAME=>'REP2');
EXEC DBMS_REPCAT.CREATE_MASTER_REPOBJECT('HR', 'JOB_HISTORY', 'TABLE', GNAME=>'REP2');
EXEC DBMS_REPCAT.CREATE_MASTER_REPOBJECT('HR', 'LOCATIONS', 'TABLE', GNAME=>'REP2');
EXEC DBMS_REPCAT.CREATE_MASTER_REPOBJECT('HR', 'REGIONS', 'TABLE', GNAME=>'REP2');
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.

Step 7:
Define the other master database in the replication setup at the master definition database i.e. add ORCL database as the other master in the replication.
Need to be executed only at the master definition DB (test). Run this as REPADMIN.

SQL> EXEC DBMS_REPCAT.ADD_MASTER_DATABASE (GNAME=> 'REP2', MASTER=>'ORCL.ORACLE.COM',USE_EXISTING_OBJECTS => TRUE,COPY_ROWS=> FALSE, PROPAGATION_MODE=> 'ASYNCHRONOUS');

PL/SQL procedure successfully completed.

Step 8:
Setup replication support for the tables marked for replication in the Step 6.
Need to be executed only at the master definition DB (test). Run this as REPADMIN.

SQL> show user
USER is "REPADMIN"
SQL> exec dbms_repcat.generate_replication_support('HR','BLOB_TEST','TABLE');
exec dbms_repcat.generate_replication_support('HR','DEPARTMENTS','TABLE');
exec dbms_repcat.generate_replication_support('HR','EMPLOYEES','TABLE');
exec dbms_repcat.generate_replication_support('HR','JOBS','TABLE');
exec dbms_repcat.generate_replication_support('HR','JOB_HISTORY','TABLE');
exec dbms_repcat.generate_replication_support('HR','LOCATIONS','TABLE');
exec dbms_repcat.generate_replication_support('HR','REGIONS','TABLE');

PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.

Step 9:
Enable replication between these databases.
Need to be executed only at the master definition DB (test). Run this as REPADMIN.

SQL> EXEC DBMS_REPCAT.RESUME_MASTER_ACTIVITY('REP2');
PL/SQL procedure successfully completed.

Step 10:
Start/create the data push job from all the master databases.
Define the time delay in the replication, what oracle needs to do when it encounters an error, do you need trace files for error, and the degree of parallelism.

(Master 1)
SQL> EXEC DBMS_DEFER_SYS.SCHEDULE_PUSH(DESTINATION =>'ORCL.ORACLE.COM', INTERVAL=>'SYSDATE + 1/60/24',NEXT_DATE=> SYSDATE,STOP_ON_ERROR => FALSE, DELAY_SECONDS => 0, WRITE_TRACE=>TRUE, PARALLELISM => 2);
PL/SQL procedure successfully completed.

(Master 2)
SQL> EXEC DBMS_DEFER_SYS.SCHEDULE_PUSH(DESTINATION =>'TEST.ORACLE.COM', INTERVAL=>'SYSDATE + 1/60/24',NEXT_DATE=> SYSDATE,STOP_ON_ERROR => FALSE, DELAY_SECONDS => 0, WRITE_TRACE=>TRUE, PARALLELISM => 2);
PL/SQL procedure successfully completed.

Step 11:
Define purge job, this purges the data\transaction which has been replicated. Need to run on all the masters.

SQL> EXEC DBMS_DEFER_SYS.SCHEDULE_PURGE(NEXT_DATE => SYSDATE,INTERVAL=>'SYSDATE + 1/24',DELAY_SECONDS=>0, ROLLBACK_SEGMENT => '');
PL/SQL procedure successfully completed.

Step 12:
Do checks:
Check for DBMS_JOBS in both masters:
SQL> @jobs
Job User Schema Last Date Next Date B F What
----------- ---------- ---------- ---------------- ---------------- ---- - -----------------------------------------------------------------
0: 66 REPADMIN REPADMIN 15.04.2009 09:18 15.04.2009 09:28 N 0 dbms_repcat.do_deferred_repcat_admin('"REP2"', FALSE);
0: 67 REPADMIN REPADMIN 15.04.2009 09:25 15.04.2009 09:26 N 0 declare rc binary_integer; begin rc := sys.dbms_defer_sys.pu
0: 68 REPADMIN REPADMIN 15.04.2009 09:25 15.04.2009 09:26 N 0 declare rc binary_integer; begin rc := sys.dbms_defer_sys.pu

Queries that can we used to check for the replication setup:

SELECT SNAME,ONAME,STATUS,GENERATION_STATUS FROM ALL_REPOBJECT; --> Status and other details of the replicating objects.
COL SOURCE FORMAT A10
COL SNAME FORMAT A10
COL ONAME FORMAT A10
SELECT ID,SOURCE,STATUS,TIMESTAMP,SNAME,ONAME,ERRNUM,MESSAGE FROM DBA_REPCATLOG; --> Status, any error details of the replication objects.
SELECT * FROM DBA_REPSITES; --> Master and Slaves site details.
SELECT * FROM DBA_REPSCHEMA; --> Schema's being replicated.
SELECT * FROM DBA_REPGROUP; --> The details of the replication groups.
SELECT * FROM DBA_REPGENERATED;
SELECT * FROM DBA_REPDDL; --> DDL of the changes that need to be sent to the other masters and slaves.
SELECT * FROM DBA_REPCONFLICT; --> Details of any replication conflicts.

Test to see if the replication is working fine:
(Master 1)
SQL> SELECT * FROM REGIONS;
REGION_ID REGION_NAME
------------ -------------------------
5 Antartica
6 Artic
1 Europe
2 Americas
3 Asia
4 Middle East and Africa

SQL> INSERT INTO REGIONS VALUES (7,'CHINA');
1 row created.

SQL> commit;
Commit complete.

SQL> SELECT * FROM REGIONS@ORCL.ORACLE.COM;
REGION_ID REGION_NAME
------------ -------------------------
5 Antartica
6 Artic
7 China
1 Europe
2 Americas
3 Asia
4 Middle East and Africa

You can see that the replicaiton works fine from one master to other. Lets try the other way around also.
(Master 2)
SQL> SELECT * FROM REGIONS;
REGION_ID REGION_NAME
---------- -------------------------
5 Antartica
6 Artic
7 China
1 Europe
2 Americas
3 Asia
4 Middle East and Africa
7 rows selected.

SQL> INSERT INTO REGIONS VALUES (8,'US');
1 row created.

SQL> commit;
Commit complete.

SQL> SELECT * FROM REGIONS@TEST.ORACLE.COM;
REGION_ID REGION_NAME
---------- -------------------------
5 Antartica
6 Artic
7 China
8 US
1 Europe
2 Americas
3 Asia
4 Middle East and Africa
8 rows selected.

While trying to setup this replication setup, I came across some other weblinks also. Please refer to them too as they have a lot of great information.
Setup Multi-master Replication with Oracle

Multimaster replication in Oracle

Advanced Replication FAQ

What is Voting Disk & Split Brain Syndrome in RAC

2009-04-01T09:33:00.003+05:30

Voting Disk

Oracle Clusterware uses the voting disk to determine which instances are members of a cluster. The voting disk must reside on a shared disk. Basically all nodes in the RAC cluster register their heart-beat information on thes voting disks. The number decides the number of active nodes in the RAC cluster. These are also used for checking the availability of instances in RAC and remove the unavailable nodes out of the cluster. It helps in preventing split-brain condition and keeps database information intact. The split brain syndrome and its affects and how it has been managed in oracle is mentioned below.
For high availability, Oracle recommends that you have a minimum of three voting disks. If you configure a single voting disk, then you should use external mirroring to provide redundancy. You can have up to 32 voting disks in your cluster. What I could understand about the odd value of the number of voting disks is that a noe should see maximun number of voting disk to continue to function, so with 2, if it can see only 1, its not the maximum value but a half value of voting disk. I am still trying to search more on this concept.

Split Brain Syndrome:

In a Oracle RAC environment all the instances/servers communicate with each other using high-speed interconnects on the private network. This private network interface or interconnect are redundant and are only used for inter-instance oracle data block transfers. Now talking about split-brain concept with respect to oracle rac systems, it occurs when the instance members in a RAC fail to ping/connect to each other via this private interconnect, but the servers are all pysically up and running and the database instance on each of these servers is also running. These individual nodes are running fine and can conceptually accept user connections and work independently. So basically due to lack of commincation the instance thinks that the other instance that it is not able to connect is down and it needs to do something about the situation. The problem is if we leave these instance running, the sane block might get read, updated in these individual instances and there would be data integrity issue, as the blocks changed in one instance, will not be locked and could be over-written by another instance. Oracle has efficiently implemented check for the split brain syndrome.

What does RAC do incase node becomes inactive:

In RAC if any node becomes inactive, or if other nodes are unable to ping/connect to a node in the RAC, then the node which first detects that one of the node is not accessible, it will evict that node from the RAC group. e.g. there are 4 nodes in a rac instance, and node 3 becomes unavailable, and node 1 tries to connect to node 3 and finds it not responding, then node 1 will evict node 3 out of the RAC groups and will leave only Node1, Node2 & Node4 in the RAC group to continue functioning.
The split brain concepts can become more complicated in large RAC setups. For example there are 10 RAC nodes in a cluster. And say 4 nodes are not able to communicate with the other 6. So there are 2 groups formed in this 10 node RAC cluster ( one group of 4 nodes and other of 6 nodes). Now the nodes will quickly try to affirm their membership by locking controlfile, then the node that lock the controlfile will try to check the votes of the other nodes. The group with the most number of active nodes gets the preference and the others are evicted. Moreover, I have seen this node eviction issue with only 1 node getting evicted and the rest function fine, so I cannot really testify that if thats how it work by experience, but this is the theory behind it.
When we see that the node is evicted, usually oracle rac will reboot that node and try to do a cluster reconfiguration to include back the evicted node.
You will see oracle error: ORA-29740, when there is a node eviction in RAC. There are many reasons for a node eviction like heart beat not received by the controlfile, unable to communicate with the clusterware etc.
A good metalink note on understanding node eviction and how to address is Note ID: 219361.1

The CSS (Cluster Synchronization Service) daemon in the clusterware maintains the heart beat to the voting disk.

How To: Enable Password Security/Complexity Check

2009-03-27T06:48:00.006+05:30

We need to make the password of Oracle user more robust and make sure that the password are complex enough so that the chance of breaking them is minimized.

Oracle provides a script called UTLPWDMG.sql located in $ORACLE_HOME/rdbms/admin which can be used to manage the complexity of database password.

This script basically creates to functions verify_function_11G (new in 11g) and verify_function (older version).

Lets see what verify_function_11G function does, its input parameters are username, new password and old password.

Checks that this scripts enforces on passwords:

Makes sure that the length of the password is more than 8.
Makes sure that the username and password are not same e.g. username is SCOTT and the password is SCOTT. This check involves that the password can be in any case (UPPER CASE/LOWER CASE/Combination of Both) of the username, it will still reject the password. It basically compares lower(username)=lower(new_password).
Makes sure that the password is not the reverse of the username. Checks for the case also, same as above.
Makes sure that the password is not the same as the database name.
Checks for the simplicity of the password, but I think this check is not so robust, as oracle just checks it against some pre-defined keywords: ('welcome1', 'database1', 'account1', 'user1234', 'password1', 'oracle123', 'computer1', 'abcdefg1', 'change_on_install'). Can't complain also, as its not really possible to compare it with the dictionary. These are some very common 8 letter passwords, people use.
Makes sure that the password is not "oracle". This is a seperate check in the function, it could have been part of the above check.
Makes sure that the combination is alpha numeric, basically atleast 1 character and 1 number.
Makes sure that the new password differs from the new password by atleast 3 characters.

I believe these are great checks to make sure that the new password are complex enough. we can also add more check if required, like add more keywords we dont want as password, check for upper case and lower case characters, and enforce that the password should have Upper case characters, make sure that teh password also has special characters like !,@,#,$,% etc etc.

After this function get compiled, the script alters the default profile to start using this function:
ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 180
PASSWORD_GRACE_TIME 7
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LOCK_TIME 1
PASSWORD_VERIFY_FUNCTION verify_function_11G;

PASSWORD_LIFE_TIME 180 --> Days after which you need to change password.
PASSWORD_GRACE_TIME 7 --> The time in days allowed after password expire, grace time.
PASSWORD_REUSE_TIME UNLIMITED --> This parameter makes sure that you cannot use the same password again.
PASSWORD_REUSE_MAX UNLIMITED --> This parameter makes sure that you cannot use the same password again.
FAILED_LOGIN_ATTEMPTS 10 --> Allowed password retries in case of entering wrong password.
PASSWORD_LOCK_TIME 1 --> Lock the user for 1 Day if it makes 10 failed password attempts.
PASSWORD_VERIFY_FUNCTION verify_function_11G; --> Set the password managment function.

Now talking about verify_function, or the older password management function provided by oracle:

Checks that this scripts enforces on passwords:

Makes sure that the username and password are not same e.g. username is SCOTT and the password is SCOTT. This check involves that the password can be in any case (UPPER CASE/LOWER CASE/Combination of Both) of the username, it will still reject the password. It basically compares lower(username)=lower(new_password).
Makes sure that the length of the password is more than 4.
Checks for the simplicity of the password, oracle just checks it against some pre-defined keywords:('welcome', 'database', 'account', 'user', 'password', 'oracle', 'computer', 'abcd')
Makes sure that the combination is alpha numeric with special character, basically atleast 1 character, 1 number and 1 special character. Special characters allowed: '!"#$%&()``*+,-/:;<=>?_'
Makes sure that the new password differs from the new password by atleast 3 characters.

ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 60
PASSWORD_GRACE_TIME 10
PASSWORD_REUSE_TIME 60
PASSWORD_REUSE_MAX 10
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 1/1440
PASSWORD_VERIFY_FUNCTION verify_function;

PASSWORD_REUSE_TIME 60 --> Can you the same password after 60 days.
PASSWORD_REUSE_MAX 10 --> Can use the same password after using 10 different passwords after 60 days.

After running the above script the default profile is set to verify_function_11G. The alter profile with verify_function is commented.
But incase you want to revert to verify_function, you can do an alter profile as above.

How to: Move/Convert Database from Linux to Windows

2009-03-24T12:17:00.004+05:30

There are two basic methods to achieve this.

Method 1
========
Steps 1:
Check the ENDIAN format of the platforms. Both Windows and linux should have the same format.
In our case we are moving from Linux 32-bit to Windows 32-bit:
col PLATFORM_NAME format a40
select PLATFORM_NAME, ENDIAN_FORMAT from V$TRANSPORTABLE_PLATFORM;
PLATFORM_NAME ENDIAN_FORMAT
---------------------------------------- --------------
Microsoft Windows IA (32-bit) Little
Linux IA (32-bit) Little
Linux IA (64-bit) Little
Microsoft Windows IA (64-bit) Little

Step 2:
Check if the database can be transported. We need to use DBMS_TDB.CHECK_DB, to check if our database can be
transported to the target OS, in the way it is currently.
Need to start the database in READ ONLY mode:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 422670336 bytes
Fixed Size 1300352 bytes
Variable Size 310380672 bytes
Database Buffers 104857600 bytes
Redo Buffers 6131712 bytes
Database mounted.
SQL> alter database open read only;

Database altered.
SQL> set serveroutput on
SQL> declare
2 check_db boolean;
3 begin
4 check_db:=dbms_tdb.check_db('Microsoft Windows IA (32-bit)');
5 end;
6 /

PL/SQL procedure successfully completed.

As we see no errors or message, so our database is ready to be transported to Windows 32-bit.

Step 3:
Check if there are any external files associated with the database, they will not be transported using RMAN.
SQL> set serveroutput on
SQL> declare
2 chk boolean;
3 begin
4 chk:=dbms_tdb.check_external;
5 end;
6 /
The following external tables exist in the database:
SH.SALES_TRANSACTIONS_EXT
The following directories exist in the database:
SYS.FOR_HR, SYS.BLOB_TEST, SYS.IDR_DIR, SYS.SUBDIR, SYS.XMLDIR, SYS.MEDIA_DIR,
SYS.LOG_FILE_DIR, SYS.DATA_FILE_DIR, SYS.AUDIT_DIR, SYS.DATA_PUMP_DIR,
SYS.ORACLE_OCM_CONFIG_DIR
The following BFILEs exist in the database:
PM.PRINT_MEDIA

PL/SQL procedure successfully completed.
The above directories exists in the database, we will need to recreate them with new locations once we complete the move.

Step 4:
Now we need to use RMAN convert database command to convert the source database to windows 32-bit.
The source database must be in read only mode.

RMAN> convert database new database 'ORCL'
2> transport script '/home/oracle/transport1.sql'
3> to platform 'Microsoft Windows IA (32-bit)'
4> db_file_name_convert '/home/oracle/product/oradata/test' '/home/oracle/con_dbf';

Starting conversion at source at 23-MAR-09
using channel ORA_DISK_1

External table SH.SALES_TRANSACTIONS_EXT found in the database

Directory SYS.FOR_HR found in the database
Directory SYS.BLOB_TEST found in the database
Directory SYS.IDR_DIR found in the database
Directory SYS.SUBDIR found in the database
Directory SYS.XMLDIR found in the database
Directory SYS.MEDIA_DIR found in the database
Directory SYS.LOG_FILE_DIR found in the database
Directory SYS.DATA_FILE_DIR found in the database
Directory SYS.AUDIT_DIR found in the database
Directory SYS.DATA_PUMP_DIR found in the database
Directory SYS.ORACLE_OCM_CONFIG_DIR found in the database

BFILE PM.PRINT_MEDIA found in the database

User SYS with SYSDBA and SYSOPER privilege found in password file
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00004 name=/home/oracle/product/oradata/test/users01.dbf
converted datafile=/home/oracle/con_dbf/users01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:05:21
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00002 name=/home/oracle/product/oradata/test/sysaux01.dbf
converted datafile=/home/oracle/con_dbf/sysaux01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00001 name=/home/oracle/product/oradata/test/system01.dbf
converted datafile=/home/oracle/con_dbf/system01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00005 name=/home/oracle/product/oradata/test/example01.dbf
converted datafile=/home/oracle/con_dbf/example01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00003 name=/home/oracle/product/oradata/test/undotbs01.dbf
converted datafile=/home/oracle/con_dbf/undotbs01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
Edit init.ora file /home/oracle/product/11g/db/dbs/init_00kal9vi_1_0.ora. This PFILE will be used to create the database on the target platform
Run SQL script /home/oracle/transport1.sql on the target platform to create database
To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target platform
To change the internal database identifier, use DBNEWID Utility
Finished conversion at source at 23-MAR-09

RMAN>

Below are our converted database files:

oracle@oracle:~/con_dbf$ pwd
/home/oracle/con_dbf
oracle@oracle:~/con_dbf$ ls -lrt
total 7267080
-rw-r----- 1 oracle oracle 5608382464 2009-03-23 16:17 users01.dbf
-rw-r----- 1 oracle oracle 882057216 2009-03-23 16:18 sysaux01.dbf
-rw-r----- 1 oracle oracle 744497152 2009-03-23 16:19 system01.dbf
-rw-r----- 1 oracle oracle 104865792 2009-03-23 16:20 example01.dbf
-rw-r----- 1 oracle oracle 94380032 2009-03-23 16:20 undotbs01.dbf
-rw-r--r-- 1 oracle oracle 1710 2009-03-23 17:01 init_00kal9vi_1_0.ora
-rw-r--r-- 1 oracle oracle 2698 2009-03-23 17:01 transport1.sql
Screen shot of the transport1.sql file:

Step 5:
FTP the files to the windows server. I have used filezilla to copy the files to the windows server.

Step 6:
Use oradim to create the service:
C:\Documents and Settings\Administrator>oradim -new -sid orcl -intpwd oracle -s
artmode manual -pfile C:\oracle\11g\product\11.1.0\db_1\dbs\init_orcl.ora
Instance created.

Use the init file (init_00kal9vi_1_0.ora) to startup nomount the db on the windows machine:
Make required changes to the paths in the init ora file.
Screen shot of the init.ora file:

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

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Mar 23 17:31:25 2009

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

Enter password:
Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 422670336 bytes
Fixed Size 1333620 bytes
Variable Size 310380172 bytes
Database Buffers 104857600 bytes
Redo Buffers 6098944 bytes
SQL>

Step 7:
Create the control file:
SQL> CREATE CONTROLFILE REUSE SET DATABASE "ORCL" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 'C:\oracle\oradata\ORCL\log1.rdo' SIZE 50M,
9 GROUP 2 'C:\oracle\oradata\ORCL\log2.rdo' SIZE 50M,
10 GROUP 3 'C:\oracle\oradata\ORCL\log3.rdo' SIZE 50M
11 DATAFILE
12 'C:\oracle\oradata\ORCL\system01.dbf',
13 'C:\oracle\oradata\ORCL\sysaux01.dbf',
14 'C:\oracle\oradata\ORCL\undotbs01.dbf',
15 'C:\oracle\oradata\ORCL\users01.dbf',
16 'C:\oracle\oradata\ORCL\example01.dbf'
17 CHARACTER SET AL32UTF8;

Control file created.

Step 8:
Open the database.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00028: your session has been killed

SQL> select name from v$database;
ERROR:
ORA-03114: not connected to ORACLE

Meanwhile and checked if the redo log files were created, Saw that they were created. Logged in again and:

SQL> select name from v$database;
NAME
---------
ORCL

SQL> select open_mode from v$database;
OPEN_MODE
----------
MOUNTED

SQL> alter database open;

Database altered.

Step 9:
Add the tempfile.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\oracle\oradata\ORCL\temp01.dbf' SIZE
54525952 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Tablespace altered.

Step 10:
Do a sanity check of the database.
Run $ORACLE_HOME\rdbms\admin\utlrp.sql

SQL> @utlrp

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2009-03-24 14:43:54
PL/SQL procedure successfully completed.
TIMESTAMP
-------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2009-03-24 14:43:56
PL/SQL procedure successfully completed.
OBJECTS WITH ERRORS
-------------------
0
ERRORS DURING RECOMPILATION
---------------------------
0
PL/SQL procedure successfully completed.
Invoking Ultra Search Install/Upgrade validation procedure VALIDATE_WK
Ultra Search VALIDATE_WK done with no error
PL/SQL procedure successfully completed.

SQL> select name, open_mode from v$database;

NAME OPEN_MODE
--------- ----------
ORCL READ WRITE

Database moved from linux to windows machine!!!!

Method 2
========
There another way to do the datafile conversion, we can convert the datafiles on the destination server also.
The rman script changes a little in that case.
RMAN> convert database on target platform
2> convert script '/home/oracle/convert.sql'
3> transport script '/home/oracle/transport.sql'
4> new database 'orcl'
5> format '/home/oracle/%U_%d';

The datafiles create by the above rman command should be copied to a temp directory on the destination server.
This will create a transport script to create the database instance on the destination server.
It will also create a rman script to convert the datafiles on the destination server.
It will look like:
run {
CONVERT DATAFILE 'C:\oracle\oradata\ORCL\SYSTEM.DBF'
FROM PLATFORM 'Linux IA (32-bit)'
FORMAT 'c:\temp\SYSTEM.DBF';
} ;
In the above case the files copied from the linux server were kept in c:\temp and then they would be converted to
C:\oracle\oradata\ORCL location.
After the conversion, run the transport.sql script after making the required changes.
After you have opened the database the rest of the steps are the same.

How To: Install 11g DB on Windows

2009-03-23T12:08:00.003+05:30

Screen Shots and Steps to Install 11g on Windows:
1. Download the software from www.oracle.com

2. Unzip the download and click on setup.exe.

Screen 1:
Select the basic installation of advanced. For our installation I am choosing advanced (Select Advanced and press next).

From Apun's Weblog

Screen 2:
Select the installation type, we are installing Enterprise Edition (Press next):

From Apun's Weblog

Screen 3:
Set the installation location. It takes about 2.77 GB of disk space (Set the installation location and press next).

From Apun's Weblog

Screen 4:
Oracle does some product specific prerequisite check (press next, fix if there are any issues).

From Apun's Weblog

Screen 5:
Select the database options you would like/need to install. I have selected all the options(Press Select all and press next).

From Apun's Weblog

Screen 6:
Ask if you want to create a database or an asm instance. I will create DB and asm instance later(Select software install only and press next).

From Apun's Weblog

Screen 7:
Information to connect to Oracle Configuration Manager. Requires information like the Oracle CSI number, metalink username etc (Press Next).

From Apun's Weblog

Screen 8:
Installation Summary Page (Press Install):

From Apun's Weblog

Screen 9:
Installation progress.

From Apun's Weblog

Screen 10:
End of successful install (Press Exit).

From Apun's Weblog

How to enable archivelog mode in Oracle 11g database

2009-03-20T12:22:00.003+05:30

First lets check the current log mode:
SQL*Plus: Release 11.1.0.6.0 - Production on Fri Mar 20 14:33:05 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 165
Current log sequence 167

SQL> select name, log_mode from v$database;

NAME LOG_MODE
--------- ------------
TEST NOARCHIVELOG

SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /home/oracle/product/11g/db/db
s/spfiletest.ora
Now lets try to set the archive log destination:

SQL> alter system set log_archive_dest_1='/home/oracle/archivelog' scope=spfile;
alter system set log_archive_dest_1='/home/oracle/archivelog' scope=spfile
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-16179: incremental changes to "log_archive_dest_1" not allowed with SPFILE

I faced the above error, thought need to enable archiving first:

SQL> alter system set log_archive_start=TRUE
2 scope=spfile;

System altered.

But I received the error again.

SQL> alter system set log_archive_dest_1='/home/oracle/archivelog' scope=spfile;
alter system set log_archive_dest_1='/home/oracle/archivelog' scope=spfile
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-16179: incremental changes to "log_archive_dest_1" not allowed with SPFILE

Checked the error description:
SQL> !oerr ora 16179
16179, 00000, "incremental changes to \"%s\" not allowed with SPFILE"
// *Cause: Incremental changes to a log_archive_dest_n parameter cannot
// be made when using an SPFILE.
// *Action: Specify either LOCATION or SERVICE plus all other attributes
// to be set in one ALTER SYSTEM/SESSION SET command.

So I did some research and found that when using spfile and trying to set a parameter like log_archive_dest_1, we need to give
complete format of the parameter like:

SQL> alter system set log_archive_dest_1='location=/home/oracle/archivelog' scope=spfile;

System altered.
(Optionally we can also give other parameters in this statement like optional, reopen etc.)

Now let shutdown the database and do a startup mount:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 422670336 bytes
Fixed Size 1300352 bytes
Variable Size 310380672 bytes
Database Buffers 104857600 bytes
Redo Buffers 6131712 bytes
Database mounted.

Enabling archivelog mode

SQL> alter database archivelog;

Database altered.

Open the database:

SQL> alter database open;

Database altered.

Test if archivelog is set properly:

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/archivelog
Oldest online log sequence 165
Next log sequence to archive 167
Current log sequence 167
SQL> select name, log_mode from v$database;

NAME LOG_MODE
--------- ------------
TEST ARCHIVELOG

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> !ls -lrt /home/oracle/archivelog
total 44496
-rw-r----- 1 oracle oracle 45509632 2009-03-20 14:43 1_167_677948664.dbf
-rw-r----- 1 oracle oracle 1024 2009-03-20 14:43 1_168_677948664.dbf

So now we have enabled archivelog mode and have some archivelog file also!!!

Deduplication Using SecureFile for LOBS in 11g

2009-03-17T13:29:00.003+05:30

In my previous blog, I had moved a lob from basicfile to securefile. I will use the same table for the dedupication example.
I had five rows in that table, 3 had similar lob data.

Storage Stats of the table:
SQL> select owner,table_name,COLUMN_NAME,SEGMENT_NAME from dba_lobs where table_name='BLOB_TEST';

OWNER TABLE_NAME COLUMN_NAME SEGMENT_NAME
---------- ---------- --------------- --------------------------------------------------
HR BLOB_TEST DATA SYS_LOB0000073688C00002$$

The below plsql block is courtesy oracle
declare
l_segment_size_blocks number;
l_segment_size_bytes number;
l_used_blocks number;
l_used_bytes number;
l_expired_blocks number;
l_expired_bytes number;
l_unexpired_blocks number;
l_unexpired_bytes number;

begin
dbms_space.space_usage(
segment_owner => 'HR',
segment_name => 'SYS_LOB0000073688C00002$$',
segment_type => 'LOB',
partition_name => NULL,
segment_size_blocks => l_segment_size_blocks,
segment_size_bytes => l_segment_size_bytes,
used_blocks => l_used_blocks,
used_bytes => l_used_bytes,
expired_blocks => l_expired_blocks,
expired_bytes => l_expired_bytes,
unexpired_blocks => l_unexpired_blocks,
unexpired_bytes => l_unexpired_bytes
);
dbms_output.put_line('Segment Size in Blocks => '|| l_segment_size_blocks);
dbms_output.put_line('Segment Size in Bytes => '|| l_segment_size_bytes);
dbms_output.put_line('Used Blocks => '|| l_used_blocks);
dbms_output.put_line('Used Bytes => '|| l_used_bytes);
dbms_output.put_line('Expired Blocks => '|| l_expired_blocks);
dbms_output.put_line('Expired Bytes => '|| l_expired_bytes);
dbms_output.put_line('Unexpired Blocks => '|| l_unexpired_blocks);
dbms_output.put_line('Unexpired Bytes => '|| l_unexpired_bytes);
end;
/
============================================
Segment Size in Blocks => 189848
Segment Size in Bytes => 1555234816
Used Blocks => 167392
Used Bytes => 1371275264 (1307.75 MB)
Expired Blocks => 22246
Expired Bytes => 182239232
Unexpired Blocks => 0
Unexpired Bytes => 0
============================================
SQL> alter table blob_test modify lob(data) (deduplicate);
Table altered.

This command takes some time to run.
Storage Stats after the deduplication.
============================================
Segment Size in Blocks => 416408
Segment Size in Bytes => 3411214336
Used Blocks => 55799
Used Bytes => 457105408 (435.93 MB)
Expired Blocks => 80882
Expired Bytes => 662585344
Unexpired Blocks => 279292
Unexpired Bytes => 2287960064
============================================

You can see that the Used Bytes column has come down visibly. As I had mentioned that there were 3 LOBs of the same size, which shows that the size has come down to 1/3rd.
The blob stored was:
a.dmp, b.dmp and c.dmp the same ones with size 428.88

Move from BasicFiles to Securefiles 11g

2009-03-17T12:39:00.006+05:30

Let do the change from a BasicFile to SecureFile in 11g using a very basic example.

Lets create a table with BLOB using BasicFile.

SQL> create table BLOB_test (name varchar2(20),data BLOB);
Table created.

Lets load some data into the table.

SQL> create directory FOR_HR as '/home/oracle/';
Directory created.

SQL> grant read,write on directory FOR_HR to hr;
Grant succeeded.

I am using the below procedure to quickly load a few files:

CREATE OR REPLACE PROCEDURE blob_load (name IN BLOB_test.name%TYPE)
IS
b_data BFILE;
e_blob BLOB;
BEGIN
insert into BLOB_test values (name,EMPTY_BLOB() ) returning data into e_blob;
b_data := bfilename( 'FOR_HR', name);
Dbms_Lob.Fileopen(b_data, Dbms_Lob.File_Readonly);
Dbms_Lob.Loadfromfile(e_blob, b_data, Dbms_Lob.Getlength(b_data));
Dbms_Lob.Fileclose(b_data);
COMMIT;
END;
/

SQL> exec blob_load('a.dmp');
PL/SQL procedure successfully completed.

......
SQL> select count(*) from blob_test;

COUNT(*)
----------
5

Now lets try to migrate to SecureFiles in 11g:
First check the system parameter db_securefile:
SQL> show parameter db_securefile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_securefile string PERMITTED

Lets create a new table with SecureFile feature.

SQL> create table blob_test_1 (name varchar2(20),data blob)
lob (data) store as securefile; 2
Table created.

Now we need to use the dbms_redefinition package to define the column mappings:

SQL> exec dbms_redefinition.start_redef_table('HR','BLOB_TEST','BLOB_TEST_1','name name ,'||'data data');
BEGIN dbms_redefinition.start_redef_table('HR','BLOB_TEST','BLOB_TEST_1','name name ,'||'data data'); END;
*
ERROR at line 1:
ORA-12089: cannot online redefine table "HR"."BLOB_TEST" with no primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 52
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1631
ORA-06512: at line 1

SQL> alter table blob_test add primary key (name);
Table altered.

SQL> alter table blob_test_1 add primary key (name);
Table altered.

SQL> exec dbms_redefinition.start_redef_table('HR','BLOB_TEST','BLOB_TEST_1','name name ,'||'data data');
PL/SQL procedure successfully completed.

Details of activity from v$sesstat, where 122 is our session id.

122 securefile allocation bytes 1,371,267,072
122 securefile allocation chunks 1,255
122 securefile direct write bytes 1,371,267,072
122 securefile direct write ops 1,809
122 securefile bytes non-transformed 1,349,152,270
122 securefile number of non-transformed flushes 326

SQL> select name from blob_test;
NAME
--------------------
a.dmp
b.dmp
c.dmp
d.log
e.log

SQL> select name from blob_test_1;
NAME
--------------------
a.dmp
b.dmp
c.dmp
d.log
e.log

SQL> exec dbms_redefinition.finish_redef_table ('HR','BLOB_TEST','BLOB_TEST_1');
PL/SQL procedure successfully completed.

SQL> select owner,table_name,COLUMN_NAME,SECUREFILE from dba_lobs where table_name='BLOB_TEST';

OWNER TABLE_NAME COLUMN_NAME SEC
---------- ---------- --------------- ---
HR BLOB_TEST DATA YES

SQL> drop table blob_test_1;
drop table blob_test_1
*
ERROR at line 1:
ORA-00942: table or view does not exist
(The temporary table got removed automatically)

My Music Station - The Best Of Best

2009-03-17T07:02:00.003+05:30

How To: Creation fo Cache Group Using Cache Administrator:

2009-03-16T07:51:00.006+05:30

http://localhost:17004/cache/
Login Page:

From Timesten

In our case:
TimesTen DSN: myDSN
Oracle Username: hr
Oracle Password: hr
TimesTen Password: hr

Welcome Page:

From Timesten

Select Create under Create Cache Group Definitions.

Shows the creation Page:

From Timesten

We will be creating a User Managed Cache Group Definition.

From Timesten

We select User Managed, propogate all tables (from the attributes section), Autorefresh every 5 seconds(from the autorefresh definitions).
Our Root table would be HR. REGIONS, child tables would be HR.COUNTRIES and HR.LOCATIONS.

Edit the table setting, by selecting the columns we need and defining the foreign key relations in the tables:

From Timesten

Defining foreign key for the table countries and regions:

From Timesten

Select required columns and attributes for the countries table:

From Timesten

Define foreign key relation between countries and locations table:

From Timesten

Define required columns and attributes for the locations table:

From Timesten

Press Save definition and it will take you to the Definition Summary Page:

From Timesten

Now click on create under the Cache Group section in the menu on the left:

From Timesten

Press Create Cache Group:

From Timesten

This will create the cache group called OUR_TEST.

Below is the SQL to create the same group manually (ttIsql myDSN):

SQL Code for creating the same user managed cache group:

CREATE USERMANAGED CACHE GROUP "OUR_TEST"
AUTOREFRESH MODE INCREMENTAL INTERVAL 5 SECONDS
STATE ON
FROM
"HR"."REGIONS" (
"REGION_ID" NUMBER NOT NULL,
"REGION_NAME" VARCHAR2(25 BYTE),
PRIMARY KEY("REGION_ID"), PROPAGATE
),
"HR"."COUNTRIES" (
"COUNTRY_ID" CHAR(2 BYTE) NOT NULL,
"COUNTRY_NAME" VARCHAR2(40 BYTE),
"REGION_ID" NUMBER,
PRIMARY KEY("COUNTRY_ID"),
FOREIGN KEY("REGION_ID")
REFERENCES "HR"."REGIONS"("REGION_ID"), PROPAGATE
),
"HR"."LOCATIONS" (
"LOCATION_ID" NUMBER(4) NOT NULL,
"STREET_ADDRESS" VARCHAR2(40 BYTE),
"POSTAL_CODE" VARCHAR2(12 BYTE),
"CITY" VARCHAR2(30 BYTE) NOT NULL,
"STATE_PROVINCE" VARCHAR2(25 BYTE),
"COUNTRY_ID" CHAR(2 BYTE),
PRIMARY KEY("LOCATION_ID"),
FOREIGN KEY("COUNTRY_ID")
REFERENCES "HR"."COUNTRIES"("COUNTRY_ID"), PROPAGATE
);

Lets check the cache group we created using the cache administrator.

oracle@oracle:~/TimesTen/timesten/startup$ ttIsql myDSN

Copyright (c) 1996-2008, Oracle. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
All commands must end with a semicolon character.

connect "DSN=myDSN";
Enter password for 'hr':
Connection successful: DSN=myDSN;UID=hr;DataStore=/home/oracle/TimesTen/data;DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=US7ASCII;DRIVER=/home/oracle/TimesTen/timesten/lib/libtten.so;OracleId=test;PermSize=16;TempSize=16;TypeMode=0;
(Default setting AutoCommit=1)
Command> cachegroups;

Cache Group HR.EMP_IN_MEM:

Cache Group Type: Read Only
Autorefresh: Yes
Autorefresh Mode: Incremental
Autorefresh State: On
Autorefresh Interval: 5 Seconds

Root Table: HR.EMPLOYEES
Table Type: Read Only

Cache Group HR.OUR_TEST:

Cache Group Type: User Managed
Autorefresh: Yes
Autorefresh Mode: Incremental
Autorefresh State: On
Autorefresh Interval: 5 Minutes

Root Table: HR.REGIONS
Table Type: Propagate

Child Table: HR.COUNTRIES
Table Type: Propagate

Child Table: HR.LOCATIONS
Table Type: Propagate

2 cache groups found.
Command>
Lets check the record count:
Command> select * from regions;
0 rows found.
Oops no rows show up....we still need to create a cache agent.

Command> load cache group our_test commit every 20 rows;
8289: Manual LOAD/REFRESH of cache group requires AUTOREFRESH STATE to be PAUSED, but the STATE of HR.OUR_TEST is ON. The LOAD is not allowed
The command failed.
Command>

We need to use ttAdmin to create a cache agent, this is to set the password for the cache agent:

oracle@oracle:~/TimesTen/timesten/bin$ ttAdmin -cacheUidPwdSet -cacheUid hr -cachePwd hr myDSN
Enter password for 'hr':
Cache User Id : hr
RAM Residence Policy : inUse
Replication Agent Policy : manual
Replication Manually Started : False
Cache Agent Policy : manual
Cache Agent Manually Started : False

Now start the cache agent:
oracle@oracle:~/TimesTen/timesten/bin$ ttAdmin -cacheStart myDSN
Enter password for 'hr':
RAM Residence Policy : inUse
Replication Agent Policy : manual
Replication Manually Started : False
Cache Agent Policy : manual
Cache Agent Manually Started : True

Now lets check the record count again.

oracle@oracle:~/TimesTen/timesten/bin$ ttIsql myDSN
Copyright (c) 1996-2008, Oracle. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
All commands must end with a semicolon character.
connect "DSN=myDSN";
Enter password for 'hr':
Connection successful: DSN=myDSN;UID=hr;DataStore=/home/oracle/TimesTen/data;DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=US7ASCII;DRIVER=/home/oracle/TimesTen/timesten/lib/libtten.so;OracleId=test;PermSize=16;TempSize=16;TypeMode=0;
(Default setting AutoCommit=1)
Command> select * from regions;
1, Europe
2, Americas
3, Asia
4, Middle East and Africa
4 rows found.

We got the rows this time!!!!
Check the Cache agent policy, by default its manual that means the agent will not start automatically when the timesten daemon restarts.
Command> call ttCachePolicyGet;
manual
1 row found.

Lets change it to auto start:
Command> call ttCachePolicySet ('always');
Command> call ttCachePolicyGet;
always
1 row found.

Lets check the status of our TimesTen daemon:
oracle@oracle:~/TimesTen/timesten/bin$ ttstatus
TimesTen status report as of Mon Mar 16 09:51:10 2009

Daemon pid 6427 port 17000 instance timesten
TimesTen server pid 6436 started on port 17002
TimesTen webserver pid 6434 started on port 17004

------------------------------------------------------------------------
Data store /home/oracle/TimesTen/data
There are 11 connections to the data store
Data store is in shared mode
Shared Memory KEY 0x4d0148ad ID 2752530
Type PID Context Connection Name ConnID
Cache Agent 7276 0x081e2758 Handler 2
Cache Agent 7276 0x0826b710 Timer 3
Cache Agent 7276 0x082dce30 Aging 4
Cache Agent 7276 0x0839d710 timestenorad 6
Cache Agent 7276 0x0841b1e0 timestenorad 7
Subdaemon 6431 0x08098e38 Worker 2042
Subdaemon 6431 0x0810f020 HistGC 2046
Subdaemon 6431 0x0816e4c0 Checkpoint 2044
Subdaemon 6431 0x081ad950 Flusher 2043
Subdaemon 6431 0x0820cdf0 Aging 2045
Subdaemon 6431 0x0824c280 Monitor 2047
Replication policy : Manual
Cache agent policy : Always
TimesTen's Cache agent is running for this data store
------------------------------------------------------------------------
Data store /home/oracle/TimesTen/timesten/info/TT_timesten
There are no connections to the data store
Replication policy : Manual
Cache agent policy : Manual
------------------------------------------------------------------------
Access control enabled.
End of report
oracle@oracle:~/TimesTen/timesten/bin$

To check the success of our configuration, lets try adding rows:
Adding row from oracle:
SQL> select * from regions;

REGION_ID REGION_NAME
---------- -------------------------
1 Europe
2 Americas
3 Asia
4 Middle East and Africa

SQL> insert into regions values (5,'Antartica');
1 row created.
SQL> commit;
Commit complete.

Checking Timesten:

oracle@oracle:~$ ttIsql myDSN
Copyright (c) 1996-2008, Oracle. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
All commands must end with a semicolon character.
connect "DSN=myDSN";
Enter password for 'hr':
Connection successful: DSN=myDSN;UID=hr;DataStore=/home/oracle/TimesTen/data;DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=US7ASCII;DRIVER=/home/oracle/TimesTen/timesten/lib/libtten.so;OracleId=test;PermSize=16;TempSize=16;TypeMode=0;
(Default setting AutoCommit=1)
Command> select * from regions;
1, Europe
2, Americas
3, Asia
4, Middle East and Africa
5, Antartica
5 rows found.

Command>

Row updated.

Now adding row from TimeTen:
Command> insert into regions values (6,'Artic');
1 row inserted.

Checking Oracle DB:
SQL> select * from regions;
REGION_ID REGION_NAME
---------- -------------------------
5 Antartica
6 Artic
1 Europe
2 Americas
3 Asia
4 Middle East and Africa
6 rows selected.

Working both ways. Now lets check the foreign key definitions we made:

Command> insert into countries values ('AT','MYCOUNTRY',10);
3001: Foreign key violation [REGIONS]: the row in child table HR.COUNTRIES has no key in the parent table HR.REGIONS
The command failed.

Well the definitions works well!!!!

How To Create Cache Group in TimesTen

2009-03-10T13:17:00.008+05:30

Cache groups are used to cache oracle database data in TimesTen in-memory system.
You can either cache the whole table, or part of it by defining the columns.
You cannot cache LOB datatypes as of now in TimesTen, the biggest available chumk is LONG RAW.
You can create a cache group of multiple tables or single table as per your requirement.
If you have multiple tables in a cache group you can define parent child relations.
Though the tables in a cache group must be joined by foreign key constraints in TimesTen,
the tables do not necessarily need to be joined in the Oracle instance .
There can be different types of cache group configurations, depending upon the requirements.

Types of cache groups:

-->READONLY - enforces a caching behavior in which updates on Oracle tables are applied to TimesTen through the AUTOREFRESH mechanism.
-->SYNCHRONOUS WRITETHROUGH - enforces a caching behavior in which cached data is updated in TimesTen and propagated to Oracle. Updates to a SWT cache group are committed synchronously.
-->ASYNCHRONOUS WRITETHROUGH (AWT) cache groups - enforces caching behavior in which cached data is updated in TimesTen and propagated to Oracle. Updates to an AWT cache group are committed asynchronously.
-->USERMANAGED - Customizable/user defined cache group.

Setting up Oracle:
Create the schema to be used for caching. I am using the oracle provide "HR" sample schema on the oracle side.

Setting up TimesTen:
1.) Create user on TimeTen:
oracle@oracle:~/TimesTen/timesten/bin$ ./ttIsql tt_timesten

Copyright (c) 1996-2008, Oracle. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
All commands must end with a semicolon character.
connect "DSN=tt_timesten";
Connection successful: DSN=TT_timesten;UID=oracle;DataStore=/home/oracle/TimesTen/timesten/info/TT_timesten;DatabaseCharacterSet=US7ASCII;ConnectionCharacterSet=US7ASCII;DRIVER=/home/oracle/TimesTen/timesten/lib/libtten.so;TypeMode=0;
(Default setting AutoCommit=1)

Command> create user hr identified by 'hr';
Command> grant admin,ddl to hr;
Command> exit
Disconnecting...
Done.

2.) Create TimesTen DSN:
Need to add the entry of the dsn in the sys.odbc.ini file on the TimesTen server and sys.ttconnect.ini on the TimesTen client.
The file is located at $TIMESTEN_HOME/info
Parameters to be defined:
[myDSN]
Driver=/TimesTen/timesten/lib/libtten.so ##See the default one in the file and use the same path.
Datastore=/TimesTen/data ##Defined the place where TimesTen can store the cache data.
PermSize=16 ##Defines permanent size of the data store.
TempSize=16 ##Temporary size
UID=hr ##TimesTen user you created.
OracleId=hr ##DB name/tnsnames.ora entry.
OraclePwd=hr ##Oracle password
DatabaseCharacterSet=AL32UTF8 ##Should be same as the oracle database

oracle@oracle:~/TimesTen$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Tue Mar 10 14:18:09 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> SELECT value FROM nls_database_parameters WHERE parameter='NLS_CHARACTERSET';
VALUE
--------------------------------------------------------------------------------
AL32UTF8

3.) Create oracle tables:
As we are using the HR schema, we will use the following tables:
COUNTRIES
DEPARTMENTS
EMPLOYEES
EMP_DETAILS_VIEW
JOBS
JOB_HISTORY
LOCATIONS
REGIONS

4.) Create the cache group:

VERY IMPORTANT THING TO REMEMBER:
Make sure that your environment has the correct location of the TNS_ADMIN variable. That means it points to the correct tnsnames.ora.
In my case I had both 10g and 11g installed and had a problem with the location and was receiving the following error(ORA-12154):
5220: Permanent Oracle connection failure error in OCIServerAttach(): ORA-12154: TNS:could not resolve the connect identifier specified rc = -1
5935: Could not validate Oracle login: uid = hr, pwd = HIDDEN, oracleId = hr

oracle@oracle:~/product/11g/db$ ttIsql myDSN
Copyright (c) 1996-2008, Oracle. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
All commands must end with a semicolon character.
connect "DSN=myDSN";
Enter password for 'hr':
Connection successful: DSN=myDSN;UID=hr;DataStore=/home/oracle/TimesTen/data;DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=US7ASCII;DRIVER=/home/oracle/TimesTen/timesten/lib/libtten.so;OracleId=test;PermSize=16;TempSize=16;TypeMode=0;
(Default setting AutoCommit=1)

Command> call ttCacheUidPwdSet('hr','hr');

Command> call ttCacheStart;

Command> CREATE READONLY CACHE GROUP EMP_IN_MEM
> AUTOREFRESH INTERVAL 5 SECONDS
> FROM EMPLOYEES
> (EMPLOYEE_ID NUMBER(6) NOT NULL PRIMARY KEY,FIRST_NAME VARCHAR2(20),LAST_NAME VARCHAR2(25) NOT NULL,EMAIL VARCHAR2(25) NOT NULL);

Command> LOAD CACHE GROUP EMP_IN_MEM COMMIT EVERY 256 ROWS;
107 cache instances affected.

Command> cachegroups;

Cache Group HR.EMP_IN_MEM:

Cache Group Type: Read Only
Autorefresh: Yes
Autorefresh Mode: Incremental
Autorefresh State: On
Autorefresh Interval: 5 Seconds

Root Table: HR.EMPLOYEES
Table Type: Read Only

1 cache group found.

Command> select count(*) from EMPLOYEES;
107
1 row found.
Command> select * from EMPLOYEES;
100, Steven, King, SKING
101, Neena, Kochhar, NKOCHHAR
102, Lex, De Haan, LDEHAAN
103, Alexander, Hunold, AHUNOLD
104, Bruce, Ernst, BERNST
...

So we were successfully able to create a read only cache group.

To test further, lets add data in the oracle database, as of now the table has 107 rows.

SQL> insert into employees values (500,'Apun','Hiran','me@me.com','1111111111','01-JAN-2007','AD_PRES',50000,NULL,NULL,NULL);
1 row created.
SQL> insert into employees values (501,'Rick','Smith','ricksmith@me.com','1234567891','01-JAN-2007','AD_PRES',50000,NULL,NULL,NULL);
1 row created.
SQL> commit;
Commit complete.

We added 2 rows. Lets see if the record count in TimesTen has changed.

oracle@oracle:~/product/11g/db$ ttIsql myDSN
Copyright (c) 1996-2008, Oracle. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
All commands must end with a semicolon character.
connect "DSN=myDSN";
Enter password for 'hr':
Connection successful: DSN=myDSN;UID=hr;DataStore=/home/oracle/TimesTen/data;DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=US7ASCII;DRIVER=/home/oracle/TimesTen/timesten/lib/libtten.so;OracleId=test;PermSize=16;TempSize=16;TypeMode=0;
(Default setting AutoCommit=1)

Command> select count(*) from EMPLOYEES;
109
1 row found.

Command> select * from EMPLOYEES where EMPLOYEE_ID in (500,501);
500, Apun, Hiran, me@me.com
501, Rick, Smith, ricksmith@me.com

2 rows found.
Command>

This completed the testing for our cache group!!!

Start/Stop TimesTen

2009-03-10T10:16:00.004+05:30

To Start/Stop/Restart Timesten:

oracle@oracle:~/TimesTen/timesten/startup$ ./tt_timesten
USAGE: ./tt_timesten { start | stop | restart }

oracle@oracle:~/TimesTen/timesten/startup$ ps -ef|grep times
oracle 8187 1 0 12:14 ? 00:00:00 /home/oracle/TimesTen/timesten/bin/timestend -initfd 13
oracle 8190 8187 0 12:14 ? 00:00:00 /timesten/bin/timestensubd -verbose -userlog tterrors.log -supportlog ttmesg.log -id 0 -facility user -accctl
oracle 8191 8187 0 12:14 ? 00:00:00 /timesten/bin/timestensubd -verbose -userlog tterrors.log -supportlog ttmesg.log -id 1 -facility user -accctl
oracle 8192 8187 0 12:14 ? 00:00:00 /timesten/bin/timestensubd -verbose -userlog tterrors.log -supportlog ttmesg.log -id 2 -facility user -accctl
oracle 8193 8187 0 12:14 ? 00:00:00 /timesten/bin/timestensubd -verbose -userlog tterrors.log -supportlog ttmesg.log -id 3 -facility user -accctl
oracle 8194 8187 0 12:14 ? 00:00:00 /timesten/bin/timestenws -verbose -userlog tterrors.log -supportlog ttmesg.log -id 4 -facility user -accctl
oracle 8196 8187 0 12:14 ? 00:00:00 /timesten/bin/ttcserver -verbose -userlog tterrors.log -supportlog ttmesg.log -id 5 -p 17002 -facility user -accctl
oracle 10252 7344 0 12:48 pts/2 00:00:00 grep times

oracle@oracle:~/TimesTen/timesten/startup$ ./tt_timesten stop
Stopping TimesTen Daemon : [ OK ]

oracle@oracle:~/TimesTen/timesten/startup$ ps -ef|grep times
oracle 10322 7344 0 12:49 pts/2 00:00:00 grep times

oracle@oracle:~/TimesTen/timesten/startup$ ./tt_timesten start
Starting TimesTen Daemon : [ OK ]
oracle@oracle:~/TimesTen/timesten/startup$ ps -ef|grep times
oracle 10338 1 0 12:49 ? 00:00:00 /timesten/bin/timestend -initfd 13
oracle 10341 10338 0 12:49 ? 00:00:00 /timesten/bin/timestensubd -verbose -userlog tterrors.log -supportlog ttmesg.log -id 0 -facility user -accctl
oracle 10342 10338 0 12:49 ? 00:00:00 /timesten/bin/timestensubd -verbose -userlog tterrors.log -supportlog ttmesg.log -id 1 -facility user -accctl
oracle 10343 10338 0 12:49 ? 00:00:00 /timesten/bin/timestensubd -verbose -userlog tterrors.log -supportlog ttmesg.log -id 2 -facility user -accctl
oracle 10346 10338 0 12:49 ? 00:00:00 /timesten/bin/timestensubd -verbose -userlog tterrors.log -supportlog ttmesg.log -id 3 -facility user -accctl
oracle 10352 10338 0 12:49 ? 00:00:00 /timesten/bin/timestenws -verbose -userlog tterrors.log -supportlog ttmesg.log -id 4 -facility user -accctl
oracle 10360 10338 0 12:49 ? 00:00:00 /timesten/bin/ttcserver -verbose -userlog tterrors.log -supportlog ttmesg.log -id 5 -p 17002 -facility user -accctl
oracle 10406 7344 0 12:49 pts/2 00:00:00 grep times

oracle@oracle:~/TimesTen/timesten/startup$ ./tt_timesten restart
Stopping TimesTen Daemon : [ OK ]
Starting TimesTen Daemon : [ OK ]

Installation of TimesTen 7.0.5 - Oracle In-Memory Database Cache

2009-03-10T09:48:00.005+05:30

You can download the latest version from TimesTen Download

unzip timesten70500.linux86.tar.zip
tar -xvf timesten70500.linux86.tar
oracle@oracle:~/software/linux86$ ls
3rdparty behaviorchanges.txt doc install.pl LINUX86 README.html setup.sh uninst.sh
oracle@oracle:~/software/linux86$
oracle@oracle:~/software/linux86$ ./setup.sh

NOTE: Each TimesTen installation is identified by a unique instance name.
The instance name must be a non-null alphanumeric string, not longer
than 255 characters.

Please choose an instance name for this installation? [ tt70 ] timesten
Instance name will be 'timesten'.
Is this correct? [ yes ] yes

Please select a product :

[1] Oracle TimesTen In-Memory Database
[2] Oracle In-Memory Database Cache

Which product would you like to install? [ 1 ] 2

Of the three components:

[1] Client/Server and Data Manager
[2] Data Manager Only
[3] Client Only

Which would you like to install? [ 1 ]
Where would you like to install the timesten instance of TimesTen? [ /home/oracle ] /home/oracle/TimesTen
Where would you like to create the daemon home directory? [ /home/oracle/TimesTen/timesten/info ]
The directory /home/oracle/TimesTen/timesten/info does not exist.
Do you want to create it? [ yes ]
Installing into /home/oracle/TimesTen/timesten ...
Uncompressing ...

The TimesTen Demo applications can take up to 64 Mbytes of disk space.
Depending on how your system is configured, you may not want to create the
DemoDataStore directory in the default location,
/home/oracle/TimesTen/timesten/info/DemoDataStore

Where would you like to create the DemoDataStore directory? [ /home/oracle/TimesTen/timesten/info ]
Creating /home/oracle/TimesTen/timesten/info/DemoDataStore ...

NOTE: All installations that replicate to each other must use the same daemon
port number that is set at installation time. The daemon port number can
be verified by running 'ttVersion'.

The default port number is 17000.

Do you want to use the default port number for the TimesTen daemon? [ yes ]
The daemon will run on the default port number (17000).

Processing /home/oracle/TimesTen/timesten/PERL/perl.tar ...

Would you like to enable datastore access control? [ no ] yes
The daemon logs will be located in /home/oracle/TimesTen/timesten/info
Would you like to specify a different location for the daemon logs? [ no ]

The following variables have been set in the file :
/home/oracle/TimesTen/timesten/bin/ttThunk

ORACLE_HOME=/home/oracle/product/10.2/db
LD_LIBRARY_PATH=/home/oracle/TimesTen/timesten/lib:/home/oracle/product/10.2/db/lib32:/home/oracle/product/10.2/db/network/lib32:/home/oracle/product/10.2/db/lib:/home/oracle/product/10.2/db/network/lib

Would you like to enable the Cache Connect to Oracle Administrator? [ yes ]
What TCP/IP port number would you like Cache Connect to Oracle Administrator to listen on? [ 17004 ]

NOTE: To access the TimesTen Cache Connect to Oracle Administrator
go to the url: http://localhost:17004/cache

NOTE: It appears that you are running version 4.1 of the g++
compiler. TimesTen ships with multiple sets of client libraries and server
binaries : one built with g++ 3.2.3, one with g++ 3.4.6, and one with
g++ 4.1.0. The installer has created links to the 4.1.0 library in the
/lib directory and to the 4.1.0 server binary in the
/bin directory. If you want to use a different compiler,
please modify the links to point to the desired library and server binary.

Installing server components ...
Would you like to log all server Connects/Disconnects? [ yes ]
What is the TCP/IP port number that you want the TimesTen Server to listen on? [ 17002 ]
Starting the daemon ...
TimesTen Daemon startup OK.

Installing client components ...
What is the name of the host running the TimesTen server? [ oracle ]
What is the TCP/IP port number that the TimesTen server is listening on? [ 17002 ]
What is the name of the instance running the TimesTen server? [ timesten ]
Creating new /home/oracle/TimesTen/timesten/info/sys.ttconnect.ini
Extracting 3rd party tools ...
Would you like to install the documentation? [ yes ]
Where would you like to create the doc directory (s=skip)? [ /home/oracle/TimesTen/timesten/doc ]
The directory /home/oracle/TimesTen/timesten/doc does not exist.
Do you want to create it? [ yes ]

NOTE: The TimesTen daemon startup/shutdown scripts have not been installed.

Run the script '/home/oracle/TimesTen/timesten/bin/setuproot' as root.
This will move the TimesTen startup script into its appropriate location.

The startup script is currently located here :
'/home/oracle/TimesTen/timesten/startup/tt_timesten'.

End of TimesTen installation.