Sunday 29 July 2012

11g Dataguard Features

11g Dataguard Features:-

A. Setup dataguard using active database.

Normally when setting up datagurad using rman we need to copy the backup files to the
destination server and then we need to start using the duplicate command, but in
11g we dont need to copy the backup files to the destination server when creating
the standby databse using active database method.

Steps:-

1. Configure th listner.ora parameter to do a static registration of the
Standby database.

Eg:-

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rhel11gstdby.manzoor.com)(PORT = 1521))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = orclstdb )
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
)
)


2. Configure the tnsnames.ora file in target and the destination server accordingly.

3. Take the backup of the init parameter file from the target and modify it accordinly and transfer it to the destination server and modify it accordingly as per the destinaton database requirements.


4. Create the password file with same password as the target password.

5. startup the instance in nomount state in the destination server and exit the session.

6. In target connect to the target and the auxiliary database and execute the duplicate command.

[oracle@rhel11g u01]$ rman target sys/admin@orcl11g auxiliary sys/admin@orclstdb

Recovery Manager: Release 11.1.0.6.0 - Production on Mon Sep 12 09:45:07 2011

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: ORCL11G (DBID=867589313)
connected to auxiliary database: ORCL11G (not mounted)

RMAN> duplicate target database for standby from active database;

Starting Duplicate Db at 12-SEP-11
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=153 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=170 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=154 device type=DISK

contents of Memory Script:
{
backup as copy reuse
file '/u01/app/oracle/product/10.2.0/db_1/dbs/orapworcl11g' auxiliary format
'/u01/app/oracle/product/10.2.0/db_1/dbs/orapworclstdb' ;
}
executing Memory Script

Starting backup at 12-SEP-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=129 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=134 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=130 device type=DISK
Finished backup at 12-SEP-11

contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/orclstdb/control01.ctl';
restore clone controlfile to '/u01/app/oracle/oradata/orclstdb/control02.ctl' from
'/u01/app/oracle/oradata/orclstdb/control01.ctl';
restore clone controlfile to '/u01/app/oracle/oradata/orclstdb/control03.ctl' from
'/u01/app/oracle/oradata/orclstdb/control01.ctl';
sql clone 'alter database mount standby database';
}
executing Memory Script

Starting backup at 12-SEP-11
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/10.2.0/db_1/dbs/snapcf_orcl11g.f tag=TAG20110912T094518 RECID=2 STAMP=761651119
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 12-SEP-11

Starting restore at 12-SEP-11
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3

channel ORA_AUX_DISK_1: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_2: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_3: skipped, AUTOBACKUP already found
channel ORA_DISK_1: no AUTOBACKUP in 7 days found
channel ORA_DISK_2: no AUTOBACKUP in 7 days found
channel ORA_DISK_3: no AUTOBACKUP in 7 days found
channel clone_default: copied control file copy
Finished restore at 12-SEP-11

Starting restore at 12-SEP-11
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3

channel ORA_AUX_DISK_1: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_2: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_3: skipped, AUTOBACKUP already found
channel ORA_DISK_1: no AUTOBACKUP in 7 days found
channel ORA_DISK_2: no AUTOBACKUP in 7 days found
channel ORA_DISK_3: no AUTOBACKUP in 7 days found
channel clone_default: copied control file copy
Finished restore at 12-SEP-11

sql statement: alter database mount standby database

contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/orclstdb/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/orclstdb/system01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/oradata/orclstdb/sysaux01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/orclstdb/undotbs01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/orclstdb/users01.dbf";
set newname for datafile 5 to
"/u01/app/oracle/oradata/orclstdb/example01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u01/app/oracle/oradata/orclstdb/system01.dbf" datafile
2 auxiliary format
"/u01/app/oracle/oradata/orclstdb/sysaux01.dbf" datafile
3 auxiliary format
"/u01/app/oracle/oradata/orclstdb/undotbs01.dbf" datafile
4 auxiliary format
"/u01/app/oracle/oradata/orclstdb/users01.dbf" datafile
5 auxiliary format
"/u01/app/oracle/oradata/orclstdb/example01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/orclstdb/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 12-SEP-11
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl11g/system01.dbf
channel ORA_DISK_2: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/orcl11g/sysaux01.dbf
channel ORA_DISK_3: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl11g/example01.dbf
output file name=/u01/app/oracle/oradata/orclstdb/example01.dbf tag=TAG20110912T094549 RECID=0 STAMP=0
channel ORA_DISK_3: datafile copy complete, elapsed time: 00:02:05
channel ORA_DISK_3: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl11g/undotbs01.dbf
output file name=/u01/app/oracle/oradata/orclstdb/undotbs01.dbf tag=TAG20110912T094549 RECID=0 STAMP=0
channel ORA_DISK_3: datafile copy complete, elapsed time: 00:02:56
channel ORA_DISK_3: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl11g/users01.dbf
output file name=/u01/app/oracle/oradata/orclstdb/users01.dbf tag=TAG20110912T094549 RECID=0 STAMP=0
channel ORA_DISK_3: datafile copy complete, elapsed time: 00:00:25
output file name=/u01/app/oracle/oradata/orclstdb/sysaux01.dbf tag=TAG20110912T094549 RECID=0 STAMP=0
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:08:16
output file name=/u01/app/oracle/oradata/orclstdb/system01.dbf tag=TAG20110912T094549 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:08:26
Finished backup at 12-SEP-11

