ASM/RAC Reff

ASM Disk create in OS level

ASM Instance creating :

1.       Pfile

Instance type = asm ASM or RDBMS

Root user log in

# $ ORACLE_HOME /bin/localconfig all or reset or set

It will start the cluster synchronization service.

$ mkdir-p $ORACLE_BASE/admin/+ASM /bdump or /cdumb or /bdumb / udumb

$ export ORACLE_SIDm = + ASM
$ orapwd file = $ ORACLE_HOME / dbs/orapw+psm

                                                                                Password = sys

$ vi $ ORACLE_HOME/dbs/init + ASM.ora

-asm-allow-only-raw-disks = false

asm_diskgroups= ‘TESTDB-DATA1’   / group name /

asm_diskstring = ‘ORCL: VOL *’    / os level file of vol1 /

background_dump_desk = /uo1/app/oracle/admin/+asm/bdump

coure_dump_desk = /uo1/app/oracle/admin/+asm/cdump

user_dump = /uo1/app/oracle/admin/+asm/udump

instance_type = asm

db_unique_name = +ASM

remote login = EX

ASM_POWER_LIMIT = 5 

compatible = 10.2.0.0.0

larger_pool_size = 12 m

$ sqlplus “/ as sysdba”

Ø  start up

ASM instance started

0 – 15110 to diskgroups mounted

Ø  create spfile  from pfile

Ø  shutdown

Ø  startup No mount

Ø  create diskgroup testdb-data1 normal

failgroup fg1 disk ‘ ORCL : VOL1’ , ‘ORCL:VOL2’

failgroup fg2  disk ‘ ORCL : VOL3’ , ‘ORCL:VOL4

Ø  shutdown immediate

Ø  start up

Started

No Mount

Mount

ASM Background Process

SQL > Drop diskgroup disk_group1

Ø  alter diskgroup disk_group1 Resize disk in

Failgroup fg1 sie 100G

Ø  alter diskgroup disk_group_1 Resize All size 100 G

Ø  alter diskgroup disk_group_1 Rebalance power 5;

Ø  alter diskgroup All dismount;

Ø  alter diskgroup All mount;

Ø  alter diskgroup disk_group_1 dismount;

Ø  alter diskgroup disk_group_1 mount;

Ø  alter diskgroup disk_group_1

Add directory ‘f disk_group_’ /my_dir;

Ø  alter diskgroup disk_group_1  Rename

directory ‘ + disk_group_1 / my dir’

To ‘ + disk_group_1 / my dir_z’;

Ø  alter diskgroup disk_group_I Drop;

Ø  select name from v$datafile;

Drop directory

Ø  alter diskgroup disk_group_1 

directory ‘ + disk_group_1 / my dir-2 / force’

Ø  alter diskgroup disk_group_1 Add

Alias  ‘ + disk_group_1 / my dir / my file.dbf’

                Force  + disk_group_1 /mydb / datafile/ my_ts.342.5’;

Ø  alter diskgroup disk_group_1  rename

Alias  ‘ + disk_group_1 / my_ dir / my_ file.dbf’

To   ‘ + disk_group_1 / my_dir / my _file2.dbf’;

Ø  alter diskgroup disk_group_1  delete

Alias  ‘ + disk_group_1 / my_ dir / my_ file.dbf’

Ø  select group_number , name , total_mb,

free-mb from v$asm_diskgroup;

Ø  select group_number , disk_number, mount_status,path,tail group from v$ asm_disk;

Ø  create tablespace my_ts datafile ‘diskgroup’ size 100 m auto extend on;

Migrating

Ø  alter database disable block change tracking;

Ø  shu immediate

Ø  exit

$ vi ORACLE/HOME/dbs/Init prod.ora

DB_CREATE_FILE_DEST = ‘+disk_group’

DB_create_online_log_dest1 = ‘ +disk_group1’

control_files = ‘ +disk_group1’

$ sqlplus ‘ / as sysdba’;

Ø  startup nomount pfile =’ initpord.ora’

Ø  create spfile from pfile

Ø  shutdown immediate

Ø  rman targe  /

rman > startup nomount

rman > restore controlfile from 1 ….. part ‘;

                rman > alter database mount;

                rman > backup as copy databse format ='disk_group_1';

                rman > switch databse to copy;

                rman > Alter database open;

                rman > alter database Enable block change Tracking;

Ø  alter databse  add logfile  group4;

Ø  alter databse  add logfile  group5;

Ø  alter databse  add logfile  group6;

Ø  alter system switch logfile;

New temporary table space create:

Ø  create temporary tablespace temp1 tempfiles size 2048m;

Ø  default tablespace temp2

and then drop old temp file

RAC Installation

# lastname

Linux1

# ifconfig –a

Eth0

Eth0 :Link encp internet hwaddr oo:1c:c4:BF:c5:A4 inet.address 192.168.1.100 breast 192.168.225.0

Eth1:

Oracle Interface Configuration

$ oifcfg iflist 

$ oifcfg iflist –p

                etho 192.168.1.100    unknown

                etho 192.168.2.100    private

$ oifcfg iflist –p –n

etho 192.168.1.100    255.255.255.0

                etho 192.168.2.100    255.255

$ oifcfg getif

                etho 192         / global public

                etho 192         / global cluster

$ oifcfg getif  -if etho

$ oifcfg getif – global eth2/192.168.110 : public

