How to forcefully ‘deconfig’ Grid cluster configuration in 11gR2

I was installing 11gR2 RAC with Grid infrastructure on a 2 node AIX cluster. I did all the steps (normally what I do on Solaris), but missed few AIX specific steps & my root.sh failed.
# /grid/11.2.0/root.sh
Running Oracle 11g root.sh script…
The following environment variables are set as:
ORACLE_OWNER= ora11gr2
ORACLE_HOME= /grid/11.2.0
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-03-02 07:55:55: Parsing the host name
2010-03-02 07:55:55: Checking for super user privileges
2010-03-02 07:55:55: User has super user privileges
Using configuration parameter file: /grid/11.2.0/crs/install/crsconfig_params
Creating trace directory
User ora11gr2 is missing the following capabilities required to run CSSD in realtime:
CAP_NUMA_ATTACH,CAP_BYPASS_RAC_VMM,CAP_PROPAGATE
To add the required capabilities, please run:
/usr/bin/chuser capabilities=CAP_NUMA_ATTACH,CAP_BYPASS_RAC_VMM,CAP_PROPAGATE ora11gr2
CSS cannot be run in realtime mode at /grid/11.2.0/crs/install/crsconfig_lib.pm line 8119.
So root.sh returned the error & asked me to run chuser command with above options. After executing this command on both the nodes, I again executed the root.sh, buut it failed with message “Deconfigure the existing cluster configuration before starting”
bash-2.05b# /grid/11.2.0/root.sh
Running Oracle 11g root.sh script…
The following environment variables are set as:
ORACLE_OWNER= ora11gr2
ORACLE_HOME= /grid/11.2.0
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]:
The file “oraenv” already exists in /usr/local/bin. Overwrite it? (y/n) [n]:
The file “coraenv” already exists in /usr/local/bin. Overwrite it? (y/n) [n]:
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-03-02 08:07:32: Parsing the host name
2010-03-02 08:07:32: Checking for super user privileges
2010-03-02 08:07:32: User has super user privileges
Using configuration parameter file: /grid/11.2.0/crs/install/crsconfig_params
Improper Oracle Clusterware configuration found on this host
Deconfigure the existing cluster configuration before starting
to configure a new Clusterware
run ‘/grid/11.2.0/crs/install/rootcrs.pl -deconfig’
to configure existing failed configuration and then rerun root.sh
So I tried, but when I executed /grid/11.2.0/crs/install/rootcrs.pl -deconfig, it error out, saying, it could not communicate with CRS & asked me to start the CRS. But funny part is, CRS was not yet configured. In short it was going in a circular fashion.
In this scenario, -force option with -deconfig, will be very handy
bash-2.05b# /grid/11.2.0/crs/install/rootcrs.pl -deconfig -force -verbose
2010-03-02 08:11:29: Parsing the host name
2010-03-02 08:11:29: Checking for super user privileges
2010-03-02 08:11:29: User has super user privileges
Using configuration parameter file: /grid/11.2.0/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
Failure at scls_scr_setval with code 8
Internal Error Information:
Category: -2
Operation: failed
Location: scrsearch3
Other: id doesnt exist scls_scr_setval
System Dependent Information: 2
CRS-4544: Unable to connect to OHAS
CRS-4000: Command Stop failed, or completed with errors.
Successfully deconfigured Oracle clusterware stack on this node
And finally, even though it could not communicate with CRS, it successfully deconfigured Oracle clusterware stack

Performance Tip

Time and Time Again,DBA's spend time and energy tuning a component of their database that is  not a top wait event and they are surprised to find that their change didnot make a huge difference in performance for example:-a faster CPU doesnot help a CPU-Bound  system.Always examine the Top Five Events :)

Cluster verify utility fails with the user eqivelence test

./runcluvfy.sh stage -pre crsinst -fixup -n rac1.locamain,rac2.localdomain -verbose

Performing pre-checks for cluster services setup

Checking node reachability...

Check: Node reachability from node "Rac1"
  Destination Node                      Reachable?
  ------------------------------------  ------------------------
  rac1                               yes
  rac1                               yes
