Most of the time, gathering incident to be submitted to Oracle Support pretty straightforward. This case, I need to walk through someone on gathering it since there are multiple HOMEs due to machine name changes.
adrci> IPS CREATE PACKAGE INCIDENT 74892
Created package 1 based on incident id 74892, correlation level typical
ADR base = "/home/oracle/app/oracle"
adrci> IPS GENERATE PACKAGE 1 IN $ORACLE_HOME
DIA-48448: This command does not support multiple ADR homes
adrci> show control
DIA-48448: This command does not support multiple ADR homes
adrci> SHOW HOMES
ADR Homes:
diag/rdbms/orcl/orcl
diag/tnslsnr/Cloud3/listener
diag/tnslsnr/Cloud2/listener
diag/tnslsnr/Cloud/listener
diag/tnslsnr/Cloud2-001/listener
adrci>
adrci> SET HOME diag/rdbms/orcl/orcl
adrci> IPS GENERATE PACKAGE 1 IN /home/oracle/app/oracle
Generated package 1 in file /home/oracle/app/oracle/ORA600qer_20150827105025_COM_1.zip, mode complete
Voila, upload this over to Oracle Support ORA600qer_20150827105025_COM_1.zip.
Thursday, August 27, 2015
Tuesday, August 25, 2015
Oracle Troubleshoot: ORA-20005: object statistics are locked (stattype = ALL)
Getting Oracle errors stating object statistics are locked. This is severe enough to cause vCenter server to go down sporadically.
Simulating the fix
This is not a common practice with vCenter database. I suspect someone might have put a lock on the table statistics for unknown reasons.
2015-08-11T12:57:01.838-14:32 [08712 error 'Default' opID=XXXXXXXXXXX] [VdbStatement] SQLError was thrown: "ODBC error: (HY000) - [Oracle][ODBC][Ora]ORA-20005: object statistics are locked (stattype = ALL)
--> ORA-06512: at "SYS.DBMS_STATS", line 23429
--> ORA-06512: at "SYS.DBMS_STATS", line 23480
--> ORA-06512: at line 1
--> " is returned when executing SQL statement "BEGIN dbms_stats.gather_table_stats(null, 'vpx_hist_stat1_125'); END;"
--> ORA-06512: at "SYS.DBMS_STATS", line 23429
--> ORA-06512: at "SYS.DBMS_STATS", line 23480
--> ORA-06512: at line 1
--> " is returned when executing SQL statement "BEGIN dbms_stats.gather_table_stats(null, 'vpx_hist_stat1_125'); END;"
VPXADMIN55> exec dbms_stats.lock_table_stats('VPXADMIN55','vpx_hist_stat1_125');
PL/SQL procedure successfully completed.
VPXADMIN55> exec dbms_stats.gather_table_stats(null, 'vpx_hist_stat1_125');
BEGIN dbms_stats.gather_table_stats(null, 'vpx_hist_stat1_125'); END;
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 20337
ORA-06512: at "SYS.DBMS_STATS", line 20360
ORA-06512: at line 1
VPXADMIN55> exec dbms_stats.unlock_table_stats('vpx_hist_stat1_125');
BEGIN dbms_stats.unlock_table_stats('vpx_hist_stat1_125'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'UNLOCK_TABLE_STATS'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
VPXADMIN55> exec dbms_stats.unlock_table_stats('VPXADMIN55','vpx_hist_stat1_125');
PL/SQL procedure successfully completed.
VPXADMIN55> exec dbms_stats.gather_table_stats(null, 'vpx_hist_stat1_125');
PL/SQL procedure successfully completed.
VPXADMIN55>This is not a common practice with vCenter database. I suspect someone might have put a lock on the table statistics for unknown reasons.
Wednesday, August 12, 2015
Oracle Troubleshoot: code, arguments: [25027], [TS#],
Updating a table causing the following error. Dropping and recreating the table fixed the issue. Do this only if the table is not a critical table. The tablespace # 29 was the culprit. Seen this the 3rd time this year. They all fixed the same way.
SQL state [60000]; error code
[600]; ORA-00600: internal error code, arguments: [25027], [29], [0], [],
[], [], [], [], [], [], [], []
; nested exception is java.sql.SQLException: ORA-00600: internal error
code, arguments: [25027], [29], [0], [], [], [], [], [], [], [], [], []
Resolution: Doc ID 1608861.1.
Summary of the Oracle note
The error is fixed by:
recreating the table using exp-drop-import.
OR
Move the lob in a new tablespace.
Alter table <name> move lob(&lob_column) store as (tablespace &tbsp);
SQL state [60000]; error code
[600]; ORA-00600: internal error code, arguments: [25027], [29], [0], [],
[], [], [], [], [], [], [], []
; nested exception is java.sql.SQLException: ORA-00600: internal error
code, arguments: [25027], [29], [0], [], [], [], [], [], [], [], [], []
Resolution: Doc ID 1608861.1.
Summary of the Oracle note
The error is fixed by:
recreating the table using exp-drop-import.
OR
Move the lob in a new tablespace.
Alter table <name> move lob(&lob_column) store as (tablespace &tbsp);
Oracle 12C: slight behavioral changes in schema creation in Oracle12C
Creating user in Oracle12C has changed a bit. Session will need to be altered to get old script working.
CREATE USER XX_A PROFILE "DEFAULT" IDENTIFIED BY "oracle" DEFAULT TABLESPACE "XX_DATEN" ACCOUNT UNLOCK
*
ERROR at line 1:
ORA-65096: invalid common user or role name
SQL> alter session set "_ORACLE_SCRIPT"=true;
Session altered.
SQL> CREATE USER "XX_" PROFILE "DEFAULT" IDENTIFIED BY "oracle" DEFAULT TABLESPACE "XX_DATEN" ACCOUNT UNLOCK;
User created.
CREATE USER XX_A PROFILE "DEFAULT" IDENTIFIED BY "oracle" DEFAULT TABLESPACE "XX_DATEN" ACCOUNT UNLOCK
*
ERROR at line 1:
ORA-65096: invalid common user or role name
SQL> alter session set "_ORACLE_SCRIPT"=true;
Session altered.
SQL> CREATE USER "XX_" PROFILE "DEFAULT" IDENTIFIED BY "oracle" DEFAULT TABLESPACE "XX_DATEN" ACCOUNT UNLOCK;
User created.
Tuesday, August 4, 2015
Oracle decompress
Oracle "Compress" feature was introduced in early 11G. Anyone aware of any customers actually using that feature at the fields and if we know it has any adverse effect in general? It seems like a pretty promising feature for cutting down space usage and at the same time having some performance gains. I am tempted to have couple of my customers to try it out on vCenter database where vpx_event and vpx_event_args are growing out of control. If we can make use of this feature without major drawbacks, it can be helpful in other areas of our product as well where we have large analytic data reside. VPX_HIST_TEMP* are one of those examples as well.
Having to set this to compress all, all the data onward will be compressed and existing data remain uncompressed.
Note: "alter table abc compress for all operations" and "alter table abc compress for oltp" basically the same thing with different name.
SQL> alter table RESOURCEFULNESS compress for all operations;
Table altered.
SQL> select compression, compress_for, table_name from user_tables where table_name like 'RESOURCEUDFKIND';
COMPRESS COMPRESS_FOR TABLE_NAME
-------- ------------ ------------------------------
DISABLED RESOURCEUDF
ENABLED OLTP RESOURCEUDFKIND
Another example to illustrate on how to compress and to reset to nocompress.
SQL> alter table vpx_event compress for oltp;
Table altered.
SQL> select table_name, compression, compress_for from dba_tables where compression ='ENABLED' OR COMPRESS_FOR='DISABLED';
TABLE_NAME COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
VPX_EVENT ENABLED OLTP
MGMT_METRICS ENABLED OLTP
MGMT_POLICIES ENABLED OLTP
SQL> alter table vpx_event nocompress;
Table altered.
SQL> select table_name, compression, compress_for from dba_tables where compression ='ENABLED' OR COMPRESS_FOR='DISABLED';
TABLE_NAME COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
MGMT_METRICS ENABLED OLTP
MGMT_POLICIES ENABLED OLTP
SQL>
Second example from another 12C database
SQL> col table_name format a30
SQL> col compress format a15
SQL> select table_name, compression, compress_for from dba_tables where compression ='ENABLED' OR COMPRESS_FOR='DISABLED';
no rows selected
SQL> alter table vpx_event compress for oltp;
Table altered.
SQL> select table_name, compression, compress_for from dba_tables where compression ='ENABLED' OR COMPRESS_FOR='DISABLED';
TABLE_NAME COMPRESS COMPRESS_FOR
------------------------------ -------- ------------------------------
VPX_EVENT ENABLED ADVANCED
SQL> alter table vpx_event nocompress;
Table altered.
SQL> select table_name, compression, compress_for from dba_tables where compression ='ENABLED' OR COMPRESS_FOR='DISABLED';
no rows selected
SQL>
Oracle 12C: connecting within container to another database in Oracle 12C
In my Oracle 12.0.1, I cannot connect to a schema directly without specifying the services name.
alter session set container=PDBVCDB;
SQL> conn vpxd/oracle
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn vpxd/oracle@pdbvcdb
Connected.
It probably trying to find the vpxd schema at the root level.
Oracle: ASMM Oracle 10g,11g and 12c
A handy dandy old note of mine and found that it is still good for Oracle 12C as well.
Script taken from Oracle NoteID: 295626.1. Example was from my own lab on Oracle 12C.
SGA_TARGET non-zero. ASMM enabled.
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean TRUE
sga_max_size big integer 4816M
sga_target big integer 4816M
unified_audit_sga_queue_size integer 1048576
Looking at the patterns of automatic management.
SQL> set lines 150
SQL> col component format a25
SQL> select component, current_size/1024/1024 "CURRENT_SIZE", min_size/1024/1024 "MIN_SIZE", user_specified_size/1024/1024 "USER_SPECIFIED_SIZE", last_oper_type "TYPE" from v$sga_dynamic_components;
COMPONENT CURRENT_SIZE MIN_SIZE USER_SPECIFIED_SIZE TYPE
------------------------- ------------ ---------- ------------------- -------------
shared pool 832 800 0 GROW
large pool 256 256 0 SHRINK
java pool 64 64 0 STATIC
streams pool 0 0 0 STATIC
DEFAULT buffer cache 3392 3392 0 SHRINK
KEEP buffer cache 0 0 0 STATIC
RECYCLE buffer cache 0 0 0 STATIC
DEFAULT 2K buffer cache 0 0 0 STATIC
DEFAULT 4K buffer cache 0 0 0 STATIC
DEFAULT 8K buffer cache 0 0 0 STATIC
DEFAULT 16K buffer cache 0 0 0 STATIC
COMPONENT CURRENT_SIZE MIN_SIZE USER_SPECIFIED_SIZE TYPE
------------------------- ------------ ---------- ------------------- -------------
DEFAULT 32K buffer cache 0 0 0 STATIC
Shared IO Pool 240 0 0 GROW
Data Transfer Cache 0 0 0 STATIC
ASM Buffer Cache 0 0 0 STATIC
15 rows selected.
Looking at the historical data of ASMM movements.
SQL> col component format a25;
SQL> set pages 100;
SQL> set lines 150;
SQL> col init format 9999;
SQL> col target format 9990;
SQL> select component, oper_type, initial_size/1024/1024 "INITIAL", TARGET_SIZE/1024/1024 "TARGET", FINAL_SIZE/1024/1024 "FINAL", TO_CHAR(end_time, 'DD/MM/YYYY HH:MI:SS') "DATE" from V$SGA_RESIZE_OPS order by end_time;
COMPONENT OPER_TYPE INIT TARGET FINAL DATE
------------------------- ------------- ----- ------ ---------- -------------------
DEFAULT 8K buffer cache STATIC 0 0 0 30/12/2014 12:51:36
KEEP buffer cache STATIC 0 0 0 30/12/2014 12:51:36
large pool STATIC 0 336 336 30/12/2014 12:51:36
shared pool STATIC 0 800 800 30/12/2014 12:51:36
java pool STATIC 0 64 64 30/12/2014 12:51:36
streams pool STATIC 0 0 0 30/12/2014 12:51:36
DEFAULT 4K buffer cache STATIC 0 0 0 30/12/2014 12:51:36
ASM Buffer Cache STATIC 0 0 0 30/12/2014 12:51:36
DEFAULT buffer cache STATIC 0 3584 3584 30/12/2014 12:51:36
RECYCLE buffer cache STATIC 0 0 0 30/12/2014 12:51:36
DEFAULT 32K buffer cache STATIC 0 0 0 30/12/2014 12:51:36
DEFAULT 16K buffer cache STATIC 0 0 0 30/12/2014 12:51:36
DEFAULT 2K buffer cache STATIC 0 0 0 30/12/2014 12:51:36
DEFAULT buffer cache INITIALIZING 3584 3584 3584 30/12/2014 12:51:37
large pool SHRINK 336 256 256 30/12/2014 12:52:18
DEFAULT buffer cache GROW 3584 3664 3664 30/12/2014 12:52:18
DEFAULT buffer cache SHRINK 3664 3424 3424 30/12/2014 12:52:29
shared pool GROW 800 832 832 02/01/2015 01:05:16
DEFAULT buffer cache SHRINK 3424 3392 3392 02/01/2015 01:05:16
19 rows selected.
Monday, August 3, 2015
Oracle Troubleshooting: Gathering orachk
Download ORAchk Oracle Metalink.
ORAchk - Health Checks for the Oracle Stack (Doc ID 1268927.2)
Ideally, download the tool and set it asides as it will extract bunch of files and you do not want to mix up with other existing and unrelated files. The rest really straightforward.
[oracle@Cloud3 oracle]$ mkdir orachk
[oracle@Cloud3 oracle]$ mv orachk.zip orachk
[oracle@Cloud3 orachk]$ unzip orachk.zip
Archive: orachk.zip
inflating: readme.txt
inflating: UserGuide.txt
inflating: rules.dat
inflating: user_defined_checks.xsd
[oracle@Cloud3 orachk]$ ./orachk
List of running databases
1. orcl
2. None of above
Select databases from list for checking best practices. For multiple databases, select 1 for All or comma separated number like 1,2 etc [1-2][1].orcl
That's it!
ORAchk - Health Checks for the Oracle Stack (Doc ID 1268927.2)
Ideally, download the tool and set it asides as it will extract bunch of files and you do not want to mix up with other existing and unrelated files. The rest really straightforward.
[oracle@Cloud3 oracle]$ mkdir orachk
[oracle@Cloud3 oracle]$ mv orachk.zip orachk
[oracle@Cloud3 orachk]$ unzip orachk.zip
Archive: orachk.zip
inflating: readme.txt
inflating: UserGuide.txt
inflating: rules.dat
inflating: user_defined_checks.xsd
[oracle@Cloud3 orachk]$ ./orachk
List of running databases
1. orcl
2. None of above
Select databases from list for checking best practices. For multiple databases, select 1 for All or comma separated number like 1,2 etc [1-2][1].orcl
That's it!
Subscribe to:
Posts (Atom)