Step Into Oracle DBA ( SID )

Wednesday, October 14, 2015

Tablespace Quota's Missing..

Recently we did migration on one of the database from HP-UX to Linux. Post migration we have provided unlimited quotas on few of its tablespace.

Later some time it came in our compliance tool that user is having UNLIMITED TABLESPACE privilege granted.

So this privilege was revoked and later few days we observed that tablespace quota's were missing.

I've generated a scenario which explains this.

##########################
## Error
##########################

Tablespace Quota's Missing

##########################
## Command Executed
##########################

Revoke unlimited tablespace from XDB;

**************************************** Step By Step Analysis ******************************************************

#########################################
# Reproduce the Issue
#########################################

a) Grant unlimited quota on specific tablespace.

Alter user XDB quota unlimited on USER_DATA_TS01;

SQL> Alter user XDB quota unlimited on USER_DATA_TS01;
User altered.

b) Grant unlimited tablespace privilege to the user.

Grant unlimited tablespace to xdb;

SQL> Grant unlimited tablespace to xdb;
Grant succeeded.
SQL>

SQL> select username,tablespace_name name,decode(greatest(max_bytes, -1),-1,'Unrestricted',
to_char(max_bytes/1024, '999,999,990')) quota, bytes/1024 used from dba_ts_quotas where username in ('XDB') order by 1,2;
2
USERNAME NAME QUOTA USED
------------------------------ ------------------------------ ------------------------- ----------
XDB USER_DATA_TS01 Unrestricted 57728

=====================================================================================================================

#########################################
# Scenario 1)
#########################################

Grant Unlimited Quota on Tablespace and Revoke UNLIMITED TABLESPACE privilege.

SQL> alter user XDB quota unlimited on USER_DATA_TS01;
User altered.
SQL> revoke unlimited tablespace from xdb;
Revoke succeeded.

#### Check Quota

SQL> select username,tablespace_name name,decode(greatest(max_bytes, -1),-1,'Unrestricted',
to_char(max_bytes/1024, '999,999,990')) quota, bytes/1024 used from dba_ts_quotas where username in ('XDB') order by 1,2;
2
no rows selected
SQL>

#### From this we can understand that revoking unlimited tablespace privilege removes unlimited tablespace quota on any/all tablespace to that user.

=====================================================================================================================

#########################################
# Scenario 2)
#########################################

Grant Specific(1gB) Quota on Tablespace and REVOKE UNLIMITED TABLESPACE privilege.

SQL> Alter user XDB quota 1g on USER_DATA_TS01;
User altered.
SQL> select username,tablespace_name name,decode(greatest(max_bytes, -1),-1,'Unrestricted',
to_char(max_bytes/1024, '999,999,990')) quota, bytes/1024 used from dba_ts_quotas where username in ('XDB') order by 1,2;
2
USERNAME NAME QUOTA USED
------------------------------ ------------------------------ ------------------------- ----------
XDB USER_DATA_TS01 1,048,576 57728
SQL> revoke unlimited tablespace from xdb;
Revoke succeeded.

#### Check Quota

SQL> select username,tablespace_name name,decode(greatest(max_bytes, -1),-1,'Unrestricted',
to_char(max_bytes/1024, '999,999,990')) quota, bytes/1024 used from dba_ts_quotas where username in ('XDB') order by 1,2;
2
no rows selected
SQL>

#### From this we can understand that revoking unlimited tablespace privilege removes all quota's on all tablespace granted to that user.

=====================================================================================================================
So the bottom line is before revoking unlimited tablespace privilege from the user, make a note of the quota's that user has and then once revoked grant the tablespace quota's back.
=====================================================================================================================

Tuesday, May 26, 2015

ORA-09945: Unable to initialize the audit trail file, PRCD-1222 : Online relocation of database "ORALIN" failed but database was restored to its original state

I was testing the online relocation of RAC One Node database from host08 to host09 server. Command was run from host08 server.

##########################
## Error
##########################

ORA-09945: Unable to initialize the audit trail file

### Full Error

[oracle@host08 trace]$ srvctl relocate database -d ORALIN -n host09 -v
Configuration updated to two instances
Online relocation failed, rolling back to original state
Configuration reverted back to one instance
PRCD-1222 : Online relocation of database "ORALIN" failed but database was restored to its original state
PRCD-1129 : Failed to start instance ORALIN_2 for database ORALIN
PRCR-1064 : Failed to start resource ora.ORALIN.db on node host09
CRS-5017: The resource action "ora.ORALIN.db start" encountered the following error:
ORA-09817: Write to audit file failed.
Linux-x86_64 Error: 28: No space left on device
Additional information: 12
ORA-09945: Unable to initialize the audit trail file
Linux-x86_64 Error: 28: No space left on device
. For details refer to "(:CLSN00107:)" in "/ofa/rac/app/oracle/grid/11.2.0.4/log/host09/agent/crsd/oraagent_oracle//oraagent_oracle.log".
CRS-2674: Start of 'ora.ORALIN.db' on 'host09' failed
[oracle@host08 trace]$

##########################
# Error Occurred
##########################

Error occured while doing a relocation of database instance of a One Node RAC in 11.2.0.4.0 version to a different server

##########################
## Command Executed
##########################

srvctl relocate database -d ORALIN -n host09 -v

**************************************** Step By Step Analysis ******************************************************

#########################################
# 1) Check the Full Error
#########################################

The above error shows that there is some problem with the audit file creation.

Lets check oragent_oracle.log file in host09 server as the problem ocurred while relocating the instance to host09.

[oracle@host09 audit]$ vi "/ofa/rac/app/oracle/grid/11.2.0.4/log/host09/agent/crsd/oraagent_oracle//oraagent_oracle.log"
[oracle@host09 audit]$
2015-05-26 16:57:41.499: [ USRTHRD][2234124608]{0:1:24} CrsCmd::destroy
2015-05-26 16:57:41.500: [ora.orawin.orawindev.svc][2234124608]{0:1:24} [check] clsnUtils::error Exception type=2 string=
CRS-5017: The resource action "ora.orawin.orawindev.svc check" encountered the following error:
ORA-09817: Write to audit file failed.
Linux-x86_64 Error: 28: No space left on device
Additional information: 12
. For details refer to "(:CLSN00109:)" in "/ofa/rac/app/oracle/grid/11.2.0.4/log/host09/agent/crsd/oraagent_oracle//oraagent_oracle.log".

=====================================================================================================================

#########################################
# 2) Reason for Failure
#########################################

Online relocation command showed that error is because of the audit. But we shouldnt take that the audit got filled up for ORALIN database.

From above "oragent_oracle.log" we can see that the error occurred on "ora.orawin.orawindev.svc". So the database in problem is orawin.

=====================================================================================================================

#########################################
# 3) Check the audit location & Filesystem
#########################################

