Use rman to build the OracleDataGuard Environment

DataGuard environment: Operating System: RedHat5.6Primary Database: IP Address: 192.168.48.131 database SID: orclDB_UNIQUE_NAME: orcl_pdStandb

Data Guard environment: Operating System: RedHat 5.6 Primary database: IP Address: 192.168.48.131 database SID: orclDB_UNIQUE_NAME: orcl_pdStandb

Data Guard environment:
OS: RedHat 5.6
Primary database:
IP Address: 192.168.48.131
Database SID: orcl
DB_UNIQUE_NAME: orcl_pd

Standby database:
IP Address: 192.168.48.132
Database SID: orcl
DB_UNIQUE_NAME: orcl_st




1. Set the master database to force logging mode.
SQL> alter database force logging;


Database altered.


2. Set the master database to archive
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination/u01/Oracle/product/dbs/arch
Oldest online log sequence 4
Current log sequence 6
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.


Total System Global Area 1653518336 bytes
Fixed Size 2228904 bytes
Variable Size 956304728 bytes
Database Buffers 687865856 bytes
Redo Buffers 7118848 bytes
Database mounted.
SQL> alter database archivelog;


Database altered.


SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination/u01/oracle/product/dbs/arch
Oldest online log sequence 4
Next log sequence to archive 6
Current log sequence 6


SQL> create spfile from pfile = '/u01/oracle/product/dbs/initorcl. ora ';
3. Add redo log file


SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/u01/oracle/oradata/orcl/redo04.log') size 50 M;


Database altered.


SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u01/oracle/oradata/orcl/redo05.log') size 50 M;


Database altered.


SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u01/oracle/oradata/orcl/redo06.log') size 50 M;


Database altered.


SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u01/oracle/oradata/orcl/redo07.log') size 50 M;


Database altered.




4. Create a password file and a control file for the slave Database

SQL> alter database create standby controlfile as '/u01/control01.ctl ';


Database altered.


You do not need to create
[Oracle @ dg1 ~] $ Orapwd file =/u01/orapworcl password = wwwwww


[Oracle @ dg1 u01] $ scp orapworcl 192.168.48.132:/u01/oracle/product/dbs/
The authenticity of host '192. 168.48.132 (192.168.48.132) 'can't be established.
RSA key fingerprint is ed: c9: 32: 48: 57: 38: 60: f3: a3: 9f: f1: e9: 89: 04: 3a: 46.
Are you sure you want to continue connecting (yes/no )? Yes
Warning: Permanently added '192. 168.48.132 '(RSA) to the list of known hosts.
Oracle@192.168.48.132's password:
Orapworcl 100% 1536 1.5KB/s




The default position of Pfile is in the $ ORACLE_HOME/dbs directory. You can also specify the position as follows:
SQL> create pfile from spfile;


File created.






5. Modify the initialization parameter file
[Root @ dg1 admin] # cd/u01/oracle/product/dbs
Add the following content in initorcl. ora:
#### Master database parameters ######
*. DB_NAME = 'orcl ';
# --
*. DB_UNIQUE_NAME = 'orcl _ pd'
# -- List all DB_UNIQUE_NAME in DG
*. LOG_ARCHIVE_CONFIG = 'dg _ CONFIG = (orcl_pd, orcl_st )'
*. LOG_ARCHIVE_DEST_1 = 'location =/u01/archive VALID_FOR = (ALL_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME = orcl_pd'
*. LOG_ARCHIVE_DEST_2 = 'service = orcl_st DB_UNIQUE_NAME = orcl_st'


*. LOG_ARCHIVE_DEST_STATE_1 = ENABLE
*. LOG_ARCHIVE_DEST_STATE_2 = ENABLE
*. REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE
##### Slave database parameters #######
*. FAL_SERVER = orcl_st
*. FAL_CLIENT = orcl_pd
*. Standby_file_management = 'auto'
*. Standby_archive_dest = '/u01/archive'








6. Start the database with the modified pfile and generate the spfile.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup pfile =? /Dbs/initorcl. ora
ORACLE instance started.
Total System Global Area 247463936 bytes
Fixed Size 1218748 bytes
Variable Size 75499332 bytes
Database Buffers 163577856 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
SQL>! Echo $ ORACLE_HOME
/U01/oracle/product
SQL> create spfile from pfile = '/u01/oracle/product/dbs/initorcl. ora ';
File created.


