Tag: 11g

PRVF-5436 PRVF-9652 Cluster Time Synchronization Services check failed

Posted on by Osama Mustafa in Database

$GRID_HOME/bin/cluvfy comp clocksync
Cause of the Problem
The problem happened because the time service (such as NTP) is not running or doesn’t have slew option set.

Solution of the Problem
The problem happened because time service NTP is not running or doesn’t have slew option set.
So, ensure that NTP service is up and running.
Based on the operating system solutions are given below.

1) Linux:

To verify service as root user issue,
# /sbin/service ntpd status
ntpd (pid 4423) is running…

Check process is running or not by.
# ps -ef|grep ntp
ntp 4209 1 0 Mar10 ? 00:00:00 ntpd -u ntp:ntp -p /var/run/ntpd.pid -x

# grep OPTIONS /etc/sysconfig/ntpd
OPTIONS=”-u ntp:ntp -p /var/run/ntpd.pid -x”

If NTP service is not started then to start the service issue,
To start the service issue,

# /sbin/service ntpd start

2) Solaris:

To verify the service issue,

# /usr/bin/svcs ntp
STATE STIME FMRI
online 3:29:11 svc:/network/ntp:default

# ps -ef|grep ntp
root 21223 1 0 Mar 10 ? 0:21 /usr/lib/inet/xntpd

# grep slewalways /etc/inet/ntp.conf
slewalways yes

To start the NTP service issue,

# /usr/sbin/svcadm enable ntp

3) HP-UX:

To verify the service issue,

# ps -ef|grep ntp
root 6022 1 0 14:23:42 ? 0:01 /usr/sbin/xntpd -x

# grep XNTPD_ARGS /etc/rc.config.d/netdaemons
export XNTPD_ARGS=”-x”

To start the service issue,
# /sbin/init.d/xntpd start

4) AIX:

To verify the service issue,

# /usr/bin/lssrc -ls xntpd
xntpd tcpip 368754 active

# ps -ef|grep ntp
root 786614 151686 0 08:02:32 – 0:00 /usr/sbin/xntpd -x

# grep xntpd /etc/rc.tcpip
start /usr/sbin/xntpd “$src_running” -a “-x”

To start the service issue,

# /usr/bin/startsrc -s xntpd -a “-x”

Thank you
Osama mustafa

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

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 

Change to Archive Log Mode In Oracle Rac

Posted on by Osama Mustafa in Database
1. Disable clustering putting cluster_database parameter FALSE.

$export ORACLE_SID=ORCL1
$sqlplus “/ as sysdba”

Check the status of archive mode of the database:

SQL>archive log list

Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     407
Current log sequence           408

SQL> alter system set cluster_database=false scope=spfile sid=’ORCL1′;

2. Shutdown all instances using srvctl utilty

$ srvctl stop database -d cobra

3. Mount tha database using one of the instance:

$ sqlplus “/ as sysdba”

SQL> startup mount

4. Enable archivelog using following command:

SQL> alter database archivelog;

5. Re-enable clustering putting instance parameter cluster_database to TRUE from the current instance:

SQL> alter system set cluster_database=true scope=spfile sid=’ORCL1′;

6. Shutdown the local instance:

SQL> shutdown immediate

7. Startup all instances using srvctl utility:

#srvctl start database -d orcl

8. If any service is not up then get up those using srvctl utility:

#srvctl start service -dorcl

9. And now check archivelog mode is enabled or not using following:

$sqlplus “/ as sysdba”

SQL> archive log list

Database log mode            Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     407
Current log sequence           408

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

Some Basic To backup Voting Disk And OCR For Oracle RAC

Posted on by Osama Mustafa in Database
We Need To Backup Voting Disk and OCR For Oracle RAC For Disaster Situation :

Add Voting Disk :

# crsctl add css votedisk

To remove a voting disk:

# crsctl delete css votedisk

If Oracle Clusterware is down on all nodes, use the –force option:

# crsctl add css votedisk -force
# crsctl delete css votedisk -force

 Backup Voting Disk :

$ crsctl query css votedisk
$ dd if= of= bs=4k

OCR BACKUP :

Oracle RAC Taking Backup For OCR By Default , You will Find It On :

 cd $ORACLE_BASE/Crs/cdata/jfv_clus

Change the default automatic backup location:

# ocrconfig –backuploc /shared/bak

Back Up OCR Manually :

 # ocrconfig –export file name

 Recover OCR Using Physical Backups:

1.Locate a physical backup:
 $ ocrconfig –showbackup