If we can login to orawin database then check audit location. (Show parameter audit)

In my case, i'm unable to login to the database, so checked the filesystems which reached 100%

[oracle@host09 bin]$ df -h /ofa/oracle_11.2.0.4_home
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg_oracle-lv0205
20G 19G 0 100% /ofa/oracle_11.2.0.4_home
[oracle@host09 bin]$

[oracle@host09 oracle_11.2.0.4_home]$ du -sh *
14G dbs

[oracle@host09 dbs]$ pwd
/ofa/oracle_11.2.0.4_home/dbs
[oracle@host09 dbs]$ ls arch*
arch1_19_871216596.dbf arch1_31_871216596.dbf arch1_43_871216596.dbf arch1_51_871216596.dbf arch1_60_871216596.dbf arch1_70_871216596.dbf
arch1_20_871216596.dbf arch1_32_871216596.dbf arch1_44_871216596.dbf arch1_52_871216596.dbf arch1_61_871216596.dbf arch1_71_871216596.dbf
arch1_21_871216596.dbf arch1_33_871216596.dbf arch1_45_871216596.dbf arch1_53_871216596.dbf arch1_62_871216596.dbf arch1_72_871216596.dbf
arch1_22_871216596.dbf arch1_34_871216596.dbf arch1_46_871216596.dbf arch1_54_871216596.dbf arch1_63_871216596.dbf arch1_73_871216596.dbf
arch1_23_871216596.dbf arch1_35_871216596.dbf arch1_47_871216596.dbf arch1_55_871216596.dbf arch1_64_871216596.dbf arch1_74_871216596.dbf
arch1_24_871216596.dbf arch1_36_871216596.dbf arch1_4_870194590.dbf arch1_56_871216596.dbf arch1_65_871216596.dbf arch1_75_871216596.dbf
arch1_25_871216596.dbf arch1_37_871216596.dbf arch1_4_870252683.dbf arch1_57_871216596.dbf arch1_66_871216596.dbf arch1_76_871216596.dbf
arch1_26_871216596.dbf arch1_38_871216596.dbf arch1_4_870864967.dbf arch1_5_870194590.dbf arch1_67_871216596.dbf arch1_77_871216596.dbf
arch1_27_871216596.dbf arch1_39_871216596.dbf arch1_4_870965663.dbf arch1_5_870864967.dbf arch1_6_870194590.dbf arch1_78_871216596.dbf
arch1_28_871216596.dbf arch1_40_871216596.dbf arch1_48_871216596.dbf arch1_5_870965663.dbf arch1_6_870864967.dbf
arch1_29_871216596.dbf arch1_41_871216596.dbf arch1_49_871216596.dbf arch1_58_871216596.dbf arch1_68_871216596.dbf
arch1_30_871216596.dbf arch1_42_871216596.dbf arch1_50_871216596.dbf arch1_59_871216596.dbf arch1_69_871216596.dbf
[oracle@host09 dbs]$

=====================================================================================================================
If above we can see that the files are the archivelogs.
=====================================================================================================================

##########################
## Solution
##########################

Backup and Delete the archive log files. (As this is the development database, i removed it without a backup)

[oracle@host09 dbs]$ rm arch*
[oracle@host09 dbs]$ df -h .
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg_oracle-lv0205
20G 8.1G 11G 43% /ofa/oracle_11.2.0.4_home
[oracle@host09 dbs]$

=====================================================================================================================
Now database relocation worked fine.
=====================================================================================================================

[oracle@host08 trace]$ srvctl relocate database -d ORALIN -n host09 -v
Configuration updated to two instances
Instance ORALIN_2 started
Services relocated
Waiting for up to 30 minutes for instance ORALIN_1 to stop ...
Instance ORALIN_1 stopped
Configuration updated to one instance
[oracle@host08 trace]$

=====================================================================================================================
Comments Are Always welcome
=====================================================================================================================

Sunday, March 1, 2015

De-Install Oracle WorkSpace Manager in 11g Database.

We have created a new database using DBCA with custom template option which has installed Oracle WorkSpace Manager in the database.

We don't use it and need to De-Install it. Below simple step can be used to De-Install it.

**************************************** Step By Step Procedure ******************************************************

#########################################
# 1) Check the users present in database..
#########################################

SQL> select username from dba_users where username like '%SYS%';
USERNAME
------------------------------
SYSTEM
SYS
APPQOSSYS
WMSYS

=====================================================================================================================

#########################################
# 2) Check for Version Tables
#########################################

Before De-Install Oracle WorkSpace manager we should make sure there is no version enabled tables on the database.

SQL> select * from all_wm_versioned_tables;
no rows selected
SQL>

=====================================================================================================================

#########################################
# 3) Script to De-Install WorkSpace Manager
#########################################

Below script is used to De-Install WorkSpace Manager from the 11g database.

$ORACLE_HOME/rdbms/admin/owmuinst.plb

#### Above Script Drops WMSYS user and its objects from the database which De-Install the Oracle WorkSpace Manager

=====================================================================================================================

#########################################
# 4) De-Install Oracle WorkSpace Manager
#########################################

