Thursday, October 20, 2011

Prevent Truncate/Drop table from own schema


To prevent the truncation of tables in your own schema, write a trigger in your schema.

CREATE OR REPLACE TRIGGER PREVENT_TRUNCATE
BEFORE DROP OR TRUNCATE
ON SCHEMA
BEGIN
RAISE_APPLICATION_ERROR(-20999, 'Attempt To Truncate A Production Table Has Been Logged');
END save_our_db;
/

Now when you try to truncate a table in your own schema, you will receive the following errors:

ORA-00604: error occurred at recursive SQL level 1
ORA-20999:Attempt To Truncate A Production Table Has Been Logged
ORA-06512: At Line 2

Another way to prevent user from dropping objects from own schema is as below:

- Here we are not using any trigger
- This would prevent drop object for SQL*PLUS only
- We could configure it for other IDE tools like TOAD, SQLDEVELOPER etc

SQL> connect system/manager

SQL> insert into product_user_profile (product,userid,attribute,char_value) values('SQL*Plus','SCOTT,'DROP','DISABLED');
SQL> commit;


SQL> select * from product_user_profiles;

PRODUCT    USERID  ATTRIBUTE  CHAR_VALUE
---------  ------- ---------- ----------------
SQL*PLUS   SCOTT   DROP       DISABLED


If for example user SCOTT attempts to Drop an object, it will get this error:

SQL> drop table xyz;
SP2-0544: invalid command: drop.

Alter Database Link


We can alter a DATABASE Link in order modify password (password changed recently for remote schema) without dropping & recreating.

Here is a simple test.

1. Create a DBLINK with wrong password.

CREATE DATABASE LINK TEST_LINK
 CONNECT TO TIGER
 IDENTIFIED BY "tiger1"
 USING 'TESTENV';

SQL> select * from dual@TEST_LINK;
select * from dual@TEST_LINK
                  *
ERROR at line 1:
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from TEST_LINK


1.    Grant link alter permission to scott.

GRANT ALTER DATABASE LINK TO SCOTT;
Access Granted.

2.    Alter the link with correct password.

ALTER DATABASE LINK TEST_LINK
  CONNECT TO TIGER IDENTIFIED BY "tiger123";

SQL> select * from dual@TEST_LINK;

D
-
X

Gather Optimizer statistics, 11g

In Oracle 11g: The automatic statistics gathering job for Oracle 11g is an internal procedure named GATHER_DATABASE_STATS_JOB_PROC.

Below are the steps in case if we want to delete & gather schema stats on adhoc basis.

Gathering statistics for all objects in a schema
  1. Ensure that the automatic statistics gathering job is scheduled, using the following SQL.
SELECT  ENABLED FROM DBA_SCHEDULER_PROGRAMS WHERE PROGRAM_NAME = 'GATHER_STATS_PROG';

Output should be 'TRUE'.
Note: GATHER_STATS_PROG is the program that runs the internal procedure GATHER_DATABASE_STATS_JOB_PROC.
  1. Delete any existing statistics, using the following command from SQLPLUS.
EXEC DBMS_STATS.DELETE_SCHEMA_STATS('SCOTT');
  1. Change the default "ESTIMATE_PERCENT" to 100% for DBMS_STATS. Use the following procedure from SQLPLUS to change the default. Run this using the Oracle SYSDBA user ID:
EXEC DBMS_STATS.SET_PARAM('ESTIMATE_PERCENT','100');

d. Run GATHER_DATABASE_STATS using the following command from SQLPLUS. Run this command using the Oracle SYSDBA user ID.

EXEC DBMS_STATS.GATHER_DATABASE_STATS(ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO', DEGREE => 2, CASCADE => TRUE);

Gathering statistics an individual table

exec dbms_stats.gather_table_stats(  -
       ownname => '  Schema_name ', -
       tabname => '  Table_name  ', -
       estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,  -
       cascade => TRUE,  -
       method_opt => 'FOR ALL COLUMNS SIZE AUTO' );
NOTE: For a more cautious approach as outlined in the text above and where column statistics are known not to be beneficial, Replace:

method_opt => 'FOR ALL COLUMNS SIZE AUTO'
with
method_opt => 'FOR ALL COLUMNS SIZE 1'

Note that the defaults for statistics gathering on different versions of Oracle are not necessarily the same, for example:

  • ESTIMATE_PERCENT: defaults:
    •  9i : 100%
    • 10g : DBMS_STATS.AUTO_SAMPLE_SIZE (using very small estimate percentage)
    • 11g: DBMS_STATS.AUTO_SAMPLE_SIZE (using larger estimate percentage - 100%)
  • METHOD_OPT: defaults:
    • 9i: "FOR ALL COLUMNS SIZE 1" effectively no detailed column statistics.
    • 10g and 11g: "FOR ALL COLUMNS SIZE AUTO" - This setting means that DBMS_STATS decides which columns to add histogram to where it believes that they may help to produce a better plan. 

Monday, October 17, 2011

How to Restore spfile from backup


1.Connect to target Database with RMAN.

i)If you have lost your spfile while your database is running then,

RMAN> CONNECT TARGET /

ii)If your database is not up and you don't use recovery catalog then use,

