rebuild datapump metadata

Some times, due to some internal errors, datapump utility binaries may get corrupted. In such cases, we can rebuild that using below ways

In Oracle database 10.1 version :

SQL> connect / as sysdba

SQL >@ $ORACLE_HOME/rdbms/admin/catdp.sql – this will install metadata

SQL >@ $ORACLE_HOME/rdbms/admin/dbmspump.sql – this will create DBMS procedures for datapump

In Oracle database 10.2 version :

SQL >@ $ORACLE_HOME/rdbms/admin/catdph.sql

SQL >@ $ORACLE_HOME/rdbms/admin/prvtdtde.plb

SQL >@ $ORACLE_HOME/rdbms/admin/catdpb.sql

SQL >@ $ORACLE_HOME/rdbms/admin/dbmspump.sql

After performing above steps, all the binaries will be re-build. So, it is better to execute utlrp.sql to recompile invalid objects

SQL >@ $ORACLE_HOME/rdbms/admin/utlrp.sql

In Oracle 11g :

SQL >@ $ORACLE_HOME/rdbms/admin/catproc.sql (catproc.sql will take care of all other script execution)

To recompile invalid objects, if any

SQL >@ $ORACLE_HOME/rdbms/admin/utlrp.sql

12C database

12c MOS:
Oracle Database 12c Release 1 (12.1) Upgrade New Features [ID 1515747.1]
NOTE:1520299.1 - Master Note For Oracle Database 12c Release 1 (12.1) Database/Client Installation/Upgrade/Migration Standalone Environment (Non-RAC)
NOTE:1493645.1 - Oracle Database 12c Release 1 (12.1) DBUA : Understanding New Changes With All New 12.1 DBUA 
NOTE:1503653.1 - Complete Checklist for Manual Upgrades to Oracle Database 12c Release 1 (12.1)
Oracle Database 12c Release 1 (12.1) Upgrade New Features [ID 1515747.1]
RMAN RECOVER TABLE Feature New to Oracle Database 12c [ID 1521524.1]
How to Merge Multiple Partitions in Oracle 12C [ID 1482263.1]
How to Create Interval-Reference Partitioned Tables in Oracle 12c [ID 1519042.1]

AWR report’s use in RAC Env

Generate AWR reports for any instance in the cluster:

Run the awrrpti script from $ORACLE_HOME/rdbms/admin/
as a sys user. The script will ask you for the output format of the report (HTML or TEXT), the instance number, the the number of days (n) will result in the most recent
(n) days of snapshots being listed (If you hit enter, you will get all the available snapshots listed), begin and end snapshot interval Ids …

 Generate AWR Cluster aggregated statistics from all the instances:

Run the awrgrpt script from $ORACLE_HOME/rdbms/admin/ as a sys user. The script will ask you for the output format of the report (HTML or TEXT), the the number of days (n) will result in the most recent
(n) days of snapshots being listed (If you hit enter, you will get all the available snapshots listed), begin and end snapshot interval Ids …

 Generate AWR Global Cluster Difference Report:

Run the awrgdrpt.sql script from $ORACLE_HOME/rdbms/admin/ as a sys user. The script will ask you for the output format of the report (HTML or TEXT), the the number of days (n) will result in the most recent
(n) days of snapshots being listed (If you hit enter, you will get all the available snapshots listed), begin and end snapshot interval Ids of the first and second pair diff intervals…
This report compares the statistic results of differences between two different snapshot intervals, for the whole cluster database.

Generate AWR Report Single Select Statement:

Run the awrsqrpt.sql script from $ORACLE_HOME/rdbms/admin/ as a sys user. The script will ask you for the output format of the report (HTML or TEXT), the the number of days (n) will result in the most recent
(n) days of snapshots being listed (If you hit enter, you will get all the available snapshots listed), begin and end snapshot interval Ids intervals and the SQL_ID for the specific SELECT statement …

How to restrict user not to run any dml from sql prompt(backend),but allow user to run dml from application?

Today one dba  asked me -
How to restrict user not to run any dml from sql prompt(backend),but allow user to run dml from application?

-
I replyed him below-

If You meant to say that privileges user should not change the data through back end using SQLPLUS BUT the same user can insert update delete through application level entry.

