Tag: steps

Shell Script For Cold Backup

Posted on by Osama Mustafa in Scripts
This shell script selects the datafiles, logfiles and control files,
 tars and gzips them and then sends them to a remote host via rsh.

Download Scripts : Cold_backup.sh

I have to upload the script since its contain codes can’t be appeared on Blog . 

<eofsql 0="" 120="" <eofsql eofsql

Enjoy
osama mustafa

Rename/Move Oracle Files / Control FIle Part 1

Posted on by Osama Mustafa in Database
In this Topics i will post how to move control file to another detestation , i will post other ORACLE FILES :

SQL> select name from v$controlfile;

NAME
-------------------------------------------------------------
C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\CONTROL01.CTL
C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\CONTROL02.CTL
C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\CONTROL03.CTL

3 rows selected.

SQL>
 
OR
 
SQL> show parameter control_files
 
NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
control_files                        string                           C:\ORACLE\ORADATA\DB10G\CONTRO
                                                                      L01.CTL, C:\ORACLE\ORADATA\ORCL
                                                                      \CONTROL02.CTL, C:\ORACLE\OR
                                                                      ADATA\ORCL\CONTROL03.CTL
SQL>

To move or rename a controlfile do the following.

  • Alter the control_files parameter using the ALTER SYSTEM comamnd.
  • Shutdown the database.
  • Rename the physical file on the OS.
  • Start the database.
SQL> ALTER SYSTEM SET control_files='C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\RENAME_CONTROL01.CTL', -
> 'C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\CONTROL02.CTL', -
> 'C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\CONTROL03.CTL' SCOPE=SPFILE;
 
 
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
 
RE-NAME CONTROL FILE TO THE SAME WE DID ON ALTER STATMENT .
 
SQL> STARTUP
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                   787968 bytes
Variable Size              61864448 bytes
Database Buffers          104857600 bytes
Redo Buffers                 262144 bytes
Database mounted.
SQL>
 
SQL> select name from v$controlfile;

NAME
-------------------------------------------------------------
C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\RENAME_CONTROL01.CTL
C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\CONTROL02.CTL
C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\CONTROL03.CTL

3 rows selected.

SQL> 

the same steps for move control file .
W

We will continue 
 
Enjoy 

osama mustafa 
 
 

Dealing With Oracle Jobs/DBMS_JOB

Posted on by Osama Mustafa in Database
scheduled_dbms_jobs.sql

set linesize 250
col log_user for a10
col job for 9999999 head ‘Job’
col broken for a1 head ‘B’
col failures for 99 head “fail”
col last_date for a18 head ‘Last|Date’
col this_date for a18 head ‘This|Date’
col next_date for a18 head ‘Next|Date’
col interval for 9999.000 head ‘Run|Interval’
col what for a60

select j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||’:’||j.last_sec last_date,
j.this_date||’:’||j.this_sec this_date,
j.next_date||’:’||j.next_sec next_date,
j.next_date – j.last_date interval,
j.what
from (select dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj) j;

running_jobs.sql