$ oifcfg getif

                etho 192.168.1.100     \ global public

                etho 192.168.2.100     \ global cluster-interconnect

                etho 192.168.1.110     \ global public

To remove

$ oifcfg delif  -global eth2

$ oifcfg getif

                etho 192.168.1.100   \ global public

                etho 192.168.2.100   \ cluster interconnect

Administering Oracle Clusterware

CRSCTL

CRS-STAT

OCRCONFIG

OCRCHECK

OCRDUMP

CRSCTL (Cluster Ready Service Control Utility)

$ ORA-CRS-HOME /bin

# crsctl check crs

# crsctl check cssd            \css  appears healthy

# crsctl check crsd            \crs  appears healthy

# crsctl check evmd         \evm  appears healthy

# crsctl start crs

# crsctl stop crs

# crsctl disable crs

# crsctl enable crs

/etc/oracle/scls-scr/<node>/root/crsstart

Managing Voting disk

# crsctl query  css votedisk

# crsctl add css votedisk    / u03/oradata/orcl/cssfile2

# crsctl stop crs

# crsctl add css votedisk / u03/oradata/orcl/cssfile2

# crsctl dtart crs

# crsctl query css votedisk

 0                             0                              /u02/tradent /css file1

1                              1                              /u03 /tradent /css file1

# crsctl delete css votedisk          /u03/oradata/orcl/cssfile2

# crsctl stop crs

# crsctl delete css votedisk    /u03/oradata/orcl/cssfile2 – force

# crsctl start crs

# crsctl query css votedisk

0              0              /uo2/oradata/orcl/cssfile1

# crsctl query crs software version

$ crs –start-t

OCRCONFIG

Add/remove/backup/recover/rebier

-sl ORA-CRS-HOM/log/ <node>/client/ocrconfig- <pid>log

-show backup

# export ORA-CRS-HOME= /u01/app/orcle/product/10.2.0/crs

# export path = $PATH:$ORA-CRS-HOME/ bin

Backup  OCR

$ ORA-CRS-HOME /crs/cdata/ <cluster-name>

# ocrconfig – showbackup

Linux1  | 2010/11/04  11:15:29 | /u01/ap1/oracl/product/16.2.0/crs/cdata/crs

 # ls –l $ ORA-CRS-HOME / CRS/Cdata / CRS

# ocrconfig –backuploc/backup/oradata/orc

Restore

# ocrconfig  - showbackup

# crsctl stop crs

# ocrconfig  -restore $ ORA-CRS-HOME / Cdatra/crs/backup oo.ocr

#clurfy comp ocr –n

# crsctl start crs

# ocrconfig  -showbackup

# crsctl stop crs

# vi /etc/oracle/ocr.loc

# ocrconfig –restore $ ORA-CRS-HOME/ cdata /crs/backup 00.ocr

# cluvfy comp ocr –n all

# crsctl start crs

Manual Backup

# crsctl stop crs

# ocrconfig  -export

# crsctl start crs

Import the OCR

# crsctl stop crs

#ocrconfig  -import  /tmp/ocrfile

# clarfy comp ocr

# crsctl start crs

Adding in OCR file

# ocrconfig  -replace  ocr/u03/oradata/urcl/ocrfile1

# ocrconfig –replace ocrmirror /u03/oradata/oracle/ocrfile2

Replacing OCR file

# orcconfig  -replace ocr/u04/oradata/oracle/ocrfile1

# orcconfig –replace ocrmirror

Deleting OCR file

# ocrconfig –replace ocr

# ocrconfig –replace ocrmirror

Repairing the OCR configuration

# crsctl stop crs

# ocrconfig –repair ocr

# cluvfy comp ocr –n all

# crsctl start crs

#OCR check

$ ORA-CRS-Home /log/ <node> / client/ocrcheck_ <pid>.log

OCRDUMP

$ ORA-CRS-HOME/log/<NOD>/…………..

# ocrdump

#ocrdump  /tmp/ocr_cluster1

Backup voting disks

# crsctl stop crs

# dd if = /u02/oradata/orcl/cssfiles /backup/oradata/orcl/cssfiles-bk

Corrupted for voting disk

# crsctl stop crs

# dd 15 = /backup /oradata/orcl/cssfiles of = /u02/oradata/orcl/cssfiles

# crsctl start crs

$ ORA-CRS-HOME /log/<node>/client

Crs à $ ORA-CRS-HOME/log/ <node> / crsd /crsd/log

CSS à $ ORA-CRS-HOME/log/ <node> / crsd/ocssd/log

EVM à $ ORA-CRS-HOME/log/ <node> / crsd/evmd.log

Vip/ons step  $ ORA-CRS-HOME/log/<nod>/racg

# ORA-CRS-HOM/bin/diagcollection.p/

# export ORA-CS-HOME /u01/app/oracle/product/10.2.0/crs

# export oracle_HOME = /u01/app/oracle/product/10.2.0/db-1

# $ORA-CRS-HOME/bin/diagcollection.p –collect

# $RA-CRS-HOME/bin/diagcollection.p –collect –crs

# $RA-CRS-HOME/bin/diagcollection.p –collect oh $ORACLE_HOME

# $RA-CRS-HOME/bin/diagcollection.p –collect – ob $ ORACLE_BASE

# $RA-CRS-HOME/bin/diagcollection.p –collect –all

