About Me

My photo
Bangalore, India
I am an Oracle Certified Professional working in SAP Labs. Everything written on my blog has been tested on my local environment, Please test before implementing or running in production. You can contact me at amit.rath0708@gmail.com.

Monday, December 16, 2013

How to change ssh port to a non default port in Unix

Yesterday I have to change default ssh port from 22 to 1022. This change in port from default to non-default  enhances security of your server a little bit. Changing port from 22 to 1022 will stop automated attacks and it becomes difficult to guess from which port server is accessible.

Solution :-

1. As root user edit the sshd_config file in /etc/ssh location.

edit Port 22 to Port 1022

2. Before changing port to a non default value, check port is not being used by any other server.

3. Once Port has been changed in /etc/ssh/sshd_config file , restart SSH service :-

  #stopsrc -s sshd
  #startsrc -s sshd

  or by 

  #service sshd restart

4. Check that the changed port is working:-

ssh -p 1022 server1

I hope this article helped you.

Regards,
Amit Rath

Friday, December 13, 2013

Query Parameter in Datapump (EXPDP/IMPDP) utility

Sometimes we need to export or imprt only some data from a huge table. This can be done by using query parameter in Datapump utility. Query parameter can be used in both EXPDP as well as IMPDP utility.

1. EXPDP :-

Query parameter can be used in two ways  :-

a. Using parameter file :-

bash-3.2$
bash-3.2$ cat test.par
TABLES=AMIT
directory=datapump
DUMPFILE=expdp_test.dmp
logfile=expdp_test.log
query=amit:"where DATE > '01-DEC-2013'"
bash-3.2$

bash-3.2$ expdp user/user parfile=test.par