[oracle@host01 admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 27 17:41:01 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> @/u01/app/oracle/product/11.2.0.3/rdbms/admin/owmuinst.plb
Procedure created.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Procedure dropped.
SQL>

SQL> select username from dba_users where username like '%SYS%';
USERNAME
------------------------------
SYSTEM
SYS
APPQOSSYS

De-Install Oracle Ware House Builder in 11g Database.

We have created a new database using DBCA with custom template option which has installed Oracle Ware House Builder in the database.

We don't use it and need to De-Install it. Below simple step can be used to De-Install it.

**************************************** Step By Step Procedure ******************************************************

#########################################
# 1) Check the users present in database..
#########################################

SQL> select username from dba_users where username like '%SYS%';
USERNAME
------------------------------
SYSTEM
SYS
APPQOSSYS
OWBSYS
OWBSYS_AUDIT

=====================================================================================================================

#########################################
# 2) Script to De-Install OWB
#########################################

Below script is used to De-Install OWB from the 11g database.

$ORACLE_HOME/owb/UnifiedRepos/clean_owbsys.sql

#### Lets see what is being stored in "clean_owbsys.sql"

[oracle@host01 UnifiedRepos]$ more clean_owbsys.sql
drop user owbsys cascade;
drop user owbsys_audit cascade;
drop role OWB_user;
drop role OWB_DESIGNCENTER_view;
drop role OWB$CLIENT;
-- Bug Fix 6233292:
-- exit
[oracle@host01 UnifiedRepos]$

=====================================================================================================================

#########################################
# 3) De-Install OWB
#########################################

[oracle@host01 UnifiedRepos]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 27 17:35:45 2015

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

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

SQL> @/u01/app/oracle/product/11.2.0.3/owb/UnifiedRepos/clean_owbsys.sql
User dropped.

User dropped.

Role dropped.

Role dropped.

Role dropped.
SQL>

SQL> select username from dba_users where username like '%SYS%';
USERNAME
------------------------------
SYSTEM
SYS
APPQOSSYS

Wednesday, February 25, 2015

ORA-39065: unexpected master process exception in DISPATCH

##########################
## Error
##########################

ORA-39065: unexpected master process exception in DISPATCH

### Full Error

ORA-39006: internal error
ORA-39065: unexpected master process exception in DISPATCH
ORA-01403: no data found
ORA-39097: Data Pump job encountered unexpected error 100

##########################
# Error Occurred
##########################

Error occured while trying to run a datapump export for estimate the size of the dumpfile in 11.2.0.4.0 version

##########################
## Command Executed
##########################

expdp \'/ as sysdba\' estimate_only=y

**************************************** Step By Step Analysis ******************************************************

#########################################
# 1) Reproduce the Error
#########################################

$ expdp \'/ as sysdba\' estimate_only=y
Export: Release 11.2.0.4.0 - Production on Wed Feb 25 16:45:05 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39006: internal error
ORA-39065: unexpected master process exception in DISPATCH
ORA-01403: no data found
ORA-39097: Data Pump job encountered unexpected error 100

$

From the error it looks like there is some problem with the library.

=====================================================================================================================

#########################################
# 2) Check the INVALID Objects
#########################################

#### Lets check if there is any INVALID objects present in SYS schema

SET LINES 200
COL OBJECT_NAME FOR A35
COL OBJECT_TYPE FOR A25
COL Owner FOR A25
Select owner,object_name,object_type,status,created,LAST_DDL_TIME from dba_objects where status='INVALID' and owner='SYS' order by object_name;

OWNER OBJECT_NAME OBJECT_TYPE STATUS CREATED LAST_DDL_
------------------------- ----------------------------------- ------------------------- ------- --------- ---------
SYS DBMS_AQELM PACKAGE BODY INVALID 10-DEC-12 10-DEC-12
DBMS_AW_EXP PACKAGE BODY INVALID 10-DEC-12 23-FEB-14
DBMS_CMP_INT PACKAGE INVALID 10-DEC-12 23-FEB-14
DBMS_DATAPUMP_UTL PACKAGE BODY INVALID 10-DEC-12 11-JUN-13
DBMS_FILE_GROUP PACKAGE BODY INVALID 10-DEC-12 23-FEB-14
DBMS_FILE_GROUP_UTL_INVOK PACKAGE BODY INVALID 10-DEC-12 23-FEB-14
DBMS_LOGMNR_INTERNAL PACKAGE BODY INVALID 07-DEC-12 23-FEB-14
DBMS_PRVTAQIP PACKAGE INVALID 07-DEC-12 23-FEB-14
DBMS_PRVTAQIP PACKAGE BODY INVALID 10-DEC-12 23-FEB-14
DBMS_REPCAT_MIGRATION PACKAGE INVALID 07-DEC-12 23-FEB-14
DBMS_REPCAT_MIGRATION PACKAGE BODY INVALID 10-DEC-12 23-FEB-14
DBMS_SQLTCB_INTERNAL PACKAGE BODY INVALID 10-DEC-12 23-FEB-14
DBMS_STREAMS_AUTH PACKAGE BODY INVALID 10-DEC-12 23-FEB-14
DBMS_STREAMS_MT PACKAGE INVALID 07-DEC-12 23-FEB-14
DBMS_STREAMS_MT PACKAGE BODY INVALID 10-DEC-12 23-FEB-14
DBMS_STREAMS_SM PACKAGE BODY INVALID 10-DEC-12 23-FEB-14
DBMS_STREAMS_SM PACKAGE INVALID 07-DEC-12 23-FEB-14
DBMS_SUMREF_UTIL PACKAGE BODY INVALID 10-DEC-12 23-FEB-14
DBMS_WORKLOAD_REPLAY PACKAGE BODY INVALID 10-DEC-12 02-MAY-14
DBMS_WRR_INTERNAL PACKAGE BODY INVALID 10-DEC-12 02-MAY-14
DBMS_XSTREAM_ADM_INTERNAL PACKAGE BODY INVALID 10-DEC-12 23-FEB-14
DBMS_XSTREAM_AUTH PACKAGE BODY INVALID 10-DEC-12 23-FEB-14
HTTPURITYPE TYPE BODY INVALID 10-DEC-12 23-FEB-14
KUPW$WORKER PACKAGE BODY INVALID 10-DEC-12 23-FEB-14
SCHEDULER$_JOB_EVENT_HANDLER PROCEDURE INVALID 10-DEC-12 23-FEB-14
URIFACTORY PACKAGE BODY INVALID 10-DEC-12 23-FEB-14
UTL_HTTP PACKAGE BODY INVALID 10-DEC-12 23-FEB-14
UTL_SMTP PACKAGE BODY INVALID 10-DEC-12 10-DEC-12
UTL_SMTP PACKAGE INVALID 07-DEC-12 25-JAN-15
UTL_TCP PACKAGE BODY INVALID 10-DEC-12 23-FEB-14
UTL_URL PACKAGE BODY INVALID 10-DEC-12 10-DEC-12
UTL_URL PACKAGE INVALID 07-DEC-12 23-FEB-14
32 rows selected.

=====================================================================================================================

#########################################
# 3) Reason for Failure
#########################################

We are receiving the error because the binary packages which belong to DATAPUMP is in INVALID state.

And one of the oracle notes (453796.1) says that there is a possibility that the last CPU patch applied might had a problem which left some of the Datapump Metadata missing.

=====================================================================================================================

#########################################
# 4) Check CPU Patch
#########################################

#### Check the latest patch applied to the database

set lines 200
col action_time for a30
col action for a25
col namespace for a9
col version for a10
col id for 99999999
col comments for a25
col bundle_series for a25
select * from registry$history;

ACTION_TIME ACTION NAMESPACE VERSION ID COMMENTS BUNDLE_SERIES
------------------------------ ------------------------- --------- ---------- --------- ------------------------- -------------------------
13-DEC-12 11.48.14.623579 AM APPLY SERVER 11.2.0.2 7 CPUOct2012 CPU
28-JAN-13 04.14.40.507736 PM APPLY SERVER 11.2.0.2 8 CPUJan2013 CPU
01-DEC-13 02.49.06.524419 PM APPLY SERVER 11.2.0.2 11 CPUOct2013 CPU
23-FEB-14 08.34.03.016928 AM VIEW INVALIDATE 8289601 view invalidation
23-FEB-14 08.34.03.159818 AM UPGRADE SERVER 11.2.0.4.0 Upgraded from 11.2.0.2.0
23-FEB-14 09.09.53.453945 AM APPLY SERVER 11.2.0.4 1 CPUJan2014 CPU
23-FEB-14 09.11.10.377869 AM APPLY SERVER 11.2.0.4 1 CPUJan2014 CPU
04-MAY-14 11.27.08.483044 AM APPLY SERVER 11.2.0.4 2 CPUApr2014 CPU
02-NOV-14 11.03.14.032696 AM APPLY SERVER 11.2.0.4 4 CPUOct2014 CPU
25-JAN-15 11.02.36.070561 PM APPLY SERVER 11.2.0.4 5 CPUJan2015 CPU
10 rows selected.

We have applied the last CPU patch on 25-Jan-2015 which is coinciding with what the document says.

#### Query one of the Datapump Metadata.

select count(*) from metanametrans$;

SQL> select count(*) from metanametrans$;
COUNT(*)
----------
0

=====================================================================================================================
Above metadata table is empty which tells us that there is a problem. When i checked the same metadata table in another database it has 3393 rows.
=====================================================================================================================

##########################
## Solution
##########################

#### Load datapump metadata using below sql and recompile all the INVALID objects in the database.

@$ORACLE_HOME/rdbms/admin/catmet2.sql
@$ORACLE_HOME/rdbms/admin/utlrp.sql

Note : You can also try once running "utlrp.sql" as its just a recompilation of INVALID objects. But in my case it was not working as the table data is missing.

=====================================================================================================================
Now there is no INVALID objects and export is running good now.
=====================================================================================================================

SQL> Select owner,object_name,object_type,status,created,LAST_DDL_TIME from dba_objects where status='INVALID' and owner='SYS' order by object_name;

no rows selected

$ expdp \'/ as sysdba\' estimate_only=y full=y
Export: Release 11.2.0.4.0 - Production on Wed Feb 25 16:53:07 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_FULL_01": "/******** AS SYSDBA" estimate_only=y full=y
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. estimated "SID_OWNER"."RECOTOR":"P201404" 4.248 GB

=====================================================================================================================

Monday, February 16, 2015

Remove OLAP components installed in a 11g Database

I need to remove OLAP components in a 11.2.0.3 version of the database as its not being used.

SELECT comp_id,COMP_NAME,schema,VERSION,STATUS,MODIFIED FROM DBA_REGISTRY where comp_name like '%OLAP%' order by 1;

COMP_ID COMP_NAME SCHEMA VERSION STATUS MODIFIED
--------------- ----------------------------------- --------------- --------------- --------------- -------------------------
AMD OLAP Catalog OLAPSYS 11.2.0.3.0 VALID 09-FEB-2015 10:23:04
APS OLAP Analytic Workspace SYS 11.2.0.3.0 VALID 09-FEB-2015 10:23:03
XOQ Oracle OLAP API SYS 11.2.0.3.0 VALID 09-FEB-2015 10:23:03

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ Remove OLAP @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

=====================================================================================================================
Step 1 : Remove OLAP Catalog
=====================================================================================================================

#### First lets remove OLAP Catalog

spool remove_olap.log

@/u01/app/oracle/product/11.2.0.3/olap/admin/catnoamd.sql

Once the script completes the execution, it has removed OLAP Catalog component from Database.

COMP_ID COMP_NAME SCHEMA VERSION STATUS MODIFIED
--------------- ----------------------------------- --------------- --------------- --------------- -------------------------
AMD OLAP Catalog OLAPSYS 11.2.0.3.0 VALID 09-FEB-2015 10:23:04

"catnoamd.sql" drops OLAPSYS user and its objects..

=====================================================================================================================
Step 2 : Remove OLAP API
=====================================================================================================================

@/u01/app/oracle/product/11.2.0.3/olap/admin/olapidrp.plb
@/u01/app/oracle/product/11.2.0.3/olap/admin/catnoxoq.sql

After we execute both the scripts, it drops the API component and update registry as removed like below,

COMP_ID COMP_NAME SCHEMA VERSION STATUS MODIFIED
--------------- ----------------------------------- --------------- --------------- --------------- -------------------------
XOQ Oracle OLAP API SYS 11.2.0.3.0 REMOVED 11-FEB-2015 17:34:59

=====================================================================================================================
Step 3 : Remove OLAP APS
=====================================================================================================================

@/u01/app/oracle/product/11.2.0.3/olap/admin/catnoaps.sql

COMP_ID COMP_NAME SCHEMA VERSION STATUS MODIFIED
--------------- ----------------------------------- --------------- --------------- --------------- -------------------------
XOQ Oracle OLAP API SYS 11.2.0.3.0 REMOVED 11-FEB-2015 17:34:59

"catnoaps.sql" updates registry and removes above OLAP API component and marks APS component as "REMOVED" in database registry.

COMP_ID COMP_NAME SCHEMA VERSION STATUS MODIFIED
--------------- ----------------------------------- --------------- --------------- --------------- -------------------------
APS OLAP Analytic Workspace SYS 11.2.0.3.0 REMOVED 11-FEB-2015 17:36:04

#### Drop the component

@/u01/app/oracle/product/11.2.0.3/olap/admin/cwm2drop.sql

Removed Below entry from database registry.

COMP_ID COMP_NAME SCHEMA VERSION STATUS MODIFIED
--------------- ----------------------------------- --------------- --------------- --------------- -------------------------
APS OLAP Analytic Workspace SYS 11.2.0.3.0 REMOVED 11-FEB-2015 17:36:04

=====================================================================================================================
Step 4 : Recompile INVALID Objects
=====================================================================================================================

As we removed the OLAP components some of the database objects goes to INVALID state. We have to recompile the database objects.

@/u01/app/oracle/product/11.2.0.3/rdbms/admin/utlrp.sql

spool off

select owner, object_name, object_type, status from dba_objects where status='INVALID';

=====================================================================================================================
Review the log file for detailed information.
=====================================================================================================================

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ END @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

ORA-20000: function-based index "XDB"."XDB$ACL_XIDX" is disabled

##########################
## Error
##########################

DBMS_STATS: GATHER_STATS_JOB encountered errors. Check the trace file.
Errors in file /u01/app/oracle/product/admin/ORALIN/diag/rdbms/ORALIN/ORALIN3/trace/ORALIN3_j000_25372.trc:
ORA-20000: function-based index "XDB"."XDB$ACL_XIDX" is disabled

##########################
# Error Occurred
##########################

This database is newly created and Error occured while auto Gather statistics Collection job is run in 11.2.0.4.0 version

##########################
## Command Executed
##########################

Gather stats command

**************************************** Step By Step Analysis ******************************************************

#########################################
# 1) Check XDB Registry and Index Status
#########################################

#### Check Database registry

SELECT comp_id,COMP_NAME,schema,VERSION,STATUS,MODIFIED FROM DBA_REGISTRY where comp_id='XDB';

COMP_ID COMP_NAME SCHEMA VERSION STATUS MODIFIED
--------------- ----------------------------------- --------------- --------------- --------------- -------------------------
XDB Oracle XML Database XDB 11.2.0.3.0 VALID 09-FEB-2015 10:23:02

#### Check the index

select owner, index_name, status, funcidx_status from dba_indexes where index_name = 'XDB$ACL_XIDX';

OWNER INDEX_NAME STATUS FUNCIDX_
------------------------------ ------------------------------ --------------- --------
XDB XDB$ACL_XIDX VALID DISABLED

=====================================================================================================================

#########################################
# 2) Reason for Failure
#########################################

Newly created database started giving these errors, looks like there is some problem with the installation which we have used is DBCA.

Most of the Database registry were in INVALID state. Made all the database components VALID and found the above error is occuring, which is due to the function based index in DISABLED state.

=====================================================================================================================

##########################
## Solution
##########################

#### Enable the index

Alter index xdb.xdb$acl_xidx enable;

SQL> Alter index xdb.xdb$acl_xidx enable;
Index altered.

SQL> select owner, index_name, status, funcidx_status from dba_indexes where index_name = 'XDB$ACL_XIDX';

OWNER INDEX_NAME STATUS FUNCIDX_
------------------------------ ------------------------------ --------------- --------
XDB XDB$ACL_XIDX VALID ENABLED

=====================================================================================================================
Now the GATHER stats job was running without any errors.
=====================================================================================================================

=====================================================================================================================
Comments Are Always welcome
=====================================================================================================================

Friday, November 28, 2014

OGG-00869, ORA-04098: trigger 'SIOD.EMP' is invalid and failed re-validation (status = 4098).

##########################
## Error
##########################

2014-11-21 02:13:33 WARNING OGG-00869 Oracle GoldenGate Delivery for Oracle, RTab.prm: OCI Error
ORA-04098: trigger 'SIOD.EMP' is invalid and failed re-validation (status = 4098). INSERT /*+ RESTRICT_ALL_REF_CONS */ INTO "SIOD"."TAB_1" ("ID",

### Full Error

2014-11-21 02:13:27 INFO OGG-00996 Oracle GoldenGate Delivery for Oracle, RTab.prm: REPLICAT RTab started.
2014-11-21 02:13:32 WARNING OGG-00869 Oracle GoldenGate Delivery for Oracle, RTab.prm: No unique key is defined for table 'TAB_1'. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
2014-11-21 02:13:33 WARNING OGG-00869 Oracle GoldenGate Delivery for Oracle, RTab.prm: OCI Error ORA-04098: trigger 'SIOD.EMP' is invalid and failed re-validation (status = 4098).
2014-11-21 02:13:33 ERROR OGG-01296 Oracle GoldenGate Delivery for Oracle, RTab.prm: Error mapping from SIOD.TAB_1 to SIOD.TAB_1.
2014-11-21 02:13:33 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, RTab.prm: PROCESS ABENDING.

##########################
# Error Occurred
##########################

Oracle Golden Gate Replicat process ABENDED with the above error.

##########################
## Command Executed
##########################

start replicat RTab

**************************************** Step By Step Analysis ******************************************************

#########################################
# 1) Check ggserr.log file
#########################################

2014-11-21 02:13:27 INFO OGG-00996 Oracle GoldenGate Delivery for Oracle, RTab.prm: REPLICAT RTab started.
2014-11-21 02:13:32 WARNING OGG-00869 Oracle GoldenGate Delivery for Oracle, RTab.prm: No unique key is defined for table 'TAB_1'. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
2014-11-21 02:13:33 WARNING OGG-00869 Oracle GoldenGate Delivery for Oracle, RTab.prm: OCI Error ORA-04098: trigger 'SIOD.EMP' is invalid and failed re-validation (status = 4098).
2014-11-21 02:13:33 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, RTab.prm: Repositioning to rba 154752882 in seqno 7060.
2014-11-21 02:13:33 ERROR OGG-01296 Oracle GoldenGate Delivery for Oracle, RTab.prm: Error mapping from SIOD.TAB_1 to SIOD.TAB_1.
2014-11-21 02:13:33 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, RTab.prm: PROCESS ABENDING.

=====================================================================================================================

#########################################
# 2) Reason for Failure
#########################################

Above error indicates that a trigger is in INVALID status due to which REPLICAT is failing to start.

=====================================================================================================================

#########################################
# 3) Check Trigger Status
#########################################

SET LINES 200
COL OBJECT_NAME FOR A30
COL OBJECT_TYPE FOR A25
COL Owner FOR A25

Select owner,object_name,object_type,status,created,LAST_DDL_TIME from dba_objects where object_name like 'EMP';

OWNER OBJECT_NAME OBJECT_TYPE STATUS CREATED LAST_DDL_
------------------------- ------------------------------ ------------------------- ------- --------- ---------
SIOD EMP TRIGGER INVALID 29-OCT-14 21-NOV-14

set lines 200
col triggering_event for a35
col table_owner for a25

Select owner,trigger_name,triggering_Event,table_owner,table_name,status from dba_triggers where trigger_name='EMP';

OWNER TRIGGER_NAME TRIGGERING_EVENT TABLE_OWNER TABLE_NAME STATUS
------------------------------ ------------------------------ ----------------------------------- ------------------------- ------------------------------ --------
SIOD EMP INSERT OR UPDATE SIOD TAB_1 ENABLED

=====================================================================================================================
From above we can see that TRIGGER is in ENABLED at the database level but it is in INVALID status.
=====================================================================================================================

#########################################
# Solution Available
#########################################

a) Fix the errors in trigger compilation.
b) We can use DBOPTIONS SUPPRESSTRIGGER to prevent Trigger from its execution.
c) Disable the trigger if not needed.

