oracle – Page 12 – Technology Geek

prerequisites to install oracle Database 11g , 10g

Posted on December 31, 2011December 23, 2018 by Osama Mustafa in Database

1-prerequisites for DB 10g RHEL5
2-prerequisites for DB 11g RHEL5

Thank you
Enjoy
osama mustafa

How to check default temporary tablespace

Posted on December 13, 2011December 23, 2018 by Osama Mustafa in Scripts

COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A30
COLUMN description FORMAT A50
SET LINESIZE 200

SELECT *

FROM database_properties

WHERE property_name like ‘%TABLESPACE’;

short topic but hope it will be useful
Thank you
Osama Mustafa

Backup an entire hard disk using dd command

Posted on December 12, 2011December 23, 2018 by Osama Mustafa in Operating system

The ‘ dd ‘ command is one of the original Unix utilities and should be in everyone’s tool box. It can strip headers, extract parts of binary files and write into the middle of floppy disks; it is used by the Linux kernel Makefiles to make boot images. It can be used to copy and convert magnetic tape formats, convert between ASCII and EBCDIC, swap bytes, and force to upper and lowercase.

# dd –help

full hard disk copy

dd if=/dev/hdx of=/dev/hdy
dd if=/dev/hdx of=/path/to/image
dd if=/dev/hdx | gzip > /path/to/image.gz

Hdx could be hda, hdb etc. In the second example gzip is used to compress the image if it is really just a backup.

Restore Backup of hard disk copy

dd if=/path/to/image of=/dev/hdx

gzip -dc /path/to/image.gz | dd of=/dev/hdx

MBR backup

In order to backup only the first few bytes containing the MBR and the partition table you can use dd as well.

dd if=/dev/hdx of=/path/to/image count=1 bs=512

MBR restore

dd if=/path/to/image of=/dev/hdx

Add “count=1 bs=446” to exclude the partition table from being written to disk. You can manually restore the table.

“All This Information was taken from the other site , just for information to take hard-disk backup , it will be useful to use it with Oracle ”

thank you

Osama mustafa

Upgrade 11.1.0.6 to 11.1.0.7 – APPS DBA

Posted on December 12, 2011December 23, 2018 by Osama Mustafa in Application

On each application tier server node,
shut down all server processes or services.

On the database tier server node,
shut down the database listener in the old Oracle home.

On the database server node,
as the owner of the Oracle 11g file system and database instance, unzip and extract the 11.1.0.7 patch set file for your platform.

Use the runInstaller in the patchset to start the OUI.

Once the OUi starts from the LOV s in the Oracle_home list choose the appropriate one to be upgraded and the location also.

The present oracle_home and its location will appear if the environment is set correctly.

Proceed with the next steps on the OUI screen.

On next Screen it will Say Configuration Assistant has failed.Skip this Step since its not required for Oracle E-Business Suite .

MOST IMPORTANT STEP IN UPGRADE .
After Installation you need to Apply the RDBMS patches7486407 and 7684818

Copy $APPL_TOP/admin/adstats.sql from the administration server node to the database server node.

$ sqlplus / as sysdba
SQL> shutdown normal;
SQL> startup restrict;
SQL> @adstats.sql
SQL> shutdown normal;
SQL> startup;
SQL> exit;

Thank You
Osama mustafa

steps to upgrade 11.1.0.6.0 to 11.1.0.7.0

Posted on December 7, 2011December 23, 2018 by Osama Mustafa in Application

O.S : Linux 64Bit
the steps the same for all Database with different patch .
Database : 11.1.0.6 (R1)

Let start upgrading :

1 – You need To install this patch p6890831_111070_Linux-x86-64.zip.
2- Bring Down Database And Services Down And make sure it .
*Database :
sqlplus / as sysdba
SQL>shutdown immediate ;
$ps -ef | grep pmon “to make sure the database is not running”

Listener :
$lsnrctl status
$lsnrctl stop
$lsnrctl status

3-Just In case Take backup of your database .

4-unzip the patch , and run it under Oracle User
$./runInstaller

Select the ORACLE_HOME to upgrade and then click next, the prerequisites steps will complete last click INSTALL. At the end up the installation it will ask to run root.sh as root user. We need root user password or sudo user privilege to run root.sh script.

5-After Upgrade do the following and its mandatory :
cd $ORACLE_HOME/rdbms/admin
sqlplus / as sysdba
SQL> startup upgrade;
SQL>spool pre_upgrade.log
SQL> @utlu111i.sql

