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.
Thursday, December 1, 2011
dbms_metadata.get_ddl & ORA-31603
Thursday, July 21, 2011
Oracle: Rename single/RAC Database
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
Thursday, January 27, 2011
Drop Materialized View takes a long time
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.
Friday, January 14, 2011
How to Setup Resource Manager to Control DB Resources
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);
Tuesday, November 30, 2010
How to refresh MVIEW in parallel
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
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
OR
Incase of existing mviews:
alter materialized view log on
3.) When you create the mview, create it with a parallel clause:
create materialized view
OR
create materialized view
Or
Incase of existing mviews:
alter table
alter materialized view
Lastly while executing the refresh:
EXECUTE DBMS_MVIEW.REFRESH(LIST=>'
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
2.) Replace
3.) Replacedblink with the name of the database link.
Thursday, September 9, 2010
Oracle 11gr2 Grid: root.sh fails on node2, asmlib issue
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:*'
Saturday, August 28, 2010
Oracle 11gR2 Grid Infrastructure Installation Steps
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
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
oifcfg getif [-node
oifcfg delif [{-node
oifcfg [-help]
Wednesday, August 18, 2010
ORA-12060: During Mview Creation
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.
Wednesday, September 23, 2009
Configuring and starting ons daemon in non RAC DB
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.
Saturday, September 19, 2009
How To: Configure Data Guard Fast-Start Failover Step By Step
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 = '
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
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
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) -->
(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
Tuesday, September 15, 2009
Step by Step Data Guard Setup for Oracle 10g
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.
