Pages

Monday, September 29, 2014

What happens when execution of orainstRoot.sh / root.sh during clusteware installation


Though it is mentioned in many blogs/ books I put this here for my reference.

[root@rac2 disks]# cd /u01/
[root@rac2 u01]#. /app/oraInventory/orainstRoot.sh
Changing permissions of /u01/app/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.

Changing groupname of /u01/app/oraInventory to oinstall.
The execution of the script is complete.

[root@rac2 /]# pwd
/
[root@rac2 /]# cd /u01
[root@rac2 u01]# cd 11.2.0/
[root@rac2 11.2.0]# cd grid/
[root@rac2 grid]# ls -ltr ro*
-rwxr-xr-x 1 grid oinstall 456 Sep 29 01:27 rootupgrade.sh
-rwxr-x--- 1 grid oinstall 443 Sep 29 01:27 root.sh

[root@rac2 grid]# ./root.sh
Performing root user operation for Oracle 11g

The following environment variables are set as:
    ORACLE_OWNER= grid
    ORACLE_HOME=  /u01/11.2.0/grid

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 script.
Now product-specific root actions will be performed.
Using configuration parameter file: /u01/11.2.0/grid/crs/install/crsconfig_param                                                                                                                                                             s
Creating trace directory
User ignored Prerequisites during installation
OLR initialization - 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 Clusterware entries to inittab
CRS-2672: Attempting to start 'ora.mdnsd' on 'rac2'
CRS-2676: Start of 'ora.mdnsd' on 'rac2' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'rac2'
CRS-2676: Start of 'ora.gpnpd' on 'rac2' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'rac2'
CRS-2672: Attempting to start 'ora.gipcd' on 'rac2'
CRS-2676: Start of 'ora.cssdmonitor' on 'rac2' succeeded
CRS-2676: Start of 'ora.gipcd' on 'rac2' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'rac2'
CRS-2672: Attempting to start 'ora.diskmon' on 'rac2'
CRS-2676: Start of 'ora.diskmon' on 'rac2' succeeded
CRS-2676: Start of 'ora.cssd' on 'rac2' succeeded

ASM created and started successfully.

Disk Group OCR_VD created successfully.

clscfg: -install mode specified
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
CRS-4256: Updating the profile
Successful addition of voting disk abbc6a6610464ff7bf09a97da611423d.
Successfully replaced voting disk group with +OCR_VD.
CRS-4256: Updating the profile
CRS-4266: Voting file(s) successfully replaced
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   abbc6a6610464ff7bf09a97da611423d (/dev/oracleasm/disks/OCR_VD01) [OCR_VD]
Located 1 voting disk(s).
CRS-2672: Attempting to start 'ora.asm' on 'rac2'
CRS-2676: Start of 'ora.asm' on 'rac2' succeeded
CRS-2672: Attempting to start 'ora.OCR_VD.dg' on 'rac2'
CRS-2676: Start of 'ora.OCR_VD.dg' on 'rac2' succeeded
CRS-2672: Attempting to start 'ora.registry.acfs' on 'rac2'
CRS-2676: Start of 'ora.registry.acfs' on 'rac2' succeeded
Configure Oracle Grid Infrastructure for a Cluster ... succeeded
[root@rac2 grid]#
[root@rac2 grid]#


Node2:

[root@rac1 u01]# cd ../
[root@rac1 /]# ./u01/app/oraInventory/orainstRoot.sh
Changing permissions of /u01/app/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.

Changing groupname of /u01/app/oraInventory to oinstall.
The execution of the script is complete.

[root@rac1 /]#. /u01/11.2.0/grid/root.sh
Performing root user operation for Oracle 11g

The following environment variables are set as:
    ORACLE_OWNER= grid
    ORACLE_HOME=  /u01/11.2.0/grid

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 script.
Now product-specific root actions will be performed.
Using configuration parameter file: /u01/11.2.0/grid/crs/install/crsconfig_params
Creating trace directory
User ignored Prerequisites during installation
OLR initialization - successful
Adding Clusterware entries to inittab
CRS-4402: The CSS daemon was started in exclusive mode but found an active CSS daemon on node rac2, number 1, and is terminating
An active cluster was found during exclusive startup, restarting to join the cluster
Configure Oracle Grid Infrastructure for a Cluster ... succeeded