# $RA-CRS-HOME/bin/diagcollection.p –collect –coreanalysiz

# $RA-CRS-HOME/bin/diagcollection.p –collect –clean

# O/S Nods –p –n –E

Cluster verification Utility

# cluvfy comp ssa  -n all-verbose

# cluvfy comp ssa  -n all- s/dev/sda

# cluvfy comp –list

# cluvfy comp space –n all – l /u01 -2

Cluster verification utility connectivity verifications

# cluvfy comp noderreach –n linux2, linux3

# cluvfy comp nodecon – n linux2,linux3 –I eth0 –verbose

# cluvfy comp nodecon –n linux2,linux3 – verbose

Cluster verification utility installation verification

#  cluvfy stage –pre crsinst –n linux1,linux2

# cluvfy stage –post crsinst –n linux1,linux2

# cluvfy stage –pre dbinst  –n linux1,linux2

# cluvfy comp crs –n all –verbose

# cluvfy comp ocr –n all –verbose

Workload Management

Client side conn load balancing

RAC = (DESCRIPTION = (ADDRESS = (PROTOCOL =TCP) (HOST =linux1) (port =1521))

                                            (ADDRESS = (PROTOCOL =TCP) (HOST =linux2) (port =1521))

                                                (LOAD-BALANCE=ON)

                                                (FAILOVER = ON)

(CONNECT _DATA = (SERVICE_NAME =RAC)

(FAILOVER_MODE = (TYPE=SELECT)(METHOD=BASIC))

Serverside.connection load balancing

Sql > EXECUTE dbms_service.create_service

                (service_name => ‘service1’

                network_name=> ‘service1’,

goal => DBMS_SERVICE.GOAL_SERVICE_TIME,

clb_goal => DBMS_SERVICE.CLB_GOAL_SHORT);

Service modify

Sql> EXECUTE dbms_service_modify_service

                (service_name => ‘service1’

                network_name=> ‘service1’,

goal => DBMS_SERVICE.GOAL_SERVICE_TIME,

clb_goal => DBMS_SERVICE.CLB_GOAL_LONG);

sql >select name,goal,clb_goal from dba_services;

sql >                                       v$ services;

sql >                                       $ ACTIVE_SERVICES;

SRVCTL

$ srvctl add service –d  orcl

                -s rac  -r orcl1  -a orcl2

TAF services

sevice1=

(DESCRIPTION =

(ADDRESS = (PROTOCOL=TCP) (HOST=linux1-VIP) (PORT =1521))

(ADDRESS = (PROTOCOL=TCP) (HOST=linux1-VIP) (PORT =1521))

(LOAD_BALANCE =YES)

(CONNECT_DATA =

(SERVER_NAME =SERVICE1)

(FAILOVER_MODE=

(TYPE = SELECT)

(METHOD = BASIC)

(RETRIES = 180)

(DELAY = 5)

 )

 )

)

RAC        ADMINISTRATION USING (SRVCTL)

ð  database

ð  instance

ð  service

ð  node application

ð  asm

ð  listener

$ srvctl –help

Command

enable / diable / start / stop / relocate / status/add/remove / modify/ config

objects : database / instance/service/nodeapps/asm/listener

srvctl <commqne> <object> -h

-d  <name> unique name for the database

-0 <stgart-option> options to startup command (e.g open , mount (or ) no mount)

-c <connstr> connect string (default : / as sysdba)

usage : srvctl start database –d<nam> [-o <start=options]

$ srvctl –v -> display the srvctl version

Administering database

srvctl start database –d <name> [-0 <start-options>] [-c <connect _str)]

$ srvctl start database –d ORCL

Srvctl stop database –d <name>  [-0 <start-options>] [-c <connect _str)]

$ srvctl stop database –d ORCL

$ srvctl config database   à display all database

$ srvctl config database   -d ORCL à display all instance and location

linux1 ORCL 1    /u01/app/oracle/product/10.2.0/db_1

linux2 ORCL

$ srvctl status database –d ORCL

Check the instance running.

Instance ORCL1 is running on node linux1

Instance ORCL2 is running on node linux2

$ srvctl add database –d TEST

 -0 $ ORACLE_HOME

$ srvctl modify  database –d TEST

                                -p   /u03/oradata/ORCL/spfiletest.ora

$ srvctl remove  database –d TEST

$ srvctl disable database –d ORCL

$ srvctl enable database –d ORCL

Administrating Instance

$ srvctl start instance –d ORCL –i ORCL1

$ srvctl start instance –d ORCL –i “ORCL1,  ORCL2”

$ srvctl stop instance –d ORCL –i ORCL1

$ srvctl stop instance –d ORCL –i “ORCL1,  ORCL2”

$ srvctl status instance –d ORCL –i ORCL1

$ srvctl status instance –d ORCL –i “ORCL1,  ORCL2”

$ srvctl add instance –d ORCL –i ORCL3

                                                                -n linux3

$ srvctl modify instance –d ORCL –i ORCL3

                                                                -n linux4

$ srvctl remove  instance –d ORCL –i ORCL3

$ srvctl disable  instance –d ORCL –i ORCL3

$ srvctl enable  instance –d ORCL –i ORCL3

Administering node application

$ srvctl start nodeapps –n linux1

$ srvctl stop nodeapps –n linux1

$ srvctl config  nodeapps –n linux1

                linux1 ORCL1/uo1/app/oracle/product/10.2.0/db/