=====================================================================================================================

##########################
## Solution ( a )
##########################

a) Fix the errors in trigger compilation.

Alter trigger SIOD.EMP compile;

SQL> Alter trigger SIOD.EMP compile;
Warning: Trigger altered with compilation errors.

SQL> sho err
Errors for TRIGGER SIOD.EMP:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/2 PLS-00049: bad bind variable 'NEW.START_DATE'

=====================================================================================================================
Compilation of Trigger is not working as there is some definition error in the trigger.
=====================================================================================================================

##########################
## Solution ( b )
##########################

b) We can use DBOPTIONS SUPPRESSTRIGGER to prevent Trigger from its execution.

GGSCI (host01.example.com) 8> view param RTab

replicat RTab
USERID gold@oralin, PASSWORD ****
ASSUMETARGETDEFS
GROUPTRANSOPS 2000
EOFDELAYCSECS 20

-- To suppress the trigger over the target table.
DBOPTIONS SUPPRESSTRIGGERS
DBOPTIONS DEFERREFCONST

#### Modified the Replicat Parameter file and included "DBOPTIONS SUPPRESSTRIGGERS" keyword and restarted the Replicat which again failed with the below error.

#### oggerr.log

2014-11-21 06:45:12 INFO OGG-00995 Oracle GoldenGate Delivery for Oracle, RTab.prm: REPLICAT RTAB starting.
2014-11-21 06:45:12 INFO OGG-03035 Oracle GoldenGate Delivery for Oracle, RTab.prm: Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.
2014-11-21 06:45:12 INFO OGG-03501 Oracle GoldenGate Delivery for Oracle, RTab.prm: WARNING: NLS_LANG environment variable is invalid or not set. Using operating system character set value of AL32UTF8.
2014-11-21 06:45:12 ERROR OGG-01746 Oracle GoldenGate Delivery for Oracle, RTab.prm: Support for parameter SUPPRESSTRIGGERS is not available in the RDBMS version you are using.
2014-11-21 06:45:12 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, RTab.prm: PROCESS ABENDING.