sql statement: alter system archive log current

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=761651617 file name=/u01/app/oracle/oradata/orclstdb/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=761651617 file name=/u01/app/oracle/oradata/orclstdb/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=761651617 file name=/u01/app/oracle/oradata/orclstdb/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=761651618 file name=/u01/app/oracle/oradata/orclstdb/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=6 STAMP=761651618 file name=/u01/app/oracle/oradata/orclstdb/example01.dbf
Finished Duplicate Db at 12-SEP-11

RMAN> exit;


7. Now do a log switch in the primary and check whether the logs are applied correctly.






B) Active Datagurad:-

Prior to 11g the logs will not be applied in physical standby database if it is open in readonly mode, but from 11g the logs will get applied even when the standby database is in read only mode.

Eg:-

SQL> select sequence#, archived, applied from V$archived_log;

SEQUENCE# ARC APP
---------- --- ---
12 YES YES
8 YES YES
11 YES YES
9 YES YES
10 YES YES
13 YES YES
14 YES YES
15 YES YES
18 YES YES
17 YES YES
16 YES YES
19 YES YES
20 YES YES
21 YES YES
22 YES YES
23 YES YES
24 YES YES
25 YES YES
26 YES YES
27 YES YES
28 YES YES
29 YES YES

22 rows selected.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

Now do some logswith in the priarmy database....and the check whether the logs are applied in the standby databse.

SQL> select sequence#, archived, applied from V$archived_log;

SEQUENCE# ARC APP
---------- --- ---
12 YES YES
8 YES YES
11 YES YES
9 YES YES
10 YES YES
13 YES YES
14 YES YES
15 YES YES
18 YES YES
17 YES YES
16 YES YES
19 YES YES
20 YES YES
21 YES YES
22 YES YES
23 YES YES
24 YES YES
25 YES YES
26 YES YES
27 YES YES
28 YES YES
29 YES YES
30 YES YES
31 YES YES

24 rows selected.

We can see that the logs are getting applied even when the physical standby database
in the read only mode.

C) Snap shot standby:-


Introduced in 11g, snapshot standby allows the standby database to be opened in read-write mode. When switched back into standby mode, all changes made whilst in read-write mode are lost. This is achieved using flashback database, but the standby database does not need to have flashback database explicitly enabled to take advantage of this feature, thought it works just the same if it is.

stop the mrp process

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database convert to snapshot standby;

Database altered.

SQL> shut immediate;
ORA-01507: database not mounted
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 422670336 bytes
Fixed Size 1300352 bytes
Variable Size 360712320 bytes
Database Buffers 54525952 bytes
Redo Buffers 6131712 bytes
Database mounted.
Database opened.

SQL> select flashback_on from V$database;

FLASHBACK_ON
------------------
RESTORE POINT ONLY

--- Do any transaction as per requirement.........

SQL> create user layana identified by fathima;

User created.

---
---

once the transactions are completed shut down the database and convert that to
physical standby database, once you shutdown the database all the transaction
will be lost. 

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 422670336 bytes
Fixed Size 1300352 bytes
Variable Size 360712320 bytes
Database Buffers 54525952 bytes
Redo Buffers 6131712 bytes
Database mounted.
SQL> alter database convert to physical standby;

Database altered.

--Once the above commands executes the database will be automatically dismounted and instance
will be in start state.

SQL> select status from V$instance;

STATUS
------------
STARTED

SQL> shut immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 422670336 bytes
Fixed Size 1300352 bytes
Variable Size 360712320 bytes
Database Buffers 54525952 bytes
Redo Buffers 6131712 bytes
Database mounted.
SQL> alter database recover managed standby database disconnect from session;

Database altered.

No comments:

Post a Comment