Tag: Osama

OBE-15409: Error in loading constraint information

Posted on by Osama Mustafa in Database

This Error Starts Appear After Migration from 9i to 10gR2 , and using forms/reports 6i I read lot about this error but nothing been useful for me  ,  I Found Some Document On metalink talk about this error , you check this error on metalink under BUG Database , the Bug Number is 309292121.
Problem description:
——————–
QUERY BUILDER is not showing the table relation between master and detailed
tables when conneted to 8.1.7 OR 9.2.0.3 database. With 9.2.0.1 data base
there is no problem.
Testcase step-by-step instructions:
———————————–
1) In the report builder, open the query builder.
3) Select the table EMP from the list and press the include button.
4) Select the table DEPT from the list and press the include button.
After I follow the above steps and rebuild the constraint the same error appear.
So i post this article maybe it will be useful.
After did some research i found that the error appears Because 10gR2 its not certified with forms/reports 6i. 
so you could try the below solution :
1-Install Oracle 10gR1.
2-Upgrade forms/reports 6i to 10g.
Thank you
Osama Mustafa

Clone Oracle Database Steps

Posted on by Osama Mustafa in Database
First :  What is Oracle Database Clone :

complete and separate copy of a database system that includes the business data, the DBMS software and any other application tiers that make up the environment. Cloning is a different kind of operation to replication and backups in that the cloned environment is both fully functional and separate in its own right. Additionally the cloned environment may be modified at its inception due to configuration changes or data subsetting.

Second : Benefit Of Clone : 

1-useful for the DBA who wants to give his developers a full-sized TEST and DEV instance by cloning the PROD instance into the development server areas. 
2-quickly migrate a system from one server to another .
3-fastest way to copy a Oracle database .

How We do it :


Step-1 : On the old system, go into SQL*Plus, sign on as SYSDBA and issue: 

SQL>alter database backup controlfile to trace;

copy and paste the below lines from your user trace file and save it as dbclone_controlfile_creation.sql in any location in your system .


STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “OLDLSQ” NORESETLOGS
NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 240
MAXINSTANCES 1
MAXLOGHISTORY 113
LOGFILE
GROUP 1 (‘/u01/oradata/oldlsq/log1a.dbf’,
‘/u01/oradata/olslsq/log1b.dbf’) SIZE 30M,
GROUP 2 (‘/u01/oradata/oldlsq/log2a.dbf’,
‘/u01/oradata/oldlsq/log2b.dbf’) SIZE 30M
DATAFILE
‘/u01/oradata/oldlsq/system01.dbf’,
‘/u01/oradata/oldlsq/mydatabase.dbf’
;
# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;

 

 Step-2 : Shutdown Old Database

SQL>Shutdown immediate ;

Step-3 : Copy and Edit the Control file – Using the output syntax from STEP 1, modify the controlfile creation script by changing the following: 


CREATE CONTROLFILE REUSE DATABASE “OLDLSQ” NORESETLOGS

CREATE CONTROLFILE SET DATABASE “NEWLSQ” RESETLOGS

 Step-4 :Remove the “recover database” and “alter database open” syntax 


Step-5:Re-names of the data files location to new clone location in a dbclone_controlfile_creation.sql.

Old:

DATAFILE
‘/u01/oradata/oldlsq/system01.dbf’,
‘/u01/oradata/oldlsq/mydatabase.dbf’

New:

DATAFILE
‘/u01/oradata/newlsq/system01.dbf’,
‘/u01/oradata/newlsq/mydatabase.dbf’

Step-6: Copy all data files into the new directories on the new server. You may change the file names if you want, but you must edit the controlfile to reflect the new data files names on the new server.

Step-7 : Create the bdump, udump and cdump directories
  

 cd /u01/admin/
mkdir newlsq
cd newlsq
mkdir bdump
mkdir udump
mkdir cdump
mkdir pfile

Step-8 : Copy-over the old init.ora file 


open the primary database pfile in a notepad or text editor and change the udump,bdump,pfile,controlfile destination and save it in new clone pfile location newlsq folder and save as newlsq.ora 
eg: primary database location /u01/admin/oldlsq/pfile/oldlsq.ora
open that oldlsq.ora file in a text editor or notepad and change the required destinations cdump,udump,bdump,controlfile destinations and save as newlsq.ora in the below destination
/u01/admin/newlsq/pfile/newlsq.ora

Step-9 : Start the new database

