Pages

Sunday, June 7, 2015

New table name in DDL using DBMS_METADATA

If you want to get a DDL with the desired name for a table this is function will be used.

Step 1:


Replace YOUR SCHEMA name in line # 13 in below block i.e., from 'A' to 'SCHEMA name'

  1  create or replace function remap_name(orig_name varchar2, new_name varchar2 )
  2  return clob is
  3    -- Define local variables.
  4    h   number; --handle returned by OPEN
  5    th  number; -- handle returned by ADD_TRANSFORM
  6    doc clob;
  7  begin
  8    -- Specify the object type.
  9    h := dbms_metadata.open('TABLE');
 10    -- Use filters to specify the particular object desired.
 11    dbms_metadata.set_filter(h
 12                            ,'SCHEMA'
 13                            ,'A');
 14    dbms_metadata.set_filter(h
 15                            ,'NAME'
 16                            ,orig_name);
 17    -- Request that the schema name be modified.
 18    th := dbms_metadata.add_transform(h
 19                                     ,'MODIFY');
 20    dbms_metadata.set_remap_param(th
 21                                 ,'REMAP_NAME'
 22                                 ,orig_name
 23                                 ,new_name);
 24    -- Request that the metadata be transformed into creation DDL.
 25    th := dbms_metadata.add_transform(h
 26                                     ,'DDL');
 27    -- Specify that segment attributes are not to be returned.
 28    dbms_metadata.set_transform_param(th
 29                                     ,'SEGMENT_ATTRIBUTES'
 30                                     ,false);
 31    dbms_metadata.set_transform_param(th,'SQLTERMINATOR',TRUE);
 32    -- Fetch the object.
 33    doc := dbms_metadata.fetch_clob(h);
 34    -- Release resources.
 35    dbms_metadata.close(h);
 36    return doc;
 37  end remap_name;
 38  /

PLEASE REMOVE LINE NUMBERS BEFORE EXECUTION

Step 2. Call the remape_name and spool it


set echo off head off feed off
spool renameDDL.sql
SELECT 'SELECT remap_name('''||  TABLE_NAME || ''','''||  TABLE_NAME || '_new'') FROM DUAL;' FROM DBA_TABLES where owner='A';
spool off

Step 3. Review the above script and execute accordingly


Example:

sys@TESTDB> SELECT remap_name('TEST_OBJ','TEST_OBJ_new') FROM DUAL;

REMAP_NAME('TEST_OBJ','TEST_OBJ_NEW')
-------------------------------------------------

  CREATE TABLE "A"."TEST_OBJ_new"
   (    "OWNER" VARCHAR2(30),
        "OBJECT_NAME" VARCHAR2(128),

Note: 

REMAP_NAME is not available in 10g , available from 11g

NO REMAP_NAME ins 10g
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_metada.htm#BGBBIEGA

REMAP_NAME is available in SET_REMAP_PARAM 11g only
http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_metada.htm#BGBBIEGA 

You will get the below error in 10g but function will be created 
ORA-31600: invalid input value REMAP_NAME for parameter NAME in function SET_REMAP_PARAM

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;