[root@rac1 /]#


Tuesday, July 22, 2014

IP address for sysdba connections

UTL_INADDR package provides a PL/SQL procedures to support internet addressing. It provides an API to retrieve host names and IP addresses of local and remote hosts.

Tested for my local sys connection

SQL> conn / as sysdba
Connected.
SQL> SELECT UTL_INADDR.get_host_address from dual;

GET_HOST_ADDRESS
--------------------------------------------------------------------------------
10.177.62.194

Reference:
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/u_inaddr.htm#ARPLS071

Monday, May 5, 2014

Database auto start Windows

Mandatory configurations to check DB auto start is working.

start -> All programs -> Oracle-Homexx -> Configuration and Migration tools -> Administration assistanct for windows-> Oracle managed objects / computers/hostname/OH/databases/ right click on DB/ 'startup/shutdown options' check both the TAb

run -> regedit -> HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\KEY_OraDb11g_home1

Run -> services.msc -> Properties of DB service

ORACLE_HOME/database/oradim

server startup time: and DB startup time

systeminfo | find "Up Time"  (or) net statistics workstation | find "Statistics since"
net statistics server


select to_char(startup_time,'HH24:MI:SS DD-MON-YYYY') DB_started from v$instance;

Thursday, March 20, 2014

SQL monitor hidden parameters



Parameter session value instance value description
_sqlmon_binds_xml_format     default     default     format of column binds_xml in [G]V$SQL_MONITOR
_sqlmon_max_plan     80     80     Maximum number of plans entry that can be monitored. Defaults to 20 per CPU
_sqlmon_max_planlines     300     300     Number of plan lines beyond which a plan cannot be monitored
_sqlmon_recycle_time     60     60     Minimum time (in s) to wait before a plan entry can be recycled
_sqlmon_threshold     5     5     CPU/IO time threshold before a statement is monitored. 0 is disabled

Monday, December 30, 2013

What happens when DATAFILE RESIZE?

There is a need to understand what will be the impact when resizing some 8000 plus datafiles consecutively.  After some search did some test like enabled 10046 trace and captured what will happen when there is a datafile resize.

Used tkprof to format and provided the output to customer explaining the behavior.

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

SQL> show user
USER is "SYS"
SQL> oradebug setmypid
Statement processed.
SQL> alter session set tracefile_identifier='mytrace_10046';

Session altered.

SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

SQL> alter database datafile 'D:\ORACLE_DB\TESTDB\TESTDB\TESTDRP1.DBF' resize 150m;

Database altered.

SQL> ALTER SESSION SET EVENTS='10046 trace name context off';

Session altered.

SQL> oradebug tracefile_name
D:\ORACLE_DB\diag\rdbms\testdb\testdb\trace\testdb_ora_556_mytrace_10046.trc
SQL>

TKPROF Output.

alter database datafile 'D:\ORACLE_DB\TESTDB\TESTDB\TESTDRP1.DBF' resize 150m

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.02          0          0          0           0
Execute      1      0.01       1.96          0          0          3           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.01       1.99          0          0          3           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  control file sequential read                   31        0.03          0.10
  db file sequential read                         2        0.02          0.04
  Disk file operations I/O                        3        0.01          0.04
  Data file init write                            1        0.00          0.00
  db file single write                            1        0.00          0.00
  control file parallel write                     3        0.00          0.00
  rdbms ipc reply                                 1        0.21          0.21
  log file sync                                   1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1       10.41         10.41

********************************************************************************

Recommendation given ,
-- See the above test, when there is a resize it will go through all this events
-- This test is done on my local machine where there is a very minimal load.
-- As it took 1.99s for a single file there will be more time considering your server.
-- Considering other factors like OS/ storage performance, it will take more time
-- Do it in a small batches and in a sequential manner. So that it will not harm database performance. 
-- Also it is best to test a small batch to estimate run  time in non business hours and proceed with the activity.

Thanks for reading, post your suggestions / comments on this. Happy reading.