SQL>startup nomount pfile=’ /u01/admin/newlsq/pfile/newlsq.ora ‘ ;
SQL> @dbclone_controlfile_creation.sql
SQL>alter database open resetlogs;

SQL>create spfile from pfile;

 Step-10 : Place the new database in archivelog mode.




Thank you 
osama mustafa

delete a duplicate rows in a oracle table

Posted on by Osama Mustafa in Scripts 
 
Check the below Steps to delete duplicate row : 
 
 

SQL>select * from emp;


 EMPID ENAME                       SAL     DEPTNO
---------- -------------------- ---------- ----------
 10005 palani                             54544         10
 10001 Scott Tiger                     1000           40
 10002 Frank Naude                 500             20
 10003 rajesh                            21000         140
 10004 ramesh                           3500          220
 10005 suresh                            34567        123
 
 

delete duplicate rows in empid column in emp table 

 
 

 SQL>delete from emp where rowid not in (select max(rowid) from emp group by empid);


1 row deleted.

SQL>select * from emp;


 EMPID ENAME                       SAL     DEPTNO
---------- -------------------- ---------- ----------
 10001 Scott Tiger                       1000         40
 10002 Frank Naude                   500           20
 10003 rajesh                             21000        140
 10004 ramesh                           3500           220
 10005 suresh                            34567        123
 
 
to delete the old duplicate row from the table instead of max(rowid) replace min(rowid) 
for exampl
 

SQL>insert into emp values(10005,’Osama’,54544,10);


1 row created.

SQL>select * from emp;


   EMPID ENAME                       SAL     DEPTNO
---------- -------------------- ---------- ----------
   10005 Osama                           54544        10
   10001 Scott Tiger                     1000          40
   10002 Frank Naude                 500            20
   10003 rajesh                            21000        140
   10004 ramesh                          3500          220
   10005 suresh                           34567        123

6 rows selected.

SQL>delete from emp where rowid not in (select min(rowid) from emp group by empid);


1 row deleted.
 

SQL>select * from emp;


   EMPID ENAME                       SAL     DEPTNO
---------- -------------------- ---------- ----------
   10005 Osama                            54544      10
   10001 Scott Tiger                      1000        40
   10002 Frank Naude                  500          20
   10003 rajesh                             21000      140
   10004 ramesh                           3500        220 
 
 
thank you 
Osama mustafa

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
 

Upgrade Steps From 10g to 11gR2

Posted on by Osama Mustafa in Articles, Database
Please Follow the Below Steps For Upgrading 10g to 11gR2 , Before Doing Anything you need to take backup for the following Files :
1.      BACKUP BINARIES, DB,TNSNAMES,LISTENER,INIT,SPFILE,PWFILE THAT ARE BEING UPGRADED.
2.      COMPATIBLE PARAMETER MUST BE SET TO MINIMUM 10.0.0.0.  THE RECOMMENDED IS 11.2.0 FOR 11gR2 PARAMETER FILE.

Step-1 :
 Install  New Oracle Database Software & Apply Any Patches Necessary.

Step-2 :

Run The Pre-Upgrade Information Tools : (Mandatory) 


1.      Copy the Pre-Upgrade Information Tool (utlul12i.sql) from the Oracle Database 11gR2 directory: $ORACLE_HOME/rdbms/admin to a temporary directory /tmp.
2.      Set your environment to the one that is being upgraded.  Assuming 10g.
3.      Change directory to /tmp that you copied utlu112i.sql to in Step 1.
4.      Start SQL*Plus and login as ‘/ as sysdba’
5.      Spool the results to a log file:
o   SQL> SPOOL upgrade_info.log
6.      Run the Pre-Upgrade Information Tool:
o   SQL> @utlul12i.sql
o   SQL> SPOOL OFF 

Open The Spool File for Checking Purpose :
 Oracle recommends gathering stats before the upgrade:  EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
■ Deprecated CONNECT Role
■ Access Control to Network Utility Packages
■ Database Links with passwords
■ TIMESTAMP WITH TIME ZONE Data Type
■ Optimizer Statistics
■ Invalid Objects
■ Save Oracle Enterprise Manager Database Control Data
■ Complete Materialized View Refreshes
■ Ensure No Files Need Media Recovery
■ Ensure No Files Are in Backup Mode
■ Resolve Outstanding Distributed Transactions
■ Sync Standby Database with the Primary Database
■ Purging the Database Recycle Bin