$ srvctl config  nodeapps –n linux  -a

VIP exists : linux1-vip/ 147.43.1.201/ 22.225.0.0/eth o

$ srvctl config  nodeapps –n linux  -g

$ srvctl config  nodeapps –n linux  -s

Listener Exists

$ srvctl status nodeapps  -n linux1

                VIP is running on mode linux1

GSD is running on mode linux1

Listener is running on mode linux1

ONS is running on node linux1

$ srvctl add nodeapps –n linux3

                                -0 $ ORACLE_HOME

                                -A linux3 –vip / 255.255.0.0/ eth 0

$ srvctl modify nodeapps –n linux3

                                -0 $ ORACLE_HOME

                                -A linux3 –vip / 255.255.0.0/ eth 0

$ srvctl remove nodeapps –n linux3

Administering Listener

$ srvctl config listener –n linux1

$ srvctl start listener –n linux1

$ srvctl start listener –n linux1 –l LISTENER

$ srvctl stop listener –n linux1

ASM Admin

$ srvctl add asm –n linux3

                                -i+ASM3  -0 $ ORACLE_HOME

$ srvctl stop  asm –n linux3 -i+ASM3

$ srvctl start  asm –n linux3 -i+ASM3

$ srvctl remove  asm –n linux3 -i+ASM3

$ srvctl disable  asm –n linux3 -i+ASM3

$ srvctl enable  asm –n linux3 -i+ASM3

$ srvctl status  asm –n linux1

$ srvctl config asm –n linux1

                +ASM/u03/app/oracle/product/10.2.0/db-1

RMAN Cloning Using backup Copy

RMAN Cloning Using backup Copy

Step:1

1)BY using RMAN Backup(command) the prod DB in NODE1.

Ex:

RMAN>backup current controlfile;

RMAN>backup database plus archivelog;

2)Move RMAN Backup copy from NODE1 to NODE2.

Step:2

Connect to RMAN in NODE2 and startup db (no mount)