Then you can use PRODUCT_USER_PROFILE table to disable certain SQL PL/SQL and SQL*Plus commands in the SQL*Plus environment on a per user basis except system user.
 This will never going to effect user's privileges at application level.

If this table is not installed then you can create PRODUCT_USER_PROFILE by running the command file named PUPBLD.sql.as SYSTEM USER. The exact format of the file extension and the location of the file are system dependent. Mostly this table are installed by default just check first.

These tables allow SQL*Plus to disable commands per user. The tables
are used only by SQL*Plus and do not affect other client tools that access the database.


example:- login in as System user

SQL> CONN SYSTEM/pwd
Connected.
SQL> insert into product_user_profile
(
PRODUCT ,
USERID ,
ATTRIBUTE ,
SCOPE ,
NUMERIC_VALUE,
CHAR_VALUE ,
DATE_VALUE ,
LONG_VALUE
)
values
('SQL*PLUS','SCOTT','INSERT',NULL,NULL,'DISABLED',NULL,NULL);

1. row created.

NOW CONNECT TO HR/HR

SQL> conn HR/HR
Connected.
SQL> insert into test_table values('testing');
SP2-0544: Command "insert" disabled in Product User Profile.

likewise you can add more entry for update,delete and even begin and end keyword of pl/sql command which totally disallowed using of pl/sql block.

skip blank lines in between SQL statements in SQL*Plus?

If a SQL script file has blank lines in between SQL statements one may see errors when executing the script in SQL*Plus so if one would like to ignore the blanklines in SQL*Plus one can ignore them by set blanklines on.

Example:
SQL> select object_name
2
SQL> from dba_objects;
SP2-0734: unknown command beginning “from dba_o…” – rest of line ignored.
SQL> set sqlblanklines on
SQL> select object_name
2
3 from dba_objects
4 where rownum < 3;

OBJECT_NAME
——————————————————————————–
ICOL$
I_USER1
– Disable blank lines in between SQL statements
SQL> set sqlblanklines off

How to force a trace file to be created when an ORA error occurs?

There was an issue we ran into where one of the users ran out temporary tablespace (TEMP) we had message in the database alert log that there oracle couldn’t extend temporary tablespace and we couldn’t find the cause and user who ran into the issue. So turning on event for the error oracle will create a trace file which will contain user, machine, os, SQL which will allow DBA to find additional information.

Example:
– ORA-1652: unable to extend temp .. (for temp tablespace)
SQL> ALTER system SET EVENTS ’1652 TRACE NAME ERRORSTACK LEVEL 3′;

It will write to the alert.log
Tue Jul 21 11:04:45 2009
Errors in file /u01/admin/TESTDB/udump/testdb_ora_17682588.trc:
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

The trace file would contain the following information including the SQL statement:
….
oct: 2, prv: 0, sql: 7000000593f3dc8, psql: 700000057c3ec30, user: 420/TEST
O/S info: user: ajaffer, term: MYCOMPUTER, ospid: 3684:2444, machine: MYCOMPUTERNAME

To turn trace off one would run the following SQL:
SQL> ALTER system SET EVENTS ’1652 TRACE NAME ERRORSTACK OFF’;

If one would like to set event in the spfile, you can set by executing the DDL below. If there are multiple events then it can be set by they have to be “:” separated. In the example below event is set for ORA-01653 and ORA-01652
SQL> alter system set event=’1653 TRACE NAME ERRORSTACK LEVEL 3:1652 TRACE NAME ERRORSTACK LEVEL 3′ scope=SPFILE;

To clear the event in the spfile, you can execute the following SQL ALTER SYSTEM SET EVENT=” SCOPE=spfile;

One can similar turn trace on for various ora errors but be aware that some of them by caused internally from within Oracle example ORA-604 which you may not want to turn on event for.

Tracing a session via a logon trigger

Awhile back I found a tip which has come in handy quite a few times. Just recently I was troubleshooting an ORA-03124 error which occurred during a run of a BI tool. The BI tool spawns many sessions while gathering and processing data so Its not practical to sit there, grab the sessions information and turn on tracing manually.

CREATE OR REPLACE TRIGGER ON_LOGON_QC
AFTER LOGON ON DATABASE
WHEN ( USER = 'QC' )
BEGIN
execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;

Of course, don't forget to disable it after!