The init.ohasd process does not start clusterware stack after the 12.2.0.1 Grid Infrastructure home is applied April 2020 Release Update. The scenario is not a typical Grid Infrastructure startup issue. It is a human error and happened when DBA, by accident, ran roothas.sh instead of rootcrs.sh while patching GI cluster home. Before knowing it is human error, the diagnose process is done as following.
Sunday, May 17, 2020
Wednesday, May 13, 2020
impdp failed at dbms_logrep_imp.instantiate_schema with ORA-01031: insufficient privileges
When execute impdp to import 12.2 database, ORA-01031 occured as following,
[oracle@host01]$ impdp system@pdb01 dumpfile=appdata.dmp schemas=appdata
Import: Release 12.2.0.1.0 - Production on Tue May 12 17:58:56 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_03": system/********@pdb01 dumpfile=appdata.dmp schemas=appdata
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
ORA-39083: Object type PROCACT_SCHEMA failed to create with error:
ORA-31625: Schema APPDATA is needed to import this object, but is unaccessible
ORA-01031: insufficient privileges
Failing sql is:
BEGIN
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'DB02', inst_scn=>'386040157882');COMMIT; END;
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-31625: Schema APPDATA is needed to import this object, but is unaccessible
ORA-01031: insufficient privileges
Failing sql is:
GRANT SELECT ON "APPDATA"."WORKLOAD_SEQ" TO "USER01"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-31625: Schema APPDATA is needed to import this object, but is unaccessible
ORA-01031: insufficient privileges
Failing sql is:
GRANT SELECT ON "APPDATA"."LOG_SEQ" TO "USER02"
The user SYSTEM, which is used by impdp to connect to database, does not have BECOME USER privilege,
system@PDB01> select * from dba_sys_privs where privilege='BECOME USER';
GRANTEE PRIVILEGE ADMIN_OPTION COMMON INHERITED
---------- --------------- ------------ ------ ---
SYS BECOME USER NO YES YES
The healthy database privilege grant looks like,
system@PDB01> select * from dba_sys_privs where privilege='BECOME USER';
GRANTEE PRIVILEGE ADMIN_OPTION COMMON INHERITED
-------------------- ------------ ------------ ------ ---
DBA BECOME USER NO YES YES
IMP_FULL_DATABASE BECOME USER NO YES YES
SYS BECOME USER NO YES YES
It can be fixed by running grand command
grant BECOME USER to DBA,IMP_FULL_DATABASE;
This privilege is normally granted to roles DBA and IMP_FULL_DATABASE. For some reason, it could be revoked. One of the scenario is that DBUA run script dvu121.sql to revoke BECOME USER privileges from DBA and IMP_FULL_DATABASE roles when upgrading database to 12.2 and Database Vault component is VALID.
Friday, April 24, 2020
opatchauto out-of-place patching Oracle Restart and Database home
Out of place (OOP) patching is a new feature introduced by Oracle 12.2 with the latest version of opatchauto. It is a patching method where patching is performed by following operations,
* Creating a copy of current oracle home
* Applying patches to the copied home
* Switching services to the copied home
This patching method helps on moving Oracle home and/or reducing the downtime of patching Oracle home and database. OPatchauto supports OOP patching for the following configurations,
* Grid Infrastructure (GI) and Real Application Cluster (RAC)
* Oracle Restart (Standalone Grid Infrastructure) (SIHA) and single instance database (SIDB)
Tuesday, December 24, 2019
DBA_REGISTRY_SQLPATCH shows END status after datapatch ran against database
During postinstallation of a database patching, datapatch utility shows following message while loading modified SQL files into the database,
It shows 'Patch 30138470 apply: SUCCESS', but the log file name is missing and patch status shows END in view DBA_REGISTRY_SQLPATCH,
Check the sql execution log,
ORA-01693 is found in line 32 of the log file. List the details of the errors,
The segment 'SYS.SYS_LOB0000190607C00013$$' reaches the extent maximum limitation (505), and it caused the SQL failure, find out what the segment is
The segment is BLOB column PATCH_DIRECTORY of table SYS.REGISTRY$SQLPATCH, both the BLOB segment and its table have extent maximum limitation 505. To remove the limitation,
Then re-run the datapatch utility,
Now, it gives the log file name. Validate the status,
The patch is installed successfully.
[oracle@host01]$ $ORACLE_HOME/OPatch/datapatch -verbose
SQL Patching tool version 12.2.0.1.0 Production on Sun Dec 22 14:32:47 2019
Copyright (c) 2012, 2019, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_18481316_2019_12_22_14_32_47/sqlpatch_invocation.log
Connecting to database...OK
Bootstrapping registry and package to current versions...done
Determining current state...done
Current state of SQL patches:
Bundle series DBRU:
ID 191015 in the binary registry and ID 190416 in the SQL registry
Adding patches to installation queue and performing prereq checks...
Installation queue:
Nothing to roll back
The following patches will be applied:
30138470 (DATABASE OCT 2019 RELEASE UPDATE 12.2.0.1.191015)
Installing patches...
Patch installation complete. Total patches installed: 3
Validating logfiles...
Patch 30138470 apply: SUCCESS
logfile: (no errors)
SQL Patching tool complete on Sun Dec 22 14:33:32 2019
SQL Patching tool version 12.2.0.1.0 Production on Sun Dec 22 14:32:47 2019
Copyright (c) 2012, 2019, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_18481316_2019_12_22_14_32_47/sqlpatch_invocation.log
Connecting to database...OK
Bootstrapping registry and package to current versions...done
Determining current state...done
Current state of SQL patches:
Bundle series DBRU:
ID 191015 in the binary registry and ID 190416 in the SQL registry
Adding patches to installation queue and performing prereq checks...
Installation queue:
Nothing to roll back
The following patches will be applied:
30138470 (DATABASE OCT 2019 RELEASE UPDATE 12.2.0.1.191015)
Installing patches...
Patch installation complete. Total patches installed: 3
Validating logfiles...
Patch 30138470 apply: SUCCESS
logfile: (no errors)
SQL Patching tool complete on Sun Dec 22 14:33:32 2019
It shows 'Patch 30138470 apply: SUCCESS', but the log file name is missing and patch status shows END in view DBA_REGISTRY_SQLPATCH,
SQL> select patch_id,version,action,status,description
2 from dba_registry_sqlpatch
3 order by action_time;
PATCH_ID VERSION ACTION STATUS DESCRIPTION
---------- ----------- -------- ---------- -------------------------------------------------
29314339 12.2.0.1 APPLY SUCCESS DATABASE APR 2019 RELEASE UPDATE 12.2.0.1.190416
30138470 12.2.0.1 APPLY END
SQL>
2 from dba_registry_sqlpatch
3 order by action_time;
PATCH_ID VERSION ACTION STATUS DESCRIPTION
---------- ----------- -------- ---------- -------------------------------------------------
29314339 12.2.0.1 APPLY SUCCESS DATABASE APR 2019 RELEASE UPDATE 12.2.0.1.190416
30138470 12.2.0.1 APPLY END
SQL>
Check the sql execution log,
[oracle@host01]$ cd /u01/app/oracle/cfgtoollogs/sqlpatch/
[oracle@host01]$ ls -l
total 104
drwxr-xr-x 3 oracle oracle 256 Jul 21 16:28 29314339
drwxr-xr-x 3 oracle oracle 256 Dec 22 14:33 30138470
drwxr-xr-x 2 oracle oracle 4096 Jul 21 16:24 sqlpatch_10879194_2019_07_21_16_24_15
drwxr-xr-x 2 oracle oracle 4096 Dec 22 14:33 sqlpatch_18481316_2019_12_22_14_32_47
-rw-r--r-- 1 oracle oracle 1373 Dec 22 14:31 sqlpatch_history.txt
[oracle@host01]$ cd 30138470
[oracle@host01]$ ls -l
total 0
drwxr-xr-x 2 oracle oracle 256 Dec 22 15:10 23136421
[oracle@host01]$ cd 23136421
[oracle@host01]$ ls -l
total 440
-rw-r--r-- 1 oracle oracle 184086 Dec 22 14:33 30138470_apply_DB01_2019Dec22_14_33_31.log
[oracle@host01]$ pwd
/u01/app/oracle/cfgtoollogs/sqlpatch/30138470/23136421
[oracle@host01]$
[oracle@host01]$ grep -n 'ORA-' 30138470_apply_DB01_2019Dec22_14_33_31.log
32:ORA-01693: max # extents (505) reached in lob segment
34:ORA-06512: at "SYS.DBMS_SQLPATCH", line 639
35:ORA-06512: at "SYS.DBMS_SQLPATCH", line 605
36:ORA-06512: at line 2
65:ORA-20004: Uninitialized state in install_file
66:ORA-06512: at "SYS.DBMS_SQLPATCH", line 738
159:ORA-20004: Uninitialized state in install_file
...
7305:SQL> PROMPT IGNORABLE ERRORS: ORA-04043
7306:IGNORABLE ERRORS: ORA-04043
7313:ORA-20004: Uninitialized state in install_file
7314:ORA-06512: at "SYS.DBMS_SQLPATCH", line 738
7407:ORA-20004: Uninitialized state in install_file
7408:ORA-06512: at "SYS.DBMS_SQLPATCH", line 738
[oracle@host01]$ ls -l
total 104
drwxr-xr-x 3 oracle oracle 256 Jul 21 16:28 29314339
drwxr-xr-x 3 oracle oracle 256 Dec 22 14:33 30138470
drwxr-xr-x 2 oracle oracle 4096 Jul 21 16:24 sqlpatch_10879194_2019_07_21_16_24_15
drwxr-xr-x 2 oracle oracle 4096 Dec 22 14:33 sqlpatch_18481316_2019_12_22_14_32_47
-rw-r--r-- 1 oracle oracle 1373 Dec 22 14:31 sqlpatch_history.txt
[oracle@host01]$ cd 30138470
[oracle@host01]$ ls -l
total 0
drwxr-xr-x 2 oracle oracle 256 Dec 22 15:10 23136421
[oracle@host01]$ cd 23136421
[oracle@host01]$ ls -l
total 440
-rw-r--r-- 1 oracle oracle 184086 Dec 22 14:33 30138470_apply_DB01_2019Dec22_14_33_31.log
[oracle@host01]$ pwd
/u01/app/oracle/cfgtoollogs/sqlpatch/30138470/23136421
[oracle@host01]$
[oracle@host01]$ grep -n 'ORA-' 30138470_apply_DB01_2019Dec22_14_33_31.log
32:ORA-01693: max # extents (505) reached in lob segment
34:ORA-06512: at "SYS.DBMS_SQLPATCH", line 639
35:ORA-06512: at "SYS.DBMS_SQLPATCH", line 605
36:ORA-06512: at line 2
65:ORA-20004: Uninitialized state in install_file
66:ORA-06512: at "SYS.DBMS_SQLPATCH", line 738
159:ORA-20004: Uninitialized state in install_file
...
7305:SQL> PROMPT IGNORABLE ERRORS: ORA-04043
7306:IGNORABLE ERRORS: ORA-04043
7313:ORA-20004: Uninitialized state in install_file
7314:ORA-06512: at "SYS.DBMS_SQLPATCH", line 738
7407:ORA-20004: Uninitialized state in install_file
7408:ORA-06512: at "SYS.DBMS_SQLPATCH", line 738
ORA-01693 is found in line 32 of the log file. List the details of the errors,
[oracle@host01]$ head -37 30138470_apply _DB01_2019Dec22_14_33_31.log
SQL>
SQL> SET PAGESIZE 0
SQL> SELECT 'Starting apply for patch 30138470/23136421 on ' ||
2 SYSTIMESTAMP FROM dual;
Starting apply for patch 30138470/23136421 on 22-DEC-19 02.33.31.103477 PM -05:0
0
SQL> SET PAGESIZE 10
SQL>
SQL> BEGIN
2 dbms_sqlpatch.patch_initialize(p_patch_id => 30138470,
3 p_patch_uid => 23136421,
4 p_flags => '&flags',
5 p_description => '&description',
6 p_action => 'APPLY',
7 p_logfile => '&full_logfile',
8 p_bundle_series => '&bundle_series');
9 END;
10 /
old 4: p_flags => '&flags',
new 4: p_flags => 'NB',
old 5: p_description => '&description',
new 5: p_description => 'DATABASE OCT 2019 RELEASE UPDATE 12.2.0.1.191015',
old 7: p_logfile => '&full_logfile',
new 7: p_logfile => '/u01/app/oracle/cfgtoollogs/sqlpatch/30138470/23136421/30138470_apply_DB01_2019Dec22_14_33_31.log',
old 8: p_bundle_series => '&bundle_series');
new 8: p_bundle_series => 'DBRU');
BEGIN
*
ERROR at line 1:
ORA-01693: max # extents (505) reached in lob segment
SYS.SYS_LOB0000190607C00013$$
ORA-06512: at "SYS.DBMS_SQLPATCH", line 639
ORA-06512: at "SYS.DBMS_SQLPATCH", line 605
ORA-06512: at line 2
[oracle@host01]$
SQL>
SQL> SET PAGESIZE 0
SQL> SELECT 'Starting apply for patch 30138470/23136421 on ' ||
2 SYSTIMESTAMP FROM dual;
Starting apply for patch 30138470/23136421 on 22-DEC-19 02.33.31.103477 PM -05:0
0
SQL> SET PAGESIZE 10
SQL>
SQL> BEGIN
2 dbms_sqlpatch.patch_initialize(p_patch_id => 30138470,
3 p_patch_uid => 23136421,
4 p_flags => '&flags',
5 p_description => '&description',
6 p_action => 'APPLY',
7 p_logfile => '&full_logfile',
8 p_bundle_series => '&bundle_series');
9 END;
10 /
old 4: p_flags => '&flags',
new 4: p_flags => 'NB',
old 5: p_description => '&description',
new 5: p_description => 'DATABASE OCT 2019 RELEASE UPDATE 12.2.0.1.191015',
old 7: p_logfile => '&full_logfile',
new 7: p_logfile => '/u01/app/oracle/cfgtoollogs/sqlpatch/30138470/23136421/30138470_apply_DB01_2019Dec22_14_33_31.log',
old 8: p_bundle_series => '&bundle_series');
new 8: p_bundle_series => 'DBRU');
BEGIN
*
ERROR at line 1:
ORA-01693: max # extents (505) reached in lob segment
SYS.SYS_LOB0000190607C00013$$
ORA-06512: at "SYS.DBMS_SQLPATCH", line 639
ORA-06512: at "SYS.DBMS_SQLPATCH", line 605
ORA-06512: at line 2
[oracle@host01]$
The segment 'SYS.SYS_LOB0000190607C00013$$' reaches the extent maximum limitation (505), and it caused the SQL failure, find out what the segment is
SQL> select owner,object_name,object_type
2 from dba_objects
3 where object_name='SYS_LOB0000190607C00013$$';
OWNER OBJECT_NAME OBJECT_TYPE
-------------------- ------------------------------ -----------------------
SYS SYS_LOB0000190607C00013$$ LOB
SQL> select owner,table_name,column_name,segment_name
2 from dba_lobs
3 where segment_name='SYS_LOB0000190607C00013$$';
OWNER TABLE_NAME COLUMN_NAME SEGMENT_NAME
---------- ------------------------------ -------------------- -------------------------
SYS REGISTRY$SQLPATCH PATCH_DIRECTORY SYS_LOB0000190607C00013$$
SQL>
SQL> desc sys.registry$sqlpatch
Name Null? Type
----------------------------- -------- -------------------------
PATCH_ID NOT NULL NUMBER
ACTION NOT NULL VARCHAR2(15)
ACTION_TIME NOT NULL TIMESTAMP(6)
DESCRIPTION VARCHAR2(100)
LOGFILE VARCHAR2(500)
STATUS VARCHAR2(25)
VERSION NOT NULL VARCHAR2(20)
PATCH_UID NOT NULL NUMBER
FLAGS VARCHAR2(10)
BUNDLE_SERIES VARCHAR2(30)
BUNDLE_ID NUMBER
INSTALL_ID NUMBER
PATCH_DIRECTORY BLOB
BUNDLE_DATA XMLTYPE
PATCH_DESCRIPTOR XMLTYPE
POST_LOGFILE VARCHAR2(500)
SQL>
SQL> select owner,segment_name,max_extents
2 from dba_segments
3 where segment_name in ('REGISTRY$SQLPATCH','SYS_LOB0000190607C00013$$');
OWNER SEGMENT_NAME MAX_EXTENTS
---------- ------------------------------ -----------
SYS REGISTRY$SQLPATCH 505
SYS SYS_LOB0000190607C00013$$ 505
2 from dba_objects
3 where object_name='SYS_LOB0000190607C00013$$';
OWNER OBJECT_NAME OBJECT_TYPE
-------------------- ------------------------------ -----------------------
SYS SYS_LOB0000190607C00013$$ LOB
SQL> select owner,table_name,column_name,segment_name
2 from dba_lobs
3 where segment_name='SYS_LOB0000190607C00013$$';
OWNER TABLE_NAME COLUMN_NAME SEGMENT_NAME
---------- ------------------------------ -------------------- -------------------------
SYS REGISTRY$SQLPATCH PATCH_DIRECTORY SYS_LOB0000190607C00013$$
SQL>
SQL> desc sys.registry$sqlpatch
Name Null? Type
----------------------------- -------- -------------------------
PATCH_ID NOT NULL NUMBER
ACTION NOT NULL VARCHAR2(15)
ACTION_TIME NOT NULL TIMESTAMP(6)
DESCRIPTION VARCHAR2(100)
LOGFILE VARCHAR2(500)
STATUS VARCHAR2(25)
VERSION NOT NULL VARCHAR2(20)
PATCH_UID NOT NULL NUMBER
FLAGS VARCHAR2(10)
BUNDLE_SERIES VARCHAR2(30)
BUNDLE_ID NUMBER
INSTALL_ID NUMBER
PATCH_DIRECTORY BLOB
BUNDLE_DATA XMLTYPE
PATCH_DESCRIPTOR XMLTYPE
POST_LOGFILE VARCHAR2(500)
SQL>
SQL> select owner,segment_name,max_extents
2 from dba_segments
3 where segment_name in ('REGISTRY$SQLPATCH','SYS_LOB0000190607C00013$$');
OWNER SEGMENT_NAME MAX_EXTENTS
---------- ------------------------------ -----------
SYS REGISTRY$SQLPATCH 505
SYS SYS_LOB0000190607C00013$$ 505
The segment is BLOB column PATCH_DIRECTORY of table SYS.REGISTRY$SQLPATCH, both the BLOB segment and its table have extent maximum limitation 505. To remove the limitation,
SQL> alter table sys.registry$sqlpatch storage (maxextents unlimited);
Table altered.
SQL> alter table sys.registry$sqlpatch
2 modify lob (patch_directory) (storage (maxextents unlimited));
Table altered.
SQL> select owner,segment_name,max_extents
2 from dba_segments
3 where segment_name in ('REGISTRY$SQLPATCH','SYS_LOB0000190607C00013$$');
OWNER SEGMENT_NAME MAX_EXTENTS
---------- ------------------------------ -----------
SYS REGISTRY$SQLPATCH 2147483645
SYS SYS_LOB0000190607C00013$$ 2147483645
Table altered.
SQL> alter table sys.registry$sqlpatch
2 modify lob (patch_directory) (storage (maxextents unlimited));
Table altered.
SQL> select owner,segment_name,max_extents
2 from dba_segments
3 where segment_name in ('REGISTRY$SQLPATCH','SYS_LOB0000190607C00013$$');
OWNER SEGMENT_NAME MAX_EXTENTS
---------- ------------------------------ -----------
SYS REGISTRY$SQLPATCH 2147483645
SYS SYS_LOB0000190607C00013$$ 2147483645
Then re-run the datapatch utility,
[oracle@host01]$ $ORACLE _HOME/OPatch/datapatch -verbose
SQL Patching tool version 12.2.0.1.0 Production on Sun Dec 22 16:43:43 2019
Copyright (c) 2012, 2019, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_35782878_2019_12_22_16_43_43/sqlpatch_invocation.log
Connecting to database...OK
Bootstrapping registry and package to current versions...done
Determining current state...done
Current state of SQL patches:
Bundle series DBRU:
ID 191015 in the binary registry and ID 190416 in the SQL registry
Adding patches to installation queue and performing prereq checks...
Installation queue:
Nothing to roll back
The following patches will be applied:
30138470 (DATABASE OCT 2019 RELEASE UPDATE 12.2.0.1.191015)
Installing patches...
Patch installation complete. Total patches installed: 1
Validating logfiles...
Patch 30138470 apply: SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/30138470/23136421/30138470_apply_DB01_2019Dec22_16_44_33.log (no errors)
SQL Patching tool complete on Sun Dec 22 16:45:22 2019
SQL Patching tool version 12.2.0.1.0 Production on Sun Dec 22 16:43:43 2019
Copyright (c) 2012, 2019, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_35782878_2019_12_22_16_43_43/sqlpatch_invocation.log
Connecting to database...OK
Bootstrapping registry and package to current versions...done
Determining current state...done
Current state of SQL patches:
Bundle series DBRU:
ID 191015 in the binary registry and ID 190416 in the SQL registry
Adding patches to installation queue and performing prereq checks...
Installation queue:
Nothing to roll back
The following patches will be applied:
30138470 (DATABASE OCT 2019 RELEASE UPDATE 12.2.0.1.191015)
Installing patches...
Patch installation complete. Total patches installed: 1
Validating logfiles...
Patch 30138470 apply: SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/30138470/23136421/30138470_apply_DB01_2019Dec22_16_44_33.log (no errors)
SQL Patching tool complete on Sun Dec 22 16:45:22 2019
Now, it gives the log file name. Validate the status,
SQL> select patch_id,version,action,status,description
2 from dba_registry_sqlpatch order by action_time;
PATCH_ID VERSION ACTION STATUS DESCRIPTION
---------- ----------- -------- ---------- -------------------------------------------------
29314339 12.2.0.1 APPLY SUCCESS DATABASE APR 2019 RELEASE UPDATE 12.2.0.1.190416
30138470 12.2.0.1 APPLY END
30138470 12.2.0.1 APPLY SUCCESS DATABASE OCT 2019 RELEASE UPDATE 12.2.0.1.191015
2 from dba_registry_sqlpatch order by action_time;
PATCH_ID VERSION ACTION STATUS DESCRIPTION
---------- ----------- -------- ---------- -------------------------------------------------
29314339 12.2.0.1 APPLY SUCCESS DATABASE APR 2019 RELEASE UPDATE 12.2.0.1.190416
30138470 12.2.0.1 APPLY END
30138470 12.2.0.1 APPLY SUCCESS DATABASE OCT 2019 RELEASE UPDATE 12.2.0.1.191015
The patch is installed successfully.
Tuesday, October 22, 2019
DBUA 12.2 Failed while PDBS Recompile Invalid Objects
When upgrading 12.1 database to 12.2 with DBUA, got following error,
Server errors encountered during the execution of "PDBS Recompile Invalid Objects".
Following information is found in DBUA log file,
These privileges were granted by SYS (SYS is grantor) in original database, and missing during upgrading.
Object privileges in original database,
Object privileges in current (partly upgraded) database,
Workaround
Manually grant the missing privileges and click retry in DBUA.
Server errors encountered during the execution of "PDBS Recompile Invalid Objects".
Following information is found in DBUA log file,
SQL> EXECUTE dbms_registry_sys.validate_components;
...(20:04:27) Starting validate_apex for APEX_180200
ORA-20001: MISSING GRANT: grant execute on "MDSYS"."SDO_DIM_ARRAY" to APEX_180200
ORA-20001: MISSING GRANT: grant execute on "MDSYS"."SDO_DIM_ELEMENT" to APEX_180200
ORA-20001: MISSING GRANT: grant execute on "MDSYS"."SDO_ELEM_INFO_ARRAY" to APEX_180200
ORA-20001: MISSING GRANT: grant execute on "MDSYS"."SDO_GEOMETRY" to APEX_180200
ORA-20001: MISSING GRANT: grant execute on "MDSYS"."SDO_ORDINATE_ARRAY" to APEX_180200
ORA-20001: MISSING GRANT: grant execute on "MDSYS"."SDO_POINT_TYPE" to APEX_180200
...(20:04:27) Starting validate_apex for APEX_180200
ORA-20001: MISSING GRANT: grant execute on "MDSYS"."SDO_DIM_ARRAY" to APEX_180200
ORA-20001: MISSING GRANT: grant execute on "MDSYS"."SDO_DIM_ELEMENT" to APEX_180200
ORA-20001: MISSING GRANT: grant execute on "MDSYS"."SDO_ELEM_INFO_ARRAY" to APEX_180200
ORA-20001: MISSING GRANT: grant execute on "MDSYS"."SDO_GEOMETRY" to APEX_180200
ORA-20001: MISSING GRANT: grant execute on "MDSYS"."SDO_ORDINATE_ARRAY" to APEX_180200
ORA-20001: MISSING GRANT: grant execute on "MDSYS"."SDO_POINT_TYPE" to APEX_180200
These privileges were granted by SYS (SYS is grantor) in original database, and missing during upgrading.
Object privileges in original database,
SQL> select GRANTEE,GRANTOR,OWNER,TABLE_NAME,PRIVILEGE from dba_tab_privs
2 where owner='MDSYS' and grantee='APEX_180200'
3 order by grantor,table_name;
GRANTEE GRANTOR OWNER TABLE_NAME PRIVILEGE
-------------- ---------- --------- ------------------------------ ----------------------
APEX_180200 MDSYS MDSYS MDERR EXECUTE
APEX_180200 MDSYS MDSYS SDO_GEOM_METADATA_TABLE DELETE
APEX_180200 MDSYS MDSYS SDO_GEOM_METADATA_TABLE SELECT
APEX_180200 MDSYS MDSYS SDO_GEOM_METADATA_TABLE INSERT
APEX_180200 MDSYS MDSYS SDO_META EXECUTE
APEX_180200 MDSYS MDSYS SDO_UTIL EXECUTE
APEX_180200 MDSYS MDSYS USER_SDO_INDEX_INFO SELECT
APEX_180200 SYS MDSYS SDO_DIM_ARRAY EXECUTE
APEX_180200 SYS MDSYS SDO_DIM_ELEMENT EXECUTE
APEX_180200 SYS MDSYS SDO_ELEM_INFO_ARRAY EXECUTE
APEX_180200 SYS MDSYS SDO_GEOMETRY EXECUTE
APEX_180200 SYS MDSYS SDO_ORDINATE_ARRAY EXECUTE
APEX_180200 SYS MDSYS SDO_POINT_TYPE EXECUTE
2 where owner='MDSYS' and grantee='APEX_180200'
3 order by grantor,table_name;
GRANTEE GRANTOR OWNER TABLE_NAME PRIVILEGE
-------------- ---------- --------- ------------------------------ ----------------------
APEX_180200 MDSYS MDSYS MDERR EXECUTE
APEX_180200 MDSYS MDSYS SDO_GEOM_METADATA_TABLE DELETE
APEX_180200 MDSYS MDSYS SDO_GEOM_METADATA_TABLE SELECT
APEX_180200 MDSYS MDSYS SDO_GEOM_METADATA_TABLE INSERT
APEX_180200 MDSYS MDSYS SDO_META EXECUTE
APEX_180200 MDSYS MDSYS SDO_UTIL EXECUTE
APEX_180200 MDSYS MDSYS USER_SDO_INDEX_INFO SELECT
APEX_180200 SYS MDSYS SDO_DIM_ARRAY EXECUTE
APEX_180200 SYS MDSYS SDO_DIM_ELEMENT EXECUTE
APEX_180200 SYS MDSYS SDO_ELEM_INFO_ARRAY EXECUTE
APEX_180200 SYS MDSYS SDO_GEOMETRY EXECUTE
APEX_180200 SYS MDSYS SDO_ORDINATE_ARRAY EXECUTE
APEX_180200 SYS MDSYS SDO_POINT_TYPE EXECUTE
Object privileges in current (partly upgraded) database,
SQL> select GRANTEE,GRANTOR,OWNER,TABLE_NAME,PRIVILEGE from dba_tab_privs
2 where owner='MDSYS' and grantee='APEX_180200'
3 order by grantor,table_name;
GRANTEE GRANTOR OWNER TABLE_NAME PRIVILEGE
-------------- ---------- ---------- ------------------------------ --------------------
APEX_180200 MDSYS MDSYS MDERR EXECUTE
APEX_180200 MDSYS MDSYS SDO_GEOM_METADATA_TABLE DELETE
APEX_180200 MDSYS MDSYS SDO_GEOM_METADATA_TABLE INSERT
APEX_180200 MDSYS MDSYS SDO_GEOM_METADATA_TABLE SELECT
APEX_180200 MDSYS MDSYS SDO_META EXECUTE
APEX_180200 MDSYS MDSYS SDO_UTIL EXECUTE
APEX_180200 MDSYS MDSYS USER_SDO_INDEX_INFO SELECT
2 where owner='MDSYS' and grantee='APEX_180200'
3 order by grantor,table_name;
GRANTEE GRANTOR OWNER TABLE_NAME PRIVILEGE
-------------- ---------- ---------- ------------------------------ --------------------
APEX_180200 MDSYS MDSYS MDERR EXECUTE
APEX_180200 MDSYS MDSYS SDO_GEOM_METADATA_TABLE DELETE
APEX_180200 MDSYS MDSYS SDO_GEOM_METADATA_TABLE INSERT
APEX_180200 MDSYS MDSYS SDO_GEOM_METADATA_TABLE SELECT
APEX_180200 MDSYS MDSYS SDO_META EXECUTE
APEX_180200 MDSYS MDSYS SDO_UTIL EXECUTE
APEX_180200 MDSYS MDSYS USER_SDO_INDEX_INFO SELECT
Workaround
Manually grant the missing privileges and click retry in DBUA.
Wednesday, August 21, 2019
12.2: Preupgrade Utility Fails with ORA-06512: at "SYS.DBMS_PREUP"
While running 12.2 Pre-Upgrade Utility (manually or with DBUA) on 12.1 database, received following errors,
This is the bug of the Pre-Upgrade Utility installed in current 12.2 home. It can be fixed by installing new version of the utility as following,
1. Download utility ZIP file from My Oracle Support following the instruction of document "How to Download and Run Oracle's Database Pre-Upgrade Utility (Doc ID 884522.1)"
2. The current available ZIP file for 12.2 (target upgrade-to version) is preupgrade_12201_cbuild_22_lf.zip, it contains following files,
3. Unzip downloaded file to directory $ORACLE_HOME/rdbms/amdin, here $ORACLE_HOME is 12.2 home.
Note: Rename existing files before unzipping, do not override any files.
Error in preupgrade tool execution.
ERROR -
ERROR - Unable to run sqlplus due to:
ORA-06512: at "SYS.DBMS_PREUP", line 4401
ORA-06512: at "SYS.DBMS_PREUP", line 9714
ORA-06512: at line 8
declare
*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 56
if you are executing the preupgrade tool from a standby database make sure the preupgrade tool has been executed in the primary database If you are executing the tool from Windows, please make sure you have given the appropriate permissions to the Oracle binaries owner.
ERROR -
ERROR - Unable to run sqlplus due to:
ORA-06512: at "SYS.DBMS_PREUP", line 4401
ORA-06512: at "SYS.DBMS_PREUP", line 9714
ORA-06512: at line 8
declare
*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 56
if you are executing the preupgrade tool from a standby database make sure the preupgrade tool has been executed in the primary database If you are executing the tool from Windows, please make sure you have given the appropriate permissions to the Oracle binaries owner.
This is the bug of the Pre-Upgrade Utility installed in current 12.2 home. It can be fixed by installing new version of the utility as following,
1. Download utility ZIP file from My Oracle Support following the instruction of document "How to Download and Run Oracle's Database Pre-Upgrade Utility (Doc ID 884522.1)"
2. The current available ZIP file for 12.2 (target upgrade-to version) is preupgrade_12201_cbuild_22_lf.zip, it contains following files,
dbms_registry_basic.sql
dbms_registry_extended.sql
preupgrade.jar
preupgrade_driver.sql
preupgrade_messages.properties
preupgrade_package.sql
dbms_registry_extended.sql
preupgrade.jar
preupgrade_driver.sql
preupgrade_messages.properties
preupgrade_package.sql
3. Unzip downloaded file to directory $ORACLE_HOME/rdbms/amdin, here $ORACLE_HOME is 12.2 home.
Note: Rename existing files before unzipping, do not override any files.
Wednesday, May 29, 2019
AIX: Failure to Apply April 2019 Grid Infrastructure Release Update on 12.2 Oracle Restart Home
Environment
Symptoms
Opatch reported following errors while applying sub-patch 29314424 (OCW Apr 2019 Release Update 12.2.0.1.190416)
Workaround
Apply Oct 2018 Grid Infrastructure Release Update (GI RU) first, then apply April 2019 GI RU.
Operation System: IBM AIX 7.2 on POWER Systems (64-bit) Oracle Restart Home: 12.2.0.1 with July 2018 Grid Infrastructure Release Update (GI RU 12.2.0.1.180717) OPatch Version: 12.2.0.1.17
Symptoms
Opatch reported following errors while applying sub-patch 29314424 (OCW Apr 2019 Release Update 12.2.0.1.190416)
UtilSession failed: ApplySession failed in system modification phase... 'ApplySession::apply failed: java.lang.UnsatisfiedLinkError: /u01/app/grid/product/12.2.0/grid/lib/libpatchgensh12.so (rtld: 0712-001 Symbol nzos_SetServerNameList was referenced
from module /u01/app/grid/product/12.2.0/grid/lib/libhasgen12.so(), but a runtime definition
of the symbol was not found.
rtld: 0712-001 Symbol lxkRegexpComp2 was referenced
from module /u01/app/grid/product/12.2.0/grid/lib/libhasgen12.so(), but a runtime definition
of the symbol was not found.
rtld: 0712-001 Symbol lxkRegexpLike2 was referenced
from module /u01/app/grid/product/12.2.0/grid/lib/libhasgen12.so(), but a ru)
from module /u01/app/grid/product/12.2.0/grid/lib/libhasgen12.so(), but a runtime definition
of the symbol was not found.
rtld: 0712-001 Symbol lxkRegexpComp2 was referenced
from module /u01/app/grid/product/12.2.0/grid/lib/libhasgen12.so(), but a runtime definition
of the symbol was not found.
rtld: 0712-001 Symbol lxkRegexpLike2 was referenced
from module /u01/app/grid/product/12.2.0/grid/lib/libhasgen12.so(), but a ru)
Workaround
Apply Oct 2018 Grid Infrastructure Release Update (GI RU) first, then apply April 2019 GI RU.
Tuesday, January 15, 2019
Disable Transparent Hugepages on Oracle Linux / Redhat Enterprise Linux 7.5
Transparent Hugepages (THP) are similar to standard HugePages. However, while standard HugePages allocate memory at startup, Transparent Hugepages memory uses the khugepaged thread in the kernel to allocate memory dynamically during runtime, using swappable HugePages.
They may cause delays in accessing memory that can result in node restarts in Oracle RAC environments, or performance issues or delays for Oracle Database single instances. Oracle recommends to disable Transparent HugePages on Oracle database servers.
Following discussion is based on Oracle Linux / Redhat Enterprise Linux 7.5, it may be different on other Linux distributions.
They may cause delays in accessing memory that can result in node restarts in Oracle RAC environments, or performance issues or delays for Oracle Database single instances. Oracle recommends to disable Transparent HugePages on Oracle database servers.
Following discussion is based on Oracle Linux / Redhat Enterprise Linux 7.5, it may be different on other Linux distributions.
Friday, December 21, 2018
Configure Python with iPython, Jupyter and MySQL on Ubuntu 18.10
If you’re thinking of learning Python, you may be struggling with where and how to start. It could be a pretty hard question before you know why you want to learn Python because Python, as a popular programming language, is used in many fields and industries such as
. web development (create web applications on a server)
. software development (work with software developing tools to create work flows)
. mathematics (scientific computing, data analysis, data visualization)
. system scripting
As I am asked the question by a university student and Python is wildly used for data analysis/visualization in university, I going to demonstrate how to qhickly build a Python data analysis/visualization developing environment on Ubuntu 18.10.
. web development (create web applications on a server)
. software development (work with software developing tools to create work flows)
. mathematics (scientific computing, data analysis, data visualization)
. system scripting
As I am asked the question by a university student and Python is wildly used for data analysis/visualization in university, I going to demonstrate how to qhickly build a Python data analysis/visualization developing environment on Ubuntu 18.10.
Sunday, November 18, 2018
Patch or Upgrade Single instance Database and Oracle Restart Using OEM 13c Fleet Maintenance
Oracle Enterprise Manager (OEM) 13c Database Fleet Maintenance is a centralized mechanism for patching/upgrading of Oracle homes and maintain consistency. It allows OEM Cloud Control administrators to patch database, Oracle Restart (Standalone Grid Infrastructure) or Grid Infrastructure with minimal downtime.
Friday, August 31, 2018
Oracle Database 18c Password Authentication with Microsoft Active Directory
Oracle database 18c introduces a new feature 'Integration of Active Directory Services with Oracle Database'. It gives us a way to authenticate and authorize users directly with Microsoft Active Directory. Oracle database users and roles can map directly to Active Directory users and groups without using Oracle Enterprise User Security (EUS) or another intermediate directory service. Here, I am going to demonstrate how to make it work step by step.
Environment:
Database Server - Oracle Database 18c Enterprise Edition on Oracle Linux Server 7.5
Microsoft Active Directory Server - Windows Server 2012 R2
Environment:
Database Server - Oracle Database 18c Enterprise Edition on Oracle Linux Server 7.5
Microsoft Active Directory Server - Windows Server 2012 R2
Tuesday, August 14, 2018
Restrict OEM agent 13.2 to use TLS protocol TLSv1.2
Oracle Enterprise Manager 13.2 agent, by default, is configured to use TLS protocol TLSv1.0, TLSv1.1 and TLSv1.2,all versions of TLS when OEM 13.2 released. To restrict agent to use only TLSv1.2 as following,
Friday, August 3, 2018
DBUA 12.2 failed with ORA-01157 at preupgrade tool execution
DBUA got following errors at 'Prerequisite Check' (perupgrade tool execution) while upgrading Oracle database 11.2.0.4 to 12.2.0.1
Error in preupgrade tool execution. ERROR - ERROR - Unable to run sqlplus due to: ORA-06512: at "SYS.DBMS_PREUP",
line 4386 ORA-06512: at "SYS.DBMS_PREUP", line 9108 ORA-06512: at line 8 declare * ERROR at line 1: ORA-01157:
cannot identify/lock data file 202 - see DBWR trace file ORA-01110: data file 202: '+DATA' ORA-06512: at line 56
if you are executing the preupgrade tool from a standby database make sure the preupgrade tool has been executed
in the primary database If you are executing the tool from Windows, please make sure you have given the appropriate
permissions to the Oracle binaries owner. (more details)
line 4386 ORA-06512: at "SYS.DBMS_PREUP", line 9108 ORA-06512: at line 8 declare * ERROR at line 1: ORA-01157:
cannot identify/lock data file 202 - see DBWR trace file ORA-01110: data file 202: '+DATA' ORA-06512: at line 56
if you are executing the preupgrade tool from a standby database make sure the preupgrade tool has been executed
in the primary database If you are executing the tool from Windows, please make sure you have given the appropriate
permissions to the Oracle binaries owner. (more details)
Thursday, March 8, 2018
Manually Delete Agent and Targets Using EMCLI in OEM 13c
Although it is recommended to remove targets through OEM console interface, command line utility is more helpful/convenient in some scenarios.
For example, the target host is crashed and cannot be restored, and a new host with new name is built for replacement. Therefore,
the old host and all targets running on this host have to be remove from OEM. Depends on number of targets, it could be a pain to remove
them through console. At this time, manually running EMCLI command becomes the best way.
Following command removed the agent running on server host01 and all targets monitored by this agent,
We can also run EMCLI command to remove a specific target,
To remove database (instance) TESTDB,
To remove ASM instance target +ASM_host01.dbaplus.ca,
To Oracle listener LISTENER_host01.dbaplus.ca,
For example, the target host is crashed and cannot be restored, and a new host with new name is built for replacement. Therefore,
the old host and all targets running on this host have to be remove from OEM. Depends on number of targets, it could be a pain to remove
them through console. At this time, manually running EMCLI command becomes the best way.
Following command removed the agent running on server host01 and all targets monitored by this agent,
[oracle@oms1]$ $EMCLI_HOME/bin/emcli login -username=sysman
Enter password :
Login successful
[oracle@oms1]$ $EMCLI_HOME/bin/emcli delete_target -name="host01.dbaplus.ca:3872" -type="oracle_emd" -delete_monitored_targets -async
Target "host01.dbaplus.ca:3872:oracle_emd" deleted successfully
[oracle@oms1]$
Enter password :
Login successful
[oracle@oms1]$ $EMCLI_HOME/bin/emcli delete_target -name="host01.dbaplus.ca:3872" -type="oracle_emd" -delete_monitored_targets -async
Target "host01.dbaplus.ca:3872:oracle_emd" deleted successfully
[oracle@oms1]$
We can also run EMCLI command to remove a specific target,
$EMCLI_HOME/bin/emcli delete_target -name="<target-name>" -type="<target-type>"
To remove database (instance) TESTDB,
$EMCLI_HOME/bin/emcli delete_target -name="TESTDB" -type="oracle_database"
To remove ASM instance target +ASM_host01.dbaplus.ca,
$EMCLI_HOME/bin/emcli delete_target -name="+ASM_host01.dbaplus.ca" -type="osm_instance"
To Oracle listener LISTENER_host01.dbaplus.ca,
$EMCLI_HOME/bin/emcli delete_target -name="LISTENER_host01.dbaplus.ca" -type="oracle_listener"
Thursday, March 1, 2018
How to delete archive log files using PL/SQL
Although RMAN is always the first choice for most DBAs to delete archive log files, PL/SQL could be easier for this scenario,
User wants to switch an existing database from NOARCHIVELOG mode to ARCHIVELOG mode in order to increase the database availability because the database backup job has to shut down database when running in NOARCHIVELOG mode.
Monday, February 19, 2018
ORA-01017 invalid username/password after 11g database upgraded to 12.2 pluggable database
Oracle database 11.2.0.3 was upgraded to 12.2.0.1 and plugged into CDB as pluggable database with 'create pluggable database ... using ...' command. The container database (CDB) was creted with 12.2 dbca and all built-in users' passwords (sys,system,dbsnmp, etc) were set to same ones as in original 11g database.
Saturday, February 10, 2018
12.2 database Alert log shows ORA-20001: Latest xml inventory is not loaded into table
While 12.2 database is being started by srvctl, the alert log shows following messages,
Unable to obtain current patch information due to error: 20001, ORA-20001: Latest xml inventory is not loaded into table
ORA-06512: at "SYS.DBMS_QOPATCH", line 777
ORA-06512: at "SYS.DBMS_QOPATCH", line 864
ORA-06512: at "SYS.DBMS_QOPATCH", line 2222
ORA-06512: at "SYS.DBMS_QOPATCH", line 740
ORA-06512: at "SYS.DBMS_QOPATCH", line 2247
===========================================================
Dumping current patch information
===========================================================
Unable to obtain current patch information due to error: 20001
===========================================================
ORA-06512: at "SYS.DBMS_QOPATCH", line 777
ORA-06512: at "SYS.DBMS_QOPATCH", line 864
ORA-06512: at "SYS.DBMS_QOPATCH", line 2222
ORA-06512: at "SYS.DBMS_QOPATCH", line 740
ORA-06512: at "SYS.DBMS_QOPATCH", line 2247
===========================================================
Dumping current patch information
===========================================================
Unable to obtain current patch information due to error: 20001
===========================================================
Thursday, January 25, 2018
AIX Disk Only Visible to One ASM Instance of Grid Infrastructure
Oracle Grid Infrastructure 12.2.0.1
AIX 7.1
New disks are added to both nodes of Grid Infrastructure, and the disk permission is configured properly,
AIX 7.1
New disks are added to both nodes of Grid Infrastructure, and the disk permission is configured properly,
[grid@host01]$ ls -l /dev/*hdisk[56]
brw------- 1 root system 21, 26 Jan 22 10:58 /dev/hdisk5
brw------- 1 root system 21, 25 Jan 22 10:58 /dev/hdisk6
crw-rw---- 1 grid asmadmin 21, 26 Jan 24 16:07 /dev/rhdisk5
crw-rw---- 1 grid asmadmin 21, 25 Jan 24 16:06 /dev/rhdisk6
[grid@host02]$ ls -l /dev/*hdisk2[56]
brw------- 1 root system 21, 25 Jan 22 11:08 /dev/hdisk5
brw------- 1 root system 21, 31 Jan 22 11:08 /dev/hdisk6
crw-rw---- 1 grid asmadmin 21, 25 Jan 22 11:08 /dev/rhdisk5
crw-rw---- 1 grid asmadmin 21, 31 Jan 24 14:49 /dev/rhdisk6
brw------- 1 root system 21, 26 Jan 22 10:58 /dev/hdisk5
brw------- 1 root system 21, 25 Jan 22 10:58 /dev/hdisk6
crw-rw---- 1 grid asmadmin 21, 26 Jan 24 16:07 /dev/rhdisk5
crw-rw---- 1 grid asmadmin 21, 25 Jan 24 16:06 /dev/rhdisk6
[grid@host02]$ ls -l /dev/*hdisk2[56]
brw------- 1 root system 21, 25 Jan 22 11:08 /dev/hdisk5
brw------- 1 root system 21, 31 Jan 22 11:08 /dev/hdisk6
crw-rw---- 1 grid asmadmin 21, 25 Jan 22 11:08 /dev/rhdisk5
crw-rw---- 1 grid asmadmin 21, 31 Jan 24 14:49 /dev/rhdisk6
Saturday, January 20, 2018
Upgrading Grid Infrastructure from 12.1 to 12.2 failed with [INS-20802]
Customer is upgrading two-node Oracle Grid Infrastructure from version 12.1.0.2 to version 12.2.0.1, and gridSetup.sh returned error [INS-20802] during Preparing for deploying Oracle Grid Infrastructure Management Repository.
Wednesday, January 17, 2018
ORA-01403: no data found on ctxposup.sql while upgrading from 11g to 12.2
From: Oracle Database - Enterprise Edition - Version 11.2.0.3 + PSU 11.2.0.3.5 (14727310)
To: Oracle Database - Enterprise Edition - Version 12.2.0.1.0
Operation System - Solaris 10 1/13 s10s_u11wos_24a SPARC
It failed to upgrade database from 11.2.3 to 12.2 with error 'ORA-01403: no data found', and following message is found from log file 'Oracle_Server.log',
Script ctxposup.sql (STATEMENT 1) which upgrades Oracle Text raised ORA-01403 while running PL/SQL block listed in the above. Testing individual statements in the block as following:
The object CTXSYS.ENT_EXT_DICT_OBJ (a policy of Oracle Text) does not exist in the database. Therefore, STATEMENT 2 (select ... into ... from) returned ORA-01403. Apparently, The purpose of STATEMENT 2 is to test whether CTXSYS.ENT_EXT_DICT_OBJ exists, but listing records is not a better choice for exist-testing than counting the number of records. Let's try to replace STATEMENT 2 in script filectxposup.sql with following code
Start the upgrade again, it succeeded!
To: Oracle Database - Enterprise Edition - Version 12.2.0.1.0
Operation System - Solaris 10 1/13 s10s_u11wos_24a SPARC
It failed to upgrade database from 11.2.3 to 12.2 with error 'ORA-01403: no data found', and following message is found from log file 'Oracle_Server.log',
REASON:
ERRORS FOUND: During Upgrade
FILENAME: /u01/app/oracle/cfgtoollogs/dbua/upgrade2018-01-17_11-42-08-AM/DB01/catupgrd0.log AT LINE NUMBER: 825076
------------------------------------------------------
Identifier CONTEXT 18-01-17 12:37:20
SCRIPT = [/u01/app/oracle/product/12.2.0/dbhome_1/ctx/admin/ctxposup.sql] <===== STATEMENT 1
ERROR = [ORA-01403: no data found ORA-06512: at line 28
ORA-06512: at line 8
]
STATEMENT = [declare
l_owner# number;
l_errnum number;
l_count number;
begin
select user# into l_owner# from sys."_BASE_USER" where name='CTXSYS';
select idx_id into l_count from dr$index where <===== STATEMENT 2
idx_name = 'ENT_EXT_DICT_OBJ' and
idx_owner# = l_owner#;
/* if this object does not exist yet, create it. */
if (0 = l_count) then
CTX_DDL.create_policy('CTXSYS.ENT_EXT_DICT_OBJ', <===== STATEMENT 3
filter => 'CTXSYS.NULL_FILTER',
section_group => 'CTXSYS.NULL_SECTION_GROUP',
lexer => 'CTXSYS.BASIC_LEXER',
stoplist => 'CTXSYS.EMPTY_STOPLIST',
wordlist => 'CTXSYS.BASIC_WORDLIST'
);
end if;
exception
when others then
l_errnum := SQLCODE;
if (l_errnum = -20000) then
null;
else
raise;
end if;
end;]
ERRORS FOUND: During Upgrade
FILENAME: /u01/app/oracle/cfgtoollogs/dbua/upgrade2018-01-17_11-42-08-AM/DB01/catupgrd0.log AT LINE NUMBER: 825076
------------------------------------------------------
Identifier CONTEXT 18-01-17 12:37:20
SCRIPT = [/u01/app/oracle/product/12.2.0/dbhome_1/ctx/admin/ctxposup.sql] <===== STATEMENT 1
ERROR = [ORA-01403: no data found ORA-06512: at line 28
ORA-06512: at line 8
]
STATEMENT = [declare
l_owner# number;
l_errnum number;
l_count number;
begin
select user# into l_owner# from sys."_BASE_USER" where name='CTXSYS';
select idx_id into l_count from dr$index where <===== STATEMENT 2
idx_name = 'ENT_EXT_DICT_OBJ' and
idx_owner# = l_owner#;
/* if this object does not exist yet, create it. */
if (0 = l_count) then
CTX_DDL.create_policy('CTXSYS.ENT_EXT_DICT_OBJ', <===== STATEMENT 3
filter => 'CTXSYS.NULL_FILTER',
section_group => 'CTXSYS.NULL_SECTION_GROUP',
lexer => 'CTXSYS.BASIC_LEXER',
stoplist => 'CTXSYS.EMPTY_STOPLIST',
wordlist => 'CTXSYS.BASIC_WORDLIST'
);
end if;
exception
when others then
l_errnum := SQLCODE;
if (l_errnum = -20000) then
null;
else
raise;
end if;
end;]
Script ctxposup.sql (STATEMENT 1) which upgrades Oracle Text raised ORA-01403 while running PL/SQL block listed in the above. Testing individual statements in the block as following:
sys@DB01> select user# from sys."_BASE_USER" where name='CTXSYS';
USER#
----------
100
sys@DB01> ALTER SESSION SET CURRENT_SCHEMA = CTXSYS;
Session altered.
sys@DB01> select idx_id from dr$index where
2 idx_name = 'ENT_EXT_DICT_OBJ' and
3 idx_owner# = 100;
no rows selected
USER#
----------
100
sys@DB01> ALTER SESSION SET CURRENT_SCHEMA = CTXSYS;
Session altered.
sys@DB01> select idx_id from dr$index where
2 idx_name = 'ENT_EXT_DICT_OBJ' and
3 idx_owner# = 100;
no rows selected
The object CTXSYS.ENT_EXT_DICT_OBJ (a policy of Oracle Text) does not exist in the database. Therefore, STATEMENT 2 (select ... into ... from) returned ORA-01403. Apparently, The purpose of STATEMENT 2 is to test whether CTXSYS.ENT_EXT_DICT_OBJ exists, but listing records is not a better choice for exist-testing than counting the number of records. Let's try to replace STATEMENT 2 in script filectxposup.sql with following code
select count(idx_id) into l_count from dr$index where
Start the upgrade again, it succeeded!
Monday, January 15, 2018
ORA-65500 When using DUPLICATE to Create Standby Database
Oracle Database - Enterprise Edition - Version 12.2.0.1 + RU 12.2.0.1.171017
Operation System - Oracle Solaris 11.3 SPARC
When running RMAN command DUPLICATE to create physical standby database as following:
Got error ORA-65500:
Oracle reported this issue as Bug 20977794 which causes the ORA-65500 to be returned with any attempt to change DB_UNIQUE_NAME for database when a CRS resource for it exists. As RMAN attempts to make a change to db_unique_name, this error will occur.
Current system is configured with Oracle Restart (Grid Infrastructure - standalone) 12.2.0.1. Although it is supposed to be fixed in 12.2.0.1 base release, the error still happened. Anyway, we can safely try to remove database resource from crs registry as workaround,
The database DBNEW does exist in the crs registry, try to remove it
Then DUPLICATE can run successfully.
Operation System - Oracle Solaris 11.3 SPARC
When running RMAN command DUPLICATE to create physical standby database as following:
RMAN> duplicate target database
2> for standby
3> from active database
4> dorecover
5> spfile
6> reset control_files
7> set db_unique_name='DBNEW'
8> set fal_server='DB01'
9> nofilenamecheck;
using target database control file instead of recovery catalog
Starting Duplicate Db at 15-JAN-18
current log archived
... ...
2> for standby
3> from active database
4> dorecover
5> spfile
6> reset control_files
7> set db_unique_name='DBNEW'
8> set fal_server='DB01'
9> nofilenamecheck;
using target database control file instead of recovery catalog
Starting Duplicate Db at 15-JAN-18
current log archived
... ...
Got error ORA-65500:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 01/15/2018 11:46:19
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of sql command on clone_default channel at 01/15/2018 11:46:19
RMAN-11003: failure during parse/execution of SQL statement: alter system set db_unique_name='DBNEW' comment='' scope=spfile
ORA-32017: failure in updating SPFILE
ORA-65500: could not modify DB_UNIQUE_NAME, resource exists
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 01/15/2018 11:46:19
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of sql command on clone_default channel at 01/15/2018 11:46:19
RMAN-11003: failure during parse/execution of SQL statement: alter system set db_unique_name='DBNEW' comment='' scope=spfile
ORA-32017: failure in updating SPFILE
ORA-65500: could not modify DB_UNIQUE_NAME, resource exists
Oracle reported this issue as Bug 20977794 which causes the ORA-65500 to be returned with any attempt to change DB_UNIQUE_NAME for database when a CRS resource for it exists. As RMAN attempts to make a change to db_unique_name, this error will occur.
Current system is configured with Oracle Restart (Grid Infrastructure - standalone) 12.2.0.1. Although it is supposed to be fixed in 12.2.0.1 base release, the error still happened. Anyway, we can safely try to remove database resource from crs registry as workaround,
[oracle@dbaplus]$ srvctl config database
DBNEW
DBNEW
The database DBNEW does exist in the crs registry, try to remove it
[oracle@dbaplus]$ srlvctl remove database -db DBNEW
Remove the database DBNEW? (y/[n]) y
[oracle@dbaplus]$
Remove the database DBNEW? (y/[n]) y
[oracle@dbaplus]$
Then DUPLICATE can run successfully.
Friday, December 15, 2017
ASM instance shows 'Down' in OEM 13.2 after upgraded to 12.2
Standalone Grid Infrastruture (Oracle Restart) is upgraded to 12.2 and everything works well. However, the ASM instance shows 'Down' in Enterprise Manager 13.2.
Checked 'Monitoring Configuration' of ASM target, all information is correct and 'Test Connection' succeeds. We can log into ASM instance page in OEM to do management tasks (creat/drop diskgroup, etc). Everything looks good except the Target Status.
Log onto the host where ASM is running on, and run following commands from OEM agent home:
1. Get the target name of ASM instance
$ emctl config agent listtargets | grep ASM
[+ASM_host01.dbaplus.ca, osm_instance]
2. Find out the 'real time' results of the 'REsponse' metric (test connection from local agent to target)
$ emctl getmetric agent +ASM_host01.dbaplus.ca,osm_instance,Response
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved.
Status,oraerr
0,Failed to connect: java.sql.SQLException: ORA-28040: No matching authentication protocol
Wow, '0' is returned with 'ORA-28040'. I believe many DBAs saw error 28040 when upgrading their databases to 12c. OEM agent (12c Release 5) is using old Oracle client (10g) to access ASM instance which is running on 12.2.
Solution:
Add following lines to $GRID_HOME/network/admin/sqlnet.ora
SQLNET.ALLOWED_LOGON_VERSION_SERVER=10
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=10
Test the connection again:
$emctl getmetric agent +ASM_host01.dbaplus.ca,osm_instance,Response
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved.
Status,oraerr
1,
'1' is returned and indicates that the ASM is up and running.
And the ASM target shows 'Up' in OEM.
Checked 'Monitoring Configuration' of ASM target, all information is correct and 'Test Connection' succeeds. We can log into ASM instance page in OEM to do management tasks (creat/drop diskgroup, etc). Everything looks good except the Target Status.
Log onto the host where ASM is running on, and run following commands from OEM agent home:
1. Get the target name of ASM instance
$ emctl config agent listtargets | grep ASM
[+ASM_host01.dbaplus.ca, osm_instance]
2. Find out the 'real time' results of the 'REsponse' metric (test connection from local agent to target)
$ emctl getmetric agent +ASM_host01.dbaplus.ca,osm_instance,Response
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved.
Status,oraerr
0,Failed to connect: java.sql.SQLException: ORA-28040: No matching authentication protocol
Wow, '0' is returned with 'ORA-28040'. I believe many DBAs saw error 28040 when upgrading their databases to 12c. OEM agent (12c Release 5) is using old Oracle client (10g) to access ASM instance which is running on 12.2.
Solution:
Add following lines to $GRID_HOME/network/admin/sqlnet.ora
SQLNET.ALLOWED_LOGON_VERSION_SERVER=10
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=10
Test the connection again:
$emctl getmetric agent +ASM_host01.dbaplus.ca,osm_instance,Response
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved.
Status,oraerr
1,
'1' is returned and indicates that the ASM is up and running.
And the ASM target shows 'Up' in OEM.
Saturday, October 28, 2017
Alert Log Filter Expression Editable when view-mode in Monitoring Template of OEM 13.2
OMS 13.2.0.0.171017
DB Plug-in 13.2.2.0.170930
EM Agent 13.2.0.0.170930
A customer asked for helping to apply patch 24334660 on the Enterprise Manager 13.2. Before starting patching, check the Oracle Home patching level as following,
Apparently, the OMS home has been applied PSU 13.2.0.0.171017, which already included the fix of the bug. Customer performs following steps to reproduce his issue,
In Monitoring Templates window, click the name of desired template and get into template details window, then click the Glasses icon in 'Edit' column besides metric 'Generic Alert Log Error'. In the new windows, the 'Alert Log Filter Expression' can be changed, but the new value cannot be saved (retained) after click OK. The value goes back to old value when come into this window again. It seems a bug/defect here. Actually, the window is a view-mode (read only) window with title 'View Advanced Settings: Generic Alert Log Error'. Yes, it gives you opportunity to type words in view-mode window, but does not change anything, a funny behaviour.
It is not a correct way to edit a metric in a monitoring template by clicking the template name. Instead, select the record (row) of the template and click 'Edit' button. In 'Edit Monitoring Template' window, the metric can be changed permanently.
DB Plug-in 13.2.2.0.170930
EM Agent 13.2.0.0.170930
A customer asked for helping to apply patch 24334660 on the Enterprise Manager 13.2. Before starting patching, check the Oracle Home patching level as following,
[oracle@oms1]$ /u01/app/oracle/product/em/middleware/OPatch/opatch lsinventory
Oracle Interim Patch Installer version 13.9.1.3.0
Copyright (c) 2017, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/em/middleware
Central Inventory : /u01/app/oracle/oraInventory
from : /u01/app/oracle/product/em/middleware/oraInst.loc
OPatch version : 13.9.1.3.0
OUI version : 13.9.1.0.0
OUI location : /u01/app/oracle/product/em/middleware/oui
Log file location : /u01/app/oracle/product/em/middleware/cfgtoollogs/opatch/opatch2017-10-28_10-43-03AM_1.log
OPatch detects the Middleware Home as "/u01/app/oracle/product/em/middleware"
<<Output truncated>>
Patch 26625183 : applied on Fri Oct 20 16:32:23 EDT 2017
Unique Patch ID: 21574333
Patch description: "EMBP Patch Set Update 13.2.0.0.171017"
Created on 8 Oct 2017, 20:17:42 hrs PST8PDT
Bugs fixed:
26782785, 25341210, 26090681, 26680573, 25110496, 24953203, 24459731
26364865, 25861385, 26027847, 26542313, 20511097, 25967196, 25230688
24701883, 25326446, 25217253, 25534065, 23506806, 25244726, 25179524
25136456, 22143858, 25354849, 25615522, 25674055, 25219896, 25255429
23251874, 24334660, 24944461, 23743109, 24426862, 25701178, 25268183
23549064, 25506784, 25497731, 25497622, 24789016, 24742172, 25355038
25163555, 25044101, 24608207, 23619694, 24753427, 24734911, 23705752
24917276, 24614251, 22901546, 23196054, 21470098
<<Output truncated>>
Oracle Interim Patch Installer version 13.9.1.3.0
Copyright (c) 2017, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/em/middleware
Central Inventory : /u01/app/oracle/oraInventory
from : /u01/app/oracle/product/em/middleware/oraInst.loc
OPatch version : 13.9.1.3.0
OUI version : 13.9.1.0.0
OUI location : /u01/app/oracle/product/em/middleware/oui
Log file location : /u01/app/oracle/product/em/middleware/cfgtoollogs/opatch/opatch2017-10-28_10-43-03AM_1.log
OPatch detects the Middleware Home as "/u01/app/oracle/product/em/middleware"
<<Output truncated>>
Patch 26625183 : applied on Fri Oct 20 16:32:23 EDT 2017
Unique Patch ID: 21574333
Patch description: "EMBP Patch Set Update 13.2.0.0.171017"
Created on 8 Oct 2017, 20:17:42 hrs PST8PDT
Bugs fixed:
26782785, 25341210, 26090681, 26680573, 25110496, 24953203, 24459731
26364865, 25861385, 26027847, 26542313, 20511097, 25967196, 25230688
24701883, 25326446, 25217253, 25534065, 23506806, 25244726, 25179524
25136456, 22143858, 25354849, 25615522, 25674055, 25219896, 25255429
23251874, 24334660, 24944461, 23743109, 24426862, 25701178, 25268183
23549064, 25506784, 25497731, 25497622, 24789016, 24742172, 25355038
25163555, 25044101, 24608207, 23619694, 24753427, 24734911, 23705752
24917276, 24614251, 22901546, 23196054, 21470098
<<Output truncated>>
Apparently, the OMS home has been applied PSU 13.2.0.0.171017, which already included the fix of the bug. Customer performs following steps to reproduce his issue,
In Monitoring Templates window, click the name of desired template and get into template details window, then click the Glasses icon in 'Edit' column besides metric 'Generic Alert Log Error'. In the new windows, the 'Alert Log Filter Expression' can be changed, but the new value cannot be saved (retained) after click OK. The value goes back to old value when come into this window again. It seems a bug/defect here. Actually, the window is a view-mode (read only) window with title 'View Advanced Settings: Generic Alert Log Error'. Yes, it gives you opportunity to type words in view-mode window, but does not change anything, a funny behaviour.
It is not a correct way to edit a metric in a monitoring template by clicking the template name. Instead, select the record (row) of the template and click 'Edit' button. In 'Edit Monitoring Template' window, the metric can be changed permanently.
Tuesday, August 15, 2017
Birthday Attacks against TLS ciphers Used by OEM 13.2 Agent
Oracle Enterprise Manager 13.2 uses following cipher suites as default value for SSL communication,
SSL_RSA_WITH_RC4_128_MD5, SSL_RSA_WITH_RC4_128_SHA and SSL_RSA_WITH_3DES_EDE_CBC_SHA
Which support ciphers as listed by script CipherScan.bsh (script details can be found at the end of this post),
Here, cipher DES-CBC3-SHA could introduce a security threat of 'Birthday Attack against TLS ciphers with 64bit block size vulnerability'. Remote attackers can obtain cleartext data via a birthday attack against a long-duration encrypted session of all versions of SSL/TLS protocol supporting cipher suites which use DES, 3DES, IDEA or RC2 as the symmetric encryption cipher in CBC mode.
It can be disabled/removed by configuring agent's properties. Before changing the agent property, one of following method can be used to check current setting,
* Run command: <AGENT_INST_HOME>/bin/emctl getproperty agent -name SSLCipherSuites or
* Check agent property file by running: grep -i <AGENT_INST_HOME>/sysman/config/emd.properties
The property is using default value and not set yet.
Remove weak cipher suite by setting SSLCipherSuites property of agent,
If the Agent is running on AIX platform, use the cipher SSL_RSA_WITH_AES_128_CBC_SHA only.
Check current value,
Restart agent,
Verify the supported ciphers,
There is no threat (weak cipher) any more.
Appendex. Script file CipherScan.bsh
SSL_RSA_WITH_RC4_128_MD5, SSL_RSA_WITH_RC4_128_SHA and SSL_RSA_WITH_3DES_EDE_CBC_SHA
Which support ciphers as listed by script CipherScan.bsh (script details can be found at the end of this post),
[oracle@host01]$ ./CipherScan.bsh host01.dbaplus.ca:3872
Following Cipher(s) is/are supported on server host01.dbaplus.ca:3872
ECDHE-RSA-AES128-SHA256
ECDHE-RSA-AES128-SHA
DHE-RSA-AES128-SHA256
DHE-RSA-AES128-SHA
AES128-SHA256
AES128-SHA
DES-CBC3-SHA
Following Cipher(s) is/are supported on server host01.dbaplus.ca:3872
ECDHE-RSA-AES128-SHA256
ECDHE-RSA-AES128-SHA
DHE-RSA-AES128-SHA256
DHE-RSA-AES128-SHA
AES128-SHA256
AES128-SHA
DES-CBC3-SHA
Here, cipher DES-CBC3-SHA could introduce a security threat of 'Birthday Attack against TLS ciphers with 64bit block size vulnerability'. Remote attackers can obtain cleartext data via a birthday attack against a long-duration encrypted session of all versions of SSL/TLS protocol supporting cipher suites which use DES, 3DES, IDEA or RC2 as the symmetric encryption cipher in CBC mode.
It can be disabled/removed by configuring agent's properties. Before changing the agent property, one of following method can be used to check current setting,
* Run command: <AGENT_INST_HOME>/bin/emctl getproperty agent -name SSLCipherSuites or
* Check agent property file by running: grep -i <AGENT_INST_HOME>/sysman/config/emd.properties
[oracle@host01]$ cd /u01/software/em/agent/agent_inst
[oracle@host01]$ ./bin/emctl getproperty agent -name SSLCipherSuites
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation. All rights reserved.
SSLCipherSuites is unset; default value is SSL_RSA_WITH_RC4_128_MD5:SSL_RSA_WITH_RC4_128_SHA:SSL_RSA_WITH_3DES_EDE_CBC_SHA
[oracle@host01]$
[oracle@host01]$ grep -i 'SSLCipherSuites' ./sysman/config/emd.properties
[oracle@host01]$
[oracle@host01]$ ./bin/emctl getproperty agent -name SSLCipherSuites
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation. All rights reserved.
SSLCipherSuites is unset; default value is SSL_RSA_WITH_RC4_128_MD5:SSL_RSA_WITH_RC4_128_SHA:SSL_RSA_WITH_3DES_EDE_CBC_SHA
[oracle@host01]$
[oracle@host01]$ grep -i 'SSLCipherSuites' ./sysman/config/emd.properties
[oracle@host01]$
The property is using default value and not set yet.
Remove weak cipher suite by setting SSLCipherSuites property of agent,
[oracle@host01]$ cd /u01/software/em/agent/agent_inst
[oracle@host01]$ ./bin/emctl setproperty agent -name SSLCipherSuites -value TLS_RSA_WITH_AES_128_CBC_SHA:TLS_RSA_WITH_AES_256_CBC_SHA:RSA_WITH_AES_256_CBC_SHA256
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation. All rights reserved.
EMD setproperty succeeded
[oracle@host01]$ ./bin/emctl setproperty agent -name SSLCipherSuites -value TLS_RSA_WITH_AES_128_CBC_SHA:TLS_RSA_WITH_AES_256_CBC_SHA:RSA_WITH_AES_256_CBC_SHA256
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation. All rights reserved.
EMD setproperty succeeded
If the Agent is running on AIX platform, use the cipher SSL_RSA_WITH_AES_128_CBC_SHA only.
[oracle@host01]$ ./bin/emctl setproperty agent -name SSLCipherSuites -value SSL_RSA_WITH_AES_128_CBC_SHA
Check current value,
[oracle@host01]$ cd /u01/software/em/agent/agent_inst
[oracle@host01]$ ./bin/emctl getproperty agent -name SSLCipherSuites
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation. All rights reserved.
SSLCipherSuites=TLS_RSA_WITH_AES_128_CBC_SHA:TLS_RSA_WITH_AES_256_CBC_SHA:RSA_WITH_AES_256_CBC_SHA256
[oracle@host01]$
[oracle@host01]$ grep -i ./sysman/config/'SSLCipherSuites' emd.properties
SSLCipherSuites=TLS_RSA_WITH_AES_128_CBC_SHA:TLS_RSA_WITH_AES_256_CBC_SHA:RSA_WITH_AES_256_CBC_SHA256
[oracle@host01]$
[oracle@host01]$ ./bin/emctl getproperty agent -name SSLCipherSuites
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation. All rights reserved.
SSLCipherSuites=TLS_RSA_WITH_AES_128_CBC_SHA:TLS_RSA_WITH_AES_256_CBC_SHA:RSA_WITH_AES_256_CBC_SHA256
[oracle@host01]$
[oracle@host01]$ grep -i ./sysman/config/'SSLCipherSuites' emd.properties
SSLCipherSuites=TLS_RSA_WITH_AES_128_CBC_SHA:TLS_RSA_WITH_AES_256_CBC_SHA:RSA_WITH_AES_256_CBC_SHA256
[oracle@host01]$
Restart agent,
[oracle@host01]$ /u01/software/em/agent/agent_13.2.0.0.0/bin/emctl stop agent
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation. All rights reserved.
Stopping agent ... stopped.
[oracle@host01]$
[oracle@host01]$ /u01/software/em/agent/agent_13.2.0.0.0/bin/emctl start agent
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation. All rights reserved.
Starting agent ............... started.
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation. All rights reserved.
Stopping agent ... stopped.
[oracle@host01]$
[oracle@host01]$ /u01/software/em/agent/agent_13.2.0.0.0/bin/emctl start agent
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation. All rights reserved.
Starting agent ............... started.
Verify the supported ciphers,
[oracle@host01]$ ./CipherScan.bsh host01.dbaplus.ca:3872
Following Cipher(s) is/are supported on server host01.dbaplus.ca:3872
AES128-SHA
Following Cipher(s) is/are supported on server host01.dbaplus.ca:3872
AES128-SHA
There is no threat (weak cipher) any more.
Appendex. Script file CipherScan.bsh
#!/usr/bin/bash
# -----------------------------------------------
# Scan available Cipher on given server with port
# Usage:
# CipherScan.bsh <SERVER_IP>:<PORT>
#
# For example,
# ./CipherScan.bsh host01.dbaplus.ca:3872
# -----------------------------------------------
SERVER=$1
CIPHER_SUPPORTED=$(openssl ciphers 'ALL:eNULL' | sed -e 's/:/ /g')
echo -e "\n Following Cipher(s) is/are supported on server $SERVER \n"
for cipher in ${CIPHER_SUPPORTED[@]}
do
result=$(echo -n | openssl s_client -cipher "$cipher" -connect $SERVER 2>&1)
if [[ "$result" =~ "Connection refused" ]] ; then
echo $result
break
fi
if ! [[ "$result" =~ ":error:" ]] ; then
echo " "$cipher
fi
done
# -----------------------------------------------
# Scan available Cipher on given server with port
# Usage:
# CipherScan.bsh <SERVER_IP>:<PORT>
#
# For example,
# ./CipherScan.bsh host01.dbaplus.ca:3872
# -----------------------------------------------
SERVER=$1
CIPHER_SUPPORTED=$(openssl ciphers 'ALL:eNULL' | sed -e 's/:/ /g')
echo -e "\n Following Cipher(s) is/are supported on server $SERVER \n"
for cipher in ${CIPHER_SUPPORTED[@]}
do
result=$(echo -n | openssl s_client -cipher "$cipher" -connect $SERVER 2>&1)
if [[ "$result" =~ "Connection refused" ]] ; then
echo $result
break
fi
if ! [[ "$result" =~ ":error:" ]] ; then
echo " "$cipher
fi
done
Wednesday, July 26, 2017
Configure SQL Server 2016 Always On with Core Version of Windows Server 2016 without Active Directory Domain
This is a step by step demonstration to help database administrator deploy SQL Server 2016 Always On high availability solution on core version of Windows server 2016 without Active Directory domain. The demonstration environment will be created as following,
Tuesday, February 21, 2017
Oracle Rules Manager 11.2.0.4.0 INVALID with invalid JAVA Classes
Oracle database 11.2.0.4 was created with INVALID component 'Oracle Rules Manager',
Find out invalid objects owned by schema 'EXFSYS' which could INVALID the component Oracle Rules Manager,
Check errors of found invalid JAVA CLASSes,
Oracle Rules Manager is in INVALID status because the referenced object/names created by installing XDK cannot be resolved/found. EXFSYS (owner of Oracle Rules Manager) JAVA Classes are dependent on XDK. Therefore installing XDK will validate Oracle Rules Manager,
Check component status,
system@DB01> select schema,comp_name,version,status from dba_registry;
SCHEMA COMP_NAME VERSION STATUS
------------------------------ -------------------------------------------------- ------------------------------
APEX_050000 Oracle Application Express 5.0.3.00.03 VALID
EXFSYS Oracle Expression Filter 11.2.0.4.0 VALID
EXFSYS Oracle Rules Manager 11.2.0.4.0 INVALID
XDB Oracle XML Database 11.2.0.4.0 VALID
WMSYS Oracle Workspace Manager 11.2.0.4.0 VALID
SYS Oracle Database Catalog Views 11.2.0.4.0 VALID
SYS Oracle Database Packages and Types 11.2.0.4.0 VALID
SYS JServer JAVA Virtual Machine 11.2.0.4.0 VALID
SCHEMA COMP_NAME VERSION STATUS
------------------------------ -------------------------------------------------- ------------------------------
APEX_050000 Oracle Application Express 5.0.3.00.03 VALID
EXFSYS Oracle Expression Filter 11.2.0.4.0 VALID
EXFSYS Oracle Rules Manager 11.2.0.4.0 INVALID
XDB Oracle XML Database 11.2.0.4.0 VALID
WMSYS Oracle Workspace Manager 11.2.0.4.0 VALID
SYS Oracle Database Catalog Views 11.2.0.4.0 VALID
SYS Oracle Database Packages and Types 11.2.0.4.0 VALID
SYS JServer JAVA Virtual Machine 11.2.0.4.0 VALID
Find out invalid objects owned by schema 'EXFSYS' which could INVALID the component Oracle Rules Manager,
system@DB01> select object_name,object_type,status
2 from dba_objects
3 where owner='EXFSYS' and status='INVALID';
OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ------------------- -------
/53ad31cb_TagsLocator JAVA CLASS INVALID
/357abd97_XPathFilter JAVA CLASS INVALID
2 from dba_objects
3 where owner='EXFSYS' and status='INVALID';
OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ------------------- -------
/53ad31cb_TagsLocator JAVA CLASS INVALID
/357abd97_XPathFilter JAVA CLASS INVALID
Check errors of found invalid JAVA CLASSes,
system@DB01> select name,text
2 from dba_errors
3 where owner='EXFSYS' and name in ('/53ad31cb_TagsLocator','/357abd97_XPathFilter');
NAME TEXT
------------------------- ------------------------------------------------------------
/357abd97_XPathFilter ORA-29534: referenced object EXFSYS.oracle/expfil/xpath/TagsLocator could not be resolved
/53ad31cb_TagsLocator ORA-29521: referenced name oracle/xml/parser/v2/DOMParser could not be found
/53ad31cb_TagsLocator ORA-29521: referenced name oracle/xml/parser/v2/XMLParseException could not be found
/53ad31cb_TagsLocator ORA-29521: referenced name oracle/xml/parser/v2/XMLDocument could not be found
2 from dba_errors
3 where owner='EXFSYS' and name in ('/53ad31cb_TagsLocator','/357abd97_XPathFilter');
NAME TEXT
------------------------- ------------------------------------------------------------
/357abd97_XPathFilter ORA-29534: referenced object EXFSYS.oracle/expfil/xpath/TagsLocator could not be resolved
/53ad31cb_TagsLocator ORA-29521: referenced name oracle/xml/parser/v2/DOMParser could not be found
/53ad31cb_TagsLocator ORA-29521: referenced name oracle/xml/parser/v2/XMLParseException could not be found
/53ad31cb_TagsLocator ORA-29521: referenced name oracle/xml/parser/v2/XMLDocument could not be found
Oracle Rules Manager is in INVALID status because the referenced object/names created by installing XDK cannot be resolved/found. EXFSYS (owner of Oracle Rules Manager) JAVA Classes are dependent on XDK. Therefore installing XDK will validate Oracle Rules Manager,
conn / as sysdba
@?/xdk/admin/initxml.sql
@?/rdbms/admin/utlrp.sql
@?/xdk/admin/initxml.sql
@?/rdbms/admin/utlrp.sql
Check component status,
system@DB01> select schema,comp_name,version,status from dba_registry;
SCHEMA COMP_NAME VERSION STATUS
------------------------------ -------------------------------------------------- ------------------------------
APEX_050000 Oracle Application Express 5.0.3.00.03 VALID
EXFSYS Oracle Expression Filter 11.2.0.4.0 VALID
EXFSYS Oracle Rules Manager 11.2.0.4.0 VALID
XDB Oracle XML Database 11.2.0.4.0 VALID
WMSYS Oracle Workspace Manager 11.2.0.4.0 VALID
SYS Oracle Database Catalog Views 11.2.0.4.0 VALID
SYS Oracle Database Packages and Types 11.2.0.4.0 VALID
SYS JServer JAVA Virtual Machine 11.2.0.4.0 VALID
SCHEMA COMP_NAME VERSION STATUS
------------------------------ -------------------------------------------------- ------------------------------
APEX_050000 Oracle Application Express 5.0.3.00.03 VALID
EXFSYS Oracle Expression Filter 11.2.0.4.0 VALID
EXFSYS Oracle Rules Manager 11.2.0.4.0 VALID
XDB Oracle XML Database 11.2.0.4.0 VALID
WMSYS Oracle Workspace Manager 11.2.0.4.0 VALID
SYS Oracle Database Catalog Views 11.2.0.4.0 VALID
SYS Oracle Database Packages and Types 11.2.0.4.0 VALID
SYS JServer JAVA Virtual Machine 11.2.0.4.0 VALID
Thursday, January 26, 2017
OMS version not checked yet while emctl upload agent
After OEM managed host was bounced, the agent status shows 'DOWN' in OEM. It happened because old version agent which pointed to old OMS server was started.
Find out if OEM agent is running on target,
There is an instance of OEM agent running from Oracle Home '/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0'. Test the communication between managed host and the Management Service,
Command 'emctl upload agent' returned error 'OMS version not checked yet', and 'emctl pingOMS' is complaining that OMS server cannot be connected. However, current OMS server is oms.dbaplus.ca and not oem.dbaplus.ca which is retired. Apparently, bouncing host automatically started an old (obsolete) agent which worked with old OMS server.
Let's fix it,
The first entry in file /etc/oragchomelist makes OS start old agent (version 12.1.0.4), remove it from /etc/oragchomelist and restart host. After host is up, check agent status,
Agent is running on managed host, and it also shows 'UP' in OEM.
Find out if OEM agent is running on target,
$ ps -ef | grep emagent
oracle 63111344 41484628 0 14:10:15 pts/0 0:00 grep emagent
oracle 7143666 1 0 13:55:39 pts/4 0:00 /u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/perl/bin/perl /u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/bin/emwd.pl agent /u01/app/oracle/product/12.1.0/agent/agent_inst/sysman/log/emagent.nohup
oracle 26280338 7143666 0 13:55:51 pts/4 0:06 /u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/jdk/bin/java -Xmx160M -server -Djava.security.egd=file:///dev/./urandom -Dsun.lang.ClassLoader.allowArraySyntax=true -Xgcpolicy:gencon -Dwatchdog.pid=7143666 -cp /u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/jdbc/lib/ojdbc5.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/ucp/lib/ucp.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/modules/oracle.http_client_11.1.1.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/lib/xmlparserv2.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/lib/jsch.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/lib/optic.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/modules/oracle.dms_11.1.1/dms.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/modules/oracle.odl_11.1.1/ojdl.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/modules/oracle.odl_11.1.1/ojdl2.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/sysman/jlib/log4j-core.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/jlib/gcagent_core.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/sysman/jlib/emagentSDK-intg.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/sysman/jlib/emagentSDK.jar oracle.sysman.gcagent.tmmain.TMMain
oracle 63111344 41484628 0 14:10:15 pts/0 0:00 grep emagent
oracle 7143666 1 0 13:55:39 pts/4 0:00 /u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/perl/bin/perl /u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/bin/emwd.pl agent /u01/app/oracle/product/12.1.0/agent/agent_inst/sysman/log/emagent.nohup
oracle 26280338 7143666 0 13:55:51 pts/4 0:06 /u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/jdk/bin/java -Xmx160M -server -Djava.security.egd=file:///dev/./urandom -Dsun.lang.ClassLoader.allowArraySyntax=true -Xgcpolicy:gencon -Dwatchdog.pid=7143666 -cp /u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/jdbc/lib/ojdbc5.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/ucp/lib/ucp.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/modules/oracle.http_client_11.1.1.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/lib/xmlparserv2.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/lib/jsch.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/lib/optic.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/modules/oracle.dms_11.1.1/dms.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/modules/oracle.odl_11.1.1/ojdl.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/modules/oracle.odl_11.1.1/ojdl2.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/sysman/jlib/log4j-core.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/jlib/gcagent_core.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/sysman/jlib/emagentSDK-intg.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/sysman/jlib/emagentSDK.jar oracle.sysman.gcagent.tmmain.TMMain
There is an instance of OEM agent running from Oracle Home '/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0'. Test the communication between managed host and the Management Service,
$ /u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/bin/emctl upload agent
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD upload error:full upload has failed: uploadXMLFiles skipped :: OMS version not checked yet. If this issue persists check trace files for ping to OMS related errors. (OMS_DOWN)
$ /u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/bin/emctl pingOMS
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD pingOMS error: Failure connecting to https://oem.dbaplus.ca:1159/empbs/upload , err Connection refused
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD upload error:full upload has failed: uploadXMLFiles skipped :: OMS version not checked yet. If this issue persists check trace files for ping to OMS related errors. (OMS_DOWN)
$ /u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/bin/emctl pingOMS
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD pingOMS error: Failure connecting to https://oem.dbaplus.ca:1159/empbs/upload , err Connection refused
Command 'emctl upload agent' returned error 'OMS version not checked yet', and 'emctl pingOMS' is complaining that OMS server cannot be connected. However, current OMS server is oms.dbaplus.ca and not oem.dbaplus.ca which is retired. Apparently, bouncing host automatically started an old (obsolete) agent which worked with old OMS server.
Let's fix it,
$ cat /etc/oragchomelist
/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0:/u01/app/oracle/product/12.1.0/agent/agent_inst
/u01/app/oracle/product/em/agent/agent_13.2.0.0.0:/u01/app/oracle/product/em/agent/agent_inst
/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0:/u01/app/oracle/product/12.1.0/agent/agent_inst
/u01/app/oracle/product/em/agent/agent_13.2.0.0.0:/u01/app/oracle/product/em/agent/agent_inst
The first entry in file /etc/oragchomelist makes OS start old agent (version 12.1.0.4), remove it from /etc/oragchomelist and restart host. After host is up, check agent status,
$ cat /etc/oragchomelist
/u01/app/oracle/product/em/agent/agent_13.2.0.0.0:/u01/app/oracle/product/em/agent/agent_inst
$ ps -ef | grep emagent
oracle 57082074 41484628 0 16:21:18 pts/0 0:00 grep emagent
oracle 63766548 1 0 15:00:53 pts/0 0:00 /u01/app/oracle/product/em/agent/agent_13.2.0.0.0/perl/bin/perl /u01/app/oracle/product/em/agent/agent_13.2.0.0.0/bin/emwd.pl agent /u01/app/oracle/product/em/agent/agent_inst/sysman/log/emagent.nohup
oracle 29950448 63766548 0 15:01:00 pts/0 1:20 /u01/app/oracle/product/em/agent/agent_13.2.0.0.0/oracle_common/jdk/bin/java -Xmx183M -server -Djava.security.egd=file:///dev/./urandom -Dsun.lang.ClassLoader.allowArraySyntax=true -Xgcpolicy:gencon -Dwatchdog.pid=63766548 -cp /u01/app/oracle/product/em/agent/agent_13.2.0.0.0/jdbc/lib/ojdbc7.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/ucp/lib/ucp.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/oracle_common/modules/jsch-0.1.53.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/oracle_common/modules/com.oracle.http_client.http_client_12.1.3.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/oracle_common/modules/oracle.xdk_12.1.3/xmlparserv2.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/oracle_common/modules/oracle.dms_12.1.3/dms.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/oracle_common/modules/oracle.odl_12.1.3/ojdl.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/oracle_common/modules/oracle.odl_12.1.3/ojdl2.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/lib/optic.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/sysman/jlib/log4j-core.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/jlib/gcagent_core.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/sysman/jlib/emagentSDK-intg.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/sysman/jlib/emagentSDK.jar oracle.sysman.gcagent.tmmain.TMMain
$ /u01/app/oracle/product/em/agent/agent_13.2.0.0.0/bin/emctl upload agent
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD upload completed successfully
$ /u01/app/oracle/product/em/agent/agent _13.2.0.0.0/bin/emctl status agent
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 13.2.0.0.0
OMS Version : 13.2.0.0.0
Protocol Version : 12.1.0.1.0
Agent Home : /u01/app/oracle/product/em/agent/agent_inst
Agent Log Directory : /u01/app/oracle/product/em/agent/agent_inst/sysman/log
Agent Binaries : /u01/app/oracle/product/em/agent/agent_13.2.0.0.0
Core JAR Location : /u01/app/oracle/product/em/agent/agent_13.2.0.0.0/jlib
Agent Process ID : 11665618
Parent Process ID : 13238612
Agent URL : https://host01.dbaplus.ca:3872/emd/main/
Local Agent URL in NAT : https://host01.dbaplus.ca:3872/emd/main/
Repository URL : https://oms.dbaplus.ca:4903/empbs/upload
Started at : 2017-01-26 14:25:33
Started by user : oracle
Operating System : AIX version 7.1 (ppc64)
Number of Targets : 23
Last Reload : (none)
Last successful upload : 2017-01-26 14:27:22
Last attempted upload : 2017-01-26 14:27:22
Total Megabytes of XML files uploaded so far : 0.62
Number of XML files pending upload : 734
Size of XML files pending upload(MB) : 0.31
Available disk space on upload filesystem : 59.62%
Collection Status : Collections enabled
Heartbeat Status : Ok
Last attempted heartbeat to OMS : 2017-01-26 14:26:26
Last successful heartbeat to OMS : 2017-01-26 14:26:26
Next scheduled heartbeat to OMS : 2017-01-26 14:27:28
---------------------------------------------------------------
Agent is Running and Ready
/u01/app/oracle/product/em/agent/agent_13.2.0.0.0:/u01/app/oracle/product/em/agent/agent_inst
$ ps -ef | grep emagent
oracle 57082074 41484628 0 16:21:18 pts/0 0:00 grep emagent
oracle 63766548 1 0 15:00:53 pts/0 0:00 /u01/app/oracle/product/em/agent/agent_13.2.0.0.0/perl/bin/perl /u01/app/oracle/product/em/agent/agent_13.2.0.0.0/bin/emwd.pl agent /u01/app/oracle/product/em/agent/agent_inst/sysman/log/emagent.nohup
oracle 29950448 63766548 0 15:01:00 pts/0 1:20 /u01/app/oracle/product/em/agent/agent_13.2.0.0.0/oracle_common/jdk/bin/java -Xmx183M -server -Djava.security.egd=file:///dev/./urandom -Dsun.lang.ClassLoader.allowArraySyntax=true -Xgcpolicy:gencon -Dwatchdog.pid=63766548 -cp /u01/app/oracle/product/em/agent/agent_13.2.0.0.0/jdbc/lib/ojdbc7.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/ucp/lib/ucp.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/oracle_common/modules/jsch-0.1.53.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/oracle_common/modules/com.oracle.http_client.http_client_12.1.3.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/oracle_common/modules/oracle.xdk_12.1.3/xmlparserv2.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/oracle_common/modules/oracle.dms_12.1.3/dms.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/oracle_common/modules/oracle.odl_12.1.3/ojdl.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/oracle_common/modules/oracle.odl_12.1.3/ojdl2.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/lib/optic.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/sysman/jlib/log4j-core.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/jlib/gcagent_core.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/sysman/jlib/emagentSDK-intg.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/sysman/jlib/emagentSDK.jar oracle.sysman.gcagent.tmmain.TMMain
$ /u01/app/oracle/product/em/agent/agent_13.2.0.0.0/bin/emctl upload agent
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD upload completed successfully
$ /u01/app/oracle/product/em/agent/agent _13.2.0.0.0/bin/emctl status agent
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 13.2.0.0.0
OMS Version : 13.2.0.0.0
Protocol Version : 12.1.0.1.0
Agent Home : /u01/app/oracle/product/em/agent/agent_inst
Agent Log Directory : /u01/app/oracle/product/em/agent/agent_inst/sysman/log
Agent Binaries : /u01/app/oracle/product/em/agent/agent_13.2.0.0.0
Core JAR Location : /u01/app/oracle/product/em/agent/agent_13.2.0.0.0/jlib
Agent Process ID : 11665618
Parent Process ID : 13238612
Agent URL : https://host01.dbaplus.ca:3872/emd/main/
Local Agent URL in NAT : https://host01.dbaplus.ca:3872/emd/main/
Repository URL : https://oms.dbaplus.ca:4903/empbs/upload
Started at : 2017-01-26 14:25:33
Started by user : oracle
Operating System : AIX version 7.1 (ppc64)
Number of Targets : 23
Last Reload : (none)
Last successful upload : 2017-01-26 14:27:22
Last attempted upload : 2017-01-26 14:27:22
Total Megabytes of XML files uploaded so far : 0.62
Number of XML files pending upload : 734
Size of XML files pending upload(MB) : 0.31
Available disk space on upload filesystem : 59.62%
Collection Status : Collections enabled
Heartbeat Status : Ok
Last attempted heartbeat to OMS : 2017-01-26 14:26:26
Last successful heartbeat to OMS : 2017-01-26 14:26:26
Next scheduled heartbeat to OMS : 2017-01-26 14:27:28
---------------------------------------------------------------
Agent is Running and Ready
Agent is running on managed host, and it also shows 'UP' in OEM.
Monday, August 8, 2016
Automated SQL Tuning Task Failed with ORA-00600: [kpdbSwitchPreRestore: Txn]
Database: Oracle 12.1.0.2 RAC + Proactive Bundle Patch 12.1.0.2.160419
Operation System: AIX 7.1
Automated SQL Tuning Task (SYS_AUTO_SQL_TUNING_TASK) failed and alert log shows errors:
The issue was not seen in non-cdb databases. According to Oracle Support explanation, the task is executed from a pluggable database but also accessing the container database through dblink to tune a remote query which triggers a bug. The bug can be fixed by patch 21283337 if it is available for your platform.
Before the patch is available or the Automated SQL Tuning Task is not really needed, as a workaround, the task (client_name is 'sql tuning advisor' in view dba_autotask_client) can be disabled,
Usage notes for dbms_auto_task_admin.disable,
* If operation and window_name are both NULL, the client is disabled.
* If operation is not NULL, window_name is ignored and the operation is disabled
* If operation is NULL and window_name is not NULL, the client is disabled in the specified window.
Note: If disabling the task as workaround, we have to disable it in all containers (PDB & root),
SYSTEM@PDB01> select client_name,status from dba_autotask_client;
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto space advisor ENABLED
auto optimizer stats collection ENABLED
sql tuning advisor DISABLED
SYSTEM@PDB01> alter session set container=cdb$root;
Session altered.
SYSTEM@CDB$ROOT> select client_name,status from dba_autotask_client;
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto space advisor ENABLED
auto optimizer stats collection ENABLED
sql tuning advisor ENABLED
SYSTEM@CDB$ROOT> select con_id,client_name,status from cdb_autotask_client;
CON_ID CLIENT_NAME STATUS
---------- ---------------------------------------------------------------- --------
1 auto optimizer stats collection ENABLED
1 auto space advisor ENABLED
1 sql tuning advisor ENABLED
3 auto optimizer stats collection ENABLED
3 auto space advisor ENABLED
3 sql tuning advisor DISABLED
6 rows selected.
SYSTEM@CDB$ROOT> exec dbms_auto_task_admin.disable('SQL TUNING ADVISOR',null,null);
PL/SQL procedure successfully completed.
SYSTEM@CDB$ROOT> select con_id,client_name,status from cdb_autotask_client;
CON_ID CLIENT_NAME STATUS
---------- ---------------------------------------------------------------- --------
1 auto optimizer stats collection ENABLED
1 auto space advisor ENABLED
1 sql tuning advisor DISABLED
3 auto optimizer stats collection ENABLED
3 auto space advisor ENABLED
3 sql tuning advisor DISABLED
6 rows selected.
Operation System: AIX 7.1
Automated SQL Tuning Task (SYS_AUTO_SQL_TUNING_TASK) failed and alert log shows errors:
Begin automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"
2016-08-07 22:00:21.020000 -05:00
<<Message truncated>>
2016-08-07 22:10:53.145000 -05:00
Errors in file /u01/app/oracle/diag/rdbms/cdb01/CDB01_1/trace/CDB01_1_j004_31719682.trc (incident=109991) (PDBNAME=PDB01):
ORA-00600: internal error code, arguments: [kpdbSwitchPreRestore: txn], [0x7000101ECFA4F00], [0], [2685403136], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/cdb01/CDB01_1/incident/incdir_109991/CDB01_1_j004_31719682_i109991.trc
2016-08-07 22:11:11.467000 -05:00
Dumping diagnostic data in directory=[cdmp_201608071221111], requested by (instance=1, osid=85983625 (J004)), summary=[incident=109991].
2016-08-07 22:00:21.020000 -05:00
<<Message truncated>>
2016-08-07 22:10:53.145000 -05:00
Errors in file /u01/app/oracle/diag/rdbms/cdb01/CDB01_1/trace/CDB01_1_j004_31719682.trc (incident=109991) (PDBNAME=PDB01):
ORA-00600: internal error code, arguments: [kpdbSwitchPreRestore: txn], [0x7000101ECFA4F00], [0], [2685403136], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/cdb01/CDB01_1/incident/incdir_109991/CDB01_1_j004_31719682_i109991.trc
2016-08-07 22:11:11.467000 -05:00
Dumping diagnostic data in directory=[cdmp_201608071221111], requested by (instance=1, osid=85983625 (J004)), summary=[incident=109991].
The issue was not seen in non-cdb databases. According to Oracle Support explanation, the task is executed from a pluggable database but also accessing the container database through dblink to tune a remote query which triggers a bug. The bug can be fixed by patch 21283337 if it is available for your platform.
Before the patch is available or the Automated SQL Tuning Task is not really needed, as a workaround, the task (client_name is 'sql tuning advisor' in view dba_autotask_client) can be disabled,
system@PDB01> select client_name,status from dba_autotask_client;
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto space advisor ENABLED
auto optimizer stats collection ENABLED
sql tuning advisor ENABLED
system@PDB01> select client_name,operation_name,status from dba_autotask_operation;
CLIENT_NAME OPERATION_NAME STATUS
---------------------------------------- ---------------------------------------- --------
auto optimizer stats collection auto optimizer stats job ENABLED
auto space advisor auto space advisor job ENABLED
sql tuning advisor automatic sql tuning task ENABLED
system@PDB01> begin
2 dbms_auto_task_admin.disable(client_name=>'SQL TUNING ADVISOR',
3 operation=>null,
4 window_name=>null);
5 end;
6 /
PL/SQL procedure successfully completed.
system@PDB01> select client_name,status from dba_autotask_client;
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
sql tuning advisor DISABLED
auto space advisor ENABLED
auto optimizer stats collection ENABLED
system@PDB01> select client_name,operation_name,status from dba_autotask_operation;
CLIENT_NAME OPERATION_NAME STATUS
---------------------------------------- ---------------------------------------- --------
auto optimizer stats collection auto optimizer stats job ENABLED
auto space advisor auto space advisor job ENABLED
sql tuning advisor automatic sql tuning task DISABLED
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto space advisor ENABLED
auto optimizer stats collection ENABLED
sql tuning advisor ENABLED
system@PDB01> select client_name,operation_name,status from dba_autotask_operation;
CLIENT_NAME OPERATION_NAME STATUS
---------------------------------------- ---------------------------------------- --------
auto optimizer stats collection auto optimizer stats job ENABLED
auto space advisor auto space advisor job ENABLED
sql tuning advisor automatic sql tuning task ENABLED
system@PDB01> begin
2 dbms_auto_task_admin.disable(client_name=>'SQL TUNING ADVISOR',
3 operation=>null,
4 window_name=>null);
5 end;
6 /
PL/SQL procedure successfully completed.
system@PDB01> select client_name,status from dba_autotask_client;
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
sql tuning advisor DISABLED
auto space advisor ENABLED
auto optimizer stats collection ENABLED
system@PDB01> select client_name,operation_name,status from dba_autotask_operation;
CLIENT_NAME OPERATION_NAME STATUS
---------------------------------------- ---------------------------------------- --------
auto optimizer stats collection auto optimizer stats job ENABLED
auto space advisor auto space advisor job ENABLED
sql tuning advisor automatic sql tuning task DISABLED
Usage notes for dbms_auto_task_admin.disable,
* If operation and window_name are both NULL, the client is disabled.
* If operation is not NULL, window_name is ignored and the operation is disabled
* If operation is NULL and window_name is not NULL, the client is disabled in the specified window.
Note: If disabling the task as workaround, we have to disable it in all containers (PDB & root),
SYSTEM@PDB01> select client_name,status from dba_autotask_client;
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto space advisor ENABLED
auto optimizer stats collection ENABLED
sql tuning advisor DISABLED
SYSTEM@PDB01> alter session set container=cdb$root;
Session altered.
SYSTEM@CDB$ROOT> select client_name,status from dba_autotask_client;
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto space advisor ENABLED
auto optimizer stats collection ENABLED
sql tuning advisor ENABLED
SYSTEM@CDB$ROOT> select con_id,client_name,status from cdb_autotask_client;
CON_ID CLIENT_NAME STATUS
---------- ---------------------------------------------------------------- --------
1 auto optimizer stats collection ENABLED
1 auto space advisor ENABLED
1 sql tuning advisor ENABLED
3 auto optimizer stats collection ENABLED
3 auto space advisor ENABLED
3 sql tuning advisor DISABLED
6 rows selected.
SYSTEM@CDB$ROOT> exec dbms_auto_task_admin.disable('SQL TUNING ADVISOR',null,null);
PL/SQL procedure successfully completed.
SYSTEM@CDB$ROOT> select con_id,client_name,status from cdb_autotask_client;
CON_ID CLIENT_NAME STATUS
---------- ---------------------------------------------------------------- --------
1 auto optimizer stats collection ENABLED
1 auto space advisor ENABLED
1 sql tuning advisor DISABLED
3 auto optimizer stats collection ENABLED
3 auto space advisor ENABLED
3 sql tuning advisor DISABLED
6 rows selected.
Subscribe to:
Posts (Atom)