[oracle@localhost ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Nov 12 11:58:20 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target sys/sys

connected to target database (not started)

RMAN> startup nomount

Oracle instance started

Total System Global Area 535662592 bytes

Fixed Size 1337720 bytes

Variable Size 163579528 bytes

Database Buffers 364904448 bytes

Redo Buffers 5840896 bytes

Step:3

Restore the Controlfile in no mount stage.

RMAN> restore controlfile from '/home/oracle/PROD/autobackup/2011_11_11/o1_mf_s_766931942_7csljsn9_.bkp';

Starting restore at 12-NOV-11

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=134 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:15

output file name=/home/oracle/app/oracle/oradata/prod/control01.ctl

output file name=/home/oracle/app/oracle/oradata/prod/control02.ctl

output file name=/home/oracle/app/oracle/oradata/prod/control03.ctl

Finished restore at 12-NOV-11

Step:4

Try to Mount the Database

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

Step:5

Register the RMAN Backup piece(Copy of NODE1) NODE2

RMAN> catalog backuppiece '/home/oracle/PROD/backupset/2011_11_11/o1_mf_annnn_TAG20111111T121851_7cskc6by_.bkp';

cataloged backup piece

backup piece handle=/home/oracle/PROD/backupset/2011_11_11/o1_mf_annnn_TAG20111111T121851_7cskc6by_.bkp RECID=6 STAMP=767016752

RMAN> catalog backuppiece '/home/oracle/PROD/backupset/2011_11_11/o1_mf_annnn_TAG20111111T121851_7cskc6by_.bkp';

cataloged backup piece

backup piece handle=/home/oracle/PROD/backupset/2011_11_11/o1_mf_annnn_TAG20111111T121851_7cskc6by_.bkp RECID=7 STAMP=767016757

RMAN> catalog backuppiece '/home/oracle/PROD/backupset/2011_11_11/o1_mf_annnn_TAG20111111T123702_7csldy4q_.bkp';

cataloged backup piece

backup piece handle=/home/oracle/PROD/backupset/2011_11_11/o1_mf_annnn_TAG20111111T123702_7csldy4q_.bkp RECID=8 STAMP=767016787

RMAN> catalog backuppiece '/home/oracle/PROD/backupset/2011_11_11/o1_mf_ncnnf_TAG20111111T123840_7cslj7hn_.bkp';

cataloged backup piece

backup piece handle=/home/oracle/PROD/backupset/2011_11_11/o1_mf_ncnnf_TAG20111111T123840_7cslj7hn_.bkp RECID=9 STAMP=767016848

RMAN> catalog backuppiece '/home/oracle/PROD/backupset/2011_11_11/o1_mf_ncsnf_TAG20111111T121913_7csldf7d_.bkp';

cataloged backup piece

backup piece handle=/home/oracle/PROD/backupset/2011_11_11/o1_mf_ncsnf_TAG20111111T121913_7csldf7d_.bkp RECID=11 STAMP=767016874

RMAN> catalog backuppiece '/home/oracle/PROD/backupset/2011_11_11/o1_mf_nnndf_TAG20111111T121913_7cskckv3_.bkp';

cataloged backup piece

backup piece handle=/home/oracle/PROD/backupset/2011_11_11/o1_mf_nnndf_TAG20111111T121913_7cskckv3_.bkp RECID=12 STAMP=767016911

RMAN> catalog backuppiece '/home/oracle/PROD/backupset/2011_11_11/o1_mf_nnndf_TAG20111111T121913_7cskckv3_.bkp';

cataloged backup piece

backup piece handle=/home/oracle/PROD/backupset/2011_11_11/o1_mf_nnndf_TAG20111111T121913_7cskckv3_.bkp RECID=13 STAMP=767016953

Step:6

Rename and Relocate the Redologs

SQL> alter database rename file '/oracle/oradata/prod/redo001.log'(Node 1 location) to '/home/oracle/app/oracle/oradata/prod/redo01.log'(Node 2 location);

Database altered.

SQL> alter database rename file '/oracle/oradata/prod/redo03.log' to '/home/oracle/app/oracle/oradata/prod/redo02.log';

Database altered.

SQL> alter database rename file '/oracle/oradata/prod/redo002.log' to '/home/oracle/app/oracle/oradata/prod/redo03.log';

Database altered.

Step:7

To know the last sequence available in the archivelog backup

Ex:

RMAN > list backup of archivelog all;

Step:8

Now restore the datafiles to new locations(Node 2) and recover.

RMAN> run

2> {

3> set until sequence 2 THREAD 1;

4> set newname for datafile 1 to '/home/oracle/app/oracle/oradata/prod/system01.dbf';

5> set newname for datafile 2 to '/home/oracle/app/oracle/oradata/prod/sysaux01.dbf';

6> set newname for datafile 3 to '/home/oracle/app/oracle/oradata/prod/undotbs01.dbf';

7> set newname for datafile 4 to '/home/oracle/app/oracle/oradata/prod/users01.dbf';

8> restore database;

9> switch datafile all;

10> recover database;

12. }

Output:

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 12-NOV-11

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to /home/oracle/app/oracle/oradata/prod/system01.dbf

channel ORA_DISK_1: restoring datafile 00002 to /home/oracle/app/oracle/oradata/prod/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00003 to /home/oracle/app/oracle/oradata/prod/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00004 to /home/oracle/app/oracle/oradata/prod/users01.dbf

channel ORA_DISK_1: reading from backup piece /home/oracle/PROD/backupset/2011_11_11/o1_mf_nnndf_TAG20111111T121913_7cskckv3_.bkp

channel ORA_DISK_1: piece handle=/home/oracle/PROD/backupset/2011_11_11/o1_mf_nnndf_TAG20111111T121913_7cskckv3_.bkp tag=TAG20111111T121913

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:02:18

Finished restore at 12-NOV-11

datafile 1 switched to datafile copy

input datafile copy RECID=5 STAMP=767017238 file name=/home/oracle/app/oracle/oradata/prod/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=6 STAMP=767017239 file name=/home/oracle/app/oracle/oradata/prod/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=7 STAMP=767017240 file name=/home/oracle/app/oracle/oradata/prod/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=8 STAMP=767017241 file name=/home/oracle/app/oracle/oradata/prod/users01.dbf

Starting recover at 12-NOV-11

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 1 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2011_11_11/o1_mf_1_1_7ct6vcqm_.arc

channel ORA_DISK_1: starting archived log restore to default destination

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=7

channel ORA_DISK_1: reading from backup piece /home/oracle/PROD/backupset/2011_11_11/o1_mf_annnn_TAG20111111T123702_7csldy4q_.bkp

channel ORA_DISK_1: piece handle=/home/oracle/PROD/backupset/2011_11_11/o1_mf_annnn_TAG20111111T123702_7csldy4q_.bkp tag=TAG20111111T123702

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

archived log file name=/home/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2011_11_12/o1_mf_1_7_7cw5t5c4_.arc thread=1 sequence=7

archived log file name=/home/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2011_11_11/o1_mf_1_1_7ct6vcqm_.arc thread=1 sequence=1

channel default: deleting archived log(s)

archived log file name=/home/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2011_11_12/o1_mf_1_7_7cw5t5c4_.arc RECID=9 STAMP=767017245

media recovery complete, elapsed time: 00:00:03

Finished recover at 12-NOV-11

Step:9

Try to open the db.

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of alter db command at 11/12/2011 12:21:44

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00704: bootstrap process failure

ORA-39700: database must be opened with UPGRADE option

Process ID: 20364

Session ID: 125 Serial number: 17

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

ORA-03114: not connected to ORACLE

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of alter db command at 11/12/2011 12:21:44

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00704: bootstrap process failure

ORA-39700: database must be opened with UPGRADE option

Process ID: 20364

Session ID: 125 Serial number: 17

[oracle@localhost ~]$

Step:10

SQL> startup upgrade

ORACLE instance started.

Total System Global Area 535662592 bytes

Fixed Size 1337720 bytes

Variable Size 163579528 bytes

Database Buffers 364904448 bytes

Redo Buffers 5840896 bytes

Database mounted.

Database opened.

SQL> select open_mode from v$database;

OPEN_MODE

--------------------

READ WRITE

SQL> select open_mode,log_mode from v$database;

OPEN_MODE LOG_MODE

-------------------- ------------

READ WRITE ARCHIVELOG

Sqlplus not open(error)

Sqlplus not open:

Error:
 [oracle@localhost bin]$ ./sqlplus
./sqlplus: error while loading shared libraries: /home/oracle/app/oracle/product/11.2.0/dbhome_1/lib/libclntsh.so.11.1: cannot restore segment prot after reloc: Permission denied

 Solution:


 disabled the SELinux using the root user ( echo 0 >/selinux/enforce) and being able to use the sqlplus .

[root@localhost bin]# echo 0 >/selinux/enforce

Dataguard in 11g

Manually Create a Physical Standby Database

Primary Database name:TEST3
Standby Database name:TEST4
Primary Database Side:
1. Create a password file if it doesn’t exist.
To check if a password file already exists, run the following command:
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS
----------------- ----- ----- -----
SYS TRUE TRUE FALSE
2) If it doesn’t exist, use the following command to create one:
Linux:$orapwd file=$ORACLE_HOME/dbs/orapw<db name> password=sys