set linesize 250
col sid for 9999 head ‘Session|ID’
col log_user for a10
col job for 9999999 head ‘Job’
col broken for a1 head ‘B’
col failures for 99 head “fail”
col last_date for a18 head ‘Last|Date’
col this_date for a18 head ‘This|Date’
col next_date for a18 head ‘Next|Date’
col interval for 9999.000 head ‘Run|Interval’
col what for a60
select j.sid,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||’:’||j.last_sec last_date,
j.this_date||’:’||j.this_sec this_date,
j.next_date||’:’||j.next_sec next_date,
j.next_date – j.last_date interval,
j.what
from (select djr.SID,
dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j;

session_jobs.sql

set linesize 250
col sid for 9999 head ‘Session|ID’
col spid head ‘O/S|Process|ID’
col serial# for 9999999 head ‘Session|Serial#’
col log_user for a10
col job for 9999999 head ‘Job’
col broken for a1 head ‘B’
col failures for 99 head “fail”
col last_date for a18 head ‘Last|Date’
col this_date for a18 head ‘This|Date’
col next_date for a18 head ‘Next|Date’
col interval for 9999.000 head ‘Run|Interval’
col what for a60
select j.sid,
s.spid,
s.serial#,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||’:’||j.last_sec last_date,
j.this_date||’:’||j.this_sec this_date,
j.next_date||’:’||j.next_sec next_date,
j.next_date – j.last_date interval,
j.what
from (select djr.SID,
dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j,
(select p.spid, s.sid, s.serial#
from v$process p, v$session s
where p.addr = s.paddr ) s
where j.sid = s.sid;

Bringing Down a DBMS_JOB

1. Find the Job You Want to Bring Down
2. Mark the DBMS_JOB as Broken

SQL> EXEC DBMS_JOB.BROKEN(job#,TRUE);

All this command does is mark the job so that if we get it to stop, it won’t start again. Let’s make one thing perfectly clear, after executing this command the job is still running.

3. Kill the Oracle Session

ALTER SYSTEM KILL SESSION ‘sid,serial#’;

 4. Kill the O/S Process

For Windows, at the DOS Prompt: orakill sid spid

For UNIX at the command line> kill ‘9 spid

5. Check if the Job is Still Running

If No , Then you are Done , But if Job Still Running Go To Step 6.

6. Determine the Current Number of Job Queue Processes

SQL> col value for a10
SQL> select name,value from v$parameter where name = ‘job_queue_processes’; 

7. Alter the Job Queue to Zero

SQL> ALTER SYSTEM SET job_queue_processes = 0;

 8. Validate that No Processes are Using the Job Queue
9. Mark the DBMS_JOB as Not Broken

SQL>EXEC DBMS_JOB.BROKEN(job#,FALSE):

10. Alter the Job Queue to Original Value

ALTER SYSTEM SET job_queue_processes = original_value;

11. Validate that DBMS_JOB Is Running

Enjoy

Osama mustafa

Documents For Upgrade Oracle 10g to 11gR2

Posted on by Osama Mustafa in Articles, Database
I Upload Documents for Upgrading Oracle 10g to 11gR2

The Following Documents like the followings :


1- Upgrade Oracle 10g Clusterware to 11gR2 Clusterware.

 ClusterWare Upgrade

2- Upgrade ASM 10g to 11gR2 .

ASM Upgrade

3- Upgrade Database 10g to 11gR2 .

Database Upgrade

Enjoy

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

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

DBCONSOLE For Real Application Cluster

Posted on by Osama Mustafa in Database
Drop DBCONSOLE FOR RAC (Uninstall) : 



we will user the following command :

emca -deconfig dbcontrol db -repos drop -cluster

Make sure you have the below information :

  • Database unique name
  • Listener port number
  • Password for SYS user
  • Password for SYSMAN user

Create DBCONSOLE FOR RAC ( Install) : 

In this case you have more than one choice you can use what you want depend on your requirements :

1-

emca -config dbcontrol db -repos create -cluster

2-

 emca -reconfig dbcontrol –cluster –EM_NODE node1 -EM_NODE_LIST node2,node3,node4

emca -reconfig dbcontrol –cluster –EM_NODE node5 -EM_NODE_LIST node6,node7,node8

 The below Information for installing DBCONSOLE :

  • The Database unique name
  • The Listener port number
  • The Cluster name
  • The Password for SYS user
  • The Password for DBSNMP user
  • The Password for SYSMAN user
  • The Email address for notifications (optional)
  • The Outgoing Mail (SMTP) server for notifications (optional)
  • The ASM ORACLE_HOME e.g. /u01/app/asm/product/10.2.0
  • The ASM port e.g. 1521
  • The ASM user role e.g. SYSDBA
  • The ASM username e.g. SYS
  • The ASM user password

Note : Sometimes you need to put the password between “” . 
Thank you
Osama mustafa

How to change SGA in Oracle Rac

Posted on by Osama Mustafa in Database
This Article for newbe Oracle DBA Since If you trying to change sga on rac with wrong way , spfile maybe be corrupted . 

Node One : ORCL1
Node Two : ORCL2

Note : You Need To Check Memory Parameter On Database , if its Have Available Size .

  • Connect To Node One :

alter system set sga_max_size=16g scope=spfile sid = 'ORCL1';
alter system set sga_target=12g scope=spfile sid = 'ORCL1';
alter system set sga_max_size=16g scope=spfile sid = 'ORCL2';
alter system set sga_target=12g scope=spfile sid = 'ORCL2';

Or In Another Way :
sql>alter system set sga_target=12G scope=spfile sid=’*’;
sql>alter system set sga_max_size=16G scope=spfile sid=’*’;
sql>alter system set sga_max_size=16G scope=spfile ;
sql>alter system set sga_target=12G scope=spfile;

 PGA :

sql>alter system set pga_aggregate_target=4G scope =spfile sid=’*’;
sql>alter system set pga_aggregate_target=4G scope=both;

  • shutdown database PROD (two instances should be shut down)

 $>srvctl stop database -d PROD

$>srvctl START database -d PROD

Check The New Size For Both Instance By :

Show parameter sga ;

Thank you
Osama mustafa 

Oracle Rac 10g Add Node Steps

Posted on by Osama Mustafa in Database
This Article Discuss Adding/Remove Node In Oracle Real application Cluster 10g The below Steps easy all you have to do is follow up with them Let’s Start :

ADD NODE : 

1-Check Pre-requisities On All Node , This Include Old Node And New Node .

cluvfy  stage -pre crsinst -n -r 10gR2

 2-From the First Node , Execute the following Command :

/oui/bin/addNode.sh

The Final Result For Step 2 .

3-We need to configure new ONS (From The First Node) :

cd cluster_home/bin
cat cluster_home/opmn/conf/ons.config (to get the remoteport)
./racgons add_config Node-name:6200 (remoteport)

4-Optional : needed Only If Specific Home Directory to host ASM (From the First Node)

cluster_home/bin/addnode.sh

5- From The First node we need to Add RAC

cd cluster_home/bin

./addnode.sh

6-Add Listener , Do This Steps From The New Node On Terminal

netca –> Cluster Configuration —>Name of New Node

7-Final Step , Add Database Instance To new node ( from the First Node ) Using

DBCA —> Oracle Real Application Cluster —> Instance Management —> Add Instance

 

\

You Need Follow Up with Screen as Normal Installation Of Database
–List of cluster databases.select your RAC database and enter SYS credentials 
– List of cluster database instances , next
–Instance naming and node selection . Instance Name
Note : 
 
if you are using ASM for your database storage,the DBCA detects the need for an ASM instance creation on the new nodeThis must be done before the DBCA can create the database instance on that node. Click Yes.

Thank You
Osama Mustafa

I will Talk Later How To Remove Node From Oracle Real Application Cluster .

Oracle Real Application Cluster Lesson # 1

Posted on by Osama Mustafa in Database
Sometimes we need Solutions to keep our database Available all the time, There are lot of solutions one of these solutions called  Oracle Real Application Cluster (RAC)/High Availability

As Lesson Number One i will take on Oracle Real Application Cluster Basics .

Lets Start :

Oracle RAC allows multiple computers to run Oracle RDBMS software simultaneously while accessing a single database, thus providing a clustered database.
In a non-RAC Oracle database, a single instance accesses a single database. The database consists of a collection of data files, control files, and redo logs located on disk. The instance comprises the collection of Oracle-related memory and operating system processes that run on a computer system.
In an Oracle RAC environment, two or more computers (each with an instance) concurrently access a single database. This allows an application or user to connect to either computer and have access to a single coordinated set of data.
Assume the  installation of Oracle 10g release 2 (10.2) RAC on Red Hat Enterprise Linux 4.

Hardware
At the hardware level, each node in a RAC cluster shares three things:

  1. Access to shared disk storage
  2. Connection to a private network
  3. Access to a public network.

 

Shared Disk Storage
Oracle RAC relies on a shared disk architecture. The database files, online redo logs, and control files for the database must be accessible to each node in the cluster. The shared disks also store the Oracle Cluster Registry and Voting Disk (discussed later). There are a variety of ways to configure shared storage including direct attached disks (typically SCSI over copper or fiber), Storage Area Networks (SAN), and Network Attached Storage (NAS).

Supported Shared Storage In RAC :

1-Oracle Cluster File System (OCFS) is a shared file system designed specifically for Oracle Real Application Cluster OCFS eliminates the requirement that Oracle database files be linked to logical drives and enables all nodes to share a single Oracle Home
2-ASM
3-RAW Device

Private Network
Each cluster node is connected to all other nodes via a private high-speed network, also known as the cluster interconnect or high-speed interconnect (HSI). This network is used by Oracle’s Cache Fusion technology to effectively combine the physical memory (RAM) in each host into a single cache. Oracle Cache Fusion allows data stored in the cache of one Oracle instance to be accessed by any other instance by transferring it across the private network. It also preserves data integrity and cache coherency by transmitting locking and other synchronization information across cluster nodes.
The private network is typically built with Gigabit Ethernet, but for high-volume environments, many vendors offer proprietary low-latency, high-bandwidth solutions specifically designed for Oracle RAC. Linux also offers a means of bonding multiple physical NICs into a single virtual NIC (not covered here) to provide increased bandwidth and availability.

Public Network
To maintain high availability, each cluster node is assigned a virtual IP address (VIP). In the event of node failure, the failed node’s IP address can be reassigned to a surviving node to allow applications to continue accessing the database through the same IP address.

Configuring the Cluster Hardware
There are many different ways to configure the hardware for an Oracle RAC cluster. Our configuration here uses two servers with two CPUs, 1GB RAM, two Gigabit Ethernet NICs, a dual channel SCSI host bus adapter (HBA), and eight SCSI disks connected via copper to each host (four disks per channel). The disks were configured as Just a Bunch Of Disks (JBOD)—that is, with no hardware RAID controller. 


Software
At the software level, each node in a RAC cluster needs:

  1. An operating system
  2. Oracle Clusterware
  3. Oracle RAC software
  4. An Oracle Automatic Storage Management (ASM) instance (optional).

Operating System
Oracle RAC is supported on many different operating systems. This guide focuses on Linux. The operating system must be properly configured for the OS–including installing the necessary software packages, setting kernel parameters, configuring the network, establishing an account with the proper security, configuring disk devices, and creating directory structures. All these tasks are described in this guide.

Oracle Cluster Ready Services becomes Oracle Clusterware
Oracle RAC 10g Release 1 introduced Oracle Cluster Ready Services (CRS), a platform-independent set of system services for cluster environments. In Release 2, Oracle has renamed this product to Oracle Clusterware.
Clusterware maintains two files: the Oracle Cluster Registry (OCR) and the Voting Disk. The OCR and the Voting Disk must reside on shared disks as either raw partitions or files in a cluster filesystem. This guide describes creating the OCR and Voting Disks using a cluster filesystem (OCFS2) and walks through the CRS installation.

Oracle RAC Software
Oracle RAC 10g Release 2 software is the heart of the RAC database and must be installed on each cluster node. Fortunately, the Oracle Universal Installer (OUI) does most of the work of installing the RAC software on each node. You only have to install RAC on one node—OUI does the rest.

Oracle Automatic Storage Management (ASM) / Or other shared Storage .
ASM is a new feature in Oracle Database 10g that provides the services of a filesystem, logical volume manager, and software RAID in a platform-independent manner. Oracle ASM can stripe and mirror your disks, allow disks to be added or removed while the database is under load, and automatically balance I/O to remove “hot spots.” It also supports direct and asynchronous I/O and implements the Oracle Data Manager API (simplified I/O system call interface) introduced in Oracle9i.

Some Other Stuff you need To check Before Installation : 

1-Crossover cables are not supported (use a high-speed switch).
2-Use at least a gigabit Ethernet for optimal performance.
3-Increase the UDP buffer sizes to the OS maximum.
4-Turn on UDP checksumming.
5-Oracle Support strongly recommends the use of UDP (TCP for WIndows )
6-SSH Connectivity .

Thank you
Osama mustafa