=====================================================================================================================
Tried placing the suppresstriggers options below userid also which didn't work out.
=====================================================================================================================

##########################
## Solution ( c )
##########################

c) Disable the trigger if not needed.

Alter trigger SIOD.EMP disable;

SQL> Alter trigger SIOD.EMP disable;

Trigger altered.

Select owner,trigger_name,triggering_Event,table_owner,table_name,status from dba_triggers where trigger_name='EMP';

OWNER TRIGGER_NAME TRIGGERING_EVENT TABLE_OWNER TABLE_NAME STATUS
------------------------------ ------------------------------ ----------------------------------- ------------------------- ------------------------------ --------
SIOD EMP INSERT OR UPDATE SIOD TAB_1 DISABLED

=====================================================================================================================
As the other options are not working we disabled the trigger as it is not needed and restarted the replicat which came up without any issues...
=====================================================================================================================
Comments Are Always welcome
=====================================================================================================================

Wednesday, August 20, 2014

ORA-10458: standby database requires recovery

We want to place the Standby database to READ ONLY mode which is in Managed recovery mode of state MOUNTED.

##########################
## Error
##########################

ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '+DATA/oralin/datafile/system.264.854321773'

### Full Error

SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '+DATA/oralin/datafile/system.264.854321773'

##########################
# Error Occurred
##########################