3. Configure a Standby Redo log.
   The size of the standby redo log files should match the size of the current Primary database online redo log files. To find out the size of your online redo log files:
   SQL> select bytes from v$log;
   BYTES
   ----------
   52428800
   52428800
   52428800
4. Use the following command to determine your current log file groups:
   
   SQL> select group#, member from v$logfile;

GROUP# MEMBER

--------------------------------------------------------------------------------

3 /oracle/11gdb/oradata/TEST3/redo03.log

2 /oracle/11gdb/oradata/TEST3/redo02.log

1 /oracle/11gdb/oradata/TEST3/redo01.log

5. Create standby Redo log groups.
My primary database had 3 log file groups originally and I created 3 standby redo log groups using the following commands:

SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 50M;

6. To verify the results of the standby redo log groups creation, run the following query:
   SQL>select * from v$standby_log;
   
7. Enable Archiving on Primary.
   If your primary database is not already in Archive Log mode, enable the archive log mode:
   SQL>shutdown immediate;
   SQL>startup mount;
   SQL>alter database archivelog;
   SQL>alter database open;
   SQL>archive log list;
   5. Set Primary Database Initialization Parameters
   Create a text initialization parameter file (PFILE) from the server parameter file (SPFILE), to add the new primary role parameters.
   1) Create pfile from spfile for the primary database:
   
   SQL>create pfile from spfile;
   
8. Edit pfileTEST3.ora to add the new primary and standby role parameters:
   

Ex:initTEST3.ora

[oracle@track dbs]$ cat initTEST3.ora

TEST3.__db_cache_size=687865856

TEST3.__java_pool_size=16777216

TEST3.__large_pool_size=16777216

TEST3.__oracle_base='/oracle/11gdb'#ORACLE_BASE set from environment

TEST3.__pga_aggregate_target=687865856

TEST3.__sga_target=1006632960

TEST3.__shared_io_pool_size=0

TEST3.__shared_pool_size=268435456

TEST3.__streams_pool_size=0

*.audit_file_dest='/oracle/11gdb/admin/TEST3/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/oracle/11gdb/oradata/TEST3/control01.ctl','/oracle/11gdb/oradata/TEST3/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.DB_FILE_NAME_CONVERT='/oracle/11gdb/oradata/TEST4/','/oracle/11gdb/oradata/TEST3/'

*.db_name='TEST3'

*.db_recovery_file_dest_size=10737418240

*.db_recovery_file_dest='/oracle/11gdb/product/11.2.0/dbhome_1/flash_back'

*.db_unique_name='TEST3'

*.diagnostic_dest='/oracle/11gdb'

#*.dispatchers='(PROTOCOL=TCP) (SERVICE=TEST3XDB)'

*.FAL_CLIENT='TEST3'

*.FAL_SERVER='TEST4'

*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(TEST3,TEST4)'

*.LOG_ARCHIVE_DEST_1='LOCATION=/oracle/11gdb/product/11.2.0/dbhome_1/flash_back/TEST3/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=TEST3'

*.LOG_ARCHIVE_DEST_2='SERVICE=TEST4 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TEST4'

*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'

*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'

*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'

*.LOG_ARCHIVE_MAX_PROCESSES=30

*.LOG_FILE_NAME_CONVERT='/oracle/11gdb/oradata/TEST4/','/oracle/11gdb/oradata/TEST3/','/oracle/11gdb/product/11.2.0/dbhome_1/flash_back/TEST4/onlinelog','/oracle/11gdb/product/11.2.0/dbhome_1/flash_back/TEST3/onlinelog'

*.memory_target=1680867328

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.STANDBY_FILE_MANAGEMENT='AUTO'

*.undo_tablespace='UNDOTBS1'

*.local_listener=TEST3

[oracle@track dbs]$

9. Create spfile from pfile, and restart primary database using the new spfile.
   Data Guard must use SPFILE. Create the SPFILE and restart database.
   SQL> shutdown immediate;
   SQL> startup nomount pfile='<Path>'
   SQL> create spfile from pfile='<Path>';
   SQL>shutdown immediate;
   SQL>startup
   
10. Standby Database Site:
    1. Create a copy of Primary database data files on the Standby Server:
    On Primary DB:
    SQL>shutdown immediate;
    On Standby Server (While the Primary database is shut down):
    1) Create directory for data files.
    2) Copy the data files and temp files over.
    3) Create directory (multiplexing) for online logs,
    4) Copy the online logs over.
    5. Create a Control File for the standby database:
    On Primary DB, create a control file for the standby to use:
    SQL>startup mount;
    SQL>alter database create standby controlfile as ‘TEST4.ctl;
    SQL>ALTER DATABASE OPEN;
    6. Copy the Primary DB pfile to Standby server and rename/edit the file.
    

