Pages

Tuesday, October 7, 2014

Logon banner in SQLPLUS

To display a customized banner while login to sqlplus

sys@TESTDB> show user
USER is "SYS"

sys@TESTDB> create or replace procedure custom_logon_message as
  2  begin
  3  dbms_output.put_line ( ' ');
  4  dbms_output.put_line ( '-------------------------' );
  5  dbms_output.put_line ( ' ');
  6  dbms_output.put_line ( 'This is restricted system' );
  7  dbms_output.put_line ( ' ');
  8  dbms_output.put_line ( '-------------------------' );
  9  dbms_output.put_line ( ' ');
 10  end;
 11  /

Call the "custom_logon_message" from glogin.sql


ORACLE_HOME\sqlplus\admin\glogin.sql
set feed off
exec custom_logon_message;

local \ OS authentication

C:\Users\mmeerha>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Oct 7 10:52:26 2014

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


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

-------------------------
This is restricted system
-------------------------
sys@TESTDB>

Remote connection


C:\Users\mmeerha>sqlplus a/a@testdb

SQL*Plus: Release 11.2.0.2.0 Production on Tue Oct 7 10:50:38 2014

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


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

-------------------------
This is restricted system
-------------------------

NOTE: though granting exec on privilege to public/ individual users failed with

sys@TESTDB> conn a/a
Connected.
BEGIN custom_logon_message; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'CUSTOM_LOGON_MESSAGE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

So created the procedure in that user it worked.

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