Thursday, May 2, 2013

Multiplexing Archive and Online Logs in primary server in a DataGuard Environment With Simulating Missing Destinations

Hello everyone,

In this post, i will multiplex archivelog destinations in a simple dataguard environment. In addition to that, i will multiplex online logs to remote disks.


Let's start.

Before starting operations on log files, i want to change my Fast Recovery Area location to remote disk which i mounted recently.

First check current destinations and log files

[oracle@primarydb ~]$ sqlplus "sys as sysdba"
...
SQL> show parameter archive
...
log_archive_dest_1 string location=/data/archive/ valid_for=(all_logfiles,all_roles) db_unique_name=PRIDB
...
log_archive_dest_2 string SERVICE=SBYDB LGWR ASYNC NOAFFIRM REGISTER VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=SBYDB
... db_recovery_file_dest     string /data/flash_recovery_area

...

SQL> select GROUP#,THREAD#,SEQUENCE#,status,archived from v$log;

    GROUP#    THREAD#  SEQUENCE# STATUS  ARC
---------- ---------- ---------- ---------------- ---
        1    1      46       INACTIVE  YES
        2    1      47       INACTIVE  YES
        3    1      48       CURRENT  NO


SQL> select GROUP#,type,member from v$logfile;

    GROUP# TYPE    MEMBER
---------- ------- ----------------------------------------------
3 ONLINE  /data/oradata/DB/redo03.log
2 ONLINE  /data/oradata/DB/redo02.log
1 ONLINE  /data/oradata/DB/redo01.log
4 STANDBY /data/oradata/DB/s_redo04.log
5 STANDBY /data/oradata/DB/s_redo05.log
6 STANDBY /data/oradata/DB/s_redo06.log
7 STANDBY /data/oradata/DB/s_redo07.log

First, i will change my Fast Recovery Area to new disk which mounted as /fra,

(Here oracle user must have read and write permissions on /fra)

[oracle@primarydb ~]$ df -alh
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda3              30G   15G   15G  51% /
proc                     0     0     0   -  /proc
sysfs                    0     0     0   -  /sys
devpts                   0     0     0   -  /dev/pts
tmpfs                 8.0G  3.6G  4.5G  45% /dev/shm
/dev/sda1              53G   22G   29G  44% /data
none                     0     0     0   -  /proc/sys/fs/binfmt_misc
sunrpc                   0     0     0   -  /var/lib/nfs/rpc_pipefs
/dev/sdb1              50G  180M   47G   1% /fra

First create Fast Recovery Area location under disk

[oracle@primarydb ~]$ mkdir -p /fra/flash_recovery_area


SQL> show parameter recovery

NAME     TYPE VALUE

------------------------------------ ----------- ------------------------------
db_recovery_file_dest     string /data/flash_recovery_area
db_recovery_file_dest_size     big integer 7201350K
recovery_parallelism     integer 0
SQL> alter system set db_recovery_file_dest='/fra/flash_recovery_area';

System altered.

SQL> alter system set db_recovery_file_dest_size=45G;

System altered.

SQL> show parameter recovery
NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest     string /fra/flash_recovery_area
db_recovery_file_dest_size     big integer 45G
recovery_parallelism     integer 0

Now, i can send one copy of my archive logs to that disk. 

First define the configuration for archivelogs

1- A destination under  physically different disk /fra
2- To standby machine
3- A destination locally resides in same disk with datafiles

For this purpose, i have to set  3 log archive destinations. As you can see the current configuration enables me the options 2 & 3. In order to complete the configuration i have to do option 1.

First create remote archive destination and then set it in the database

[oracle@primarydb ~]$ mkdir -p /fra/archive

SQL> alter system set log_archive_dest_3='location=/fra/archive';

System altered.

SQL>  alter system set log_archive_dest_state_3='enable';

System altered.

SQL> show parameter archive
...
log_archive_dest_3     string location=/fra/archive
...
log_archive_dest_state_3     string enable
....

Now test it if archivelogs are written or not, first force several archive events. 

SQL> alter system switch logfile;

System altered.

Check log archive destinations