Ex:
[oracle@track dbs]$ cat initTEST4.ora
TEST4.__db_cache_size=687865856
TEST4.__java_pool_size=16777216
TEST4.__large_pool_size=16777216
TEST4.__oracle_base='/oracle/11gdb'#ORACLE_BASE set from environment
TEST4.__pga_aggregate_target=687865856
TEST4.__sga_target=1006632960
TEST4.__shared_io_pool_size=0
TEST4.__shared_pool_size=268435456
TEST4.__streams_pool_size=0
*.audit_file_dest='/oracle/11gdb/admin/TEST4/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/oracle/11gdb/oradata/TEST4/TEST4.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='TEST3'
*.db_recovery_file_dest_size=10737418240
*.db_recovery_file_dest='/oracle/11gdb/product/11.2.0/dbhome_1/flash_back'
*.diagnostic_dest='/oracle/11gdb'
#*.dispatchers='(PROTOCOL=TCP) (SERVICE=TEST4XDB)'
#*.memory_target=1680867328
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_unique_name=TEST4
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(TEST3,TEST4)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/oracle/11gdb/product/11.2.0/dbhome_1/flash_back/TEST4/archivelog/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=TEST4'



*.LOG_ARCHIVE_DEST_2='SERVICE=TEST3 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TEST3'



*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.remote_login_passwordfile='EXCLUSIVE'
*.FAL_SERVER=TEST3
*.FAL_CLIENT=TEST4
*.STANDBY_FILE_MANAGEMENT=AUTO



*.DB_FILE_NAME_CONVERT='/oracle/11gdb/oradata/TEST3/','/oracle/11gdb/oradata/TEST4/'
*.LOG_FILE_NAME_CONVERT='/oracle/11gdb/oradata/TEST3/','/oracle/11gdb/oradata/TEST4/','/oracle/11gdb/product/11.2.0/dbhome_1/flash_back/TEST3/onlinelog','/oracle/11gdb/product/11.2.0/dbhome_1/flash_back/TEST4/onlinelog'
*.local_listener=TEST4
[oracle@track dbs]$
7. On Standby server, create all required directories for dump and archived log destination:
Create directories adump, bdump, cdump, udump, and archived log destinations for the standby database.
8. Copy the standby control file ‘TEST4.ctl’ from primary to standby destinations ;
9. Copy the Primary password file to standby and rename it to pwdTEST4.ora.

Ex:
$oradim –NEW –SID STAN –STARTMODE manual

or

create a password file

Ex:

Linux:$orapwd file=$ORACLE_HOME/dbs/orapw<db name> password=sys

10. Configure listeners for the primary and standby databases.
On Primary system: use Oracle Net Manager to configure a listener for TEST3 and TEST4. Then restart the listener.

Ex:listener.ora

[oracle@track admin]$ cat listener.ora
# listener.ora Network Configuration File: /oracle/11gdb/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.



TEST3 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.108)(PORT = 1523))
)
)
SID_LIST_TEST3 =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME=/oracle/11gdb/product/11.2.0/dbhome_1)
(SID_NAME = TEST3)
)
)



TEST4 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.108)(PORT = 1524))
)
)



SID_LIST_TEST4 =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME=/oracle/11gdb/product/11.2.0/dbhome_1/)
(SID_NAME = TEST4)
)
)






ADR_BASE_TEST3 = /oracle/11gdb



[oracle@track admin]$

$lsnrctl stop TEST3

$lsnrctl start TEST3
11. On Standby server: use Net Manager to configure a listener for TEST3 and TEST4. Then restart the listener.
$lsnrctl stop TEST4

$lsnrctl start TEST4
12.Create Oracle Net service names.

Ex:tnsnames.ora

[oracle@track admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /oracle/11gdb/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.



TEST3 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.108)(PORT = 1523))
)
(CONNECT_DATA =
(SERVICE_NAME = TEST3)
(INSTANCE_NAME=TEST3)
)
)



TEST4 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.108)(PORT = 1524))
)
(CONNECT_DATA =
(SERVICE_NAME = TEST4)
(INSTANCE_NAME=TEST4)
)
)



[oracle@track admin]$
13. On Primary system: use Oracle Net Manager to create network service names for TEST3 and TEST4. Check tnsping to both services:
$tnsping TEST3
$tnsping TEST4
14.On Standby system: use Oracle Net Manager to create network service names for TEST3 and TEST4. Check tnsping to both services:
$tnsping TEST3
$tnsping TEST4
15. On Standby server, setup the environment variables to point to the Standby database.
Set up ORACLE_HOME and ORACLE_SID.
16. Start up nomount the standby database and generate a spfile.

SQL>startup nomount pfile=<path>

17. Start Redo apply
    On the standby database, to start redo apply:
    SQL>alter database recover managed standby database disconnect from session;
    If you ever need to stop log apply services:
    SQL> alter database recover managed standby database cancel;
    18. Verify the standby database is performing properly:
    On Standby perform a query:
    SQL>select sequence#, first_time, next_time from v$archived_log;
    On Primary, force a logfile switch:
    SQL>alter system switch logfile;
    19. On Standby, verify the archived redo log files were applied:
    SQL>select sequence#, applied from v$archived_log order by sequence#;
    20. If you want the redo data to be applied as it is received without waiting for the current standby redo log file to be archived, enable the real-time apply.
    To start real-time apply:
    SQL> alter database recover managed standby database using current logfile disconnect;
    

