Tag: Osama

Checking O2CB heartbeat: Not active

Posted on by Osama Mustafa in Database

[root@rac01]# /etc/init.d/o2cb  status

Module “configfs”: Loaded
Filesystem “configfs”: Mounted
Module “ocfs2_nodemanager”: Loaded
Module “ocfs2_dlm”: Loaded
Module “ocfs2_dlmfs”: Loaded
Filesystem “ocfs2_dlmfs”: Mounted
Checking O2CB cluster ocfs2: Online
  Heartbeat dead threshold: 61
  Network idle timeout: 30000
  Network keepalive delay: 2000
  Network reconnect delay: 2000
Checking O2CB heartbeat: Not active

The Solution like the Following :

[root@rac01]# /etc/init.d/o2cb  configure

Configuring the O2CB driver.
This will configure the on-boot properties of the O2CB driver.
The following questions will determine whether the driver is loaded on
boot.  The current values will be shown in brackets (‘[]’).  Hitting
without typing an answer will keep that current value.  Ctrl-C
will abort.
Load O2CB driver on boot (y/n) [y]: y
Cluster to start on boot (Enter “none” to clear) [ocfs2]:
Specify heartbeat dead threshold (>=7) [31]: 61
Specify network idle timeout in ms (>=5000) [30000]:
Specify network keepalive delay in ms (>=1000) [2000]:
Specify network reconnect delay in ms (>=2000) [2000]:
Writing O2CB configuration: OK
O2CB cluster ocfs2 already online

[root@rac01]# /etc/init.d/o2cb  stop
Stopping O2CB cluster ocfs2: OK
Unmounting ocfs2_dlmfs filesystem: OK
Unloading module “ocfs2_dlmfs”: OK
Unmounting configfs filesystem: OK
Unloading module “configfs”: OK

 [root@rac01]# /etc/init.d/o2cb  start
Loading module “configfs”: OK
Mounting configfs filesystem at /config: OK
Loading module “ocfs2_nodemanager”: OK
Loading module “ocfs2_dlm”: OK
Loading module “ocfs2_dlmfs”: OK
Mounting ocfs2_dlmfs filesystem at /dlm: OK
Starting O2CB cluster ocfs2: OK

Now

  [root@rac01]# ocfs2console

Then Mount Command .

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

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

Instance Recovery and RAC

Posted on by Osama Mustafa in Database

Here are some guidelines you can use to make sure that instance recovery in your RAC environment is faster:
•Use parallel instance recovery by setting RECOVERY_PARALLISM.
•Increase PARALLEL_EXECUTION_MESSAGE_SIZE from its default of 2,148 bytes to 4 KB or 8 KB. This should provide better recovery slave performance.
•Set PARALLEL_MIN_SERVERS to CPU_COUNT-1. This will prespawnrecovery slaves at startup time.
•Using asynchronous I/O is one of the most crucial factors in recovery time. The first-pass log read uses asynchronous I/O.
•Instance recovery uses 50 percent of the default buffer cache for recovery buffers. If this is not enough, some of the steps of instance recovery will be done in several passes. You should be able to identify such situations by looking at your alert.logfile. In that case, you should increase the size of your default buffer cache.

ocfs2console: o2cb_ctl: Unable to access cluster service while creating node

Posted on by Osama Mustafa in Database
For the first time, during configuring the nodes, ocfs2console fails with message:
“o2cb_ctl: Unable to access cluster service while creating node”
TODO:
– Quit ocfs2console
– Stop the service
– Remove file /etc/ocfs2/cluster.conf
– Restart ocfs2console
– Configure the nodes again

[root@rac1 ~]#  /etc/init.d/ocfs2 stop
Stopping Oracle Cluster File System (OCFS2)                [  OK  ]

[root@rac1 ~]# /etc/init.d/o2cb offline ocfs2