[oracle@primarydb ~]$ ls -ltrh /data/archive/
....
-rw-r----- 1 oracle oinstall 9.5K Apr 24 15:44 1_47_813428522.dbf
-rw-r----- 1 oracle oinstall 9.7M Apr 24 18:46 1_48_813428522.dbf
-rw-r----- 1 oracle oinstall 1.5K Apr 24 18:46 1_49_813428522.dbf
-rw-r----- 1 oracle oinstall 1.5K Apr 24 18:46 1_50_813428522.dbf
-rw-r----- 1 oracle oinstall 1.0K Apr 24 18:46 1_51_813428522.dbf

[oracle@primarydb ~]$ ls -ltrh /fra/archive/

-rw-r----- 1 oracle oinstall 9.7M Apr 24 18:46 1_48_813428522.dbf
-rw-r----- 1 oracle oinstall 1.5K Apr 24 18:46 1_49_813428522.dbf
-rw-r----- 1 oracle oinstall 1.5K Apr 24 18:46 1_50_813428522.dbf
-rw-r----- 1 oracle oinstall 1.0K Apr 24 18:46 1_51_813428522.dbf

Now check standby location in where archive logs must be :)

[oracle@sbydb ~]$ ls -ltrh /data/archive/
...
-rw-r----- 1 oracle oinstall 9.5K Apr 24 15:44 1_47_813428522.dbf
-rw-r----- 1 oracle oinstall 9.7M Apr 24 18:46 1_48_813428522.dbf
-rw-r----- 1 oracle oinstall 1.5K Apr 24 18:46 1_49_813428522.dbf
-rw-r----- 1 oracle oinstall 1.5K Apr 24 18:46 1_50_813428522.dbf
-rw-r----- 1 oracle oinstall 1.0K Apr 24 18:46 1_51_813428522.dbf

Ok, it seems ok . Now let me check what happens if one destination disappears. 
Let me unmount /fra disk and see what happens.

[root@primarydb ~]# umount -l /dev/sdb1 
[root@primarydb ~]# df -alh
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda3              30G   15G   15G  51% /
proc                     0     0     0   -  /proc
sysfs                    0     0     0   -  /sys
devpts                   0     0     0   -  /dev/pts
tmpfs                 8.0G  3.6G  4.5G  45% /dev/shm
/dev/sda1              53G   22G   29G  44% /data
none                     0     0     0   -  /proc/sys/fs/binfmt_misc
sunrpc                   0     0     0   -  /var/lib/nfs/rpc_pipefs
Here is no  /fra entry 

And force an archive event 

SQL> alter system switch logfile;

System altered.

Let me check destinations again

[oracle@primarydb ~]$ ls -ltrh /data/archive/
....
-rw-r----- 1 oracle oinstall 9.5K Apr 24 15:44 1_47_813428522.dbf
-rw-r----- 1 oracle oinstall 9.7M Apr 24 18:46 1_48_813428522.dbf
-rw-r----- 1 oracle oinstall 1.5K Apr 24 18:46 1_49_813428522.dbf
-rw-r----- 1 oracle oinstall 1.5K Apr 24 18:46 1_50_813428522.dbf
-rw-r----- 1 oracle oinstall 1.0K Apr 24 18:46 1_51_813428522.dbf
-rw-r----- 1 oracle oinstall 191K Apr 24 18:52 1_52_813428522.dbf


[oracle@sbydb ~]$ ls -ltrh /data/archive/
...
-rw-r----- 1 oracle oinstall 9.5K Apr 24 15:44 1_47_813428522.dbf
-rw-r----- 1 oracle oinstall 9.7M Apr 24 18:46 1_48_813428522.dbf
-rw-r----- 1 oracle oinstall 1.5K Apr 24 18:46 1_49_813428522.dbf
-rw-r----- 1 oracle oinstall 1.5K Apr 24 18:46 1_50_813428522.dbf
-rw-r----- 1 oracle oinstall 1.0K Apr 24 18:46 1_51_813428522.dbf
-rw-r----- 1 oracle oinstall 191K Apr 24 18:52 1_52_813428522.dbf

Meanwhile in alert log of primarydb,

...
Wed Apr 24 18:52:22 2013
Thread 1 advanced to log sequence 53 (LGWR switch)
  Current log# 2 seq# 53 mem# 0: /data/oradata/DB/redo02.log
Wed Apr 24 18:52:22 2013
ARC0: Error 19504 Creating archive log file to '/fra/archive1_52_813428522.dbf'
Archived Log entry 30 added for thread 1 sequence 52 ID 0x20a4f2b0 dest 1:
Wed Apr 24 18:52:22 2013
LNS: Standby redo logfile selected for thread 1 sequence 53 for destination LOG_ARCHIVE_DEST_2
...