Step-3 : 
Prepare 11gR2 Home :

1.      Copy configuration files (init file, tnsnames, listener, pwfile) from old Oracle Home to new.
2.      Update init files with new COMPATIBLE parameter, fix any deprecated ones, and adjust the values to at least the minimum values indicated by the Pre-Upgrade Tool.
3.      Update any relative path names in parameter file to fully path names.

Step-4 : 
After all the Above Steps , you will start the upgrade Database for 10g Home :

1.      Shutdown the database:
o   SQL> SHUTDOWN IMMEDIATE;
2.      Make sure the following checks:
o   The oratab file points to Oracle Database 11g Release 2 Oracle Home
o   The following environment variables point to the Oracle 11g Release 2 directories:
§  ORACLE_HOME
§  PATH
3.      Change to the $ORACLE_HOME/rdbms/admin directory and start SQL*Plus
o   sqlplus ‘/ as sysdba’
4.      Start the instance by issuing following command (you may get messages that parameters are obsolete, fix those and start up the db again):
o   SQL>  STARTUP UPGRADE;
o   SQL>  SPOOL upgrade.log
5.      Run the catupgrd.sql script:
o   SQL>  @catupgrd.sql
6.      Once completed, shutdown the database and restart it.
o   SQL>  SHUTDOWN IMMEDIATE;
o   SQL>  STARTUP;
7.      Run the Post-Upgrade Status Tool to provide a summary of the upgrade. (If there are any INVALID components, then check upgrade manual for fixes.)
o   SQL> @utlul12s.sql
8.      Run catuppst.sql to perform upgrade actions that do not require db to be in upgrade mode:
o   SQL> $ORACLE_HOME/rdbms/admin/catuppst.sql
9.      Run utlrp to recompile any remaining stored PL/SQL and other objects.
o   SQL> $ORACLE_HOME/rdbms/admin/utlrp.sql
10.  Verify that all objects are valid:
o   SQL> SELECT count(*) FROM dba_invalid_objects;
o   SQL> SELECT distinct object_name FROM dba_invalid_objects;
Thank you 
Osama mustafa

 


ORA-12516: TNS:listener could not find available handler

Posted on by Osama Mustafa in Database
after the increasing the Number processes in the Oracle database, it solved the problem.

But Before this , i am gonna show you some steps to check the process via OS and Database LETS BEGIN :

  • check if the number of connections reaches the database’s  process parameter using following Unix command: 
ps -ef | grep oracleSID | grep -v grep | wc -l
or
ps aux | grep oracleSID | grep -v grep | wc -l
  •  check if the number of connections reaches the database’s  process parameter using following Database Command :
SQL> show parameter processes

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

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

  COUNT(*)
----------
       149
SQL> show parameter sessions

NAME                TYPE        VALUE
------------------ ----------- -------
Session              integer     150

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

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

Now We need To Increase the Both Parameter By :

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

System altered.
 
SQL> alter system set Session=300 scope=spfile;

System altered.

If this Solution Not Work For you , Try this One :

SQL> alter system set local_listener=
“(ADDRESS=(PROTOCOL=tcp)(HOST=10.122.28.12)(PORT=1521))” sid=’ORCL1′;

System altered.

SQL> show parameter local

NAME                                 TYPE        VALUE
———————————— ———– ——————————
local_listener                       string      (ADDRESS=(PROTOCOL=tcp)(HOST=1
                                                 0.122.28.12)(PORT=1521))

Thank you 

Osama Mustafa

SCHEDULER[0x51B5]:DEFAULT_MAINTENANCE_PLAN via scheduler window

Posted on by Osama Mustafa in Database
Oracle Database Resource Manager (the Resource Manager) : 
enables you to optimize resource allocation among the many concurrent database sessions.

When database resource allocation decisions are left to the operating system, you may encounter the following problems:

  • Excessive overhead.

Excessive overhead results from operating system context switching between Oracle Database server processes when the number of server processes is high.

  • Inefficient scheduling

 The operating system deschedules database servers while they hold latches, which is inefficient.

  • Inappropriate allocation of resources

The operating system distributes resources equally among all active processes and is unable to prioritize one task over another.

  • Inability to manage database-specific resources, such as parallel execution servers and active sessions

Check The Resource Manager On your Database :

SQL> show parameter resource_manager_plan

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_manager_plan                string      SCHEDULER[0x3003]:DEFAULT_MAIN
                                                 TENANCE_PLAN

