Athil's Oracle blog

Wednesday, January 11, 2017

Proud to be with my virtual Guru

With Mr. RAC, Syed jaffer Hussain

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