Error occured while placing a standby database which is in mount stage to READ ONLY mode in 11.2.0.3.0 version

##########################
## Command Executed
##########################

alter database open read only;

**************************************** Step By Step Analysis ******************************************************

#########################################
# 1) Check MRP is running
#########################################

SQL> !ps -ef|grep mrp
oracle 26516 1 0 15:09 ? 00:00:00 ora_mrp0_oralin2
oracle 26753 21120 0 15:10 pts/2 00:00:00 /bin/bash -c ps -ef|grep mrp
oracle 26755 26753 0 15:10 pts/2 00:00:00 grep mrp

=====================================================================================================================

#########################################
# 2) Reason for Failure
#########################################

To make standby database READ ONLY, all the datafiles should be consistent. Error is occuring because MRP is still running in the database which keeps the media recover on.

=====================================================================================================================

##########################
## Solution
##########################

#### Stop the Media recovery and then place the database in read only mode..

SQL> recover managed standby database cancel;
Media recovery complete.

SQL> alter database open read only;
Database altered.

=====================================================================================================================
Then if you want to start the MRP, we can do like below,
=====================================================================================================================

SQL> Recover managed standby database disconnect from session using current logfile;
Media recovery complete.
SQL>

SQL> !ps -ef|grep mrp
oracle 26516 1 0 15:09 ? 00:00:00 ora_mrp0_oralin2
oracle 26753 21120 0 15:10 pts/2 00:00:00 /bin/bash -c ps -ef|grep mrp
oracle 26755 26753 0 15:10 pts/2 00:00:00 grep mrp

Wednesday, July 16, 2014

ORA-19505: failed to identify file "/opt/backup/arch/orahost3_oralin_oralin_20140707_1096_1_1_arch"

##########################
## Error
##########################

ORA-19505: failed to identify file "/opt/backup/arch/orahost3_oralin_oralin_20140707_1096_1_1_arch"

### Full Error

RMAN> restore archivelog logseq 5481 thread 2;
Starting restore at 07-JUL-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=5481
channel ORA_DISK_1: reading from backup piece /opt/backup/arch/orahost3_oralin_oralin_20140707_1096_1_1_arch
channel ORA_DISK_1: ORA-19870: error while restoring backup piece /opt/backup/arch/orahost3_oralin_oralin_20140707_1096_1_1_arch
ORA-19505: failed to identify file "/opt/backup/arch/orahost3_oralin_oralin_20140707_1096_1_1_arch"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

##########################
# Error Occurred
##########################

Error occured while trying to restore a archivelog from a backup in 11.2.0.3.0 version of RAC Database.

##########################
## Command Executed
##########################

restore archivelog logseq 5481 thread 2;

**************************************** Step By Step Analysis ******************************************************

#########################################
# 1) Check Archive log Information
#########################################

set lines 200
col name for a75
col thread# for 9999
col creator for a8
col completion_time for a20

Select name,creator,thread#,sequence#,applied,status,to_char(first_time,'DD-MON-YYYY HH24:MI:SS') "first_time",to_char(completion_time,'DD-MON-YYYY HH24:MI:SS') "completion_time",resetlogs_id,archived,backup_count
from v$archived_log where sequence#>=5481 and thread#=2 order by sequence# desc;

NAME CREATOR THREAD# SEQUENCE# APPLIED S completion_time RESETLOGS_ID ARC BACKUP_COUNT
-------------------------------------------------------------------------------- -------- ------- ---------- --------- - -------------------- ------------ --- ------------
+RECO/oralin/archivelog/2014_07_07/thread_2_seq_5484.411.852301159 ARCH 2 5484 NO A 07-JUL-2014 14:19:32 825774564 YES 0
+RECO/oralin/archivelog/2014_07_07/thread_2_seq_5483.683.852301111 ARCH 2 5483 NO A 07-JUL-2014 14:18:46 825774564 YES 0
ARCH 2 5482 NO D 07-JUL-2014 12:01:56 825774564 YES 0
ARCH 2 5481 NO D 07-JUL-2014 12:01:54 825774564 YES 1

Backup_count is 1, so archive log sequence 5481 is backed up once.

[oracle@orahost3 arch]$ ls -ltr
total 10648680
-rw-r----- 1 oracle oinstall 2199391232 Jul 7 12:04 orahost3_oralin_oralin_20140707_1096_1_1_arch
[oracle@orahost3 arch]$

=====================================================================================================================