Export: Release 11.2.0.1.0 - Production on Fri Dec 13 12:20:47 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "USER"."SYS_EXPORT_TABLE_01":   user/****** parfile=test.par
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 384 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "USER"."AMIT"             30.67 KB      75 rows
Master table "USER"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for USER.SYS_EXPORT_TABLE_01 is:
  /disk1/datapump/expdp_etst.dmp
Job "USER"."SYS_EXPORT_TABLE_01" successfully completed at 12:20:53

b. In Command Line :-

expdp user/user directory=datapump dumpfile=expdp_taa_01.dmp logfile=expdp_taa_01.log query=amit:\"where DATE \> \'01-DEC-2013\'\" tables=AMIT

Export: Release 11.2.0.1.0 - Production on Fri Dec 13 12:24:50 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "USER"."SYS_EXPORT_TABLE_01":  user/******** directory=datapump dumpfile=expdp_taa_01.dmp logfile=expdp_taa_01.log query=amit:"where DATE > '01-DEC-2013'" tables=AMIT
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 384 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "USER"."AMIT"             30.67 KB      75 rows
Master table "USER"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for USER.SYS_EXPORT_TABLE_01 is:
  /disk1/datapump/expdp_taa_01.dmp
Job "USER"."SYS_EXPORT_TABLE_01" successfully completed at 12:24:56

2. IMPDP :-

a. Using par file :-

bash-3.2$ cat test.par

schemas=DATA
include=TABLE:"IN('TEST')"
directory=datapump
DUMPFILE=expdp_fullDB_12_Dec_13.dmp
logfile=expdp_etst.log
query=test:"where DATE > '01-DEC-2013'"
remap_schema=DATA:AMIT

bash-3.2$ impdp amit/amit parfile=test.par

Import: Release 11.2.0.1.0 - Production on Fri Dec 13 12:44:22 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "AMIT"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "AMIT"."SYS_IMPORT_SCHEMA_01":  amit/******** parfile=test.par
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . imported "AMIT"."TEST"                    51.17 KB      75 out of 1614 rows
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Job "AMIT"."SYS_IMPORT_SCHEMA_01" successfully completed at 12:44:44

b. In Command line :-

bash-3.2$ impdp amit/amit directory=datapump dumpfile=expdp_fullDB_12_Dec_13.dmp logfile=test.log query=TEST:\"where DATE \> \'01-DEC-2013\'\" schemas= DATA  include=TABLE:\"IN\(\'TEST\'\)\" remap_schema=DATA:AMIT

Import: Release 11.2.0.1.0 - Production on Fri Dec 13 12:38:29 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "AMIT"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "AMIT"."SYS_IMPORT_SCHEMA_01":  amit/******** directory=datapump dumpfile=expdp_fullDB_12_Dec_13.dmp logfile=test.log query=test:"where DATE > '01-DEC-2013'" schemas=DATA include=TABLE:"IN('TEST')" remap_schema=DATA:AMIT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . imported "AMIT"."TEST"                    51.17 KB      75 out of 1614 rows
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Job "AMIT"."SYS_IMPORT_SCHEMA_01" successfully completed at 12:38:55

I hope this article helped you.

Regards,
Amit Rath

Tuesday, December 10, 2013

ORA-14460: only one COMPRESS or NOCOMPRESS clause may be specified

Yesterday I was importing a dumpfile using Datapump utility and I faced below mentioned error :-

Import: Release 11.2.0.1.0 - Production on Tue Dec 09 09:14:11 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table  "AMIT" ."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting  "AMIT" ."SYS_IMPORT_FULL_01":  amit/******** directory=datapump dumpfile=expdp_amit_04.dmp logfile=impdp_amit_01.log remap_schema=amit21:amit TRANSFORM=STORAGE:n
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"AMIT"."TEST" failed to create with error:
ORA-14460: only one COMPRESS or NOCOMPRESS clause may be specified
Failing sql is:
CREATE TABLE "AMIT"."TEST" ("ID" NUMBER(12) , "NAME" VARCHAR2(20 BYTE)
ORA-39083: Object type TABLE:"AMIT"."TEST" failed to create with error:
ORA-14460: only one COMPRESS or NOCOMPRESS clause may be specified

Solution :-

Use TRANSFORM=segment_attributes:n in IMPDP

impdp amit/amit directory=datapump dumpfile=expdp_amit_04.dmp logfile=impdp_amit_01.log remap_schema=amit21:amit TRANSFORM=STORAGE:n,segment_attributes:n &
Import: Release 11.2.0.1.0 - Production on Tue Dec 09 09:15:49 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table  "AMIT" ."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting  "AMIT" ."SYS_IMPORT_FULL_01":  amit/******** directory=datapump dumpfile=expdp_amit_04.dmp logfile=impdp_amit_01.log remap_schema=amit21:amit TRANSFORM=STORAGE:n,SEGMENT_ATTRIBUTES:n &
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "INCMS2_MG"."SYS_IMPORT_FULL_01" completed at 09:16:34

I hope this article helped you.

Regards,
Amit Rath

Thursday, December 5, 2013

How to determine schemas or contents inside an expdp dumpfile

Sometimes we need to list out the contents of an expdp dumpfile before importing it. There is a parameter in Datapump IMPDP utility in which we can list out ddl's present inside a dump file. PFB steps :-

bash-3.2$ impdp system/system dumpfile=expdp_fullDB_12_Nov_13.dmp logfile=amit.log sqlfile=dump.txt directory=datapump &

Import: Release 11.2.0.1.0 - Production on Thu Dec 5 10:13:53 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_SQL_FILE_FULL_01":  system/******** dumpfile=expdp_fullDB_12_Nov_13.dmp logfile=amit.log sqlfile=dump.txt directory=datapump
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/ROLE
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/CONTEXT
Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/PRE_TABLE_ACTION
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/CROSS_SCHEMA/OBJECT_GR
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATI
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/PACKAGE_SPEC
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/FUNCTION
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/GRANT/OWNER_GRANT/OBJECT
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/COMPILE_PACKAGE/PACKAGE_SP
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/ALTER_FUNCTION
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/OWNER_GRANT/OBJECT_GRAN
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/CROSS_SCHEMA/OBJECT_GRA
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BOD
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/FUNCTIONAL_
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_TABLE_ACTION
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Job "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully completed at 10:14:38

bash-3.2$ ls -ltr|tail 

total 26568216

-rw-r--r--    1 oracle   oinstall   28361199 Dec 05 10:14 dump.txt
-rw-r--r--    1 oracle   oinstall       3937 Dec 05 10:14 amit.log

Above dump.txt includes ddl's of all schemas, tablespaces present inside the dumpfile.

I hope this article helped you.

Regards,
Amit Rath

Monday, December 2, 2013

Difference between Static listener and Dynamic listener registrations

Database can be registered in two ways with a listener :-

1. Static Registration :- A static registration of database to a listener is used when we want to remotely start our database. Static registration is like hardcoding a instance details in listener.ora file. In a static registration an instance is registered with the listener whether its up or not. When a client request comes listener opens a dedicated connection , and server later find out that instance is not up then it gives error message as "Oracle not available". 

2. Dynamic Registration :- In Dynamic registration , registration is performed by PMON process. Once a Database instance starts, its PMON process registers instance details with associated listener. Dynamic registration does not require any manual configuration in the listener.ora file where as Static configuration does.From Oracle 8i dynamic configuration introduced. 

PFB listener.ora file having static configuration :-

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = AMIT)
      (ORACLE_HOME = /oracle_home/oracle/ora11g/product)
    )
    (SID_DESC =
      (SID_NAME = TEST)
      (ORACLE_HOME = /oracle_home/oracle/ora11g/product)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.11.1.10)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /oracle_home/oracle

PFB listener.ora file having dynamic configuration :-

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.11.1.10)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /oracle_home/oracle
As we can see dynamic configuration is much easier as compared to static configuration.

PFB listener status details when using Dynamic regitration :-

bash-3.2$ lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production on 02-DEC-2013 10:51:59

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=amit)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production
Start Date                01-DEC-2013 16:19:04
Uptime                    10 days 18 hr. 32 min. 56 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      ON
Listener Parameter File   /oracle_home/oracle/ora11g/product/network/admin/listener.ora
Listener Log File         /oracle_home/oracle/diag/tnslsnr/new/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=amit)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "amit" has 1 instance(s).
  Instance "amit", status READY, has 1 handler(s) for this service...
Service "amitXDB" has 1 instance(s).
  Instance "amit", status READY, has 1 handler(s) for this service...
The command completed successfully

Above status as "READY" resembles that listener has checked instance details and found its status as up or running to handle client request.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=amit)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production
Start Date                01-DEC-2013 16:42:04
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      ON
Listener Parameter File   /oracle_home/oracle/ora11g/product/network/admin/listener.ora
Listener Log File         /oracle_home/oracle/diag/tnslsnr/new/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=amit)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

Above status resembles that either Instance is not up, or its not been registered with listener.

PFB commands to register database instance with listerner if its not automatically registering with listener :-

bash-3.2$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 2 11:43:39 2013

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

Enter user-name: /as sysdba

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

SQL> alter system register;

System altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
bash-3.2$ lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production on 02-DEC-2013 11:43:51

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=amit)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production
Start Date                01-DEC-2013 16:44:04
Uptime                    0 days 0 hr. 1 min. 47 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      ON
Listener Parameter File   /oracle_home/oracle/ora11g/product/network/admin/listener.ora
Listener Log File         /oracle_home/oracle/diag/tnslsnr/new/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=amit)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "amit" has 1 instance(s).
  Instance "amit", status READY, has 1 handler(s) for this service...
Service "amitXDB" has 1 instance(s).
  Instance "amit", status READY, has 1 handler(s) for this service...
The command completed successfully

PFB listener status details when using static regitration also :-

bash-3.2$ lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production on 02-DEC-2013 11:50:28

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST= amit)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production
Start Date                01-DEC-2013 18:10:27
Uptime                    0 days 02 hr. 40 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      ON
Listener Parameter File   /oracle/oracle/ora11g/product/network/admin/listener.ora
Listener Log File         /oracle/oracle/diag/tnslsnr/pacs3/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=amit)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "amit" has 2 instance(s).
  Instance "amit", status UNKNOWN, has 1 handler(s) for this service...
  Instance "amit", status READY, has 1 handler(s) for this service...
Service "amitXDB" has 1 instance(s).
  Instance "amit", status READY, has 1 handler(s) for this service...
The command completed successfully

Above status as "UNKNOWN" resembles that listener has not checked instance status details that whether its up or not, it has just registered the details of instance. So listener is not sure that whether instance can handle client request or not.

I hope this article helped you.

Regards,
Amit Rath

H2 Database ( In Memory Database)

H2 database is a RDBMS , IN Memory database. Its a java SQL database. Main Features of H2 database are :-

1. Very fast ,open source , JDBC Api
2. Can have both embedded and server modes
3. Browser based console application.
4. Small footprint of 1MB size jar file.
5. Commercial support is available.

Maximum Size of H2 Database :-

1. Database File Size Limit :- 4 TB or higher ( using default page size 2KB).
2. Maximum rows per table :- 2^64
3. Memory Requirements for IN memory database :- Larger Database needs more memory. As 1.1 version storage mechanism, minimum memory required for 12 GB database is 240 MB.

There are two modes of H2 database :-

1. Embedded Mode :- In this mode, application and database are in same Virtual Machine( VM )  . Application open database within same VM. It is the fastest and easiest connection mode. But in this mode database will be open in one VM only. Both persistent as well as IN memory database can be opened. NO Limit is there on number of database open currently and number of open connections.

H2 database in Embedded , persistent mode :-

bash-3.2$ pwd
/home/oracle/h2/bin
bash-3.2$ java -cp h2-1.3.170.jar org.h2.tools.Shell

Welcome to H2 Shell 1.3.170 (2012-11-30)
Exit with Ctrl+C
[Enter]   jdbc:h2:~/test
URL       jdbc:h2:~/amit
[Enter]   org.h2.Driver
Driver
[Enter]   sa
User      amit
[Enter]   Hide
Password  amit
Connected
Commands are case insensitive; SQL statements end with ';'
help or ?      Display this help
list           Toggle result list / stack trace mode
maxwidth       Set maximum column width (default is 100)
autocommit     Enable or disable autocommit
history        Show the last 20 statements
quit or exit   Close the connection and exit

sql> show tables;
TABLE_NAME | TABLE_SCHEMA
AA         | PUBLIC
AMIT       | PUBLIC
(2 rows, 23 ms)

H2 database in Embedded , Memory mode :-

bash-3.2$ java -cp h2-1.3.170.jar org.h2.tools.Shell

Welcome to H2 Shell 1.3.170 (2012-11-30)
Exit with Ctrl+C
[Enter]   jdbc:h2:~/test
URL       jdbc:h2:mem:
[Enter]   org.h2.Driver
Driver
[Enter]   sa
User      amit_mem
[Enter]   Hide
Password  amit_mem
Connected
Commands are case insensitive; SQL statements end with ';'
help or ?      Display this help
list           Toggle result list / stack trace mode
maxwidth       Set maximum column width (default is 100)
autocommit     Enable or disable autocommit
history        Show the last 20 statements
quit or exit   Close the connection and exit

sql> show tables;
TABLE_NAME | TABLE_SCHEMA
(0 rows, 221 ms)
sql>

2. Server Mode :-  In this mode an application open a database residing on another server. A server can be started on same or another virtual machine. Many applications can connect to same database at same time . Internally server process open database in Embedded mode. Server process is slower than embedded mode as data is transferred over TCP/IP.

H2 database in Server mode (persistent mode) :-

1. First start the server 

bash-3.2$ pwd
/home/oracle/h2/bin
bash-3.2$ java -cp h2-1.3.170.jar org.h2.tools.Server
Web Console server running at http://10.10.7.11:8082 (only local connections)
Failed to start a browser to open the URL http://10.10.7.11:8082: Browser detection failed and system property h2.browser not set
TCP server running at tcp://10.10.7.11:9092 (only local connections)
PG server running at pg:// 10.10.7.11:5435 (only local connections)

2. open local connections to this server from another session :-

bash-3.2$ java -cp h2-1.3.170.jar org.h2.tools.Shell

Welcome to H2 Shell 1.3.170 (2012-11-30)
Exit with Ctrl+C
[Enter]   jdbc:h2:~/test
URL       jdbc:h2:tcp://10.10.7.11:9092/~/amit
[Enter]   org.h2.Driver
Driver
[Enter]   sa
User      incms
[Enter]   Hide
Password  incms
Connected
Commands are case insensitive; SQL statements end with ';'
help or ?      Display this help
list           Toggle result list / stack trace mode
maxwidth       Set maximum column width (default is 100)
autocommit     Enable or disable autocommit
history        Show the last 20 statements
quit or exit   Close the connection and exit


sql> 
show tables;
TABLE_NAME | TABLE_SCHEMA
AA         | PUBLIC
AMIT       | PUBLIC
(2 rows, 64 ms)

H2 database in Server mode (Memory mode) :- We can open a IN memory H2 database in Server mode also. Its called a Named Memory H2 database. We can open Named memory H2 database from multiple sessions :-

bash-3.2$ java -cp h2-1.3.170.jar org.h2.tools.Shell

Welcome to H2 Shell 1.3.170 (2012-11-30)
Exit with Ctrl+C
[Enter]   jdbc:h2:~/test
URL       jdbc:h2:tcp://10.10.7.11:9092/mem:memory
[Enter]   org.h2.Driver
Driver
[Enter]   sa
User      incms
[Enter]   Hide
Password  incms121
Connected
Commands are case insensitive; SQL statements end with ';'
help or ?      Display this help
list           Toggle result list / stack trace mode
maxwidth       Set maximum column width (default is 100)
autocommit     Enable or disable autocommit
history        Show the last 20 statements
quit or exit   Close the connection and exit

sql>  create table aaa
...> ( id number, name varchar2(12));
(Update count: 0, 0 ms)
sql>
sql> insert into aaa values ('1' ,'amit');
(Update count: 1, 60 ms)
sql> insert into aaa values ('21' ,'sunil');
(Update count: 1, 0 ms)
sql> commit;
(Update count: 0, 0 ms)
sql> select * from aaa;
ID | NAME
1  | amit
21 | sunil
(2 rows, 150 ms)
sql>

Open this memory database from another session :-

bash-3.2$ java -cp h2-1.3.170.jar org.h2.tools.Shell

Welcome to H2 Shell 1.3.170 (2012-11-30)
Exit with Ctrl+C
[Enter]   jdbc:h2:~/test
URL       jdbc:h2:tcp://10.10.7.11:9092/mem:memory
[Enter]   org.h2.Driver
Driver
[Enter]   sa
User      incms
[Enter]   Hide
Password  incms121
Connected
Commands are case insensitive; SQL statements end with ';'
help or ?      Display this help
list           Toggle result list / stack trace mode
maxwidth       Set maximum column width (default is 100)
autocommit     Enable or disable autocommit
history        Show the last 20 statements
quit or exit   Close the connection and exit

sql> select * from aaa;
.ID | NAME
1  | amit
21 | sunil
(2 rows, 139 ms)

For more clarifications related to H2 database visit http://www.h2database.com//html/main.html 

I hope this article helped you.

Regards,
Amit Rath

Wednesday, October 23, 2013

Profiles in Oracle

Profiles are used for restricting access to Oracle Database. Default Profile of Oracle Database is "DEFAULT". We can also create our own profile with our own user defined restrictions.

In order to use Profiles limits on a Oracle User, Parameter resource_limit must be set to true.

Once we assign a profile to user then that user cannot exceeds limits defined in that profile.

There are two type of parameters in Profile :-

1. Resource Parameters :-  Parameters related to sessions, CPU, connect_time, idle_time, Logical_reads , private_sga comes under this                                                        category.

  a. Session_per_user :- Specify number of concurrent sessions for a user.
  b. Cpu_per_session :- Specify CPU time limit for a session, expressed in hundredth of second.
  c. Cpu_per_call       :- Specify CPU time limit for a call,  expressed in hundredth of second.
  d. Connect_time      :- Specify the time limit for a session, expressed in minutes.
  e. Logical_reads_per_session :- Specify number of data blocks reads in a session.
  f.  Logical_reads_per_cal       :- Specify number of data blocks read for a call to process a sql statement.
  g. Private_SGA       :-  Specify the amount of private space a session can have.

2. Password Parameters :- Parameters sets length of time are defined in number of days, however we can specify minutes(n/1440) or                                                         seconds (n/86400) also.

   a. Failed_login_attempts :- Specify number of failed attempts before a account is locked.
   b. Password_life_time    :-  Specify the life time of a password in number of days.
   c. Password_lock_time  :-  Specify number of days account will be locked after failed login attempts.
   d. Password_grace_time :- Specify the grace period given to a user after it exceeds failed login attempts, if in grace time user has not        changed its password, it will be lock.
   e. Password_verify_function :- its a PL/SQL function which checks that complexity of a password.
   f. Password_reuse_time and Password_reuse_max :- both are used in conjunction, three values can be possible for both of them :-
       1. Both can be set as integer. For eg Password_reuse_time is 50 and Password_reuse_max is 5. In this case user can reuse old            password after 50 days and after changing 5 times.
      2. One is set to integer and another to unlimited, In this case user cannot reuse a password.
      3. Both set to unlimited then database ignores both of them.

SQL> show parameter resource

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_limit                       boolean     FALSE

SQL> alter system set resource_limit=TRUE;

System altered.

Profile creation :-

CREATE PROFILE AMIT_PROFILE LIMIT
  SESSIONS_PER_USER unlimited
  CPU_PER_SESSION DEFAULT
  CPU_PER_CALL DEFAULT
  CONNECT_TIME unlimited
  IDLE_TIME unlimited
  LOGICAL_READS_PER_SESSION DEFAULT
  LOGICAL_READS_PER_CALL DEFAULT
  COMPOSITE_LIMIT DEFAULT
  PRIVATE_SGA DEFAULT
  FAILED_LOGIN_ATTEMPTS unlimited
  PASSWORD_LIFE_TIME unlimited
  PASSWORD_REUSE_TIME unlimited
  PASSWORD_REUSE_MAX UNLIMITED
  PASSWORD_LOCK_TIME 1
  PASSWORD_GRACE_TIME 7
  PASSWORD_VERIFY_FUNCTION NULL;

A resource in a profile can have three different values :-

1. UNLIMITED :- when a resource has value as UNLIMITED , then user can use unlimited amount of this resource.

2. DEFAULT :- If value of a resource is DEFAULT, then that resource is assigned value as it has in DEFAULT profile

3. Number (1,2,3) :- If a value is assigned to a resource, then that resource cannot exceeds that value.

How to Alter a Profile :-

Alter Profile DBA LIMIT <profile_item_name> <value> ;
eg:- Alter Profile Amit_profile LIMIT SESSIONS_PER_USER  10;

How to assign a Profile to a user :-

a. Assigning a profile along with user creation :-
   Create user Amit identified by amit profile Amit_profile;

b. Assigning a profile after user creation :-
   Alter user Amit profile Amit_profile;

I hope this article helped you.

Regards,
Amit Rath

Tuesday, October 15, 2013

Concept of ROWNUM, Why ROWNUM=2 gives "no rows selected

ROWNUM is a psuedocolumn , its value is not predefined in a table. A number is assigned in the form of 1,2,3,...N to every row returned by a query when its executed, that number is called as ROWNUM.  Its generated dynamically.

Example :- Consider a table having 5 rows. PFB output of below select queries :-

1. SQL> select * from amit;

        ID NAME
---------- ------------------------------------
         1
         2
         3
         4
         5

2. SQL> select * from amit where rownum=1;

        ID NAME
---------- ------------------------------------
         1

3. SQL> select * from amit where rownum<3;

        ID NAME
---------- ------------------------------------
         1
         2

4. SQL> select * from amit where rownum=2;

no rows selected

5. SQL> select * from amit where rownum>2;

no rows selected

We see that when we use Rownum in Where clause then it gives output only for rownum<3 and Rownum=1 . But when we we use rownum=2 and rownum>2 it gives output as "no rown selected". 

Reason for this behaviour of rownum is that its value is not predefined. when a select query fetch some output then a number is assingned in the form of 1,2,3...N to all rows fetched by query.First selected row is always assigned as rownum=1.

Rownum value is incremented after a query passes the where clause.

So when we use rownum=1 or rownum<3 in where clause then where condition is matched as rownum=1 at that time value of rownum is 1 so it passes the where condition and gives us the output. IF rownum < 3 has been used in where clause then it gives us output for rownum=1 as it passes where clasuse, now rownum value incremented and becomes 2 again it passes where clause (rownum<3 ). Now  rownum value incremented and becomes 3, when rownum value becomes 3 then it fails the where clause(rownum<3) and gives us only two rows as output.

But when We have rownum=2 or rownum>2 then numbers is assigned to the output as 1,2,3..N , when first row's rownum which is 1 is matched with rownum=2 or rownum>2 then at this time rownum has value as 1 and it fails the where condition(rownum=2 or rownum>2) criteria on first attempt and gives us output as "no rows selected" 

We have to remember this statement that "Rownum is not preassigned in table"

I hope this article helped you.

Regards,
Amit Rath

Monday, September 30, 2013

Disable Case Sensitive in Oracle Database 11g

Prior to 11g Passwords in Oracle are not case sensitive. Now from 11g Oracle passwords are Case sensitive. Parameter sec_case_sensitive_logon has been included in Oracle 11g.

SQL> show parameter logon

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
sec_case_sensitive_logon             boolean                           TRUE

SQL> conn amit/AMIT
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL> conn amit/amit
Connected.

Disable Password case sensitive :-

SQL> alter system set sec_case_sensitive_logon=false;

System altered.

SQL> conn amit/AMIT
Connected.

Case Sensitive has been Disabled.

I hope this article helped you.

Regards,
Amit Rath

How to RollBack a Database PSU

PFB steps to Rollback a Database PSU applied on Oracle Database :-

In my case Database PSU is 10.2.0.5.12 and Database is 10.2.0.5

1. Check details of patch applied :-


[oracle@localhost admin]$ opatch lsinventory
Invoking OPatch 10.2.0.5.1

Oracle Interim Patch Installer version 10.2.0.5.1
Copyright (c) 2010, Oracle Corporation.  All rights reserved.

Oracle Home       : /oracle_home/app
Central Inventory : /oracle11gr2/app/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 10.2.0.5.1
OUI version       : 10.2.0.5.0
OUI location      : /oracle_home/app/oui
Log file location : /oracle_home/app/cfgtoollogs/opatch/opatch2013-09-30_09-59-14AM.log

Patch history file: /oracle_home/app/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /oracle_home/app/cfgtoollogs/opatch/lsinv/lsinventory2013-09-30_09-59-14AM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (2):

Oracle Database 10g                                                  10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 4                            10.2.0.5.0
There are 2 products installed in this Oracle Home.

Interim patches (1) :

Patch  16619894     : applied on Mon Sep 30 09:57:21 GMT+05:30 2013
Unique Patch ID:  16519126
   Created on 28 Jun 2013, 01:27:47 hrs PST8PDT
   Bugs fixed:
     13596521, 8865718, 11790175, 13489660, 9020537, 9772888, 8650138
     8664189, 10091698, 14275629, 14469008, 10092858, 12551710, 7519406
     9821321, 13349665, 8771916, 7509714, 16619894, 8822531, 10139235
     10159846, 13257247, 8350262, 11792865, 7119382, 13632738, 11724962
     16309604, 16309605, 16309606, 8966823, 9320130, 16961614, 16961615
     13775862, 16961616, 11674645, 16961617, 16961618, 15877957, 7026523
     16961619, 15877958, 15877959, 9399589, 14841459, 9672816, 13503598
     9499302, 9150282, 9448311, 9659614, 13632743, 14220725, 9949948, 8882576
     10327179, 7612454, 7111619, 9711859, 9714832, 9735237, 9952230, 15877960
     12780098, 13561951, 15877961, 15877962, 14665116, 15877963, 8660422
     11066597, 16703112, 16279401, 14546673, 14105702, 14459552, 9713537
     14105703, 14105704, 13483152, 13737773, 13737775, 14269955, 12925532
     12748240, 9694101, 14390396, 12862186, 12862187, 10249537, 14727319
     9586877, 8211733, 6694396, 9548269, 7115910, 7710224, 9337325, 8354642
     7602341, 14076510, 10157402, 11856395, 12565867, 6402302, 10327190
     10269717, 13015379, 11693109, 14023636, 10017048, 8546356, 8394351
     9024850, 13561750, 8224558, 9770451, 9360157, 8488233, 9109487, 10132870
     14841558, 9171933, 16817117, 10173237, 9532911, 10068982, 7361418
     10306945, 8666117, 11725006, 6157713, 10214450, 9184754, 14205448
     8544696, 9767674, 16306019, 9323583, 8277300, 13343467, 16279211
     9726739, 16382448, 13791364, 8412426, 10326338, 10165083, 10208905
     12419392, 6651220, 9145204, 13554409, 11076894, 7450366, 11893577
     8970313, 14492313, 6690853, 6011045, 14492314, 11814891, 10162036
     14492315, 10248542, 14492316, 16742123, 9469117, 13359623, 9952270
     9842573, 13343471, 12710774, 10324526, 14546638, 12419258, 9322219
     8636407, 16056270, 10010310, 12828105, 9689310, 9390484, 13736501
     13736502, 9824435, 13736503, 13736504, 13736505, 13736506, 9963497
     9032322, 13736507, 12551700, 12551701, 14035825, 12551702, 11858315
     12551703, 12551704, 10076669, 16270946, 12551705, 12551706, 14040433
     12551707, 6076890, 14258925, 12551708, 9308296, 13916709, 12827745
     12880299, 14038805, 13923855, 9072105, 8528171, 11737047
--------------------------------------------------------------------------------

OPatch succeeded.

2. Shutdown Database and listener :-

[oracle@localhost admin]$ sqlplus

SQL*Plus: Release 10.2.0.5.0 - Production on Mon Sep 30 10:02:42 2013

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Enter user-name: /as sysdba

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

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@localhost admin]$ lsnrctl stop

LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 30-SEP-2013 10:06:03

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
The command completed successfully
[oracle@localhost admin]$

3. Before Rollback check that "catbundle_PSU_<DATABASE_NAME>_ROLLBACK.sql" file exist in ORACLE_HOME/rdbms/admin. if exists start the Rollback process :-

[oracle@localhost DBSOFT]$ opatch rollback -id 16619894
Invoking OPatch 10.2.0.5.1

Oracle Interim Patch Installer version 10.2.0.5.1
Copyright (c) 2010, Oracle Corporation.  All rights reserved.

Oracle Home       : /oracle_home/app
Central Inventory : /oracle11gr2/app/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 10.2.0.5.1
OUI version       : 10.2.0.5.0
OUI location      : /oracle_home/app/oui
Log file location : /oracle_home/app/cfgtoollogs/opatch/opatch2013-09-30_10-08-49AM.log

Patch history file: /oracle_home/app/cfgtoollogs/opatch/opatch_history.txt

RollbackSession rolling back interim patch '16619894' from OH '/oracle_home/app'

Running prerequisite checks...

OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/oracle_home/app')

Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files affected by the patch '16619894' for restore. This might take a while...
Execution of 'sh /oracle_home/app/.patch_storage/16619894_Jun_28_2013_01_27_47/original_patch/custom/scripts/pre -rollback 16619894 ':

Return Code = 0

Patching component oracle.rdbms, 10.2.0.5.0...
Deleting "kstst.o" from archive "/oracle_home/app/lib/libserver10.a"
Deleting "kststqad.o" from archive "/oracle_home/app/lib/libserver10.a"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/kcbl.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/qecsel.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/ksfd.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/qkexr.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/xty.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/qergh.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/qergs.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/ktsx.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/kdt.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/kkpod.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/kdiss.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/qerix.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/knld.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/ktein.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/kkzu.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/tbsdrv.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/ktec.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/kteop.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/ktsp.o"
.
.
.
.
Updating jar file "/oracle_home/app/sysman/jlib/emjsp.jar" with "/oracle_home/app/.patch_storage/16619894_Jun_28_2013_01_27_47/files//sysman/jlib/emjsp.jar/_database/_dbclone/_dbClone__Warning$__jsp_StaticText.class"
Updating jar file "/oracle_home/app/sysman/jlib/emjsp.jar" with "/oracle_home/app/.patch_storage/16619894_Jun_28_2013_01_27_47/files//sysman/jlib/emjsp.jar/_database/_dist/_streams/_queue/_editQueue.class"
Updating jar file "/oracle_home/app/sysman/jlib/emjsp.jar" with "/oracle_home/app/.patch_storage/16619894_Jun_28_2013_01_27_47/files//sysman/jlib/emjsp.jar/_database/_dist/_streams/_queue/_editQueue$__jsp_StaticText.class"
Copying file to "/oracle_home/app/oc4j/j2ee/oc4j_applications/applications/em/em/admin/rep/editUserSummary.uix"
Copying file to "/oracle_home/app/oc4j/j2ee/oc4j_applications/applications/em/em/WEB-INF/web.xml"

Patching component oracle.xdk.rsf, 10.2.0.5.0...
Updating archive file "/oracle_home/app/lib/libxml10.a"  with "lib/libxml10.a/lpxpar.o"
Updating archive file "/oracle_home/app/lib32/libxml10.a"  with "lib32/libxml10.a/lpxpar.o"

Patching component oracle.precomp.common, 10.2.0.5.0...

Patching component oracle.rdbms.rman, 10.2.0.5.0...
Copying file to "/oracle_home/app/rdbms/admin/recover.bsq"

Patching component oracle.sdo.locator, 10.2.0.5.0...
Updating archive file "/oracle_home/app/lib/libordsdo10.a"  with "lib/libordsdo10.a/mdidx.o"
Updating archive file "/oracle_home/app/lib/libordsdo10.a"  with "lib/libordsdo10.a/mdrcr.o"
Updating archive file "/oracle_home/app/lib/libordsdo10.a"  with "lib/libordsdo10.a/mdrt.o"
Updating archive file "/oracle_home/app/lib/libordsdo10.a"  with "lib/libordsdo10.a/mdopp.o"
Updating archive file "/oracle_home/app/lib/libordsdo10.a"  with "lib/libordsdo10.a/mdgr.o"
Copying file to "/oracle_home/app/md/admin/mdprivs.sql"

Patching component oracle.network.listener, 10.2.0.5.0...

Patching component oracle.network.client, 10.2.0.5.0...
Copying file to "/oracle_home/app/bin/adapters"

Patching component oracle.ovm, 10.2.0.5.0...
Copying file to "/oracle_home/app/rdbms/admin/owmr1116.plb"
Copying file to "/oracle_home/app/rdbms/admin/owmv1116.plb"

Patching component oracle.oem.oemlt, 10.2.0.5.0...
Copying file to "/oracle_home/app/rdbms/admin/execocm.sql"

Patching component oracle.javavm.server, 10.2.0.5.0...
Copying file to "/oracle_home/app/lib/libjox10.so"
Copying file to "/oracle_home/app/lib32/libjox10.so"
Running make for target client_sharedlib
Running make for target ioracle
Running make for target iwrap
Running make for target client_sharedlib
Running make for target proc
Running make for target irman
Running make for target itnslsnr
RollbackSession removing interim patch '16619894' from inventory

The local system has been patched and can be restarted.

OPatch succeeded.

4. Start database and run "catbundle_PSU_ORCL_ROLLBACK.sql" script :-

[oracle@localhost admin]$ sqlplus

SQL*Plus: Release 10.2.0.5.0 - Production on Mon Sep 30 10:10:21 2013

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Enter user-name: /as sysdba
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  599785472 bytes
Fixed Size                  2098112 bytes
Variable Size             230689856 bytes
Database Buffers          360710144 bytes
Redo Buffers                6287360 bytes
Database mounted.
Database opened.
SQL> @catbundle_PSU_ORCL_ROLLBACK.sql
SQL> COLUMN spool_file NEW_VALUE spool_file NOPRINT
SQL> SELECT '/oracle_home/app/cfgtoollogs/catbundle/' || 'catbundle_PSU_' || name || '_ROLLBACK_' || TO_CHAR(SYSDATE, 'YYYYMonDD_hh24_mi_ss', 'NLS_DATE_LANGUAGE=''AMERICAN''') || '.log' AS spool_file FROM v$database;

SQL> SPOOL &spool_file
SQL> exec dbms_registry.set_session_namespace('SERVER')

PL/SQL procedure successfully completed.

SQL> PROMPT Processing Oracle Enterprise Manager...
Processing Oracle Enterprise Manager...
SQL> ALTER SESSION SET current_schema = SYSMAN;

Session altered.

SQL> @?/sysman/admin/emdrep/sql/core/latest/ecm/ecm_util_pkgdef.sql
SQL> Rem
SQL> Rem $Header: ecm_util_pkgdef.sql 31-oct-2003.14:19:54 kchiasso Exp $
SQL> Rem
SQL> Rem ecm_util_pkgdef.sql
SQL> Rem
SQL> Rem Copyright (c) 2002, 2003, Oracle Corporation.  All rights reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem         ecm_util_pkgdef.sql - <one-line expansion of the name>
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem         <short description of component this file declares/defines>
SQL> Rem
SQL> Rem    NOTES
SQL> Rem         <other useful comments, qualifications, etc.>
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YY)
SQL> Rem    vkhizder    10/31/03 - adding functions for getting
SQL> Rem                           patchsets/patches/bugs for a home
SQL> Rem    groyal      08/28/03 - Enhance concat to support a limit
SQL> Rem    jmansur     08/21/03 - update get_clone_source to match latest design
SQL> Rem    shuberma    04/16/03 - Removing procedure that is not longer used
SQL> Rem    shuberma    02/12/03 - Document new column in returned cursor
SQL> Rem    shuberma    01/03/03 - Adding procedure for clone home source
SQL> Rem    shuberma    12/23/02 - Adding an optional argument to the concat_col call
SQL> Rem    rmenon      10/18/02 - added CONCAT_COMPONENT_VERSION definition
SQL> Rem    rpinnama    05/15/02 - rpinnama_reorg_rep_scripts
SQL> Rem    rpinnama    05/15/02 - Created
SQL> Rem
SQL>
SQL> rem********************************************************************
SQL> rem
SQL> rem  PURPOSE
SQL> rem
SQL> rem        The ECM_UTIL package contains procedures and functions for various
SQL> rem        purposes including returning a list of target names for jobs, or admins
SQL> rem        for rules.
SQL> rem
SQL> rem  PROCEDURES and FUNCTIONS
SQL> rem
SQL> rem   JOB_TARGET_LIST
SQL> rem        ARGUMENTS:  All are IN parameters unless otherwise noted.
SQL> rem          job_id -- The internal job id for which to return the target list.
SQL> rem         RETURNS: a VARCHAR2 which is the space separated target list.
SQL> rem
SQL> rem   HOST_HOME_TARGET_LIST
SQL> rem        ARGUMENTS:  All are IN parameters unless otherwise noted.
SQL> rem          host_name -- The name of the host for which to compare for ORACLE_HOME.
SQL> rem          oracle_home -- The path of the ORACLE_HOME for which to compare the host.
SQL> rem          target_type -- The type of target for which to compare for ORACLE_HOME.
SQL> rem         RETURNS: a VARCHAR2 which is the comma separated target list.
SQL> rem
SQL> rem  NOTES
SQL> rem
SQL> rem   The methods in this package do not make any assumptions about
SQL> rem   transacations.  Essentially, it's up to the call to commit or rollback,
SQL> rem   unless otherwise noted.
SQL> rem
.
.
.
.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Session altered.

Updating registry...

1 row created.

Commit complete.

Check the following log file for errors:
SQL> @?/cpu/scripts/bug11057369.sql
SQL> Rem
SQL> Rem $Header: rdbms/admin/bug11057369.sql st_server_jheng_rfi_bug-11057369/1 2011/02/25 18:31:55 jheng Exp $
SQL> Rem
SQL> Rem bug11057369.sql
SQL> Rem
SQL> Rem Copyright (c) 2011, Oracle and/or its affiliates. All rights reserved.
SQL> Rem
.
.
.
.
Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Session altered.

Updating registry...

1 row created.

Commit complete.

Check the following log file for errors:
/oracle_home/app/cfgtoollogs/catbundle/catbundle_PSU_ORCL_ROLLBACK_2013Sep30_10_11_02.log

Check Log file /oracle_home/app/cfgtoollogs/catbundle/catbundle_PSU_ORCL_ROLLBACK_2013Sep30_10_11_02.log for any errors occured.

5. Check for Invalid objects :-

SQL> @utlrp.sql

TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2013-09-30 10:11:28

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.

TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END  2013-09-30 10:11:29

PL/SQL procedure successfully completed.

DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
                  0

DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
                          0

PL/SQL procedure successfully completed.

6. Check that Patch has been successfully rollbacked :-

[oracle@localhost admin]$ opatch lsinventory
Invoking OPatch 10.2.0.5.1

Oracle Interim Patch Installer version 10.2.0.5.1
Copyright (c) 2010, Oracle Corporation.  All rights reserved.

Oracle Home       : /oracle_home/app
Central Inventory : /oracle11gr2/app/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 10.2.0.5.1
OUI version       : 10.2.0.5.0
OUI location      : /oracle_home/app/oui
Log file location : /oracle_home/app/cfgtoollogs/opatch/opatch2013-09-30_10-11-56AM.log

Patch history file: /oracle_home/app/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /oracle_home/app/cfgtoollogs/opatch/lsinv/lsinventory2013-09-30_10-11-56AM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (2):

Oracle Database 10g                                                  10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 4                            10.2.0.5.0
There are 2 products installed in this Oracle Home.

There are no Interim patches installed in this Oracle Home.
--------------------------------------------------------------------------------
OPatch succeeded.


I hope this article helped you.

Regards,
Amit Rath

Sunday, September 29, 2013

Applying Database PSU 10.2.0.5.12 to Oracle database 10.2.0.5

Yesterday I applied Database PSU (includes CPU July2013) 10.2.0.5.12 to Oracle Database version 10.2.0.5 . PFB steps to apply :-

Download the Latest PSU patch (p16619894_10205_Linux-x86-64.zip) from Metalink. In my case Patch number is 16619894.

Backup your Database and ORACLE_HOME before applying PSU.

1. Prerequisites to Apply PSU :-

[oracle@localhost ~]$ which opatch
/oracle_home/app/OPatch/opatch
[oracle@localhost ~]$
[oracle@localhost ~]$ cd /DBSOFT/
[oracle@localhost DBSOFT]$ opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ./16619894
Invoking OPatch 10.2.0.4.9

Oracle Interim Patch Installer version 10.2.0.4.9
Copyright (c) 2009, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /oracle_home/app
Central Inventory : /oracle11gr2/app/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 10.2.0.4.9
OUI version       : 10.2.0.5.0
OUI location      : /oracle_home/app/oui
Log file location : /oracle_home/app/cfgtoollogs/opatch/opatch2013-09-27_16-19-20PM.log

Patch history file: /oracle_home/app/cfgtoollogs/opatch/opatch_history.txt

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

From output its clear that My Opatch Version is lower than the Patch version which has to be applied . Patch has to be applied with same or higher OPatch version otherwise we will get below mentioned error :-

[oracle@localhost 16619894]$ opatch apply
Invoking OPatch 10.2.0.4.9

Oracle Interim Patch Installer version 10.2.0.4.9
Copyright (c) 2009, Oracle Corporation.  All rights reserved.

Oracle Home       : /oracle_home/app
Central Inventory : /oracle11gr2/app/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 10.2.0.4.9
OUI version       : 10.2.0.5.0
OUI location      : /oracle_home/app/oui
Log file location : /oracle_home/app/cfgtoollogs/opatch/opatch2013-09-27_16-32-38PM.log

Patch history file: /oracle_home/app/cfgtoollogs/opatch/opatch_history.txt

ApplySession applying interim patch '16619894' to OH '/oracle_home/app'
ApplySession failed: ApplySession failed to prepare the system.

Patch 16619894 requires OPatch version 10.2.0.5.0.
The OPatch version being used (10.2.0.4.9) doesn't meet the minimum version required by the patch(es). Please download latest OPatch from My Oracle Support.

System intact, OPatch will not attempt to restore the system

OPatch failed with error code 73

2. Download the latest Opatch version from Metalink and copy it in inside ORACLE_HOME

[oracle@localhost 16619894]$pwd
/oracle_home/app
[oracle@localhost app]$ unzip p6880880_102000_Linux-x86-64.zip
Archive:  p6880880_102000_Linux-x86-64.zip
 extracting: OPatch/ocm/ocm.zip
  inflating: OPatch/ocm/lib/osdt_jce.jar
  inflating: OPatch/ocm/lib/osdt_core3.jar
  inflating: OPatch/ocm/lib/emocmclnt-14.jar
replace OPatch/ocm/lib/emocmutl.jar? [y]es, [n]o, [A]ll, [N]one, [r]ename: y
  inflating: OPatch/ocm/lib/emocmutl.jar
  inflating: OPatch/ocm/bin/emocmrsp
  inflating: OPatch/ocm/ocm_platforms.txt
replace OPatch/crs/patch112.pl? [y]es, [n]o, [A]ll, [N]one, [r]ename: y
  inflating: OPatch/crs/patch112.pl
replace OPatch/crs/crsdelete.pm? [y]es, [n]o, [A]ll, [N]one, [r]ename: y
  inflating: OPatch/crs/crsdelete.pm
replace OPatch/crs/crspatch.pm? [y]es, [n]o, [A]ll, [N]one, [r]ename: y
  inflating: OPatch/crs/crspatch.pm
replace OPatch/crs/s_crsconfig_defs? [y]es, [n]o, [A]ll, [N]one, [r]ename: y
  inflating: OPatch/crs/s_crsconfig_defs
replace OPatch/crs/crsconfig_lib.pm? [y]es, [n]o, [A]ll, [N]one, [r]ename: A
  inflating: OPatch/crs/crsconfig_lib.pm
  inflating: OPatch/crs/oracss.pm
  inflating: OPatch/crs/auto_patch.pl
  inflating: OPatch/crs/s_crsconfig_lib.pm
  inflating: OPatch/opatch
  inflating: OPatch/opatchprereqs/opatch/runtime_prereq.xml
  inflating: OPatch/opatchprereqs/opatch/opatch_prereq.xml
  inflating: OPatch/opatchprereqs/opatch/rulemap.xml
  inflating: OPatch/opatchprereqs/prerequisite.properties
  inflating: OPatch/opatchprereqs/oui/knowledgesrc.xml
  inflating: OPatch/opatch.ini
  inflating: OPatch/emdpatch.pl
  inflating: OPatch/opatch.pl
  inflating: OPatch/jlib/opatchprereq.jar
  inflating: OPatch/jlib/opatchactions.jar
  inflating: OPatch/jlib/opatchutil.jar
  inflating: OPatch/jlib/opatchfmw.jar
  inflating: OPatch/jlib/opatchext.jar
  inflating: OPatch/jlib/opatch.jar
  inflating: OPatch/opatch.bat
  inflating: OPatch/docs/Prereq_Users_Guide.txt
  inflating: OPatch/docs/FAQ
  inflating: OPatch/docs/Users_Guide.txt
  inflating: OPatch/README.txt

[oracle@localhost OPatch]$ opatch lsinventory
Invoking OPatch 10.2.0.5.1

Oracle Interim Patch Installer version 10.2.0.5.1
Copyright (c) 2010, Oracle Corporation.  All rights reserved.

Oracle Home       : /oracle_home/app
Central Inventory : /oracle11gr2/app/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 10.2.0.5.1
OUI version       : 10.2.0.5.0
OUI location      : /oracle_home/app/oui
Log file location : /oracle_home/app/cfgtoollogs/opatch/opatch2013-09-27_16-59-28PM.log

Patch history file: /oracle_home/app/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /oracle_home/app/cfgtoollogs/opatch/lsinv/lsinventory2013-09-27_16-59-28PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (2):

Oracle Database 10g                                                  10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 4                            10.2.0.5.0
There are 2 products installed in this Oracle Home.

There are no Interim patches installed in this Oracle Home.

--------------------------------------------------------------------------------

OPatch succeeded.

Opatch version is upgraded to the latest version .

3. As Its a Single Instance Database. We need to Shutdown database as well as Listeners and EM's :-

[oracle@localhost ~]$ sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Sep 27 13:20:57 2013

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

Enter user-name: /as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
[oracle@localhost ~]$ emctl stop dbconsole
TZ set to Asia/Calcutta
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation.  All rights reserved.
http://localhost.localdomain:1158/em/console/aboutApplication
Stopping Oracle Enterprise Manager 10g Database Control ...
 ...  Stopped.
[oracle@localhost ~]$ lsnrctl stop

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 27-SEP-2013 13:23:34

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
The command completed successfully
[oracle@localhost ~]$ isqlplusctl stop
iSQL*Plus 10.2.0.1.0
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Stopping iSQL*Plus ...
iSQL*Plus stopped.

4. Apply the Patch on Database :-

[oracle@localhost OPatch]$
[oracle@localhost OPatch]$
[oracle@localhost OPatch]$ cd /DBSOFT/
[oracle@localhost DBSOFT]$ ls -ltr
total 4351288
-rw-r--r-- 1 oracle oinstall      35417 Apr 22  2010 utltzpv4.sql
drwxr-xr-x 7 oracle oinstall       4096 Nov  3  2010 OPatch
-rw-r--r-- 1 oracle oinstall  801603584 Nov  9  2011 10201_database_linux_x86_64.cpio
-rw-r--r-- 1 root   root     1239269270 Sep 24 12:41 linux.x64_11gR2_database_1of2.zip
-rw-r--r-- 1 root   root     1111416131 Sep 24 13:13 linux.x64_11gR2_database_2of2.zip
-rw-r--r-- 1 oracle oinstall       6475 Sep 27 10:32 utltzpv4.zip
-rw-r--r-- 1 oracle oinstall 1249857866 Sep 27 13:07 p8202632_10205_Linux-x86-64.zip
-rw-r--r-- 1 root   root       21714481 Sep 27 14:59 p16619894_10205_Linux-x86-64.zip
drwxr-xr-x 5 oracle oinstall       4096 Sep 27 16:01 16619894
-rw-r--r-- 1 root   root       27412455 Sep 27 16:56 p6880880_102000_Linux-x86-64.zip
[oracle@localhost DBSOFT]$
[oracle@localhost DBSOFT]$ cd 16619894/
[oracle@localhost 16619894]$ opatch apply
Invoking OPatch 10.2.0.5.1

Oracle Interim Patch Installer version 10.2.0.5.1
Copyright (c) 2010, Oracle Corporation.  All rights reserved.

Oracle Home       : /oracle_home/app
Central Inventory : /oracle11gr2/app/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 10.2.0.5.1
OUI version       : 10.2.0.5.0
OUI location      : /oracle_home/app/oui
Log file location : /oracle_home/app/cfgtoollogs/opatch/opatch2013-09-27_17-00-06PM.log

Patch history file: /oracle_home/app/cfgtoollogs/opatch/opatch_history.txt

ApplySession applying interim patch '16619894' to OH '/oracle_home/app'

Running prerequisite checks...
Patch 16619894: Optional component(s) missing : [ oracle.rdbms.dv, 10.2.0.5.0 ] , [ oracle.rdbms.dv.oc4j, 10.2.0.5.0 ] , [ oracle.network.cman, 10.2.0.5.0 ]
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name:

You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]:  Y

OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/oracle_home/app')

Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files and inventory (not for auto-rollback) for the Oracle Home
Backing up files affected by the patch '16619894' for restore. This might take a while...
Backing up files affected by the patch '16619894' for rollback. This might take a while...
Execution of 'sh /DBSOFT/16619894/custom/scripts/pre -apply 16619894 ':

Return Code = 0

Patching component oracle.rdbms, 10.2.0.5.0...
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/kcbl.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/qecsel.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/ksfd.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/qkexr.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/xty.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/qergh.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/qergs.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/ktsx.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/kdt.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/kkpod.o"
Updating archive file "/oracle_home/app/lib/libserver10.a"  with "lib/libserver10.a/kdiss.o"
.
.
.
.

Copying file to "/oracle_home/app/rdbms/admin/bundledata_PSU.xml"
Copying file to "/oracle_home/app/rdbms/lib/kkxwtp.o"
Copying file to "/oracle_home/app/rdbms/lib/ksms.o"
Copying file to "/oracle_home/app/rdbms/lib/jox.o"
Copying file to "/oracle_home/app/rdbms/mesg/oraus.msg"
Copying file to "/oracle_home/app/rdbms/mesg/oraus.msb"
Copying file to "/oracle_home/app/psu/10.2.0.5.12/catpsu.sql"
Copying file to "/oracle_home/app/psu/10.2.0.5.12/catpsu_rollback.sql"

Patching component oracle.rdbms.rsf, 10.2.0.5.0...
Updating archive file "/oracle_home/app/lib/libgeneric10.a"  with "lib/libgeneric10.a/qcop.o"
Updating archive file "/oracle_home/app/lib/libgeneric10.a"  with "lib/libgeneric10.a/kgh.o"
Updating archive file "/oracle_home/app/lib/libgeneric10.a"  with "lib/libgeneric10.a/kgl.o"
Updating archive file "/oracle_home/app/lib/libgeneric10.a"  with "lib/libgeneric10.a/kgl2.o"
Updating archive file "/oracle_home/app/lib/libgeneric10.a"  with "lib/libgeneric10.a/qcs2.o"
Updating archive file "/oracle_home/app/lib/libgeneric10.a"  with "lib/libgeneric10.a/kgx.o"
Updating archive file "/oracle_home/app/lib/libgeneric10.a"  with "lib/libgeneric10.a/kgkp.o"
Updating archive file "/oracle_home/app/lib/libgeneric10.a"  with "lib/libgeneric10.a/kgsk.o"
Updating archive file "/oracle_home/app/lib/libgeneric10.a"  with "lib/libgeneric10.a/kgski.o"
Updating archive file "/oracle_home/app/lib/libgeneric10.a"  with "lib/libgeneric10.a/kgls.o"
.
.
.
.

Patching component oracle.javavm.server, 10.2.0.5.0...
Copying file to "/oracle_home/app/lib/libjox10.so"
Copying file to "/oracle_home/app/lib32/libjox10.so"
Running make for target client_sharedlib
Running make for target ioracle
Running make for target iwrap
Running make for target client_sharedlib
Running make for target proc
Running make for target irman
Running make for target itnslsnr
ApplySession adding interim patch '16619894' to inventory

Verifying the update...
Inventory check OK: Patch ID 16619894 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 16619894 are present in Oracle Home.

The local system has been patched and can be restarted.

OPatch succeeded.
[oracle@localhost 16619894]$

Patch has been Successfully applied. You can Check Logfile for any errors 
/oracle_home/app/cfgtoollogs/opatch/opatch2013-09-27_17-00-06PM.log

5. Post Installation Instructions :-

a. Start Database :-

[oracle@localhost admin]$ sqlplus

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Sep 27 17:06:22 2013

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Enter user-name: /as sysdba
Connected to an idle instance.

SQL>
SQL> startup
ORACLE instance started.

Total System Global Area  599785472 bytes
Fixed Size                  2098112 bytes
Variable Size             218106944 bytes
Database Buffers          373293056 bytes
Redo Buffers                6287360 bytes
Database mounted.
Database opened.

Run the CatBundle script :-

SQL> @catbundle.sql psu apply

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Generating apply and rollback scripts...
Check the following file for errors:
/oracle_home/app/cfgtoollogs/catbundle/catbundle_PSU_ORCL_GENERATE_2013Sep27_17_06_50.log
Apply script: /oracle_home/app/rdbms/admin/catbundle_PSU_ORCL_APPLY.sql
Rollback script: /oracle_home/app/rdbms/admin/catbundle_PSU_ORCL_ROLLBACK.sql

PL/SQL procedure successfully completed.

Executing script file...

SQL> COLUMN spool_file NEW_VALUE spool_file NOPRINT
SQL> SELECT '/oracle_home/app/cfgtoollogs/catbundle/' || 'catbundle_PSU_' || name || '_APPLY_' || TO_CHAR(SYSDATE, 'YYYYMonDD_hh24_mi_ss', 'NLS_DATE_LANGUAGE=''AMERICAN''') || '.log' AS spool_file FROM v$database;

SQL> SPOOL &spool_file
SQL> exec dbms_registry.set_session_namespace('SERVER')

PL/SQL procedure successfully completed.

SQL> PROMPT Processing Oracle Enterprise Manager...
Processing Oracle Enterprise Manager...
SQL> ALTER SESSION SET current_schema = SYSMAN;

Session altered.

SQL> @?/sysman/admin/emdrep/sql/core/latest/ecm/ecm_util_pkgdef.sql
SQL> Rem
SQL> Rem $Header: emdw_src_1/source/oracle/sysman/emdrep/sql/core/latest/ecm/ecm_util_pkgdef.sql /st_recommended_10.2.0.5.0sa/1 2011/04/27 02:54:51 agor Exp $
SQL> Rem
SQL> Rem ecm_util_pkgdef.sql
SQL> Rem
SQL> Rem Copyright (c) 2002, 2011, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem      ecm_util_pkgdef.sql - <one-line expansion of the name>
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem      <short description of component this file declares/defines>
SQL> Rem
SQL> Rem    NOTES
SQL> Rem      <other useful comments, qualifications, etc.>
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YY)
SQL> Rem    agor        04/23/11 - Backport of txn niramach_bug-8837368 .
SQL> Rem    vkhizder    10/31/03 - adding functions for getting
SQL> Rem                           patchsets/patches/bugs for a home
SQL> Rem    groyal      08/28/03 - Enhance concat to support a limit
SQL> Rem    jmansur     08/21/03 - update get_clone_source to match latest design
SQL> Rem    shuberma    04/16/03 - Removing procedure that is not longer used
SQL> Rem    shuberma    02/12/03 - Document new column in returned cursor
SQL> Rem    shuberma    01/03/03 - Adding procedure for clone home source
SQL> Rem    shuberma    12/23/02 - Adding an optional argument to the concat_col call
SQL> Rem    rmenon      10/18/02 - added CONCAT_COMPONENT_VERSION definition
SQL> Rem    rpinnama    05/15/02 - rpinnama_reorg_rep_scripts
SQL> Rem    rpinnama    05/15/02 - Created
SQL> Rem
.
.
.
.
.
.
..
Grant succeeded.

Grant succeeded.

Grant succeeded.

Session altered.

Updating registry...

1 row created.

Commit complete.

Check the following log file for errors:
/oracle_home/app/cfgtoollogs/catbundle/catbundle_PSU_ORCL_APPLY_2013Sep27_17_06_57.log

Cat Bundle script Completed . 

b. Check for invalid Objects :-

SQL>  @utlrp.sql

TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2013-09-28 00:04:49

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.

TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END  2013-09-28 00:04:50

PL/SQL procedure successfully completed.

DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
                  0

DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
                          0

PL/SQL procedure successfully completed.

No Invalid Objects . PSU applied Successfully on Database.

6. Check with OPacth lsinventory that Patch successfully applied and bugs are fixed or not :-

[oracle@localhost admin]$ opatch lsinventory
Invoking OPatch 10.2.0.5.1

Oracle Interim Patch Installer version 10.2.0.5.1
Copyright (c) 2010, Oracle Corporation.  All rights reserved.

Oracle Home       : /oracle_home/app
Central Inventory : /oracle11gr2/app/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 10.2.0.5.1
OUI version       : 10.2.0.5.0
OUI location      : /oracle_home/app/oui
Log file location : /oracle_home/app/cfgtoollogs/opatch/opatch2013-09-28_00-06-02AM.log

Patch history file: /oracle_home/app/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /oracle_home/app/cfgtoollogs/opatch/lsinv/lsinventory2013-09-28_00-06-02AM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (2):

Oracle Database 10g                                                  10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 4                            10.2.0.5.0
There are 2 products installed in this Oracle Home.

Interim patches (1) :

Patch  16619894     : applied on Fri Sep 27 17:02:06 GMT+05:30 2013
Unique Patch ID:  16519126
   Created on 28 Jun 2013, 01:27:47 hrs PST8PDT
   Bugs fixed:
     13596521, 8865718, 11790175, 13489660, 9020537, 9772888, 8650138
     8664189, 10091698, 14275629, 14469008, 10092858, 12551710, 7519406
     9821321, 13349665, 8771916, 7509714, 16619894, 8822531, 10139235
     10159846, 13257247, 8350262, 11792865, 7119382, 13632738, 11724962
     16309604, 16309605, 16309606, 8966823, 9320130, 16961614, 16961615
     13775862, 16961616, 11674645, 16961617, 16961618, 15877957, 7026523
     16961619, 15877958, 15877959, 9399589, 14841459, 9672816, 13503598
     9499302, 9150282, 9448311, 9659614, 13632743, 14220725, 9949948, 8882576
     10327179, 7612454, 7111619, 9711859, 9714832, 9735237, 9952230, 15877960
     12780098, 13561951, 15877961, 15877962, 14665116, 15877963, 8660422
     11066597, 16703112, 16279401, 14546673, 14105702, 14459552, 9713537
     14105703, 14105704, 13483152, 13737773, 13737775, 14269955, 12925532
     12748240, 9694101, 14390396, 12862186, 12862187, 10249537, 14727319
     9586877, 8211733, 6694396, 9548269, 7115910, 7710224, 9337325, 8354642
     7602341, 14076510, 10157402, 11856395, 12565867, 6402302, 10327190
     10269717, 13015379, 11693109, 14023636, 10017048, 8546356, 8394351
     9024850, 13561750, 8224558, 9770451, 9360157, 8488233, 9109487, 10132870
     14841558, 9171933, 16817117, 10173237, 9532911, 10068982, 7361418
     10306945, 8666117, 11725006, 6157713, 10214450, 9184754, 14205448
     8544696, 9767674, 16306019, 9323583, 8277300, 13343467, 16279211
     9726739, 16382448, 13791364, 8412426, 10326338, 10165083, 10208905
     12419392, 6651220, 9145204, 13554409, 11076894, 7450366, 11893577
     8970313, 14492313, 6690853, 6011045, 14492314, 11814891, 10162036
     14492315, 10248542, 14492316, 16742123, 9469117, 13359623, 9952270
     9842573, 13343471, 12710774, 10324526, 14546638, 12419258, 9322219
     8636407, 16056270, 10010310, 12828105, 9689310, 9390484, 13736501
     13736502, 9824435, 13736503, 13736504, 13736505, 13736506, 9963497
     9032322, 13736507, 12551700, 12551701, 14035825, 12551702, 11858315
     12551703, 12551704, 10076669, 16270946, 12551705, 12551706, 14040433
     12551707, 6076890, 14258925, 12551708, 9308296, 13916709, 12827745
     12880299, 14038805, 13923855, 9072105, 8528171, 11737047
--------------------------------------------------------------------------------

OPatch succeeded.

I hope this article helped you.

Regards,
Amit Rath