RMAN>CONNECT TARGET /
RMAN>SET DBID 3386862614


2)Start the instance with dummy parameter file.

RMAN>STARTUP FORCE NOMOUNT

3) Restore server parameter file.

i)             To restore in default location,

RMAN>RESTORE SPFILE FROM AUTOBACKUP;

ii)            To restore in another location,

RMAN> RESTORE SPFILE TO 'new_location' FROM AUTOBACKUP;


iii)           If you want to restore to a pfile then use,

RMAN> RESTORE SPFILE TO PFILE '/tmp/initTEMP.ora';


4) Start the instance.

RMAN>STARTUP;

Cleanup Message Queue (MQ) Gateway Agent

Message queue terminated as DB was terminated unexpectedly. Once db is up, MQ won't start with following error.

SQL> exec dbms_mgwadm.shutdown
PL/SQL procedure successfully completed.

SQL> exec dbms_mgwadm.startup
BEGIN dbms_mgwadm.startup; END;
*
ERROR at line 1:
ORA-32846: Messaging Gateway agent cannot be started; status is BROKEN
ORA-06512: at "SYS.DBMS_MGWADM", line 88
ORA-06512: at "SYS.DBMS_MGWADM", line 66
ORA-06512: at line 1

No new LOG is genrating while restarting MQ.


SQL> select agent_status,agent_ping, last_error_date, last_error_msg, max_connections, max_memory, max_threads, agent_status,agent_ping from mgw_gateway;

AGENT_STATUS AGENT_PING LAST_ERROR_MSG MAX_CONNECTIONS AGENT_STATUS AGENT_PING
BROKEN UNREACHABLE ORA-32845: Messaging Gateway agent is already running 100 64 1 BROKEN UNREACHABLE

SOLUTION:

1.    Shutdown Message Queue

exec dbms_mgwadm.shutdown

2.    Check for invalid packages -

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

3.    Recompile any invalid objects.  Either run UTLRP.SQL or recompile individually.
The dbms_mgwadm and mgwi_amdin packages should be valid.

4.    If the Messaging Gateway shutdown still does not work, then issue the following to cleanup the gateway -
Exec DBMS_MGWADM.CLEANUP_GATEWAY(1);

OR
exec dbms_mgwadm.cleanup_gateway(dbms_mgwadm.CLEAN_STARTUP_STATE);


This runs recovery tasks that set the gateway to a known state when the gateway agent has crashed or some other abnormal event occurs so that the gateway cannot be started.

NOTE: This command will only work if a previous shutdown command has been attempted otherwise error messages will be displayed.

Friday, October 07, 2011

Register database with recovery catalog

If you are connected to RMAN catalog but didn’t register the database with catalog server then you are allowed to connect to catalog