Here one of the archive log destinations is missing but system does not crash as we expected.

Now remount fra location and see if archiving continues again to remote destination

[root@primarydb ~]# mount /dev/sdb1 /fra/

And force an archive event 

SQL> alter system switch logfile;

System altered.

[oracle@primarydb ~]$ ls -ltrh /data/archive/
...
-rw-r----- 1 oracle oinstall 1.5K Apr 24 18:46 1_49_813428522.dbf
-rw-r----- 1 oracle oinstall 1.5K Apr 24 18:46 1_50_813428522.dbf
-rw-r----- 1 oracle oinstall 1.0K Apr 24 18:46 1_51_813428522.dbf
-rw-r----- 1 oracle oinstall 191K Apr 24 18:52 1_52_813428522.dbf
-rw-r----- 1 oracle oinstall 136K Apr 24 18:56 1_53_813428522.dbf
-rw-r----- 1 oracle oinstall  18K Apr 24 18:57 1_54_813428522.dbf

[oracle@primarydb ~]$ ls -ltrh /fra/archive/
total 9.7M
-rw-r----- 1 oracle oinstall 9.7M Apr 24 18:46 1_48_813428522.dbf
-rw-r----- 1 oracle oinstall 1.5K Apr 24 18:46 1_49_813428522.dbf
-rw-r----- 1 oracle oinstall 1.5K Apr 24 18:46 1_50_813428522.dbf
-rw-r----- 1 oracle oinstall 1.0K Apr 24 18:46 1_51_813428522.dbf

As you can see the new archivelogs are not shipped this location. At this time, we have to reenable state of this log destination so that Oracle starts log shipping again.

SQL> alter system set log_archive_dest_state_3=enable;

System altered.

SQL> alter system switch logfile;

System altered.

Meanwhile in alert log of primarydb,

...
Wed Apr 24 18:59:35 2013
ALTER SYSTEM SET log_archive_dest_state_3='ENABLE' SCOPE=BOTH;
...

[oracle@primarydb ~]$ ls -ltrh /data/archive/
...
-rw-r----- 1 oracle oinstall 1.5K Apr 24 18:46 1_49_813428522.dbf
-rw-r----- 1 oracle oinstall 1.5K Apr 24 18:46 1_50_813428522.dbf
-rw-r----- 1 oracle oinstall 1.0K Apr 24 18:46 1_51_813428522.dbf
-rw-r----- 1 oracle oinstall 191K Apr 24 18:52 1_52_813428522.dbf
-rw-r----- 1 oracle oinstall 136K Apr 24 18:56 1_53_813428522.dbf
-rw-r----- 1 oracle oinstall  18K Apr 24 18:57 1_54_813428522.dbf
-rw-r----- 1 oracle oinstall  98K Apr 24 19:00 1_55_813428522.dbf

[oracle@primarydb ~]$ ls -ltrh /fra/archive/
...
-rw-r----- 1 oracle oinstall 9.7M Apr 24 18:46 1_48_813428522.dbf
-rw-r----- 1 oracle oinstall 1.5K Apr 24 18:46 1_49_813428522.dbf
-rw-r----- 1 oracle oinstall 1.5K Apr 24 18:46 1_50_813428522.dbf
-rw-r----- 1 oracle oinstall 1.0K Apr 24 18:46 1_51_813428522.dbf
-rw-r----- 1 oracle oinstall  98K Apr 24 19:00 1_55_813428522.dbf

Here we can see that log shipping started again to remote destination.

Now we are ready to multiplex online logs to a remote destination. 

First create a remote log destination,

[oracle@primarydb ~]$ mkdir -p /fra/remotelog/

Now add every redo log group a new member which resides in remotelog directory.


SQL> select GROUP#,THREAD#,SEQUENCE#,status,archived from v$log;

    GROUP#    THREAD#  SEQUENCE# STATUS  ARC
---------- ---------- ---------- ---------------- ---
1    1      55 INACTIVE  YES
2    1      56 CURRENT  NO
3    1      54 INACTIVE  YES

SQL> select GROUP#,type,member from v$logfile;

    GROUP# TYPE    MEMBER
---------- ------- --------------------------------------------------------------
         3 ONLINE  /data/oradata/DB/redo03.log