SQL> startup pfile =? /Dbs/initorcl. ora
ORA-32006: STANDBY_ARCHIVE_DEST initialization parameter has been deprecated
ORACLE instance started.


Total System Global Area 1653518336 bytes
Fixed Size 2228904 bytes
Variable Size 956304728 bytes
Database Buffers 687865856 bytes
Redo Buffers 7118848 bytes
Database mounted.
Database opened.


SQL>! Echo $ ORACLE_HOME
/U01/oracle/product


SQL> create spfile from pfile = '/u01/oracle/product/dbs/initorcl. ora ';


File created.



6. Modify the listener. ora and tnsnames. ora files.
[Root @ dg1 admin] # cat listener. ora
# Listener. ora Network Configuration File:/u01/oracle/product/network/admin/listener. ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME =/u01/oracle/product)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME =/u01/oracle/product)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl1)
(ORACLE_HOME =/u01/oracle/product)
(SID_NAME = orcl)
)
)




[Root @ dg1 admin] # cat tnsnames. ora
# Tnsnames. ora Network Configuration File:/u01/oracle/product/network/admin/tnsnames. ora
# Generated by Oracle configuration tools.




ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = dg1) (PORT = 1521 ))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)




ORCL_ST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = dg2) (PORT = 1521 ))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)






ORCL_PD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = dg1) (PORT = 1521 ))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)


2. standby node settings


Ii. Standby configuration
1. Create backup inventory and put data files and background tracking directories.
[Oracle @ dg2 trace] $ echo $ ORACLE_BASE
/U01/oracle
[Oracle @ dg2 trace] $ mkdir-p/u01/oracle/oradata/orcl
[Oracle @ dg2 trace] $ mkdir-p/u01/oracle/admin/orcl
[Oracle @ dg2 trace] $ mkdir-p/u01/oracle/admin/orcl/adump
[Oracle @ dg2 trace] $ mkdir-p/u01/oracle/admin/orcl/bdump
[Oracle @ dg2 trace] $ mkdir-p/u01/oracle/admin/orcl/cdump
[Oracle @ dg2 trace] $ mkdir-p/u01/oracle/admin/orcl/ddump
[Oracle @ dg2 trace] $ mkdir-p/u01/oracle/admin/orcl/pfile
[Oracle @ dg2 trace] $ mkdir-p/u01/oracle/admin/orcl/udump


2. initialize the copied parameters.
[Oracle @ dg2 trace] cd/u01/oracle/product/dbs
Copy from the master database and modify it as follows:
*. DB_NAME = 'orcl ';
# --
*. DB_UNIQUE_NAME = 'orcl _ st'
# -- List all DB_UNIQUE_NAME in DG
*. LOG_ARCHIVE_CONFIG = 'dg _ CONFIG = (orcl_pd, orcl_st )'
*. LOG_ARCHIVE_DEST_1 = 'location =/u01/archive VALID_FOR = (ALL_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME = orcl_st'
*. LOG_ARCHIVE_DEST_2 = 'service = orcl_pd DB_UNIQUE_NAME = orcl_pd'


*. LOG_ARCHIVE_DEST_STATE_1 = ENABLE
*. LOG_ARCHIVE_DEST_STATE_2 = ENABLE
*. REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE
##### Slave database parameters #######
*. FAL_SERVER = orcl_pd
*. FAL_CLIENT = orcl_st
*. Standby_file_management = 'auto'
*. Standby_archive_dest = '/u01/archive'




3. Modify the listener. ora and tnsnames. ora files. If they do not exist, copy them from the master database.
Copy the initialization parameter file
[Oracle @ dg1 admin] $ scp initorcl. ora 192.168.48.132:/u01/oracle/product/dbs

Copy the listener. ora and tnsnames. ora files.
[Oracle @ dg1 admin] $ scp listener. ora 192.168.48.132:/u01/oracle/product/network/admin
Oracle@192.168.48.132's password:
Listener. ora 100% 609 0.6KB/s
[Oracle @ dg1 admin] $ scp tnsnames. ora 192.168.48.132:/u01/oracle/product/network/admin
Oracle@192.168.48.132's password:
Tnsnames. ora 100% 734 0.7KB/s


4. rman backup
[Oracle @ dg1 admin] $ rman target/


Recovery Manager: Release 11.2.0.3.0-Production on Mon Jan 27 16:59:37 2014


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