Switchover to Physical Standby Database

Primary Database Side:

Step 1 : Verify whether it is possible to perform a switchover

SQL> select switchover_status from v$database ;

SWITCHOVER_STATUS

--------------------

TO STANDBY

Step 2: Check the status of primary database.

SQL> select name,open_mode,db_unique_name from v$database;

NAME OPEN_MODE DB_UNIQUE_NAME

--------- -------------------- ------------------------------

TEST3 READ WRITE TEST3

Step 3: perform a log switch on the primary database

SQL> alter system switch logfile ;

System altered.

SQL> /

System altered.

SQL> /

System altered.

Step 4:Switch the Primary to Standby

SQL> alter database commit to switchover to physical standby;

Database altered.

SQL> select name,open_mode,db_unique_name from v$database;

select name,open_mode,db_unique_name from v$database

*

ERROR at line 1:

ORA-01507: database not mounted

SQL> shu immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

Step 5: Startup the database (mount)

SQL> startup mount

ORACLE instance started.

Total System Global Area 1690705920 bytes

Fixed Size 1336960 bytes

Variable Size 1426065792 bytes

Database Buffers 251658240 bytes

Redo Buffers 11644928 bytes

Database mounted.

Step 6: Check the databases status

SQL> select name,open_mode,db_unique_name from v$database;

NAME OPEN_MODE DB_UNIQUE_NAME

--------- -------------------- ------------------------------

TEST3 MOUNTED TEST3

SQL> select name,open_mode,db_unique_name,switchover_status from v$database;

NAME OPEN_MODE DB_UNIQUE_NAME SWITCHOVER_STATUS

--------- -------------------- ------------------------ --------------------

TEST3 MOUNTED TEST3 TO PRIMARY

SQL> alter database open;

Database altered.

SQL> select name,open_mode ,db_unique_name,switchover_status from v$database;

NAME OPEN_MODE DB_UNIQUE_NAME SWITCHOVER_STATUS

--------- -------------------- ------------------------------ --------------------

TEST3 READ ONLY TEST3 RECOVERY NEEDED

Standby Database Side:

Step 1: Check the databases status
SQL> select name,open_mode,db_unique_name from v$database;
NAME OPEN_MODE DB_UNIQUE_NAME
--------- -------------------- ------------------------------
TEST3 READ ONLY TEST4
Step 2:Switch the target physical standby database role to the primary role
SQL> alter database commit to switchover to primary ;
alter database commit to switchover to primary
*
ERROR at line 1:
ORA-16139: media recovery required
Step 3: Try to recover
SQL> alter database recover managed standby database disconnect from session;



Database altered.



SQL> alter database commit to switchover to primary ;
alter database commit to switchover to primary
*
ERROR at line 1:
ORA-16139: media recovery required



Step 4: Down the database
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.



Total System Global Area 150667264 bytes
Fixed Size 1335080 bytes
Variable Size 92274904 bytes
Database Buffers 50331648 bytes
Redo Buffers 6725632 bytes
Database mounted.
Database opened.
Step 5: Try to recover
SQL> alter database recover managed standby database disconnect from session;
Database altered.
Step 6:Switch the target physical standby database role to the primary role
SQL> alter database commit to switchover to primary ;
Database altered.
SQL> shu immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 150667264 bytes
Fixed Size 1335080 bytes
Variable Size 92274904 bytes
Database Buffers 50331648 bytes
Redo Buffers 6725632 bytes
Database mounted.
Database opened.
Step 7: Check the databases status
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE



SQL> select name,open_mode ,db_unique_name,switchover_status from v$database;
NAME OPEN_MODE DB_UNIQUE_NAME SWITCHOVER_STATUS
--------- -------------------- - ----------------------------- --------------------
TEST3         READ WRITE         TEST4                         TO STANDBY


--------------
SWITCHOVER_STATUS  output:

• NOT ALLOWED - Either this is a standby database and the primary database has not been switched first or this is a primary database and there are no standby databases.
  
• SESSIONS ACTIVE - Indicates that there are active SQL sessions attached to the primary or standby database that need to be disconnected before the switchover operation is permitted. Query the V$SESSION view to identify the specific processes that need to be terminated.
  
• SWITCHOVER PENDING - This is a standby database and the primary database switchover request has been received but not processed.
  
• SWITCHOVER LATENT - The switchover was in pending mode, but did not complete and went back to the primary database.
  
• TO PRIMARY - This is a standby database and is allowed to switch over to a primary database.
  
• TO STANDBY - This is a primary database and is allowed to switch over to a standby database.
  
• RECOVERY NEEDED - This is a standby database that has not received the switchover request.
  
• PREPARING SWITCHOVER - Either this is a primary database that is accepting redo data from a logical standby database in preparation for switch over to the logical standby database role, or it is a logical standby database sending redo data to a primary database and other standby databases in preparation for switch over to the primary database role. In the latter case, a completed dictionary has already been sent to the primary database and other standby databases.
  
• PREPARING DICTIONARY - This is a logical standby database that is sending redo data to a primary database and other standby databases in the configuration in preparation for switch over to the primary database role.
  
• TO LOGICAL STANDBY - This is a primary database that has received a complete dictionary from a logical standby database.