Result: Node reachability check passed from node "rac1"


Checking user equivalence...

Check: User equivalence for user "grid"
  Node Name                             Comment
  ------------------------------------  ------------------------
  rac1                               failed
  rac2                               failed
Result: PRVF-4007 : User equivalence check failed for user "grid"

ERROR:
User equivalence unavailable on all the specified nodes
Verification cannot proceed


Pre-check for cluster services setup was unsuccessful on all the nodes.

Fix:
Say you have two nodes, rac1 and rac2 and user oracle. You have created the ssh keys in oracle on both nodes.

On node: rac1

ssh rac2
ssh rac2.domain
ssh rac2-priv
ssh rac2-priv.domain
ssh rac1
ssh rac1.domain
ssh rac1-priv
ssh rac1-priv.domain
exec /usr/bin/ssh-agent $SHELL
/usr/bin/ssh-add

On node: rac2

ssh rac2
ssh rac2.domain
ssh rac2-priv
ssh rac2-priv.domain
ssh rac1
ssh rac1.domain
ssh rac1-priv
ssh rac1-priv.domain
exec /usr/bin/ssh-agent $SHELL
/usr/bin/ssh-add
Phew! Now all ssh combos are covered and you can reach everywhere including yourself. A lot of typing YES and even worse when the the node count increases as each has to reach all the others! I have had the equivalence check fail and realised I hadn't checked on the node, back to itself, just to the other node.

 

Checking User priviledges On your system for every user

set lines 110 pages 1000 ver off
col role for a16
col pv for a75 hea 'PRIVILEGE OR ROLE'
bre on role on type skip 1

SQL>  define usercheck ='COLLECTION'
SQL> select grantee
, 'ROL' type
, granted_role pv
from dba_role_privs
where grantee = '&usercheck'
union
select grantee
, 'PRV' type
, privilege pv
from dba_sys_privs
where grantee = '&usercheck' union
select grantee
, 'OBJ' type,
regexp_replace(max(decode(privilege,'WRITE','WRITE,'))||
max(decode(privilege,'READ','READ,'))||
max(decode(privilege,'EXECUTE','EXECUTE')),'WRITE,READ,EXECUTE','ALL')||
regexp_replace(max(decode(privilege,'SELECT','SELECT'))||
max(decode(privilege,'DELETE',',DELETE'))||
max(decode(privilege,'UPDATE',',UPDATE'))||
max(decode(privilege,'INSERT',',INSERT')),'SELECT,DELETE,UPDATE,INSERT','ALL')||
' ON '||object_type||' "'||a.owner||'"."'||table_name||'"' pv
from dba_tab_privs a
, dba_objects b
where a.owner=b.owner
and a.table_name = b.object_name
and a.grantee='&usercheck'
group by a.owner
, table_name
, object_type
, grantee
union
select grantee
, 'COL' type,
privilege||' ('||column_name||') ON "'||owner||'"."'||table_name||'"' pv
from dba_col_privs
where grantee='&usercheck'
union
select username grantee
, '---' type
, 'empty user ---' pv
from dba_users
where not username in (select distinct grantee from dba_role_privs)
and not username in (select distinct grantee from dba_sys_privs)
and not username in (select distinct grantee from dba_tab_privs)
and username like '%&usercheck%'
group by username
order by grantee
, type
, pv;

GRANTEE                        TYP PRIVILEGE OR ROLE
------------------------------ --- ---------------------------------------------------------------------------
COLLECTION                     OBJ SELECT ON TABLE "CTS.SERVICE_PARAMETER"
COLLECTION                         SELECT ON TABLE "SUB.ALLOWED_MSISDN"
COLLECTION                         SELECT ON VIEW "CTS.SERVICE_VIEW"

COLLECTION                     ROL CONNECT
COLLECTION                         DSXE
COLLECTION                         RESOURCE


6 rows selected.

SQL> SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE ='DSXE'
  2  ;

no rows selected

SQL> SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE ='RESOURCE';