All this is Introduction about the Error that appear when you upgrade to 11g , you will getting the following messages in the alert.log.

Setting Resource Manager plan SCHEDULER[0x51B5]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Thu Feb 05 22:00:03 2009
Begin automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"
Thu Feb 05 22:00:39 2009
End automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"

Solution to  Disable the resource manager , Steps to Do this :

1-set the current resource manager plan to null

alter system set resource_manager_plan='' scope=both 

2-change the active windows to use the null resource manager plan 

    execute dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW','RESOURCE_PLAN','');
    execute dbms_scheduler.set_attribute('WEEKEND_WINDOW','RESOURCE_PLAN','');

3-run :

SQL> execute dbms_scheduler.set_attribute('','RESOURCE_PLAN','');

You Can Also Disable SQL tuning By :

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/


Thank you
Osama Mustafa

Check Your Database Size

Posted on by Osama Mustafa in Scripts
Datafiles : 

select sum(bytes)/1048576 “DATAFILES_SIZE_MB” from dba_data_files;

Tempfiles:

select sum(bytes)/1048576 “TEMPFILES_SIZE_MB” from dba_temp_files;

Your redo logs can also use up a large amount of disk space – especially if your database has more than the minimum number of 2 redo log groups. (You may also have several members within each group).

Redologs:

select sum(bytes)/1048576 “REDOLOGS_SIZE_MB” from v$log;

The database obviously needs controlfiles to record information such as which datafiles belong to the database.  If your CONTROL_FILE_RECORD_KEEP_TIME is set to a large value, then your controlfiles can become quite large.

Controlfiles:


select round(sum(block_size*file_size_blks)/1048576,2) “CONTROLFILESIZE_MB” from v$controlfile;

From 10g onwards, flashback database is not enabled by default, but if it is, then this area can grow rapidly over time.

Flash Recovery Area:


select * from v$recovery_file_dest; 
select * from v$flash_recovery_area_usage;  

These views will show sizing details and free space available.

Note: If your backups are held outside of the flash recovery area, then you’ll also need to allow space for these. This will depend on your backup strategy and backup retention policy. (Export/datapump export dumpfiles also need to be planned for).
If you are using RMAN incremental backups and have block change tracking enabled, then include this file:

Block change tracking file


select filename, nvl(bytes/1048576,0) “BLOCK_CT_SIZE_MB” from v$block_change_tracking;

Files referenced by database directories or the utl_file_dir parameter:


Your application may read from, or write to external files via database directories or the utl_file_dir parameter.

Other examples of using external directories are for

(a) External tables –

select a.owner||’.’||a.table_name||’ stored in directory ‘||b.directory_path “EXTERNAL_TABLES”
from dba_external_locations a, dba_directories b
where a.directory_owner=b.owner
and a.directory_name=b.directory_name;

(b) If you are storing multiple versions of the same tablespace within a file group repository. (i.e. tablespace versioning).

select a.tablespace_name, a.version, a.file_group_owner, a.file_group_name,
b.file_name, b.file_directory
from dba_file_group_tablespaces a, dba_file_group_files b
where a.file_group_owner=b.file_group_owner
and a.file_group_name=b.file_group_name;

Thank you 
osama Mustafa

Limit Access to your Database

Posted on by Osama Mustafa in Database
Its Simple Easy Way to Limit Access for your Database to Prevent People to miss Around , we all know there’s File Called “sqlnet.ora” All you Have to do is Follow The Below Steps and Add what you want :

Sqlnet.ora : $ORACLE_HOME/network/admin

TCP.EXCLUDED_NODES

Purpose
Use the parameter TCP.EXCLUDED_NODES to specify which clients are denied access to the database.

Example
TCP.EXCLUDED_NODES=(finance.us.acme.com, mktg.us.acme.com, 144.25.5.25)

TCP.INVITED_NODES

Purpose
Use the parameter TCP.INVITED_NODES to specify which clients are allowed access to the database.
 This list takes precedence over the TCP.EXCLUDED_NODES parameter if both lists are present.

Example
TCP.INVITED_NODES=(sales.us.acme.com, hr.us.acme.com, 144.185.5.73)

TCP.VALIDNODE_CHECKING

 Purpose
Use the parameter TCP.VALIDNODE_CHECKING to check for the TCP.INVITED_NODES and TCP.
EXCLUDED_NODES to determine which clients to allow or deny access.