-bash-3.2$ rman target RMANUSER/****@SOURCEDB catalog backup_user/*****@catalog_rman.world

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Oct 7 09:13:06 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: SOURCEDB (DBID=3625558458)
connected to recovery catalog database

But,  Backup or any other operations will fail!

RMAN> report schema;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of report command at 10/07/2011 09:13:14
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20001: target database not found in recovery catalog


Register your database using following command.

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> report schema;

RMAN> report schema;

Report of database schema for database with db_unique_name SOURCEDB

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    890      SYSTEM               YES     /oradata/DB/system01.dbf
2    750      SYSAUX               NO      /oradata/DB/sysaux01.dbf
3    1835     UNDOTBS1             YES     /oradata/DB/undotbs01.dbf
4    645      USERS                NO      /oradata/DB/users01.dbf
......................................................................

Tuesday, October 04, 2011

Alter database open resetlogs error ORA-00349

After restore & recovery from RAC ASM to single instance (non ASM) I got error:

alter database open resetlogs
*
ERROR at line 1:
ORA-00349: failure obtaining block size for '+ASMDISK1/DW/onlinelog/redo_g1_a'

In alert.log:
Tue Oct 04 06:01:07 2011
Errors in file /oracle/admin/dw/udump/DW_ora_3115.trc:
ORA-00313: open failed for members of log group 4 of thread 1
ORA-00312: online log 4 thread 1: '+DISK1/DW/redo_g4_b'
ORA-17503: ksfdopn:2 Failed to open file +DISK1/DW/redo_g4_b
ORA-15173: entry 'redo_g4_b' does not exist in directory 'DW'
ORA-00312: online log 4 thread 1: '+DISK1/DW/redo_g4_a'
ORA-17503: ksfdopn:2 Failed to open file +DG01/DW/redo_g4_a
ORA-15173: entry 'redo_g4_a' does not exist in directory 'DW'

Reason:
Redo log locations are unknown to new Instance.

Solution:
Rename redo log locations from ASM to existing location.

ALTER DATABASE RENAME FILE '+ASMDG01/XXX/onlinelog/redo_g1_a' TO '+DG01/XXX/redo_g1_a';

SQL> alter database open resetlogs;
Database altered.

Monday, October 03, 2011

Schedule job failure: ORA-48318

While running RMAN restore job it got stuck and alert log was showing  ORA-48318: ADR Relation [HM_FINDING] of version=3 cannot be supported.

Weird!!!

This problem has been identified and verified in an unpublished Bug  Usually the error is reported by Diagnosibility Framework after Downgrading from Release 11.2 to 11.1

After downgrading from Oracle Database release 11.2 to 11.1, the error ORA-48318 may occur in the alert log, or when using the ADRCI utility.

Errors in file /u01/app/oracle/prd/diag/rdbms/prd/prd/trace/prd_m000_22828.trc:
ORA-51106: check failed to complete due to an error.  See error below
ORA-48318: ADR Relation [HM_FINDING] of version=3 cannot be supported
Mon Oct 03 14:55:55 2011
Errors in file /u01/app/oracle/prd/diag/rdbms/prd/prd/trace/prd_m000_22987.trc:

ORA-51106: check failed to complete due to an error.  See error below
ORA-48318: ADR Relation [HM_FINDING] of version=3 cannot be supported
Mon Oct 03 15:40:56 2011

Solution:

The only options to fix the error is the following workaround:

1.    Determine the location of the ADR home:

    SQL> select value from v$diag_info where name = 'ADR Home';
    VALUE
    ------------------------------------------------------------------------------
   /u01/app/oracle/prd/diag/rdbms/prd/prd/trace
 
2. Shutdown the database instance

3. Remove the ADR home directory using OS utilities: It will be automatically recreated with the proper versions when the instance is restarted.

Wednesday, August 24, 2011

Datapump export / Import to a Downward version

If you are migrating in between Upper to Lower version of Oracle database using DATAPUMP then you may runinto below error.

For my case, I was trying to export from 11.2.0.2 to 11.1.0.6

ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 3.1 in dump file "/datapump/scott.dmp"

Solution is to specify the VERSION of target Database on datapump export parameter. And then import using regular command.

Example of using VERSION inside datapump command:

expdp system/*******  VERSION=11.1.0.0.0 SCHEMAS=SCOTT directory=DATAPUMP dumpfile=SCOTT.dmp logfile=SCOTT_exp.log

Tuesday, July 26, 2011

Reinstalling OCM thows ORA-20007: ORA-31003: Parent /sys/acls/ already contains child entry

On the process of reinstalling OCM; ORACLE_OCM user was dropped. After running setupCCR the setup of installCCRSQL.sh was failing with below:

[oracle@test ccr]$ $ORACLE_HOME/ccr/admin/scripts/installCCRSQL.sh collectconfig
An error had occured.
For details, check the log file at /u01/app/oracle/product/11.2.0/db_1/ccr/hosts/test/log/collectconfigkandevdb.log

ERROR at line 1:
ORA-20007: ORA-31003: Parent /sys/acls/ already contains child entry
oracle-sysman-ocm-Resolve-Access.xml
ORA-06512: at line 163
The problem is, though ORACLE_OCM is removed but the dependencies are not removed.

Following is the Workaround:
begin
DBMS_NETWORK_ACL_ADMIN.DROP_ACL('oracle-sysman-ocm-Resolve-Access.xml');
commit;
end;


And then run installCCRSQL.sh and should be good