ROLE             PRIVILEGE                                ADM
---------------- ---------------------------------------- ---
RESOURCE         CREATE CLUSTER                           NO
                 CREATE SEQUENCE                          NO
                 CREATE TRIGGER                           NO
                 CREATE TABLE                             NO
                 CREATE PROCEDURE                         NO
                 CREATE TYPE                              NO
                 CREATE OPERATOR                          NO
                 CREATE INDEXTYPE                         NO

Default users in oracle

Known schemas in Oracle

This is a (most probably unfinished) list of users  that come with an Oracle installation or with installing the Oracle sample schemas For most of these users, the default password is the same as the username. Where this is not the case, I have noted it.

anonymous

Used for HTTP access to XML DB.

apex_public_user

apex_030200

appqossys

bi

This user is created with the oracle sample schemas. It owns the Business Intelligence schema.

ctxsys

The owner of Oracle text (formerly: interMedia text).

dbsnmp

Part of the Oracle intelligent agent.

The password is assigned during the installation.

This user is created (or re-created) with ORACLE_HOME/rdbms/admin/catsnmp.sql and dropped with $ORACLE_HOME/rdbms/admin/catnsnmp.sql.

dip

Used by the Directory Integration Platform (DIP) which synchronizes changes in the Oracle Internet Directory with applications in the database.

dmsys

This user is used for Data Mining.

exfsys

This schema is used for expression filters.

flows_XXXXXX

This seems to be the user for APEX (Oracle Application Express) (on Oracle Express Edition???). XXXXXX seems to be the version number of APEX. So, APEX 2.1 has flows_020100.

hr

Installed with Oracle sample schemas. It owns the Human Resource schema.

ix

Installed with Oracle sample schemas. It owns the Information Transport schema.

lbacsys

The administration account for Oracle Label Security.

mddata

Used by Oracle Spatial to store Geocoder and router data.

mdsys

The owner of spatial (which is a part of interMedia).

mgmt_view

Used for the Oracle Enterprise Manager Database Control.

Its password is generated randomly.

odm

The Data mining schema. Since Oracle 10g, the name can be freely chosen.

odm_mtr

Used for the data repository for data mining samples.

Default password: mtrpw.

oe

Installed with Oracle sample schemas. It owns the Order Entry schema.

olapsys

The schema that owns the OLAP catalogs.

Default password: manager.

oracle_ocm

orddata

ordplugins

Like mdsys: part of interMedia. Third party plugins (as well as Oracle plugins) for interMedia are installed into this schema.

ordsys

The administration account for interMedia.

outln

Used for the query plan stability feature.

outln owns ol$, ol$hints and ol$nodes.

pm

Installed with Oracle sample schemas. It owns the Product Media schema.

scott

Used for Oracle examples.

Its default password is tiger.

This schema is created by executing $ORACLE_HOME/sqlplus/demo/demobld.sql (demodrop.sql drops scott.)

sh

Installed with Oracle sample schemas. It owns the Sales History schema.

si_informtn_schema

Used for SQL/MM Still Image Standard.

spatial_csw_admin_usr

spatial_wfs_admin_usr

sys

Used for database administration. See this link.

The password is set when the database is installed.

sysman

The default super user account used to set up and administer enterprise manager.

The password is set when the database is installed.

system

Used for database administration. See this link.

The password is set when the database is installed.

tracesrv

Used for Oracle trace. Not used anymore since Oracle 8.

mtssys

Used for Microsoft transaction server support.

oaspublic

olapsys

Used for OLAP.

owbsys

owbsys_audit

websys

wk_proxy

Used for ultrasearch.

Default password: wksys.

wksys

Used for ultrasearch.

Default password: wmsys.

wk_test

wmsys

The owner for Workspace Manager.

xdb

Used for XML DB.

OSE$HTTP$ADMIN

AURORA$JIS$UTILITY$

AURORA$ORB$UNAUTHENTICATED

Auditing Oracle logins and logoff step by step trigger

SQL> create tablespace audit_test datafile '/data/DB/DEVDB/audit_test.dbf' size 10G;