Example
TCP.VALIDNODE_CHECKING=yes
TCP.VALIDNODE_CHECKING=no

Simple Way to keep your database Clean . you maybe need to restart your Listener after this

Thank you
Osama Mustafa

Oracle Real Application Cluster Managment

Posted on by Osama Mustafa in Database
1. CRSCTL: 

Cluster Control utility performs various administrative operation of oracle clusterware. It is located in $ORA_CRS_HOME/bin and must be executed by the “root” user.

a. To check the current state of all oracle clusterware daemon:

[root@rac1 bin]# ./crsctl check crs
CSS appears healthy
CRS appears healthy
EVM appears healthy

b.You can also check the state of individual oracle clusterware daemon:

[root@rac1 bin]# ./crsctl check cssd
CSS appears healthy

[root@rac1 bin]# ./crsctl check crsd
CRS appears healthy

[root@rac1 bin]# ./crsctl check evmd
EVM appears healthy

c. To start oracle clusterware

[root@rac1 bin]# ./crsctl start crs

Attempting to start CRS stack
The CRS stack will be started shortly

d. To stop oracle clusterware

[root@rac1 bin]# ./crsctl stop crs
Stopping resources.
Successfully stopped CRS resources
Stopping CSSD.
Shutting down CSS daemon.
Shutdown request successfully issued.

e. To disable  oracle clusterware:

[root@rac1 bin]# ./crsctl disable crs

f.  To enable  oracle clusterware:

[root@rac1 bin]# ./crsctl enable crs

g. To get current value of CSS parameter

[root@rac1 bin]# ./crsctl get css
for  example: to get value of misscount parameter
 [root@rac1 bin]# ./crsctl get css misscount
60

h. To set a new value of CSS parameter

[root@rac1 bin]# ./crsctl set css
for  example: to set value of misscount parameter

[root@rac1 bin]# ./crsctl set css misscount 120
Configuration parameter misscount is now set to 120.

i. To unset CSS parameter value

[root@rac1 bin]# ./crsctl unset css
for  example: to unset value of misscount parameter

[root@rac1 bin]# ./crsctl unset css misscount
Configuration parameter misscount is now undefined.

j. To list the module for debugging in CSS

[root@rac2 bin]# ./crsctl lsmodules css

The following are the CSS modules ::
CSSD
COMMCRS
COMMNS

2. CRS_STAT:
 It reports the current state of resources configured in the OCR.

[oracle@rac1 bin]$ ./crs_stat -t
Name                      Type              Target       State         Host
———————————————————————————–
ora….C1.inst             application    ONLINE    ONLINE          rac1
ora….C2.inst             application    ONLINE    ONLINE          rac2
ora….AC1.srv            application    ONLINE    ONLINE          rac1
ora.RAC.abc.cs          application    ONLINE    ONLINE          rac1
ora.RAC.db                application    ONLINE    ONLINE         rac2
ora….AC1.srv            application    ONLINE    ONLINE          rac1
ora….ice2.cs             application    ONLINE    ONLINE          rac1
ora….AC1.srv            application    ONLINE    ONLINE          rac1

3. CRS_STOP:

 This command used to stop resource or cluster member.

[root@rac1 bin]# ./crs_stop ora.rac1.ons
Attempting to stop `ora.rac1.ons` on member `rac1`
Stop of `ora.rac1.ons` on member `rac1` succeeded.

4.  CRS_START:

 This command used to start resource or cluster member.

[root@rac1 bin]# ./crs_start ora.rac1.ons
Attempting to start `ora.rac1.ons` on member `rac1`
Start of `ora.rac1.ons` on member `rac1` succeeded.

5.  OCRDUMP :

 It dumps the contents of OCR into a text file.

 [root@rac1 bin]# ./ocrdump /home/oracle/ocr.dmp

6. OCRCHECK :

 It verifies the integrity of the OCR.

[root@rac2 bin]# ./ocrcheck

Status of Oracle Cluster Registry is as follows :
Version                                :    2
Total space (kbytes)         :    5237072
Used space (kbytes)         :    9360
Available space (kbytes)  :    5227712
ID                                        :    794527192
Device/File Name             :    /apps/oracle/oradata/ocr
Device/File integrity check succeeded
Cluster registry integrity check succeeded

7. OCRCONFIG:

 It perform various administrative operation on the OCR.