2.Review its contents:

# ocrdump –backupfile file_name
 3.Stop Oracle Clusterwareon all nodes:

 # crsctl stop crs
4.Restore the physical OCR backup:

# ocrconfig –restore <CRS HOME>/cdata/jfv_clus/day.ocr

5.Restart Oracle Clusterware on all nodes:

 # crsctl start crs
6.Check OCR integrity:

$ cluvfy comp ocr -n all

Thank You
Osama Mustafa 

 



Change Listener Port For Oracle RAC

Posted on by Osama Mustafa in Database
I think its useful to document this since maybe i will need it too 🙂

Environment

2 Node , Linux  …..

RAC database name: ORCL
Node 1 vip: myhost1-vip
Node 2 vip: myhost2-vip
Instance 1: ORCL1
Instance 2: ORCL2
Version: 11.1.0.7
New port: 1522

STEPS :

1) First, use netca to adjust the the listener port from 1521 to 1522. It’s a good idea to use netca to do this cluster-wide and keep the OCR in check.  
NOTE: This will shutdown the listeners across both nodes.

2)

2) In the tnsnames.ora for ASM and RDBMS, ensure the following entry exists (they’ll probably be there but with the 1521 port, so adjust accordingly):

LISTENERS_ORCL=
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = myhost1-vip)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = myhost2-vip)(PORT = 1522))
)

LISTENER_ORCL2=
(ADDRESS = (PROTOCOL = TCP)(HOST = myhost2-vip)(PORT = 1522))

LISTENER_ORCL1=
(ADDRESS = (PROTOCOL = TCP)(HOST = myhost1-vip)(PORT = 1522))

3)
Connect to each instance and set the local_listener to parameter to either LISTENER_MYRACDB1 or 2 depending on the instance you connect to. Ensure you do this so it applies to the instance you are connected to only, and does not apply globally.

e.g. if connected to ORCL1

alter system set local_listener=’LISTENER_ORCL1′ sid=’ORCL1′;

4) Set the remote_listener parameter to LISTENERS_MYRACDB across all instances

e.g. alter system set remote_listener=’LISTENERS_ORCL’;

5) Repeat the above for both ASM instances

6) You should be able to start the listener on each node now.

e.g. srvctl start listener -n myhost1 

Thank you

Note :

On Single node instance all you need to do is
1-stop the listener
lsnrctl stop

2-Go to $ORACLE_HOME/network/admin/listener.ora
modified the port for new one .

3-start the listener
lsnrctl start

DONE

Osama mustafa
Oracle Database consultant

Resize Redo Log

Posted on by Osama Mustafa in Database
Here is a newly created database redo log information:

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
———- ———- ———- ———- ———- — —————- ————- ——————-
1 1 17 52428800 1 NO INACTIVE 996238 06/09/2007 22:01:59
2 1 18 52428800 1 NO INACTIVE 1006432 06/09/2007 22:13:32
3 1 19 52428800 1 NO CURRENT 1036439 07/09/2007 09:56:44

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER
———- ——- ——- ————————————————-
3 ONLINE /database/data/redo03.log
2 ONLINE /database/data/redo02.log
1 ONLINE /database/data/redo01.log

Here is how i changed this to five 200M redo logs:

SQL> alter database add logfile group 4 (‘/database/data/redo04.log’) size 200M;
SQL> alter database add logfile group 5 (‘/database/data/redo05.log’) size 200M;

while running following sql commands, if you hit an error like this:

ORA-01623: log 3 is current log for instance RPTDB (thread 1) – cannot drop
ORA-00312: online log 3 thread 1: ‘/database/data/redo03.log’

you should run ” alter system switch logfile;” until current log is 4 or 5. Then execute “alter system checkpoint;”

SQL> alter database drop logfile group 1;
SQL> alter database drop logfile group 2;
SQL> alter database drop logfile group 3;

then move (or maybe drop) old redo logs

mv /database/data/redo01.log /database/data/redo01_old.log
mv /database/data/redo02.log /database/data/redo02_old.log
mv /database/data/redo03.log /database/data/redo03_old.log

finally

SQL> alter database add logfile group 1 (‘/database/data/redo01.log’) size 200M;
SQL> alter database add logfile group 2 (‘/database/data/redo02.log’) size 200M;
SQL> alter database add logfile group 3 (‘/database/data/redo03.log’) size 200M;

steps to upgrade 11.1.0.6.0 to 11.1.0.7.0

Posted on 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