Connected to target database: ORCL (DBID = 1365744995)




RUN {
Allocate channel c1 type disk;
Allocate channel c2 type disk;
SQL 'alter system archive log current ';
Backup current controlfile for standby format = '/oradata/backup/control _ % U ';
BACKUP format'/oradata/backup/orcl _ % U _ % t' skip inaccessible filesperset 5 DATABASE;
SQL 'alter system archive log current ';
BACKUP format'/oradata/backup/arch _ % U _ % t' skip inaccessible filesperset 5 archivelog all delete input;
Release channel c2;
Release channel c1;
}
Backup full database include current controlfile for standby plus archivelog format'/oradata/backup/orcl _ % U _ % t' skip inaccessible filesperset 5;


5. Copy the backup folder to the dg2 server.
[Oracle @ dg1 oradata] $ cd/oradata/backup/
[Oracle @ dg1 backup] $ scp * 192.168.48.132:/oradata/backup




6. Execute duplicate to copy the standby Database
[Oracle @ dg1 admin] $ rman target/auxiliary sys/wwwwww @ orcl_st;
Recovery Manager: Release 10.2.0.1.0-Production on Tue Jul 20 22:32:59 2010
Copyright (c) 1982,200 5, Oracle. All rights reserved.
Connected to target database: ORCL (DBID = 1248423599)
Connected to auxiliary database: ORCL (not mounted)
RMAN> duplicate target database for standby nofilenamecheck dorecover;




7. Add redo log file in the slave Database
If no redo log file is added to the master database, you can start the database to the mount state by using the copied initialization file. After creating a spfile, add the redo log.
SQL> create spfile from pfile = '/u01/oracle/product/dbs/initorcl. ora ';
Add a new Standby Redologs group (note that the Group number should not be the same as the existing Online Redologs group) and specify a member for the group:
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/u01/oracle/oradata/orcl/redo04.log') size 50 M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u01/oracle/oradata/orcl/redo05.log') size 50 M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u01/oracle/oradata/orcl/redo06.log') size 50 M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u01/oracle/oradata/orcl/redo07.log') size 50 M;




Startup sequence: First standby, then primary;
Closed sequence: first primary and then standby;

Start the instance to the mount status in the slave database:
SQL> startup nomount;
SQL> alter database mount standby database;
SQL> alter database recover managed standby database disconnect from session;
Start the listener in the slave database:
$ Lsnrctl start
Start the instance in the master database:
SQL> startup;
Start the listener in the master database:
$ Lsnrctl start






8. Verify dg
Run the following command on the host:
SQL> create user hbhe identified by wwwwww default tablespace users temporary tablespace temp;


User created.


SQL> grant select on dba_pending_transactions to hbhe;


Grant succeeded.


SQL> grant connect, resource to hbhe;


Grant succeeded.


SQL> grant insert any table to hbhe;


Grant succeeded.


SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[Oracle @ dg1 dbs] $ sqlplus hbhe/wwwwww


SQL * Plus: Release 11.2.0.3.0 Production on Tue Jan 28 18:45:36 2014


Copyright (c) 1982,201 1, Oracle. All rights reserved.




Connected:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> create table sales1 (empid number, depid number, area varchar (20), salenum number );


Table created.


SQL> insert into sales1 values (1, 1, 'China', 10 );


1 row created.


SQL> insert into sales1 values (2, 1, 'China', 10 );


1 row created.




Run the following command on the slave machine:
[Oracle @ dg2 dbs] $ sqlplus "/as sysdba"


SQL * Plus: Release 11.2.0.3.0 Production on Tue Jan 28 19:14:36 2014


Copyright (c) 1982,201 1, Oracle. All rights reserved.




Connected:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options




SQL> alter database recover managed standby database cancel;


Database altered.


SQL> alter database open;


Database altered.


SQL> conn hbhe/wwwwww
Connected.
SQL> select * from sales1;


EMPID DEPID
--------------------
AREA SALENUM
----------------------------------------------------------------------
1 1
China 10


2 1
China 10

References:

Important configuration parameters of Oracle Data Guard

Configure Oracle 11g Data Guard based on the same host

Explore Oracle 11g elastic uard

Oracle Data Guard (RAC + DG) archive deletion policies and scripts

Role conversion for Oracle Data Guard

FAL gap in Oracle Data Guard logs

Oracle 11g Data Guard Error 16143 Heartbeat failed to connect to standby