2 ONLINE  /data/oradata/DB/redo02.log
1 ONLINE  /data/oradata/DB/redo01.log
4 STANDBY /data/oradata/DB/s_redo04.log
5 STANDBY /data/oradata/DB/s_redo05.log
6 STANDBY /data/oradata/DB/s_redo06.log
7 STANDBY /data/oradata/DB/s_redo07.log

7 rows selected.

SQL> ALTER DATABASE ADD LOGFILE MEMBER '/fra/remotelog/redo011.log' to group 1;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE MEMBER '/fra/remotelog/redo022.log' to group 2;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE MEMBER '/fra/remotelog/redo033.log' to group 3;

Database altered.

SQL> select GROUP#,type,member from v$logfile;

    GROUP# TYPE    MEMBER
---------- ------- --------------------------------------------------------------           
         3 ONLINE  /data/oradata/DB/redo03.log
2 ONLINE  /data/oradata/DB/redo02.log
1 ONLINE  /data/oradata/DB/redo01.log
4 STANDBY /data/oradata/DB/s_redo04.log
5 STANDBY /data/oradata/DB/s_redo05.log
6 STANDBY /data/oradata/DB/s_redo06.log
7 STANDBY /data/oradata/DB/s_redo07.log
1 ONLINE  /fra/remotelog/redo011.log
2 ONLINE  /fra/remotelog/redo022.log
3 ONLINE  /fra/remotelog/redo033.log

10 rows selected.

SQL> select GROUP#,THREAD#,SEQUENCE#,status,archived from v$log;

    GROUP#    THREAD#  SEQUENCE# STATUS  ARC
---------- ---------- ---------- ---------------- ---
1    1      55 INACTIVE  YES
2    1      56 CURRENT  NO
3    1      54 INACTIVE  YES


Meanwhile in alert log of primarydb,
...

ALTER DATABASE ADD LOGFILE MEMBER '/fra/remotelog/redo011.log' to group 1
Completed: ALTER DATABASE ADD LOGFILE MEMBER '/fra/remotelog/redo011.log' to group 1
Wed Apr 24 19:07:34 2013
ALTER DATABASE ADD LOGFILE MEMBER '/fra/remotelog/redo022.log' to group 2
Completed: ALTER DATABASE ADD LOGFILE MEMBER '/fra/remotelog/redo022.log' to group 2
Wed Apr 24 19:07:46 2013
ALTER DATABASE ADD LOGFILE MEMBER '/fra/remotelog/redo033.log' to group 3
Completed: ALTER DATABASE ADD LOGFILE MEMBER '/fra/remotelog/redo033.log' to group 3
...

Now force several archive event 

SQL> alter system switch logfile;

System altered.

Meanwhile in alert log of primarydb,
...

Wed Apr 24 19:10:13 2013
LNS: Standby redo logfile selected for thread 1 sequence 61 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 62 (LGWR switch)
  Current log# 2 seq# 62 mem# 0: /data/oradata/DB/redo02.log
  Current log# 2 seq# 62 mem# 1: /fra/remotelog/redo022.log
Archived Log entry 55 added for thread 1 sequence 61 ID 0x20a4f2b0 dest 1:
Archived Log entry 56 added for thread 1 sequence 61 ID 0x20a4f2b0 dest 3:
LNS: Standby redo logfile selected for thread 1 sequence 62 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 63 (LGWR switch)
  Current log# 3 seq# 63 mem# 0: /data/oradata/DB/redo03.log
  Current log# 3 seq# 63 mem# 1: /fra/remotelog/redo033.log
Archived Log entry 58 added for thread 1 sequence 62 ID 0x20a4f2b0 dest 1:
Archived Log entry 59 added for thread 1 sequence 62 ID 0x20a4f2b0 dest 3:
LNS: Standby redo logfile selected for thread 1 sequence 63 for destination LOG_ARCHIVE_DEST_

...

Here we can see that Oracle started to write both locations as we expected.

Now lets simulate what happens if one of the member of current logfiles disappears.


SQL> select GROUP#,THREAD#,SEQUENCE#,status,archived from v$log;

    GROUP#    THREAD#  SEQUENCE# STATUS  ARC
---------- ---------- ---------- ---------------- ---
1    1      61 INACTIVE  YES
2    1      62 INACTIVE  YES
3    1      63 CURRENT  NO

Now let me remove redo033.log of group 3 . And force several archive event

