Error
Page
|
|||
|
|
Component
|
Processes
|
Owner
|
|
Cluster
Ready Service (CRS)
|
crsd
|
Root
|
|
Cluster
Synchronization Service (CSS)
|
ocssd,cssdmonitor,
cssdagent
|
grid owner,root,root
|
|
Event
Manager (EVM)
|
evmd,
evmlogger
|
grid owner
|
|
Cluster Time
Synchronization Service
(CTSS)
|
octssd
|
root
|
|
Oracle
Notification Service (ONS)
|
ons,
eons
|
grid owner
|
|
Oracle
Agent
|
oraagent
|
grid owner
|
|
Oracle
Root Agent
|
orarootagent
|
root
|
|
Grid
Naming Service (GNS)
|
gnsd
|
root
|
|
Grid
Plug and Play (GPnP)
|
gpnpd
|
grid owner
|
|
Multicast
domain name service (mDNS)
|
mdnsd
|
grid owner
|
COLUMN CLIENT_INFO FORMAT a30 COLUMN SID FORMAT 999 COLUMN SPID FORMAT 9999 SELECT s.sid, p.spid, s.client_info,s.event FROM v$process p, v$session sWHERE p.addr = s.paddr AND CLIENT_INFO LIKE 'rman%';
I wish those sample examples help you out.SQL> SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM V$SESSION_LONGOPS WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK; SID SERIAL# CONTEXT SOFAR TOTALWORK %_COMPLETE --- ------- ------- ------- --------- ---------- 20 90 1 9470 15360 61.65 39 100 1 15871 28160 56.36
[oracle@radbstage1 oracleup3]$ csscan FULL=Y FROMCHAR=AR8ISO8859P6 TOCHAR=AL32UTF8 ARRAY=1024000 LOG=charcheck CAPTURE=Y PROCESS=4Character Set Scanner v2.2 : Release 11.2.0.3.0 - Production on Wed Sep 5 12:07:14 2012Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.Username: sys/password as sysdbaConnected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, Oracle Label Security, OLAP, Data Mining,Oracle Database Vault and Real Application Testing optionsEnumerating tables to scan.... process 1 scanning SYSTEM.AUD$[AAAOQsAABAAAOZYAAA]. process 2 scanning SYS.SOURCE$[AAAADgAABAAAAXgAAA]].................... process 1 scanning SYSMAN.MGMT_TYPE_PROPERTIES[AAAOg9AACAAAF5wAAA]Message 9999 not found; product=CSMIG; facility=CSS. process 3 scanning SYSMAN.MGMT_ARU_OUI_COMPONENTS[AAAOljAACAAAGBIAAA]. ................... process 1 scanning SYS.DUAL[AAAAB0AABAAAAOgAAA]Message 9999 not found; product=CSMIG; facility=CSS. process 4 scanning SYS.TRIGGERJAVAC$[AAAACoAABAAAAU4AAA]]. ..................................... process 4 scanning SYSMAN.MGMT_TASK_QTABLE[AAAOYKAACAAAFY4AAA]Message 9999 not found; product=CSMIG; facility=CSS. process 2 scanning SYSMAN.MGMT_CATEGORIES[AAAOg2AACAAAF64AAA]A]....................................... process 2 scanning EXFSYS.RLM$VALIDPRIVSCreating Database Scan Summary Report...Creating Individual Exception Report...Scanner terminated successfully.
;;;
Export: Release 11.2.0.3.0 - Production on Wed Sep 5 10:01:57 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01": /******** AS SYSDBA directory=DATA_PUMP_DIR dumpfile=schemas-backup-5-9-2012.dmp logfile=schemas-backup-5-9-2012.log schemas=APPADMTST,RFETECMUCADM,RFETECMUCAPP,RFETEPRTADM,RFETEPRTAPP,RFETEUCADM,RFETEUCAPP,RFETEUCDAT,RTETECMUCADM,RTETECMUCAPP,RTETEPRTADM,RTETEPRTAPP,RTETEUCADM,RTETEUCAPP,RTETEUCDAT
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 3.327 GB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
. . exported "RTETEUCADM"."SCH_T_FILE_PART" 149.8 MB 604267 rows
. . exported "RTETEUCADM"."IM_L_TRANSACTION_BLOCK" 124.2 MB 1599134 rows
............................................
. . exported "RTETEUCDAT"."ROAM_TAP_AGG":"DEF" 0 KB 0 rows
. . exported "RTETEUCDAT"."SUBS_BSCS":"DEF" 0 KB 0 rows
. . exported "RTETEUCDAT"."SUBS_BSCS_AGG":"DEF" 0 KB 0 rows
. . exported "RTETEUCDAT"."TAP_OUT_IOT" 0 KB 0 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/oracle/oracle11/product/11.2.0/dbhome_1/rdbms/log/schemas-backup-5-9-2012.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:05:00
Data Dictionary Tables: Datatype Changeless Convertible Truncation Lossy --------------------- ---------------- ---------------- ---------------- ---------------- VARCHAR2 18,427,017 412 0 0 CHAR 16,903 0 0 0 LONG 204,199 0 0 0 CLOB 1,336,770 31,521 0 0 VARRAY 20,577 0 0 0 --------------------- ---------------- ---------------- ---------------- ---------------- Total 20,005,466 31,933 0 0 Total in percentage 99.841% 0.159% 0.000% 0.000%
conn / AS sysdba
SELECT DISTINCT z.owner_name
|| '.'
|| z.table_name
|| '('
|| z.column_name
|| ') - '
|| z.column_type
|| ' - '
|| z.error_type
|| ' ' UserColumns
FROM csmig.csmv$errors z
WHERE z.owner_name NOT IN
(SELECT DISTINCT username FROM csmig.csm$dictusers
)
ORDER BY UserColumns
/
conn / AS sysdba
SELECT DISTINCT z.owner_name
|| '.'
|| z.table_name
|| '('
|| z.column_name
|| ') - '
|| z.column_type
|| ' - '
|| z.error_type
|| ' ' NotHandledDataDictColumns
FROM csmig.csmv$errors z
WHERE z.owner_name IN
(SELECT DISTINCT username FROM csmig.csm$dictusers
) minus
SELECT DISTINCT z.owner_name
|| '.'
|| z.table_name
|| '('
|| z.column_name
|| ') - '
|| z.column_type
|| ' - '
|| z.error_type
|| ' ' DataDictConvCLob
FROM csmig.csmv$errors z
WHERE z.error_type ='CONVERTIBLE'
AND z.column_type = 'CLOB'
AND z.owner_name IN
(SELECT DISTINCT username FROM csmig.csm$dictusers
)
ORDER BY NotHandledDataDictColumns
/
Conn / as sysdba -- check the current retention of stats -- the default value is 31 select systimestamp - dbms_stats.get_stats_history_availability from dual; -- now disable stats retention exec dbms_stats.alter_stats_history_retention(0); -- remove all stats
DECLAREts TIMESTAMP WITH TIME ZONE;BEGIN SELECT SYSTIMESTAMP INTO ts FROM dual; dbms_stats.purge_stats(ts); END;
-- check there are no more left
select count(*) from SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;
-- this will clear SYS.WRI$_OPTSTAT_HISTGRM_HISTORY(EPVALUE ) -- now run the csscan again to see the new fixes we did and then run csalter. exec dbms_stats.alter_stats_history_retention(31);
[Data Dictionary Conversion Summary] Data Dictionary Tables: Datatype Changeless Convertible Truncation Lossy --------------------- ---------------- ---------------- ---------------- ---------------- VARCHAR2 16,275,600 0 0 0 CHAR 16,860 0 0 0 LONG 204,199 0 0 0 CLOB 1,243,848 31,585 0 0 VARRAY 20,577 0 0 0 --------------------- ---------------- ---------------- ---------------- ---------------- Total 17,761,084 31,585 0 0 Total in percentage 99.822% 0.178% 0.000% 0.000%
The data dictionary can be safely migrated using the CSALTER script
[Application Data Conversion Summary]
Datatype Changeless Convertible Truncation Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2 0 0 0 0
CHAR 0 0 0 0
LONG 0 0 0 0
CLOB 0 0 0 0
VARRAY 0 0 0 0
--------------------- ---------------- ---------------- ---------------- ----------------
Total 0 0 0 0
Total in percentage 0.000% 0.000% 0.000% 0.000%
[oracle@radbstage1 oracleup3]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Sep 5 12:09:24 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> @?/rdbms/admin/csalter.plb
0 rows created.
Function created.
Function created.
Procedure created.
This script will update the content of the Oracle Data Dictionary.
Please ensure you have a full backup before initiating this procedure.
Would you like to proceed (Y/N)?y
old 6: if (UPPER('&conf') <> 'Y') then
new 6: if (UPPER('y') <> 'Y') then
Checking data validity...
begin converting system objects
1 row in table SYS.RULE$ is converted
2778 rows in table SYS.SCHEDULER$_EVENT_LOG are converted
2555 rows in table SYS.WRH$_SQLTEXT are converted
1788 rows in table SYSMAN.MGMT_IP_REPORT_ELEM_PARAMS are converted
20551 rows in table SYS.WRI$_ADV_SQLT_PLANS are converted
27 rows in table SYS.WRI$_REPT_FILES are converted
21 rows in table SYS.WRI$_DBU_HWM_METADATA are converted
10072 rows in table SYS.WRI$_ADV_RATIONALE are converted
130 rows in table SYSMAN.MGMT_IP_ELEM_DEFAULT_PARAMS are converted
5591 rows in table SYS.WRI$_ADV_OBJECTS are converted
31 rows in table SYSMAN.MGMT_IP_SQL_STATEMENTS are converted
3326 rows in table SYS.WRI$_ADV_ACTIONS are converted
171 rows in table SYS.WRI$_DBU_FEATURE_METADATA are converted
179 rows in table SYS.METASTYLESHEET are converted
5 rows in table SYS.WRI$_ADV_DIRECTIVE_META are converted
5301 rows in table SYS.WRI$_ADV_SQLT_PLAN_STATS are converted
64548 rows in table SYS.WRH$_SQL_PLAN are converted
164 rows in table SYS.WRI$_DBU_FEATURE_USAGE are converted
PL/SQL procedure successfully completed.
Alter the database character set...
CSALTER operation completed, please restart database
PL/SQL procedure successfully completed.
0 rows deleted.
Function dropped.
Function dropped.
Procedure dropped.
;;; Import: Release 11.2.0.3.0 - Production on Wed Sep 5 13:01:12 2012 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. ;;; Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_SCHEMA_01": /******** AS SYSDBA directory=DATA_PUMP_DIR dumpfile=schemas-backup-5-9-2012.dmp logfile=schemas-backup-import-5-9-2012.log schemas=APPADMTST,RFETECMUCADM,RFETECMUCAPP,RFETEPRTADM,RFETEPRTAPP,RFETEUCADM,RFETEUCAPP,RFETEUCDAT,RTETECMUCADM,RTETECMUCAPP,RTETEPRTADM,RTETEPRTAPP,RTETEUCADM,RTETEUCAPP,RTETEUCDAT Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM Processing object type SCHEMA_EXPORT/DB_LINK Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA . . imported "RTETEUCADM"."SCH_T_FILE_PART" 149.8 MB 604267 rows ........................................... . . imported "RTETEUCDAT"."SUBS_BSCS_AGG":"DEF" 0 KB 0 rows . . imported "RTETEUCDAT"."TAP_OUT_IOT" 0 KB 0 rows Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC Processing object type SCHEMA_EXPORT/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION Processing object type SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/VIEW/VIEW Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/TRIGGER Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA Job "SYS"."SYS_IMPORT_SCHEMA_01" completed at 13:07:53
The patch 13923374 is for stand alone database only.[oracle@caisvoim02 ~]$ unzip p6880880_112000_Linux-x86-64.zipArchive: p6880880_112000_Linux-x86-64.zipcreating: OPatch/creating: OPatch/oplan/inflating: OPatch/oplan/README.htmlinflating: OPatch/oplan/README.txt
............................................................creating: OPatch/oplan/jlib/inflating: OPatch/crs/patch112.plinflating: OPatch/crs/s_crsconfig_defsinflating: OPatch/crs/s_crsconfig_lib.pm[oracle@caisvoim02 ~]$ ls13923374 p10404530_112030_Linux-x86-64_2of7.zipOPatch p13923374_112030_Linux-x86-64.ziporadiag_oracle p6880880_112000_Linux-x86-64.zipp10404530_112030_Linux-x86-64_1of7.zip[oracle@caisvoim02 ~]$ mv $ORACLE_HOME/Opatch Opatch.original[oracle@caisvoim02 ~]$ cp -rfp OPatch $ORACLE_HOME/
To apply the patch to stand alone database with no grid infrastructure make sure to shutdown the lisener and the database and follow below steps to apply PSU to the database:[oracle@caisvoim02 ~]$ unzip p13923374_112030_Linux-x86-64.zip
Archive: p13923374_112030_Linux-x86-64.zip
creating: 13923374/
inflating: 13923374/patchmd.xml
creating: 13923374/13923374/
creating: 13923374/13923374/files/
.........................................
creating: 13923374/13343438/etc/xml/
inflating: 13923374/13343438/etc/xml/ShiphomeDirectoryStructure.xml
inflating: 13923374/13343438/etc/xml/GenericActions.xml
inflating: 13923374/README.html
[oracle@caisvoim02 ~]$ ls
13923374 p10404530_112030_Linux-x86-64_2of7.zip
OPatch p13923374_112030_Linux-x86-64.zip
oradiag_oracle p6880880_112000_Linux-x86-64.zip
p10404530_112030_Linux-x86-64_1of7.zip
[oracle@caisvoim02 dbhome_1]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Aug 29 13:12:50 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
db_name string OIMDB
db_unique_name string OIMDB
global_names boolean FALSE
instance_name string OIMDB
lock_name_space string
log_file_name_convert string
processor_group_name string
service_names string OIMDB
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
also we have to shutdown the listener:[oracle@caisvoim02 dbhome_1]$ lsnrctlLSNRCTL for Linux: Version 11.2.0.3.0 - Production on 29-AUG-2012 13:14:06Copyright (c) 1991, 2011, Oracle. All rights reserved.Welcome to LSNRCTL, type "help" for information.LSNRCTL> statusConnecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=caisvoim02)(PORT=1521)))STATUS of the LISTENER------------------------Alias LISTENERVersion TNSLSNR for Linux: Version 11.2.0.3.0 - ProductionStart Date 29-AUG-2012 13:13:57Uptime 0 days 0 hr. 0 min. 10 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /app/oracle/product/11.2.0/dbhome_1/network/admin/listener.oraListener Log File /app/oracle/diag/tnslsnr/caisvoim02/listener/alert/log.xmlListening Endpoints Summary...(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=caisvoim02)(PORT=1521)))(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))The listener supports no servicesThe command completed successfullyLSNRCTL>To apply the patch:[oracle@caisvoim02 ~]$ cd 13923374 [oracle@caisvoim02 13923374]$ /app/oracle/product/11.2.0/dbhome_1/OPatch/opatch apply Oracle Interim Patch Installer version 11.2.0.3.0 Copyright (c) 2012, Oracle Corporation. All rights reserved. Oracle Home : /app/oracle/product/11.2.0/dbhome_1 Central Inventory : /app/oraInventory from : /app/oracle/product/11.2.0/dbhome_1/oraInst.loc OPatch version : 11.2.0.3.0 OUI version : 11.2.0.3.0 Log file location : /app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2012-08-29_12-24-15PM_1.log Verifying environment and performing prerequisite checks... Prerequisite check "CheckActiveFilesAndExecutables" failed. The details are: Following executables are active : /app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed. Log file location: /app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2012-08-29_12-24-15PM_1.log OPatch failed with error code 73 [oracle@caisvoim02 13923374]$ lsnrctl LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 29-AUG-2012 11:24:45 Copyright (c) 1991, 2011, Oracle. All rights reserved. Welcome to LSNRCTL, type "help" for information. LSNRCTL> status Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=caisvoim02)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production Start Date 27-AUG-2012 15:29:38 Uptime 1 days 19 hr. 55 min. 9 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora Listener Log File /app/oracle/diag/tnslsnr/caisvoim02/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=caisvoim02)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) The listener supports no services The command completed successfully LSNRCTL> stop Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=caisvoim02)(PORT=1521))) The command completed successfully LSNRCTL> LSNRCTL> LSNRCTL> LSNRCTL> LSNRCTL> LSNRCTL> exit [oracle@caisvoim02 13923374]$ /app/oracle/product/11.2.0/dbhome_1/OPatch/opatch apply Oracle Interim Patch Installer version 11.2.0.3.0 Copyright (c) 2012, Oracle Corporation. All rights reserved. Password (optional): Unable to establish connection to Oracle Configuration Manager server. Hostname (https://ccr.oracle.com) is unknown. Unable to establish a network connection to Oracle. Specify the URL for an Oracle Support Hub in this format: http[s]://<OracleSupportHubHost>:<OracleSupportHubPort> If you do not wish to configure OCM through an Oracle Support Hub, enter NONE Oracle Support Hub URL: NONE Unable to establish a network connection to Oracle. If your systems require a proxy server for outbound Internet connections, enter the proxy server details in this format: [<proxy-user>@]<proxy-host>[:<proxy-port>] If you want to remain uninformed of critical security issues in your configuration, enter NONE Proxy specification: NONE Please shutdown Oracle instances running out of this ORACLE_HOME on the local system. (Oracle Home = '/app/oracle/product/11.2.0/dbhome_1') Is the local system ready for patching? [y|n] y User Responded with: Y Backing up files... Applying sub-patch '13343438' to OH '/app/oracle/product/11.2.0/dbhome_1' Patching component oracle.rdbms.rsf, 11.2.0.3.0... Patching component oracle.rdbms, 11.2.0.3.0... Patching component oracle.rdbms.dbscripts, 11.2.0.3.0... Verifying the update... Applying sub-patch '13696216' to OH '/app/oracle/product/11.2.0/dbhome_1' Patching component oracle.rdbms.rsf, 11.2.0.3.0... Patching component oracle.rdbms, 11.2.0.3.0... Patching component oracle.sdo.locator, 11.2.0.3.0... Patching component oracle.sysman.console.db, 11.2.0.3.0... Patching component oracle.sysman.oms.core, 10.2.0.4.4... Verifying the update... Applying sub-patch '13923374' to OH '/app/oracle/product/11.2.0/dbhome_1' ApplySession: Optional component(s) [ oracle.network.cman, 11.2.0.3.0 ] not present in the Oracle Home or a higher version is found. Patching component oracle.rdbms.rsf, 11.2.0.3.0... Patching component oracle.rdbms, 11.2.0.3.0... Patching component oracle.rdbms.dbscripts, 11.2.0.3.0... Patching component oracle.network.rsf, 11.2.0.3.0... Patching component oracle.network.listener, 11.2.0.3.0... Patching component oracle.sysman.console.db, 11.2.0.3.0... Verifying the update... Composite patch 13923374 successfully applied. Log file location: /app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2012-08-29_12-24-59PM_1.log OPatch succeeded.After applying the patch we have to load modified SQL files into the database:
SQL> startup ORACLE instance started. Total System Global Area 534462464 bytes Fixed Size 2230072 bytes Variable Size 239077576 bytes Database Buffers 285212672 bytes Redo Buffers 7942144 bytes Database mounted. Database opened. SQL> !ls $ORACLE_HOME/rdbms/admin/catbundle.sql /app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catbundle.sql SQL> @$ORACLE_HOME/rdbms/admin/catbundle.sql psu apply PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. Generating apply and rollback scripts... Check the following file for errors: /app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/catbundle/catbundle_PSU_OIMDB_GENERATE_2012Aug29_11_31_10.log Apply script: /app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catbundle_PSU_OIMDB_APPLY.sql Rollback script: /app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catbundle_PSU_OIMDB_ROLLBACK.sql PL/SQL procedure successfully completed. Executing script file... SQL> COLUMN spool_file NEW_VALUE spool_file NOPRINT SQL> SELECT '/app/oracle/product/11.2.0/dbhome_1/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 Database Packages and Types... Processing Oracle Database Packages and Types... SQL> ALTER SESSION SET current_schema = sys; Session altered. SQL> @?/psu/scripts/bug9858539.sql SQL> Rem SQL> Rem $Header: rdbms/admin/bug9858539.sql /st_rdbms_11.2.0.3.0dbpsu/1 2012/04/19 06:42:27 mjangir Exp $ SQL> Rem SQL> Rem bug9858539.sql SQL> Rem SQL> Rem Copyright (c) 2012, Oracle and/or its affiliates. All rights reserved. SQL> Rem SQL> Rem NAME SQL> Rem bug9858539.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 mjangir 04/18/12 - Created SQL> Rem SQL> SQL> SET ECHO ON SQL> SET FEEDBACK 1 SQL> SET NUMWIDTH 10 SQL> SET LINESIZE 80 SQL> SET TRIMSPOOL ON SQL> SET TAB OFF SQL> SET PAGESIZE 100 SQL> SQL> -- load XSL stylesheets connect / as sysdba SQL> SQL> execute sys.dbms_metadata_util.load_stylesheets; PL/SQL procedure successfully completed. SQL> @?/rdbms/admin/prvsawr.plb SQL> /* ------------------------------------------------------------------------- > * DBMS_SWRF_REPORT_INTERNAL PACKAGE SQL> * SQL> * This package will handle the reporting for AWR. The report main SQL> * routine will be called by the dbms_workload_repository.awr_report SQL> * function. SQL> * ------------------------------------------------------------------------- */ SQL> /* SQL> * ------------------------------------------------------------------------- SQL> * DBMS_SWRF_REPORT_INTERNAL error code summary SQL> * SQL> * -20001 : error in the report main, at the top of the error stack SQL> * -20002: internal error in ith_displayable_child routine when passed SQL> * invalid arg tree_idx SQL> * -20003: internal error in ith_displayable child routine when passed SQL> * invalid arg child_idx SQL> * -20004: invalid argument passed to dflt_align_for_type, internally SQL> * -20005: error initializing the report due to missing snapshot data SQL> * -20006: missing start value for dba_hist_librarycache SQL> * -20007: missing end value for dba_hist_librarycache SQL> * -20008: missing init.ora param SQL> * -20009: missing system stat SQL> * -20010: missing start value for dba_hist_waitstat SQL> * -20011: missing end value for dba_hist_waitstat SQL> * -20012: missing start time wait value for dba_hist_system_event SQL> * -20013: missing end time wait value for dba_hist_system_event SQL> * -20014: missing start value for DBA_HIST_LATCH gets and misses SQL> * -20015: missing end value for DBA_HIST_LATCH gets and misses SQL> * -20016: missing value for SGASTAT SQL> * -20017: Missing start value for DLM statistic SQL> * -20018: Missing end value for DLM statistic SQL> * -20019: invalid begin_snap/end_snap pair specified by user SQL> * -20020: invalid dbid/inst_num pair specified by user SQL> * -20021: missing start value for time model stat SQL> * -20022: missing end value for time model stat SQL> * -20023: missing start and end values for time model stat SQL> * -20024: failed to reset time zone SQL> * SQL> * -20100: invalid flush level specified to create_snapshot SQL> * -20101: no valid snapshots in range (bid, eid) for database id dbid SQL> * -20102: user name 'schname' is invalid SQL> * -20103: directory name 'dmpdir' is invalid SQL> * -20104: not allowed to specify the 'SYS' user SQL> * -20105: unable to move AWR data from schema to SYS SQL> * -20106: cannot move data from newer AWR schema SQL> * -20107: not allowed to move AWR data for local dbid SQL> * -20108: cannot move data from newer AWR schema SQL> * -20109: error encountered during move_to_awr SQL> * -20110: invalid Top N SQL value. not allowed to specify 0 or 1. SQL> * -20111: invalid Top N SQL string: topnsql SQL> * SQL> * -20500: Invalid input to generate ASH report SQL> * -20501: Invalid input to helper function to create report SQL> * SQL> * -20600: Invalid (dbid, version) for DB Feature Usage SQL> * -20601: Invalid (dbid, version) for High Water Mark SQL> */ SQL> create or replace package dbms_swrf_report_internal wrapped 2 a000000 15 abcd 16 abcd 17 abcd 18 abcd 19 9 20 9738 206d 21 evIH7Hvw061aB+cWkM5JC6OuMFkwg1VMHscFYaeacjrVV7y3DcdJ3qL8THiQa1paBuxmiKyb 22 jA9XGGLG7MWn6vETn7ADqvjhj5mPHXsaiVXaHinNLqTk/61XlnZ2qRqw/eqSNj4oD7zNFVX+ 135 136 / Package created. SQL> SHOW ERRORS; No errors. SQL> @?/rdbms/admin/prvtawr.plb SQL> /* ------------------------------------------------------------------------- > * DBMS_SWRF_REPORT_INTERNAL PACKAGE BODY SQL> * SQL> * This package will handle the reporting for AWR. It will have the SQL> * following interfaces: SQL> * ------------------------------------------------------------------------- */ SQL> create or replace package body dbms_swrf_report_internal wrapped 2 a000000 3 1 12 abcd 13 abcd 14 abcd 15 abcd 16 abcd 17 abcd 18 abcd 19 b 20 8a8a7 1752c 21 kJdswIBy2atlN05e2rPpIOORqLYwg4pz9hL9eMIWXz/BZN3/owOhG6BW+qHJ4JWtrrd+oXpI 22 wilIJcIXZHOba2yDPIiT0sjDZYfau/nNWwK6hhl9GzItgEFfYvdna2lcTcWOngJSdxKIkNtN 23 EzOaUW2/TzwDgSwK/gM9Bk7ADVhMhyBvCh46lKtd8DnmpSw61mn1p9fCMG7JtzFwIXV0UZGl .....................................................
...................................................... 1329 ka1oEYTwlxs7hcqGoJE+iYdHcoIUQG0UE5W3H7WGtR0rVWgD 1330 1331 / Package body created. SQL> show errors; No errors. SQL> /* -------------------------------------------- > * DBMS_WORKLOAD_REPOSITORY package body SQL> * -------------------------------------------- */ SQL> CREATE OR REPLACE PACKAGE BODY dbms_workload_repository wrapped 2 a000000 3 1 4 abcd 5 abcd ...............................
............................... 121 kDQP 122 123 / Package body created. SQL> SHOW ERRORS; No errors. SQL> @?/rdbms/admin/prvtawrs.plb SQL> CREATE OR REPLACE PACKAGE BODY dbms_awr_report_layout wrapped 2 a000000 3 1 4 abcd 5 abcd 6 abcd ..............................................
.............................................. 3114 ECNCCHK1wbVbMHoTLw== 3115 3116 / Package body created. SQL> SHOW ERRORS; No errors. SQL> ALTER SESSION SET current_schema = SYS; Session altered. SQL> PROMPT Updating registry... Updating registry... SQL> INSERT INTO registry$history 2 (action_time, action, 3 namespace, version, id, 4 bundle_series, comments) 5 VALUES 6 (SYSTIMESTAMP, 'APPLY', 7 SYS_CONTEXT('REGISTRY$CTX','NAMESPACE'), 8 '11.2.0.3', 9 3, 10 'PSU', 11 'PSU 11.2.0.3.3'); 1 row created. SQL> COMMIT; Commit complete. SQL> SPOOL off SQL> SET echo off Check the following log file for errors: /app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/catbundle/catbundle_PSU_OIMDB_APPLY_2012Aug29_11_31_11.log SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing optionsTo check the oracle logs for the loading the modified sql into the database :
[oracle@caisvoim02 13923374]$ cd $ORACLE_HOME/cfgtoollogs/catbundle [oracle@caisvoim02 catbundle]$ ls catbundle_PSU_OIMDB_APPLY_2012Aug29_11_31_11.log catbundle_PSU_OIMDB_GENERATE_2012Aug29_11_31_10.log