Header

Wednesday, March 29, 2017

Local Archiving In Oracle RAC

In a
cluster database we can store archive-logs on

1) shared
location e.g. ASM OR

2) Local archive log destination for each instance

If archive logs are locally stored and an instance is trying to
perform recovery, it will need logs from all the instances. Hence, it is
recommended that local archive log destinations be created for each instance
with NFS-read mount points to all other instances. This is known as the local
archive with network file system (NFS) scheme.

During
recovery, one instance may access the logs from any host without having to
first copy them to the local destination.

SYS@orcl2>alter system set
log_archive_dest_2='location=/home/oracle/arch2' scope=both sid='orcl2';

System altered.

SYS@orcl2>alter system switch logfile;

System altered.

SYS@orcl2>alter system switch logfile;

System altered.

[oracle@rac2 arch2]$ pwd

/home/oracle/arch2

[oracle@rac2 arch2]$ ls -ltr

total 2500

-rw-r----- 1 oracle oinstall 2545664 Mar 29 07:13
2_78_936514658.dbf

-rw-r----- 1 oracle oinstall 1536 Mar 29 07:13 2_79_936514658.dbf

-rw-r----- 1 oracle oinstall 4096 Mar 29 07:13 2_80_936514658.dbf

SYS@orcl1>alter system switch logfile;

System altered.

SYS@orcl1>/

System altered.

SYS@orcl1>!ls -ltr

total 11820

-rw-r----- 1 oracle oinstall 12051968 Mar 29 07:13
1_90_936514658.dbf

-rw-r----- 1 oracle oinstall 25088 Mar 29 07:15 1_91_936514658.dbf

-rw-r----- 1 oracle oinstall 3072 Mar 29 07:15 1_92_936514658.dbf

SYS@orcl1>alter tablespace arch_test offline immediate;

Tablespace altered.

SYS@orcl1>alter system checkpoint;

System altered.

SYS@orcl1>alter system switch logfile;

System altered.

SYS@orcl2>alter system switch logfile;

System altered.

If we
try to change the tablespace online then we need to perform recovery and that
recovery will get fail if archive locations are local and not shareable. Because node1 will required the archive logs of node2 as well.

Let's
try to use NFS scheme to perform recovery successful.

# Make
the folders containing archived logs on node2 sharable and start portmap and
nfs service

[root@rac2 ~]# vi /etc/exports

# add this line "/home/oracle/arch2 *(rw,sync)"

[root@rac2 ~]# cat /etc/exports

/home/oracle/arch2 *(rw,sync)

# start portmap and nfs service on node2

[root@rac2 ~]# service portmap restart

Stopping portmap: [
OK ]

Starting portmap: [ OK ]

[root@rac2 ~]# service nfs restart

Shutting down NFS mountd: [FAILED]

Shutting down NFS daemon: [FAILED]

Shutting down NFS quotas: [FAILED]

Shutting down NFS services: [FAILED]

Starting NFS services: [ OK ]

Starting NFS quotas: [
OK ]

Starting NFS daemon: [ OK ]

Starting NFS mountd: [ OK ]

#
On node1, create folders where archive log folders from node2 will be mounted

rac01$mkdir /home/oracle/arch2

#As
root user on node1, start portmap and
nfs service Mount the archive
folders on node2

[root@rac1 ~]# service portmap restart

Stopping portmap:
[ OK ]

Starting portmap:
[ OK ]

[root@rac1 ~]#
service nfs restart

Shutting down NFS mountd: [FAILED]

Shutting down NFS daemon: [FAILED]

Shutting down NFS quotas: [FAILED]

Shutting down NFS services: [FAILED]

Starting NFS services: [ OK ]

Starting NFS quotas: [ OK ]

Starting NFS daemon: [ OK ]

Starting NFS mountd: [ OK ]

[root@rac1 ~]# mount rac2:/home/oracle/arch2 /home/oracle/arch2

#
Check that archivelogs on node2

[root@rac1 ~]# su - oracle

[oracle@rac1 ~]$ cd /home/oracle/arch2

[oracle@rac1 arch2]$ ls -ltr

total 2504

-rw-r----- 1 oracle oinstall 2545664 Mar 29 07:13
2_78_936514658.dbf

-rw-r----- 1 oracle oinstall 1536 Mar 29 07:13 2_79_936514658.dbf

-rw-r----- 1 oracle oinstall 4096 Mar 29 07:13 2_80_936514658.dbf

-rw-r----- 1 oracle oinstall 2048 Mar 29 07:15 2_81_936514658.dbf

#Try to bring example tablespace
online – Needs media recovery

SYS@orcl1>alter tablespace arch_test online;

alter tablespace arch_test online

*

ERROR at line 1:

ORA-01113: file 8 needs media recovery

ORA-01110: data file 8:
'+DATA/orcl/datafile/arch_test.434.939886351'

# Recover
example tablespace from node1 – it'll complete successfully as archive logs
from node2 will be available to node1 as well.