SQL> !
[oracle@primarydb ~]$ cd /fra/remotelog/
[oracle@primarydb remotelog]$ ls
redo011.log  redo022.log  redo033.log
[oracle@primarydb remotelog]$ mv redo033.log redo033.log.corrupt
[oracle@primarydb remotelog]$ exit

SQL> 
SQL> alter system switch logfile;

System altered.

SQL>  alter system switch logfile

System altered.

Meanwhile in alert log of primarydb,
...
Wed Apr 24 19:14:56 2013
Errors in file /home/oracle/app/oracle/admin/DB/bdump/diag/rdbms/pridb/DB/trace/DB_m000_5050.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/fra/remotelog/redo033.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Checker run found 1 new persistent data failures
Thread 1 advanced to log sequence 65 (LGWR switch)
  Current log# 2 seq# 65 mem# 0: /data/oradata/DB/redo02.log
  Current log# 2 seq# 65 mem# 1: /fra/remotelog/redo022.log
Wed Apr 24 19:15:02 2013
Archived Log entry 63 added for thread 1 sequence 64 ID 0x20a4f2b0 dest 1:
Archived Log entry 64 added for thread 1 sequence 64 ID 0x20a4f2b0 dest 3:
LNS: Standby redo logfile selected for thread 1 sequence 65 for destination LOG_ARCHIVE_DEST_2
...

SQL> select GROUP#,type,member from v$logfile;

    GROUP# TYPE    MEMBER
---------- ------- ----------------------------------------------------------------------------------------------------------------
3 ONLINE  /data/oradata/MERKEZ/redo03.log
2 ONLINE  /data/oradata/MERKEZ/redo02.log
1 ONLINE  /data/oradata/MERKEZ/redo01.log
4 STANDBY /data/oradata/MERKEZ/s_redo04.log
5 STANDBY /data/oradata/MERKEZ/s_redo05.log
6 STANDBY /data/oradata/MERKEZ/s_redo06.log
7 STANDBY /data/oradata/MERKEZ/s_redo07.log
1 ONLINE  /fra/remotelog/redo011.log
2 ONLINE  /fra/remotelog/redo022.log
3 ONLINE  /fra/remotelog/redo033.log

Oracle still tries to write missing logfile. so i have to drop that file and new log member to group 2.

SQL> ALTER DATABASE DROP LOGFILE MEMBER '/fra/remotelog/redo033.log';

Database altered.

SQL> ALTER DATABASE ADD LOGFILE MEMBER '/fra/remotelog/redo033.log' to group 3;

Database altered.


And force several archive event


SQL> alter system switch logfile;

System altered.


Meanwhile in alert log of primarydb,
...

Wed Apr 24 19:18:19 2013
ALTER DATABASE DROP LOGFILE MEMBER '/fra/remotelog/redo033.log'
Completed: ALTER DATABASE DROP LOGFILE MEMBER '/fra/remotelog/redo033.log'
Wed Apr 24 19:18:39 2013
ALTER DATABASE ADD LOGFILE MEMBER '/fra/remotelog/redo033.log' to group 3
Completed: ALTER DATABASE ADD LOGFILE MEMBER '/fra/remotelog/redo033.log' to group 3
...
Wed Apr 24 19:18:55 2013
Thread 1 advanced to log sequence 66 (LGWR switch)
  Current log# 3 seq# 66 mem# 0: /data/oradata/MERKEZ/redo03.log
  Current log# 3 seq# 66 mem# 1: /fra/remotelog/redo033.log
Wed Apr 24 19:18:55 2013
Archived Log entry 66 added for thread 1 sequence 65 ID 0x20a4f2b0 dest 1:
Archived Log entry 67 added for thread 1 sequence 65 ID 0x20a4f2b0 dest 3:
Wed Apr 24 19:18:56 2013
LNS: Standby redo logfile selected for thread 1 sequence 66 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 67 (LGWR switch)
  Current log# 1 seq# 67 mem# 0: /data/oradata/MERKEZ/redo01.log
  Current log# 1 seq# 67 mem# 1: /fra/remotelog/redo011.log
....

Here as you can see, Oracle now writes the newly added logfile and succesfully rotates redo log groups. 

If all redo log members of current group disappears, you will have an terrible headache. 

So it will be good practice to make one copy to physically different disks.

Thanks for reading :)

No comments :

Post a Comment