Tag: Database

This section related to Oracle database different version

ORA-12557: TNS:protocol adapter not loadable

Posted on by Osama Mustafa in Database
this problem On windows Platforms .

and it must be related to Windows Environment or Oracle Home PATH because sqlplus command works smoothly when I execute it inside ORACLE_HOME\bin.

RUN: SYSDM.CPL to open Windows System Properties
Click on Advanced Tab > Environment Variables…

Click the Path variable under System Variable, then click  Edit…

change the order between Oracle Client Home and Oracle DB Home:

From: D:\oracle\product\10.2.0\client_1\bin;D:\oracle\product\10.2.0\db_1\bin;

To: D:\oracle\product\10.2.0\db_1\bin;D:\oracle\product\10.2.0\client_1\bin;

in other words, put the Oracle DB Home in front of the other path.

Or i Don’t love this way , Since its not actual solution but its solve problem sometimes :
1-Remove ORACLE_HOME From environment Variable .


2- Restart PC 
Done

Osama mustafa 

drop all tables in a tablespace

Posted on by Osama Mustafa in Scripts
set echo off
prompt
prompt this script is used to generate SQL file for deleting all tables in a tablespace.
prompt
prompt specify tablespace name 1:
define tsname=&1
prompt Add purge clause, y for yes, n for no 2:
define prg=&2
prompt enter output sql file name 3:
define filename=&3
set heading off
set verify off
set feedback off
start del_ts_tb.sql
set verify on
set heading on
set echo on
set feedback on
spool &filename
select 'drop table ' || owner || '.' || table_name || decode(upper('&prg'),'Y',' purge','') || ';' DropState
from dba_tables
where tablespace_name = upper('&tsname');
spool off 




Osama mustafa

Gather Schema Statistics fails with Ora-20001

Posted on by Osama Mustafa in Database
Cause: FDPSTP failed due to ORA-06550: line 1, column 7:
PLS-00307: too many declarations of ‘ GATHER_ALL_COLUMN_STATS’ match this call
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored.

Error #1: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_LINE_TYPE_MAP***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
Error #2: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_LOGS***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
Error #3: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_VAT_REP_RULES***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
Solution :
To Check which objects or tables are locked
sql > SELECT OWNER,TABLE_NAME,STATTYPE_LOCKED
FROM DBA_TAB_STATISTICS
WHERE STATTYPE_LOCKED IS NOT NULL;

TO Unlock all the tables in a schema at once :
sql> exec dbms_stats.unlock_schema_stats(‘schema_owner’);
e.g : sql> exec dbms_stats.unlock_schema_stats(‘apps’);
TO Unlock all Individual tables in a schema at once
sql> exec dbms_stats.unlock_schema_stats(‘table_owner’,’table_name’);
e.g : sql > exec dbms_stats.unlock_schema_stats(‘AR’,’AR_REV_REC_QT’); 
There are two reasons for that error message:
1 ) There are duplicate rows on FND_HISTOGRAM_COLS table for JE_BE_LINE_TYPE_MAP table.
Because of this problem, FND_STATS tries to gather histogram information using wrong command and it fails with ora-20001 errors.
Following SQL should have returned one row , not two.
SQL> select a.column_name, nvl(a.hsize,254) hsize
from FND_HISTOGRAM_COLS a
where table_name = ‘JE_BE_LINE_TYPE_MAP’
order by column_name;
COLUMN_NAME HSIZE
—————————— ———-
SOURCE 254
SOURCE 254
2) Column does not exist on the table but still listed in FND_HISTOGRAMS_COL table.
Solution:
Find out all duplicates and/or obsolete rows in FND_HISTOGRAM_COLS and delete one of them.
Remember to take backup of the FND_HISTOGRAM_COLS table before deleting any data.
— identify duplicate rows
select table_name, column_name, count(*)
from FND_HISTOGRAM_COLS
group by table_name, column_name
having count(*) > 1;
— Use above results on the following SQL to delete duplicates
delete from FND_HISTOGRAM_COLS
where table_name = ‘&TABLE_NAME’
and column_name = ‘&COLUMN_NAME’
and rownum=1;
— Use following SQL to delete obsoleted rows
delete from FND_HISTOGRAM_COLS
where (table_name, column_name) in
(
select hc.table_name, hc.column_name
from FND_HISTOGRAM_COLS hc , dba_tab_columns tc
where hc.table_name =’&TABLE_NAME’
and hc.table_name= tc.table_name (+)
and hc.column_name = tc.column_name (+)
and tc.column_name is null
);

DBCA hangs at 85%

Posted on by Osama Mustafa in Database
cause:

kernel BUG at kernel/exit.c:904!
kernel/exit.c:904!

solution:

Upgraded the Kernel to version RHEL4.7

Refer to Metalink Note : 729543.1
Summary by kernel version number:

Bug Introduced in RHEL 4.6:

Broken in RHEL kernel : 2.6.9-67.0.20.EL
Fixed in  RHEL kernel : 2.6.9-67.0.22.EL 

Broken in OEL kernel : 2.6.9-67.0.20.0.1.EL
Fixed in  OEL kernel : 2.6.9-67.0.20.0.2.EL
Fixed in  OEL kernel : 2.6.9-67.0.22.0.1.EL


4.7:
 

Broken in RHEL kernel: 2.6.9-78.EL
Fixed in RHEL kernel : 2.6.9-78.0.1.EL

Broken in OEL kernel : N/A -  OEL 4.7 base (GA) kernel includes the fix for 
                                 this crash (2.6.9-78.0.0.0.1.EL)
Fixed in OEL kernel  : 2.6.9-78.0.0.0.1.EL
Fixed in OEL kernel  : 2.6.9-78.0.1.0.1.EL
 

How to check whether user has datapump privilege

Posted on by Osama Mustafa in Scripts 
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

Posted on by Osama Mustafa in Articles, Database
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

Posted on by Osama Mustafa in 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

Posted on by Osama Mustafa in Database
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

Posted on by Osama Mustafa in Application, Articles
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

Posted on by Osama Mustafa in Database 
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