[root@rac1 ~]# /etc/init.d/o2cb unload
Unmounting ocfs2_dlmfs filesystem: OK
Unloading module "ocfs2_dlmfs": OK
Unmounting configfs filesystem: OK
Unloading module "configfs": OK

[root@rac1 ~]# rm -f /etc/ocfs2/cluster.conf
[root@rac1 ~]# /usr/sbin/ocfs2console &

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

Prevent developers from using TOAD,other tools on production databases

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

compile invalid objects in an APPS (EBS)

Posted on by Osama Mustafa in Application
You need to know Count of Invalid Object for your APPS :

SELECT COUNT(*)
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID';

For a more detailed query, use the following script :

SELECT OWNER, OBJECT_TYPE, COUNT(*)
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID'
GROUP BY OWNER, OBJECT_TYPE;

To recompile an individual object, connect to SQL*PLUS as the owner of the object (generally apps) and use one of the following depending on the object type :

alter package compile; (package specification)
alter package compile body; (package body)
alter view compile; (view)

If the object compiles with warnings, use either of the following to see the errors that caused the warnings :

show errors

OR

select * from user_errors where name = '';

Another way to correct invalid objects is to run the adadmin utility as follows:

1. Log in as APPS User : /

2. Start the adadmin-Utility from the Unix prompt with this command :

adadmin

The utility will then ask you a series of questions.

3. Under the Maintain Applications Database Objects Menu, select Compile APPS schema(s)

Other Way :
 
Also try running $ORACLE_HOME/rdbms/admin/utlrp.sql ( as sysdba )

Within Applications, there is a script to compile INVALID objects – called ADCOMPSC.pls

 Arguments for ADCOMPSC.pls :

1 – Schema to run in
2 – Password for schema
3 – Check errors for objects starting with #3

NOTE: The order in which to compile Invalid Objects in schemas is SYS, SYSTEM, APPS and then all others. APPS_DDL and APPS_ARRAY_DDL should exist in all schema’s. In case of an ORA-1555 error while running adcompsc.pls, restart the script.

The script can be run as followed :

cd $AD_TOP/sql
sqlplus @adcompsc.pls SCHEMA_NAME SCHEMA_PASSWORD %

Example : SQL> @adcompsc.pls apps apps %

After the script completes, check for invalid objects again. If the number has decreased, but invalid objects still exist, run adcompsc.pls again. Keep running adcompsc.pls until number of invalid objects stops decreasing.

If there are any objects still left INVALID, verify them by using the script ‘aderrchk.sql’ to record the remaining INVALID objects. ‘Aderrchk.sql’ uses the same syntax as ‘adcompsc.pls’. This script is also supplied with the Applications. Send the aderrchk.sql to a file using the spool command in sqlplus.

e.g. sqlplus x/y @aderrchk.sql SCHEMA_NAME SCHEMA_PASSWORD %

For objects which will not compile, try the following :

select text
from user_source
where name = 'OBJECTNAME'
and text like '%Header%';

This script will provide the script that creates the packages/recreates the packages.

SQL>@packageheader
SQL>@packagebody

If recreating the package does not make the package valid, analyze the user_errors table to determine the cause of the invalid package :

select text
from user_errors
where name = '';

Thank You
Osama Mustafa

waiting for dictionary redo first scn

Posted on by Osama Mustafa in Database
Capture status : Streams Waiting for dictionary first scn

Example :

Capture is waiting on redo log file with SCN 5611274208824
Capture

The First SCN 5611441137818
Start SCN 5611441137818

Applied Scn 5611441373264
Required SCN 5611441137818

You can get the above information from enterprise manager
Maintenance -> Stream -> management -> capture (choose capture name and edit).

Note : 
1-Applied Scn +  Required SCN : you can’t change them (read only).

2-Set First SCN + Start SCN same as Required SCN .

 [Document 313279.1] Master Note for Troubleshooting Streams capture ‘WAITING For REDO’ or INITIALIZING

ACTION Plan #1 :