#########################################
# 2) Reason for Failure
#########################################

RMAN tries to fetch the backup piece to restore the archive log but it can't find the backup piece.

This is a RAC cluster database. Backups are scheduled to run from orahost3 server. Restore command was run from orahost1 server.

=====================================================================================================================

#########################################
# 3) Reproduce the Error
#########################################

[oracle@orahost1 srini]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Mon Jul 7 15:20:31 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: oralin (DBID=422297444)
RMAN> restore archivelog logseq 5481 thread 2;
Starting restore at 07-JUL-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=5481
channel ORA_DISK_1: reading from backup piece /opt/backup/arch/orahost3_oralin_oralin_20140707_1096_1_1_arch
channel ORA_DISK_1: ORA-19870: error while restoring backup piece /opt/backup/arch/orahost3_oralin_oralin_20140707_1096_1_1_arch
ORA-19505: failed to identify file "/opt/backup/arch/orahost3_oralin_oralin_20140707_1096_1_1_arch"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
failover to previous backup
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/07/2014 15:20:54
RMAN-06026: some targets not found - aborting restore
RMAN-06025: no backup of archived log for thread 2 with sequence 5481 and starting SCN of 9619365298 found to restore
RMAN>

=====================================================================================================================

##########################
## Solution
##########################

Backups are scheduled to run in orahost3, and the backup mount point /opt seems to be not a shared one.

Backup mount point mostly will be a shared one in RAC databases, but looks like in this server has some exemption for it.

Ran a restore by logging into orahost3 and the restore went successful.

=====================================================================================================================
In case if you encounter the same issues, please check the backup pieces in all the nodes of a cluster before coming to the conclusion that there is no backups available.
=====================================================================================================================

[oracle@orahost3 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Mon Jul 7 15:23:06 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: oralin (DBID=422297444)
RMAN> restore archivelog logseq 5481 thread 2;
Starting restore at 07-JUL-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=566 instance=oralin3 device type=DISK
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=5481
channel ORA_DISK_1: reading from backup piece /opt/backup/arch/orahost3_oralin_oralin_20140707_1096_1_1_arch
channel ORA_DISK_1: piece handle=/opt/backup/arch/orahost3_oralin_oralin_20140707_1096_1_1_arch tag=%TAG
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 07-JUL-14
RMAN>

Restore of Archive log gives RMAN-20242: specification does not match any archived log in the repository

##########################
## Error
##########################

RMAN-20242: specification does not match any archived log in the repository

### Full Error

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11657 instance=oralin1 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/07/2014 15:20:43
RMAN-20242: specification does not match any archived log in the repository

##########################
# Error Occurred
##########################

Error occured while trying to restore an archive log in 11.2.0.3.0 version of RAC database.

##########################
## Command Executed
##########################

restore archivelog logseq 5481;

**************************************** Step By Step Analysis ******************************************************

#########################################
# 1) Check Archive log Status
#########################################

set lines 200
col name for a75
col thread# for 9999
col creator for a8
col completion_time for a20

Select name,creator,thread#,sequence#,applied,status,to_char(first_time,'DD-MON-YYYY HH24:MI:SS') "first_time",to_char(completion_time,'DD-MON-YYYY HH24:MI:SS') "completion_time",resetlogs_id,archived,backup_count
from v$archived_log where sequence#>=5481 and thread#=2 order by sequence# desc;

NAME CREATOR THREAD# SEQUENCE# APPLIED S completion_time RESETLOGS_ID ARC BACKUP_COUNT
-------------------------------------------------------------------------------- -------- ------- ---------- --------- - -------------------- ------------ --- ------------
+RECO/oralin/archivelog/2014_07_07/thread_2_seq_5484.411.852301159 ARCH 2 5484 NO A 07-JUL-2014 14:19:32 825774564 YES 0
+RECO/oralin/archivelog/2014_07_07/thread_2_seq_5483.683.852301111 ARCH 2 5483 NO A 07-JUL-2014 14:18:46 825774564 YES 0
ARCH 2 5482 NO D 07-JUL-2014 12:01:56 825774564 YES 0
ARCH 2 5481 NO D 07-JUL-2014 12:01:54 825774564 YES 1

=====================================================================================================================

#########################################
# 2) Reason for Failure
#########################################

Error is occuring when a restore of archive logs is done. v$archived_log shows backup_count as 1 which means the archive log 5481 sequence is backed up once.

=====================================================================================================================

##########################
## Solution
##########################

Step 1) shows backup has been taken, but RMAN says that backup is not available.

Well the database in which i'm playing is a RAC database. When it comes to rac cluster database we have to use thread# with the sequence number.

restore archivelog logseq 5481 thread 2;

RMAN> restore archivelog logseq 5481 thread 2;
Starting restore at 07-JUL-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=566 instance=oralin3 device type=DISK
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=5481
channel ORA_DISK_1: reading from backup piece /opt/backup/arch/erwin_ch2_29_oralin_oralin_20140707_1096_1_1_arch
channel ORA_DISK_1: piece handle=/opt/backup/arch/erwin_ch2_29_oralin_oralin_20140707_1096_1_1_arch tag=%TAG
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 07-JUL-14
RMAN>

DBMS_STATS.GATHER_TABLE_STATS gives ORA-20005: object statistics are locked (stattype = ALL)

##########################
## Error
##########################

ORA-20005: object statistics are locked (stattype = ALL)

### Full Error

SQL> exec dbms_stats.gather_table_Stats('STHIRUNAVUKKARASU','T1');
BEGIN dbms_stats.gather_table_Stats('STHIRUNAVUKKARASU','T1'); END;
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 23154
ORA-06512: at "SYS.DBMS_STATS", line 23205
ORA-06512: at line 1

##########################
# Error Occurred
##########################

Error occured while trying to gather statistics for a table is run in 11.2.0.3.5 version

##########################
## Command Executed
##########################

exec dbms_stats.gather_table_Stats('STHIRUNAVUKKARASU','T1');

**************************************** Step By Step Analysis ******************************************************

#########################################
# 1) Check the table Statistics
#########################################

set lines 200
Select owner,table_name,to_char(last_analyzed,'DD-MON-YYYY HH24:MI:SS') from dba_tables where table_name='T1';

OWNER TABLE_NAME TO_CHAR(LAST_ANALYZED,'DD-MON
------------------------------ ------------------------------ -----------------------------
STHIRUNAVUKKARASU T1 10-JUL-2014 07:24:38

#### Check for table with Locked Statistics

SQL> select owner,table_name,stattype_locked from dba_tab_statistics where stattype_locked is not null and owner='STHIRUNAVUKKARASU';

OWNER TABLE_NAME STATT
------------------------------ ------------------------------ -----
STHIRUNAVUKKARASU T1 ALL

=====================================================================================================================

#########################################
# 2) Reason for Failure
#########################################

Error is occuring when stats for a single table is being gathered.

From Step 1), we can see Statistics has been locked for the table. Usually application developers or DBA's lock statistics for some table such that
the path oracle uses to fetch records from the table will not get changed.

