How to check whether user has datapump privilege

SQL>SET lines 100 
COL privilege FOR a50
SELECT grantee, granted_role, default_role
FROM dba_role_privs
WHERE granted_role IN ('DBA', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE')
ORDER BY 1,2;
GRANTEE                        GRANTED_ROLE                   DEF
------------------------------ ------------------------------ ---
ARJU DBA YES
DBA EXP_FULL_DATABASE YES
DBA IMP_FULL_DATABASE YES
FARUK DBA YES
MOMIN DBA YES
SYS DBA YES
SYS EXP_FULL_DATABASE YES
SYS IMP_FULL_DATABASE YES
SYSMAN DBA YES
SYSTEM DBA YES
TEST EXP_FULL_DATABASE YES

 Check for granted privileges on directory objects

SET lines 110
COL privilege FOR a12
COL grantee FOR a25
COL owner FOR a25
SELECT p.grantee, p.privilege, p.owner, d.directory_name
FROM dba_tab_privs p, dba_directories d
WHERE p.table_name=d.directory_name
AND (grantee IN ('TEST', 'PUBLIC')
OR grantee IN (SELECT granted_role FROM dba_role_privs
WHERE grantee IN ('TEST', 'PUBLIC')))
ORDER BY 4,3,2;
A typical output is 
GRANTEE PRIVILEGE OWNER DIRECTORY_NAME
-------------------- ---------- -------------------- -----------------
TEST READ SYS D
TEST WRITE SYS D
EXP_FULL_DATABASE READ SYS DATA_PUMP_DIR
EXP_FULL_DATABASE WRITE SYS DATA_PUMP_DIR

Directory Name : TEST 

Check for tablespace quota
 
SET lines 100 numwidth 12
SELECT q.username, q.tablespace_name, q.bytes, q.max_bytes
FROM dba_ts_quotas q, dba_users u
WHERE q.username=u.username AND q.username in ('TEST');
USERNAME                       TABLESPACE_NAME                       BYTES    MAX_BYTES
------------------------------ ------------------------------ ------------ ------------
TEST SYSTEM 65536 10485760


Article About Data Recovery Method

1-  If you lost all data files

SQL> startup mount;
RMAN> restore database;
RMAN> recover database;
SQL> alter database open;

2- If you lost a tablespace

SQL> alter tablespace users offline;
RMAN> restore tablespace users;
RMAN> recover tablespace users;
SQL> alter tablespace users online;

 if you can not offline tablespace;

$ sqlplus “/ as sysdba”
SQL> shutdown abort;
SQL> startup mount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> restore tablespace users;
RMAN> recover tablespace users;
SQL> alter database open;

3- if you lost a datafile

SQL> alter database datafile '/oracle/oradata/users.dbf' offline;
RMAN> restore datafile '/oracle/oradata/users.dbf'
RMAN> recover datafile '/oracle/oradata/users.dbf'
SQL> alter database datafile '/oracle/oradata/users.dbf' online;
 

if you cannot offline datafile;

$ sqlplus “/ as sysdba”
SQL> shutdown abort;
SQL> startup mount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> restore datafile '/oracle/oradata/users.dbf';
RMAN> recover datafile '/oracle/oradata/users.dbf';
SQL> alter database open;
 

4-  if you lost your controlfiles

$ sqlplus “/ as sysdba”
SQL> shutdown abort;
SQL> startup nomount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> set dbid = 3970640872;
RMAN> restore controlfile;
SQL> alter database mount;
SQL> alter database open;

you will receive an error ORA-01589 when you open database
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> shutdown abort;
SQL> startup mount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> recover database;
SQL> alter database open resetlogs;
RMAN> reset database;

if you open database with resetlogs, SCN number will be zero. In this situation
all previous backups will be invalid. You must full backup.

5- May be a special situation. You need to incomplete recovery

A. Time-Based incomplete recovery

$ sqlplus "/ as sysdba"
SQL> shutdown abort;
SQL> startup mount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> restore database until time "to_date('06/05/11 12:0:00','DD/MM/YY HH24:MI:SS')";
RMAN> recover database until time "to_date('06/05/11 12:0:00','DD/MM/YY HH24:MI:SS')";
SQL> alter database open resetlogs;
 
 
B. SCN-Based incomplete recovery
$ sqlplus "/ as sysdba"
SQL> shutdown abort;
SQL> startup mount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> restore database until scn 1000;
RMAN> recover database until scn 1000;
SQL> alter database open resetlogs;
C. Archive log sequence based incomplete recovery

$ sqlplus "/ as sysdba"
SQL> shutdown abort;
SQL> startup mount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> restore database until sequence 9923;
RMAN> recover database until sequence 9923;
SQL> alter database open resetlogs;

6-  if you need some archive logs in your backup
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> restore ARCHIVELOG FROM TIME 'SYSDATE-1' UNTIL TIME 'SYSDATE';
 

OR

RMAN> restore ARCHIVELOG FROM TIME "to_date('07/11/05 00:00:01','MM/DD/YY HH24:MI:SS')
UNTIL TIME 'SYSDATE';
 
7- if your data block is corrupted you will receive an error below.

Error:
ORA-01578: ORACLE data block corrupted (file # 8, block # 13)
ORA-01110: data file 8: ‘/oracle/oradata/users.dbf’

for recover data block;

$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN>blockrecover datafile 8 block 13;
 

For Block-Level Media Recovery – Concept & Example (Doc ID 144911.1)

To recover, we can give a specific backup set;

# recovery from backupset
RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19 FROM BACKUPSET;
# recovery from image copy
RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19
FROM DATAFILECOPY;
# recovery from backupset which have "FULL" tag
RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 199
FROM TAG = FULL;

During backup or “Validate Backup” command, RMAN finds corrupted blocks and writes to V$DATABASE_BLOCK_CORRUPTION view. When the RMAN recover the corrupt block then automatically updates this view. List of all the corruption of the past, can be viewed over V$BACKUP_CORRUPTION and V$COPY_CORRUPTION views.  If you run the following command,  RMAN will recover all the corrupted blocks in view V$DATABASE_BLOCK_CORRUPTION.

  RMAN>BLOCKRECOVER CORRUPTION LIST RESTORE UNTIL TIME ‘SYSDATE-10’;

8- if you have a image copy backup and your datafile number 2 has problems then you
can switch datafile number2 to image copy.

RMAN>sql ‘alter database datafile 2 offline’;
RMAN>switch datafile 2 to copy;
RMAN>recover datafile 2;
RMAN>sql ‘alter database datafile 2 online’;

Orginial Article
 Thank You
 Osama Mustafa

ORA-01110 When trying to Open Database

ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 5: ‘ /u01/test/users02.dbf’

This Error Maybe Appear On Different Database Files Because DBF Not Exists .

Solution
1-Restore From Rman if its Not Found .

Or

SQL> select file#,name, status from v$datafile where file#=5;
FILE# NAME STATUS
———- ————————————————- ———–
5 /u01/test/users02.dbf ONLINE

SQL>
SQL> alter database datafile ‘/u01/test/users02.dbf’ offline drop;
Database altered.

SQL> alter database open;
Database altered.
SQL>

Thank you
Osama Mustafa

Repair Database Corrpution Using 11g New Tools

As Database Administrator you Face Some Issue About The Database Corruption  But In 11g You have Some New Tool Called “data recovery advisor” .

With DRA you check the block corruptions and loss of data files and control files. The GUI feature also available from Enterprise manager.

Lets Start Using This Tool :

RMAN> BACKUP VALIDATE

Starting backup at 01-FEB-12

21 FAILED 0 169474 2048000 1046708826
File Name: /dbs1/orcl/system01.dbf
Block Type Blocks Failing Blocks Processed

 To Use This Tool Follow The Below Step :

RMAN> LIST FAILURE

List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
1041 HIGH OPEN 01-FEB-12 Datafile 21: ‘/dbs1/orcl/system.dbf ‘ contains one or more corrupt blocks

RMAN> ADVISE FAILURE;

List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
1041 HIGH OPEN01-FEB-12 Datafile 21:”/dbs1/orcl/system.dbf ‘ contains one or more corrupt blocks

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=319 device type=DISK
analyzing automatic repair options complete

RMAN> Repair failure preview;
RMAN> REPAIR FAILURE;
RMAN> REPAIR FAILURE NO PROMPT;

Thank you
Osama Mustafa 

Steps To Clone Oracle EBS R12

There’s Two major Parts In This Topics :
1- Pre Clone Steps .
2 – Post Clone Steps.

LETS START :

1- Pre Clone Steps : 

On Application Tier : 

1-    Execute Apps environment file
2-    cd $ADMIN_SCRIPTS_HOME
3-    ./adautocfg.sh
4-    Perl adpreclone.pl appsTier

Database Tier : 

1-    Execute Database environment ( under ORACLE_HOME)
2-    cd $ORACLE_HOME/appsutil/scripts/(CONTEXT_NAME)
3-    ./adautocfg.sh
4-    perl adpreclone.pl dbTier

shutdown application and database Copy your virtual nodes on new servers .

Post clone Steps:

Database Server

1-    cd $ORACLE_HOME/appsutil/clone/bin
2-    perl adcfgclone.pl dbTier

Application Server:

1-    cd $COMMON_TOP/clone/bin
2-    perl adcfgclone.pl appsTier

Notes:
$COMMON_TOP: APPLICATION_BASE/apps/apps_st/comn
CONTEXT_NAME: SERVICE_NAME_HOSTNAME

Thank you
Osama mustafa

ORA-00020: maximum number of processes (%s) exceeded

SQL> show parameter processes

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
processes integer 150

SQL> select count(*) from v$process;

COUNT(*)
----------
149

SQL> alter system set processes=300 scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

SQL> show parameter processes

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
processes integer 300

Thank you
Osama mustafa

LEARN HOW OPEN RESETLOGS WITHOUT RECOVERY

Amazing Oracle Trick I found today while i am Browsing OTN , I see to post it here .

What is Open resetlog mode 
 
Online redo logs are re-created . The log sequence is reset to 1.
If the databaseis running in archive log mode, the archived redo logs should then be deleted. Otherwise, chances are, that Oracle will eventually try to create an archived redo log whose filename already exists. 

First i will show its Normal Database this mean not in recovery mode :

sqlplus / as sysdba
Connected to an idle instance.

SQL> ORACLE instance started.

Total System Global Area 838860800 bytes
Fixed Size 2087672 bytes
Variable Size 213910792 bytes
Database Buffers 616562688 bytes
Redo Buffers 6299648 bytes
Database mounted.
Database opened.
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit  

Now Lets Open it :

sqlplus / as sysdba

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 838860800 bytes
Fixed Size 2087672 bytes
Variable Size 213910792 bytes
Database Buffers 616562688 bytes
Redo Buffers 6299648 bytes
Database mounted.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
 
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery


SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL> 
 
 
 

 
 
 

How To Check Oracle Physical Standby is in Sync with the Primary or Not?

On Primary

set pages 1000
set lines 120
column DEST_NAME format a20
column DESTINATION format a35
column ARCHIVER format a10
column TARGET format a15
column status format a10
column error format a15
select DEST_ID,DEST_NAME,DESTINATION,TARGET,STATUS,ERROR from v$archive_dest
where DESTINATION is NOT NULL

At Physical Standby


SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1



SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP



select process,status,sequence# from v$managed_standby

Compare the output of the query its should be equal .

Prevent developers from using TOAD,other tools on production databases

When I was Browsing On internet Today i saw Amazing Article Talking about how to prevent Developers From using Toad and other tools on production Database its Small script (After Logon Trigger ) On database level :

    CONNECT / AS SYSDBA;
    
    CREATE OR REPLACE TRIGGER block_tools_from_prod
      AFTER LOGON ON DATABASE
    DECLARE
      v_prog sys.v_$session.program%TYPE;
    BEGIN
      SELECT program INTO v_prog
        FROM sys.v_$session
      WHERE  audsid = USERENV(‘SESSIONID’)
        AND  audsid != 0  — Don’t Check SYS Connections
        AND  ROWNUM = 1;  — Parallel processes will have the same AUDSID’s
    
      IF UPPER(v_prog) LIKE ‘%TOAD%’ OR UPPER(v_prog) LIKE ‘%T.O.A.D%’ OR — Toad
         UPPER(v_prog) LIKE ‘%SQLNAV%’ OR     — SQL Navigator
         UPPER(v_prog) LIKE ‘%PLSQLDEV%’ OR — PLSQL Developer
         UPPER(v_prog) LIKE ‘%BUSOBJ%’ OR   — Business Objects
         UPPER(v_prog) LIKE ‘%EXCEL%’       — MS-Excel plug-in
      THEN
         RAISE_APPLICATION_ERROR(-20000, ‘Development tools are not allowed here.’);
      END IF;
    END;
    /
    SHOW ERRORS

 Where I saw it :

http://www.psoug.org/snippet/Block_TOAD_and_other_tools_516.htm