A) Restore archived redo logs starting with sequence   
B) If you can not restore logs, then capture must be droppend and recreated.
Note 471695.1 – Required Steps to Recreate a Capture Process

You should be running dbms_capture_adm.build on a regular basis to allow you to rebuild capture without having to resync.

ACTION Plan #2 :

look at the output from below sql and ensure that ALL logs (by time) are online and available.

++ Registered Log Files for Capture ++
COLUMN CONSUMER_NAME HEADING ‘Capture|Process|Name’ FORMAT A15
COLUMN SOURCE_DATABASE HEADING ‘Source|Database’ FORMAT A10
COLUMN SEQUENCE# HEADING ‘Sequence|Number’ FORMAT 999999
COLUMN NAME HEADING ‘Archived Redo Log|File Name’ format a35
column first_scn HEADING ‘Archived Log|First SCN’
COLUMN FIRST_TIME HEADING ‘Archived Log Begin|Timestamp’
column next_scn HEADING ‘Archived Log|Last SCN’
COLUMN NEXT_TIME HEADING ‘Archived Log Last|Timestamp’
COLUMN MODIFIED_TIME HEADING ‘Archived Log|Registered Time’
COLUMN DICTIONARY_BEGIN HEADING ‘Dictionary|Build|Begin’ format A6
COLUMN DICTIONARY_END HEADING ‘Dictionary|Build|End’ format A6
COLUMN PURGEABLE HEADING ‘Purgeable|Archive|Log’ format a9

SELECT r.CONSUMER_NAME,
r.SOURCE_DATABASE,
r.SEQUENCE#,
r.NAME,
r.first_scn,
r.FIRST_TIME,
r.next_scn,
r.next_time,
r.MODIFIED_TIME,
r.DICTIONARY_BEGIN,
r.DICTIONARY_END,
r.purgeable
FROM DBA_REGISTERED_ARCHIVED_LOG r, DBA_CAPTURE c
WHERE r.CONSUMER_NAME = c.CAPTURE_NAME
AND r.SEQUENCE# > 48056
ORDER BY source_database, consumer_name, r.first_scn;

After verifying that all those logs are available, then for capture , advance first/start snc
login to strmadmin

exec dbms_capture_adm.alter_capture(‘capture-name’,first_scn=>same as the above);
exec dbms_capture_adm.alter_capture(‘capture-name’,start_scn=>same as the above);

Verify

SELECT CAPTURE_NAME, FIRST_SCN, START_SCN, APPLIED_SCN, REQUIRED_CHECKPOINT_SCN FROM ALL_CAPTURE;

Restart capture.

 ##To check if SCN changed :

SELECT SUBSTR(s.PROGRAM,INSTR(S.PROGRAM,'(‘)+1,4) PROCESS_NAME,
c.CAPTURE_NAME,
C.STARTUP_TIME,
c.SID,
c.SERIAL#,
c.STATE,
c.state_changed_time,
FROM gV$STREAMS_CAPTURE c, gV$SESSION s
WHERE c.SID = s.SID AND
c.SERIAL# = s.SERIAL#;

ACTION PLAN #3 : 

If the Missing Archivelog found on ASM but Capture can’t see them you have to do the following :

ALTER DATABASE REGISTER LOGICAL LOGFILE ” FOR ”; 

OR

ALTER DATABASE REGISTER OR REPLACE LOGICAL LOGFILE ‘PATH’ FOR ‘Capture-name’;

Then Check By :

select name, sequence# from v$archived_log — use gv$archived_log in RAC
where between FIRST_CHANGE# and NEXT_CHANGE#
order by name;

Hope this will be Useful for you
Finally Don’t forget after you resolve the problem ro run this command once everyday to rebuild capture without having to resync

SQL> exec DBMS_CAPTURE_ADM.BUILD;

Check This Link : Stream Capture

 Thank you
Osama Mustafa
Oracle Database Consultant