Tablespace created.

SQL> create user audit_test identified by test_1 default tablespace audit_test;

User created.

SQL> grant dba to audit_test;

Grant succeeded.

SQL> grant select on v_$session to audit_test;

Grant succeeded.

Now we come to the table and trigger creation steps:

SQL> conn audit_test
Enter password:
Connected.
SQL> CREATE TABLE logonaudittable
(
  event       VARCHAR2(10),
  sid         NUMBER,
  serial#     NUMBER,
  timestamp   DATE,
  username    VARCHAR2(30),
  osuserid    VARCHAR2(30),
  machinename VARCHAR2(64)
)
/
  2    3    4    5    6    7    8    9   10   11
Table created.

SQL> CREATE OR REPLACE TRIGGER logonauditing AFTER LOGON ON database
DECLARE
  machinename VARCHAR2(64);
  osuserid    VARCHAR2(30);
  v_sid       NUMBER(10);
  v_serial    NUMBER(10);
  2    3    4    5    6    7
  8    CURSOR c1 IS
    SELECT sid, serial#, osuser, machine
  9        FROM v$session WHERE audsid = userenv('sessionid');
 10   11  BEGIN
  OPEN c1;
 12   13    FETCH c1 INTO v_sid, v_serial, osuserid, machinename;
 14
 15    INSERT INTO audit_test.logonaudittable VALUES ( 'LOGON', v_sid, v_serial, sysdate,
 16        user, osuserid, machinename );
 17
 18    CLOSE c1;
 19  END;
 20  /

Trigger created.

3. Create LOGOFF trigger

CREATE OR REPLACE TRIGGER logoffauditing
BEFORE LOGOFF ON database
DECLARE
  machinename VARCHAR2(64);
  osuserid    VARCHAR2(30);
  v_sid       NUMBER(10);
  v_serial    NUMBER(10);
  
  CURSOR c1 IS
    SELECT sid, serial#, osuser, machine
      FROM v$session WHERE audsid = userenv('sessionid');
BEGIN
  OPEN c1;
  FETCH c1 INTO v_sid, v_serial, osuserid, machinename;

  INSERT INTO audit_test.logonaudittable VALUES ( 'LOGOFF', v_sid, v_serial, sysdate,
    user, osuserid, machinename );

  CLOSE c1;
END;
/

End of the lesson

Step By Step Guide On Creating Physical Standby Using RMAN Duplicate In ASM Filesystem For ASM Primary

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.2.0.0 - Release: 10.2 to 11.2

when to use it:

This document offers step by step procedure to create ASM physical standby from ASM primary.

Solution

Pre-Requisite
a) Database (PRIMARY) is up and running and the datafiles, controlfiles residing in ASM.
b) ASM instance and diskgroups configured in STANDBY server.
c) Network connectivity between PRIMARY and STANDBY server
PROCEDURE
1.Enable force logging in PRIMARY.
2.Create SRL(standby redo logs) in PRIMARY.
3.Backup the PRIMARY database.
4.Make proper changes in the parameter file of PRIMARY.
5.Create the parameter file for STANDBY
6.Copy the files (RMAN Backup, init.ora) to STANDBY
7.Establish the connectivity between PRIMARY and STANDBY.
8.Start the STANDBY instance and use RMAN duplicate to create standby database
9. Create SRL on standby.
10.Start the MRP process,
11. Verify whether the log are shipped and applied properly @the standby


Consider two databases of names PRIMARY= PRIMA  and STANDBY= MYSTD

1. Enable Forced Logging on PRIMARY

SQL> ALTER DATABASE FORCE LOGGING;
This step must be applied before taking the backup

2. Configure Standby Redo Log on PRIMARY

NOTE:SIZE OF STANDBY LOGFILE SHOULD BE SAME AS ONLINE LOGFILE

a. Check the log files and sizes,

SQL>SELECT GROUP#,BYTES FROM V$LOG;

b. Create SRL
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 100M;

NOTE: The SRL will be created in the ASM Disk group mentioned in init.ora parameter DB_CREATE_ONLINE_LOG_DEST_1