=====================================================================================================================

##########################
## Solution
##########################

#### In case if statistics for the table needs to be gathered, it can be done in 2 ways..

=====================================================================================================================
a) Gather Statistics with Force option
=====================================================================================================================

exec dbms_Stats.gather_table_stats(ownname=>'STHIRUNAVUKKARASU',tabname=>'T1',force=>TRUE);

SQL> exec dbms_Stats.gather_table_stats(ownname=>'STHIRUNAVUKKARASU',tabname=>'T1',force=>TRUE);
PL/SQL procedure successfully completed.
SQL>

Select owner,table_name,to_char(last_analyzed,'DD-MON-YYYY HH24:MI:SS') from dba_tables where table_name='T1';

OWNER TABLE_NAME TO_CHAR(LAST_ANALYZED,'DD-MON
------------------------------ ------------------------------ -----------------------------
STHIRUNAVUKKARASU T1 10-JUL-2014 07:26:16

select owner,table_name,stattype_locked from dba_tab_statistics where stattype_locked is not null and owner='STHIRUNAVUKKARASU';

OWNER TABLE_NAME STATT
------------------------------ ------------------------------ -----
STHIRUNAVUKKARASU T1 ALL

=====================================================================================================================
b) Unlock the table stats and run normal stats gather on that table.
=====================================================================================================================

exec dbms_stats.unlock_table_Stats('STHIRUNAVUKKARASU','T1');

SQL> exec dbms_stats.unlock_table_Stats('STHIRUNAVUKKARASU','T1');
PL/SQL procedure successfully completed.

SQL> select owner,table_name,stattype_locked from dba_tab_statistics where stattype_locked is not null and owner='STHIRUNAVUKKARASU';

no rows selected

SQL>

exec dbms_stats.gather_table_Stats('STHIRUNAVUKKARASU','T1');

SQL> exec dbms_stats.gather_table_Stats('STHIRUNAVUKKARASU','T1');
PL/SQL procedure successfully completed.

SQL>

SQL> Select owner,table_name,to_char(last_analyzed,'DD-MON-YYYY HH24:MI:SS') from dba_tables where table_name='T1';

OWNER TABLE_NAME TO_CHAR(LAST_ANALYZED,'DD-MON
------------------------------ ------------------------------ -----------------------------
STHIRUNAVUKKARASU T1 10-JUL-2014 07:28:18

Archive log files are not getting stored in DB_RECOVERY_FILE_DEST

Archive log files are configured to use db_Recovery_file_dest which has been set to ASM. But the archive log files are getting stored in $ORACLE_HOME/dbs instead of ASM disk group

**************************************** Step By Step Procedure ******************************************************

#########################################
# 1) Check Archive logs location
#########################################

set lines 200
col name for a90
col thread# for 9999
col creator for a8
col completion_time for a20

Select name,creator,thread#,sequence#,applied,status,to_char(completion_time,'DD-MON-YYYY HH24:MI:SS') "completion_time",archived,backup_count
from v$archived_log order by sequence#;

NAME CREATOR THREAD# SEQUENCE# APPLIED S completion_time ARC BACKUP_COUNT
------------------------------------------------------------------------------------------ -------- ------- ---------- --------- - -------------------- --- ------------
/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/USE_DB_RECOVERY_FILE_DEST,1_3_852382837.dbf ARCH 1 3 NO A 08-JUL-2014 18:22:57 YES 0
/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/USE_DB_RECOVERY_FILE_DEST,1_4_852382837.dbf ARCH 1 4 NO A 08-JUL-2014 18:23:36 YES 0
/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/USE_DB_RECOVERY_FILE_DEST,1_13_852382837.dbf ARCH 1 13 NO A 09-JUL-2014 16:50:34 YES 0

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST,
Oldest online log sequence 14
Next log sequence to archive 17
Current log sequence 17
SQL>

SQL> sho parameter log_archive_dest_1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string location=USE_DB_RECOVERY_FILE_
DEST, valid_for=(ALL_LOGFILES,
ALL_ROLES)

=====================================================================================================================

#########################################
# 3) So What's the Issue
#########################################

From Step 1), we can see that archive logs are getting stored in $ORACLE_HOME/dbs location

From Step 2), it clearly shows the db_recovery_file_dest is being used.

Seems like the parameters are configured correctly then why archive logs are not stored in ASM disk group.

=====================================================================================================================

#########################################
# Solution
#########################################

Eventhough the configuration seems to be correct, there is a small mistake in defining log_archive_dest_1 parameter,

If we can see the keyword used in log_archive_dest_1 ( location=USE_DB_RECOVERY_FILE_DEST, )

A Comma (,) is being used at the end of the location keyword which seems to be the root cause of this issue.

#### Modifying the Parameter after removing comma (,)

Alter system set log_archive_dest_1 = 'location=USE_DB_RECOVERY_FILE_DEST valid_for=(ALL_LOGFILES, ALL_ROLES)' sid='*';

SQL> Alter system set log_archive_dest_1 = 'location=USE_DB_RECOVERY_FILE_DEST valid_for=(ALL_LOGFILES, ALL_ROLES)' sid='*';
System altered.

SQL> sho parameter log_archive_dest_1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string location=USE_DB_RECOVERY_FILE_
DEST valid_for=(ALL_LOGFILES,
ALL_ROLES)
SQL> Alter system switch logfile;
System altered.
SQL>

NAME CREATOR THREAD# SEQUENCE# APPLIED S completion_time ARC BACKUP_COUNT
------------------------------------------------------------------------------------------ -------- ------- ---------- --------- - -------------------- --- ------------
/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/USE_DB_RECOVERY_FILE_DEST,1_13_852382837.dbf ARCH 1 13 NO A 09-JUL-2014 16:50:34 YES 0
/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/USE_DB_RECOVERY_FILE_DEST,1_14_852382837.dbf ARCH 1 14 NO A 09-JUL-2014 16:50:34 YES 0
/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/USE_DB_RECOVERY_FILE_DEST,1_15_852382837.dbf ARCH 1 15 NO A 09-JUL-2014 16:50:34 YES 0
/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/USE_DB_RECOVERY_FILE_DEST,1_16_852382837.dbf ARCH 1 16 NO A 10-JUL-2014 12:16:19 YES 0
+ARCH/oralin/archivelog/2014_07_10/thread_1_seq_17.264.852568337 ARCH 1 17 NO A 10-JUL-2014 16:32:17 YES 0

The new archive logs are getting created in +ARCH disk group.

=====================================================================================================================
Comments Are Always welcome
=====================================================================================================================