6-cd $ORACLE_HOME/rdbms/admin
sqlplus / as sysdba
SQL> spool upgrade_date.log
SQL> @catupgrd.sql

Take check the validity, version of the installed components

sqlplus / as sysdba
sql>set pages 250
sql> set lines 1000
sql> col comp_name for a40
sql> select comp_name, version, status from dba_registry;

7-SQL>shutdown immediate;

8-Recompiling the invalid objects
cd $ORACLE_HOME/rdbms/admin
sqlplus / as sysdba
SQL>startup;
SQL>spool recompile.log
SQL> @utlrp.sql

Cold Backup Steps for your Database :

Controlfiles, datafiles, redo logs, archive files, tempfiles must be included in the backup. If the files are in different paths, the same has to be backedup.

As Oracle User :

$mkdir /backup_date
$cp /u01/oracle/oradata/orcl/*.* /backup/backup_date
$cd $ORACLE_HOME/dbs
$cp inittest.ora inittest.ora_bkpdate
$cp spfiletest.ora spfiletest.or_bkpdate
$cp orapwtest.ora orapwtest.ora_bkpdate

**Take a backup of home path
$mkdir product_bkp
$cd /u01/oracle/product
$tar czf /backup/oracle_11106.tar.gz 11.1.0

**dbhome, coraenv,oraenv backup
As Root User :
#cd /usr/local/bin
#cp dbhome dhome_bkpdate
#cp coraenv coraenv_bkpdate
#cp oraenv oraenv_bkpdate

revert back to old version :

1. untar the binary backup
2. Backup the current database and then replace them with cold back taken before applying the patch.

As Root User :

cd /usr/local/bin
#cp dbhome_bkpdate dbhome
#cp coraenv_bkpdate coraenv
#cp oraenv_bkpdate oraenv

As Oracle User :
oracle binary restoration
cd /u01/oracle/product
$rm -rf 11.1.0
$mkdir 11.1.0
$cp /backup/oracle_11106.tar.gz 11.1.0
$cd 11.1.0
$gunzip oracle_11106.tar.gz
$tar -xvf oracle_11106.tar

Datafile backup restoration
cd /u01/oracle/oradata/orcl/
$rm *
$cd /backup/backup_date
$cp *.* /App1/oracle/oradata/orcl/
$cd $ORACLE_HOME/dbs
$cp inittest.ora_bkpdate inittest.ora
$cp spfiletest.ora_bkpdate spfiletest.ora
$cp orapwtest.ora_bkpdate orapwtest.ora

sqlplus / as sysdba
SQL>startup;
SQL>select * from v$version;
SQL>select comp_name, version, status from dba_registry;

Osama mustafa

Osamamustafa.blogspot.com

ORACLE Racing Wallpaper: Need a new image on your desktop ?

Posted on December 6, 2011December 23, 2018 by Osama Mustafa in Others

Change Enterprise manager password for 11g

Posted on December 3, 2011December 23, 2018 by Osama Mustafa in Application

For the DB Control Release 11.2 and higher, you need to set the environment variable ORACLE_UNQNAME to the value of the DB_UNIQUE_NAME database parameter.

Steps :

1.Stop the DB Control
On Unix
$ emctl stop dbconsole

On Windows
Open a Command Window and type **> emctl stop dbconsole

2.Check that the DB Control is stopped
On Unix
$ emctl status dbconsole

On Windows
Open a Command Window and type
**>emctl status dbconsole

3.Connect to the database as a user with DBA privilege with SQL*Plus and execute:

SQL> alter user sysman identified by ;

4.Check the new password

SQL> connect sysman/[@database_alias]

5.From the database directory $ORACLE_HOME/bin, execute:
On Unix

$ emctl setpasswd dbconsole
Provide the new SYSMAN password

On Windows

**>: emctl setpasswd dbconsoleProvide the new SYSMAN password

6.Restart the DB Control
On Unix

$ emctl start dbconsole

On Windows

Open a DOS Command Window and type
**>: emctl start dbconsole

Thank you

Osama Mustafa

Change Oracle Enterprise Manager 10g Password

Posted on December 1, 2011December 23, 2018 by Osama Mustafa in Application

How to change the password of the Database User SYSMAN when the database is hosting a DB Control Repository.

1.Stop the DB Control
On Unix
$ emctl stop dbconsole

On Windows
Open a Command Window and type
**> emctl stop dbconsole

2.Check that the DB Control is stopped
On Unix

$ emctl status dbconsole

On Windows
Open a DOS Command Window and type
**> emctl status dbconsole

3.Connect to the database as a user with DBA privilege with SQL*Plus and execute:

SQL> alter user sysman identified by ;

4.Check the new password

SQL> connect sysman/[@database_alias]

5.Go to $ORACLE_HOME/host_sid/sysman/config do the following :

Save the file emoms.properties to emoms.properties.orig
Edit the file emoms.properties

Search for the line beginning with:
oracle.sysman.eml.mntr.emdRepPwd=
Replace the encrypted value by the new password value
Example:
oracle.sysman.eml.mntr.emdRepPwd=rainbow
Search for the line:
oracle.sysman.eml.mntr.emdRepPwdEncrypted=TRUE
Replace TRUE by FALSE

6.Restart the DB Control
On Unix

$ emctl start dbconsole

Open a DOS Command Window and type

**> emctl start dbconsole

7.Check that the password has been encrypted

Edit the file $ORACLE_HOME/host_sid/sysman/config/emoms.properties

Search for the line beginning with:
oracle.sysman.eml.mntr.emdRepPwd=
Check that the password is encrypted
Search for the line beginning with:
oracle.sysman.eml.mntr.emdRepPwdEncrypted=
Check that the value is TRUE

I Publish the same lesson but on 11g .

Thank you
Osama mustafa

Oracle Statistics Tables.

Posted on November 10, 2011December 23, 2018 by Osama Mustafa in Database

Hi ,

I am going to talk about Some Oracle Statistics Table that will be useful for tuning your database , and gathering information about your database , these table are very useful

For example Dynamic Performance View :
**The below query will gives you the most sql statement taking more time in the CPU .
1-Select Sql_text , executions from v$sqlstats where cpu_time > 1000 ;
**Below Query Gives you some Details about Specific Machine
2-Select * from v$session where machine = ‘OSAMA_PC’ and where
logon_time > sysdate -1 ;
**finally , Lock in your database
3- select sid,ctime from v$lock where block > 0 ;

Statistics Tables are :
**Display System Wide Statistics :
V$Sysstat
V$Statname
V$Sesstat

**Display System Wide Statistics :

V$Sgastat
V$Event_name
V$system_event

**Display Session-Related Statistics
V$statname
V$Session
V$Session _Event
V$Session_Wait

**Display Service-Related Statistics
V$Service_stats
V$Services
V$Service_event
V$Service_wait_Class

**Display Information About Wait Class :

V$Session_Wait_Class
V$System_Wait_Class
V$Service_Wait_Class

**Display Session Wait

V$Session_Event : Session Wait By event for each session had to wait .
V$Session_Wait : Session Wait By Event For current active session that are waiting .
V$System_Event : Total waits for an event (All Session Together).

Wait Event : Information About session that had to wait or must wait for Different Reasons (V$Event_name).

Some Other Useful Tables :
-V$SQL
-V$SQLAREA

Thank You
Osama Mustafa

TKPROF And Oracle Trace Analysis

Posted on November 10, 2011December 23, 2018 by Osama Mustafa in Database

What is the TKPROF ??!!

The TKPROF program :

converts Oracle trace files into a more readable form. If you have a problem query you can user TKPROF to get more information.

But first you need to enable this tools since its unactivated in oracle to start using it follow the below steps :

1-ALTER SYSTEM SET TIMED_STATISTICS = TRUE;

2-we need table called PLAN_TABLE if its dosen’t found create it by following steps :

@ORACLE_HOME\rdbms\admin\utlxplan.sql
CREATE PUBLIC SYNONYM PLAN_TABLE FOR SYS.PLAN_TABLE;
GRANT SELECT, INSERT, UPDATE, DELETE ON SYS.PLAN_TABLE TO PUBLIC;

3-after doing the Previous steps , now you can use the amazing tools for example :

ALTER SESSION SET SQL_TRACE = TRUE;

SELECT COUNT(*)
FROM   dual;

ALTER SESSION SET SQL_TRACE = FALSE;

OR Another Way to trace file :

TKPROF

explain=user/password@service table=sys.plan_table

Output will be like :

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing 
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

 SELECT COUNT(*)
 FROM   dual

call    count    cpu elapsed    disk   query current     rows
------- -----  ----- ------- ------- ------- -------  -------
Parse       1   0.02    0.02       0       0       0        0
Execute     1   0.00    0.00       0       0       0        0
Fetch       2   0.00    0.00       0       1       4        1
------- -----  ----- ------- ------- ------- -------  -------
total       4   0.02    0.02       0       1       4        1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 121  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE 
      1   TABLE ACCESS FULL DUAL

Thank you

Osama mustafa