c. Verify the standby redo log file groups were created(do this after the creation of stanby database)

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

3. Use RMAN to backup PRIMARY database with archivelog & controlfile.

RMAN>backup database format '/tmp/dbbkp/PRIMA_%U';
RMAN>backup archivelog all format '/tmp/dbbkp/PRIMA_ARC_%U';
RMAN> backup current controlfile for standby format '/tmp/dbbkp/PRIMA_CONTROL.bkp';

4. Make the necessary changes to PRIMARY  .

DB_NAME=pri
DB_UNIQUE_NAME=pri
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIMA,MYSTD)'
LOG_ARCHIVE_DEST_2= 'SERVICE=stdby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=MYSTD'
LOG_ARCHIVE_DEST_STATE_2=DEFER
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=MYSTD
FAL_CLIENT=PRIMA
DB_FILE_NAME_CONVERT='MYSTD','PRIMA'
LOG_FILE_NAME_CONVERT='MYSTD','PRIMA'
STANDBY_FILE_MANAGEMENT=AUTO


5. Create the parameter file for standby,

a. CREATE PFILE='<specify any location>' from spfile; (@primary,)
b. Make the necessary changes, for example,

DB_NAME=PRIMA
DB_UNIQUE_NAME=MYSTD
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIMA,MYSTD)'
CONTROL_FILES='+DATA','+FRA'
DB_FILE_NAME_CONVERT='PRIMA','MYSTD'
LOG_FILE_NAME_CONVERT='PRIMA','MYSTD'
LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
LOG_ARCHIVE_DEST_2= 'SERVICE=PRIMA LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIMA'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=PRIMA
FAL_CLIENT=MYSTD

Note: If the ASM Disk groups names are different between PRIMARY and STANDBY do the necessary changes in STANDBY with the parameters DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n

6. Copy the files (RMAN Backup, init.ora) to STANDBY
a) Copy the RMAN backup files created in /tmp/dbbkp (step3) to STANDBY in identical location
Example:scp /tmp/dbbkp/* oracle@core1:/tmp/dbbkp/*
b) Copy the init.ora (step 5) to STANDBY
c) Recreate the password file in standby using orapwd utility


7. Establish the connectivity between primary and standby.

In PRIMARY (TNSNAMES.ORA)

MYSTD=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.9.200.108)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = MYSTD.server.com)
)
)

In STANDBY (TNSNAMES.ORA)
PRIMA=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.9.200.107)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRIMA.server.com)
)
)

NOTE: Use TNSPING <servicename> to check the connectivity between PRIMARY and STANDBY

8. Start the STANDBY instance and use RMAN duplicate to create standby database

NOTE: Make sure the ASM instance also running.
$export ORACLE_SID=MYSTD
SQL>create spfile from pfile=’<specify the newly created parameter location>’
SQL>startup nomount

NOTE: Connect to catalog if your primary database has catalog database.

$RMAN target sys/<passwd>@primary catalog RMAN/RMAN@RMAN auxiliary sys/<passwd>
RMAN> RUN {
allocate auxiliary channel C1 device type disk;
duplicate target database for standby;
}
9. Create SRL(Standby Redo logs) on standby,

NOTE:SIZE OF STANDBY LOGFILE SHOULD BE SAME AS ONLINE LOGFILE

For Example,

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 100M;

10. Start the MRP process in PRIMARY

In PRIMARY

SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

In STANDBY
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

11. Verify whether the log are shipped and applied properly in the standby

a. execute on PRIMARY database

SQL> ALTER SYSTEM SWITCH LOGFILE;

b. execute on STANDBY database

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

c. Issue more log switches at PRIMARY

SQL> ALTER SYSTEM SWITCH LOGFILE;

d. Verify the new redo data was archived on the standby database.On the standby database, query the V$ARCHIVED_LOG view to verify the redo data was received and archived on the standby database:

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME  FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

NOTE: Verify new archived redo log files were applied. At the standby database, query the V$ARCHIVED_LOG view to verify the archived redo log files were applied.

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;