RedHat build physical Data Guard

RedHat build physical Data Guard

My environment:

Both machines are Red Hat Enterprise Linux Server release 5.4

Database Version: Oracle10g10.2.0

Install oracle software on the primary machine and create the database orcl

Only the oralce software is installed on standby, and no database is required.

Basic Configuration:

Source database:

IP: 10.37.1.1

Database SID: orcl_p

Db_unique_name: orcl1

Standby database:

IP: 10.37.1.2

Database SID: orcl_s

Db_unique_name: orcl2

Configuration steps:

1. Configure the primary database archive and set the local archive path.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1218316 bytes

Variable Size 83888372 bytes

Database Buffers 79691776 bytes

Redo Buffers 2973696 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter system set log_archive_dest_1 = 'location =/u01/arch ';

System altered.

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination/u01/arch

Oldest online log sequence 2

Next log sequence to archive 4

Current log sequence 4

2. Place the primary database in force logging mode.

SQL> select force_logging from v $ database;

FOR

---

NO

SQL> alter database force logging;

Database altered.

SQL> select force_logging from v $ database;

FOR

---

YES

3. Create and modify the initialization parameter file of the primary database

SQL> create pfile = '/u01/p_pfile.ora' from spfile;

File created.

[Oracle @ localhost ~] $ Vi/u01/p_pfile.ora

Modify the following content:

*. Db_unique_name = orcl1

*. Log_archive_config = 'dg _ config = (orcl1, orcl2 )'

*. Log_archive_dest_2 = 'service = orcl_s.2_tns arch valid_for = (online_logfiles, primary_role) db_unique_name = orcl2'

*. Log_archive_dest_state_2 = defer

*. Fal_server = orcl_s.2_tns

*. Fal_client = orcl_p.20.tns

*. Db_file_name_convert = '/u01/app/oracle/oradata/orcl', '/u01/app/oracle/oradata/orcl_s'

*. Log_file_name_convert = '/u01/app/oracle/oradata/orcl', '/u01/app/oracle/oradata/orcl_s'

*. Standby_file_management = auto

Disable the database and use the modified pfile to create a spfile.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>! Export ORACLE_SID = orcl_p

SQL> create spfile from pfile = '/u01/p_pfile.ora ';

File created.

SQL> startup

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1218316 bytes

Variable Size 62916852 bytes

Database Buffers 100663296 bytes

Redo Buffers 2973696 bytes

Database mounted.

Database opened.

4. Create a backup:

Create a standby control file:

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

Database altered.

Create a backup of all data files (Here we only use the data files in the users tablespace as an example. All other data files must be backed up)

SQL> alter tablespace users begin backup;

Tablespace altered.

SQL>! Cp/u01/app/oracle/oradata/orcl/users01.dbf/u01

SQL> alter tablespace users end backup;

Tablespace altered.

5. Configure the primary listener and tns Service

[Oracle @ localhost ~] $ Cd $ ORACLE_HOME/network/admin

[Oracle @ localhost admin] $ vi listener. ora

The configuration is as follows:

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = orcl_p)

(ORACLE_HOME =/u01/app/oracle/10.2.0/db_1)

(GLOBAL_DBNAME = orcl_p)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = 10.37.1.1) (PORT = 1521 ))

)

)

Configure tns

[Oracle @ localhost admin] $ vi tnsnames. ora

The configuration is as follows:

Orcl_p.20.tns =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST = 10.37.1.1) (PORT = 1521 ))

)

(CONNECT_DATA =

(SID = orcl_p)

(SERVER = DEDICATED)

)

)

Orcl_s.2_tns =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST = 10.37.1.2) (PORT = 1521 ))

)

(CONNECT_DATA =

(SID = orcl_s)

(SERVER = DEDICATED)

)

)

Restart the listener and test the tns configuration of the source database.

[Oracle @ localhost admin] $ lsnrctl stop

LSNRCTL for Linux: Version 10.2.0.1.0-Production on 26-FEB-2014 19:11:44

Copyright (c) 1991,200 5, Oracle. All rights reserved.

Connecting to (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 10.37.1.1) (PORT = 1521 )))

The command completed successfully

[Oracle @ localhost admin] $ lsnrctl start

LSNRCTL for Linux: Version 10.2.0.1.0-Production on 26-FEB-2014 19:11:53

Copyright (c) 1991,200 5, Oracle. All rights reserved.

Starting/u01/app/oracle/10.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0-Production

System parameter file is/u01/app/oracle/10.2.0/db_1/network/admin/listener. ora

Log messages written to/u01/app/oracle/10.2.0/db_1/network/log/listener. log

Listening on: (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = 10.37.1.1) (PORT = 1521 )))

Connecting to (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 10.37.1.1) (PORT = 1521 )))

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 10.2.0.1.0-Production

Start Date 26-FEB-2014 19:11:53

Uptime 0 days 0 hr. 0 min. 0 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File/u01/app/oracle/10.2.0/db_1/network/admin/listener. ora

Listener Log File/u01/app/oracle/10.2.0/db_1/network/log/listener. log

Listening Endpoints Summary...

(DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = 10.37.1.1) (PORT = 1521 )))

Services Summary...

Service "orcl_p" has 1 instance (s ).

Instance "orcl_P", status UNKNOWN, has 1 handler (s) for this service...

The command completed successfully

[Oracle @ localhost admin] $ tnsping orcl_p.20.tns

TNS Ping Utility for Linux: Version 10.2.0.1.0-Production on 26-FEB-2014 19:12:38

Copyright (c) 1997,200 5, Oracle. All rights reserved.

Used parameter files:

/U01/app/oracle/10.2.0/db_1/network/admin/sqlnet. ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 10.37.1.1) (PORT = 1521) (CONNECT_DATA = (SID = orcl_p) (SERVER = DEDICATED )))

OK (0 msec)

6. Create a directory structure for the standby database and copy the parameter files, backup control files, and password files of the source database to the corresponding location.

7,

[Oracle @ localhost ~] $ Mkdir-p/u01/arch2

[Oracle @ localhost u01] $ mkdir-p/u01/app/oracle/flash_recovery_area

[Oracle @ localhost ~] $ Mkdir-p/u01/app/oracle/oradata/orcl_s

[Oracle @ localhost ~] $ Mkdir-p/u01/app/oracle/admin/orcl_s/{a, B, c, u} dump

[Oracle @ localhost ~] $ Scp 10.37.1.1:/u01/*. dbf/u01/app/oracle/oradata/orcl_s/

[Email protected]'s password:

Example01.dbf 100% 100 MB 20.0 MB/s

Sysaux01.dbf 100% 240 MB 16.0 MB/s

System01.dbf 100% 480 MB 14.6 MB/s

Undotbs01.dbf 100% 30 MB 15.0 MB/s

Users01.dbf 100% 5128KB 5.0 MB/s

[Oracle @ localhost ~] $ Scp 10.37.1.1:/u01/*. ctl/u01/app/oracle/oradata/orcl_s/

[Email protected]'s password:

Orcl2control01. ctl 100% 6896KB 6.7 MB/s

[Oracle @ localhost ~] $ Scp 10.37.1.1:/u01/p_pfile.ora/u01s_pfile.ora

[Email protected]'s password:

P_pfile.ora 100% 1508 1.5KB/s

[Oracle @ localhost ~] $ Scp 10.37.1.1:/u01/app/oracle/10.2.0/db_1/dbs/orapworcl_p/u01/app/oracle/10.2.0/db_1/dbs/orapworcl_s

[Email protected]'s password:

Orapworcl_p 100% 5120 5.0KB/s

7. Modify the sid of the standby database to orcl_s and configure the copy parameter file.

[Root @ localhost ~] # Vi/u01/s_pfile.ora

The content to be modified is as follows: (you can modify the content based on your actual situation. Note that the red part is the key part to be modified)

*. Audit_file_dest = '/u01/app/oracle/admin/orcl_s/adump'

*. Background_dump_dest = '/u01/app/oracle/admin/orcl_s/bdump'

*. Control_files = '/u01/app/oracle/oradata/orcl_s/orcl2control01. ctl ','/u01/app/oracle/oradata/orcl_s/orcl2control02. ctl ','/u01/app/oracle/oradata/orcl_s/orcl2control03. ctl'

*. Core_dump_dest = '/u01/app/oracle/admin/orcl_s/cdump'

*. User_dump_dest = '/u01/app/oracle/admin/orcl_s/udump'

*. Db_unique_name = orcl2

*. Log_archive_dest_1 = 'location =/u01/arch2'

*. Log_archive_dest_2 = 'service = orcl_p.20.tns arch valid_for = (online_logfiles, primary_ro

Le) db_unique_name = orcl1'

*. Log_archive_dest_state_2 = enable

*. Fal_server = orcl_p.20.tns

*. Fal_client = orcl_s.2_tns

8. Configure the monitoring and service name of the standby Database

[Oracle @ localhost admin] $ cd $ ORACLE_HOME/network/admin

[Oracle @ localhost admin] $ vi listener. ora

# Listener. ora Network Configuration File:/u01/app/oracle/10.2.0/db_1/network/admin/listener. ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = orcl_s)

(ORACLE_HOME =/u01/app/oracle/10.2.0/db_1)

(GLOBAL_DBNAME = orcl_s)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = 10.37.1.2) (PORT = 1521 ))

)

)

[Oracle @ localhost admin] $ vi tnsnames. ora

# Tnsnames. ora Network Configuration File:/u01/app/oracle/10.2.0/db_1/network/admin/tnsnames. ora

# Generated by Oracle configuration tools.

Orcl_s.2_tns =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST = 10.37.1.2) (PORT = 1521 ))

)

(CONNECT_DATA =

(SID = orcl_s)

(SERVER = DEDICATED)

)

)

Orcl_p.20.tns =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST = 10.37.1.1) (PORT = 1521 ))

)

(CONNECT_DATA =

(SID = orcl_p)

(SERVER = DEDICATED)

"Tnsnames. ora" 36L, 764C

Restart the listening service after the configuration is complete.

[Oracle @ localhost admin] $ lsnrctl stop

[Oracle @ localhost admin] $ lsnrctl start

Now that the listener and service configuration are complete, you must be able to ping both services by using the tnsping command on the primary and standby terminals. The configuration is successful if you can log on to the two databases remotely.

SQL> conn sys/oracle@orcl_p.1 _ tns as sysdba

Connected.

SQL> conn sys/oracle@orcl_s.2 _ tns as sysdba

Connected to an idle instance.

9. Configure the stanby database, start it to the mount state, and accept archive files.

Connect any terminal to the standby Database

[Oracle @ localhost ~] $ Sqlplus sys/oracle@orcl_s.2 _ tns as sysdba

Use the common standby spfile of s_pfile.ora

SQL> create spfile from pfile = '/u01/s_pfile.ora ';

File created.

SQL> startup mount

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1218316 bytes

Variable Size 62916852 bytes

Database Buffers 100663296 bytes

Redo Buffers 2973696 bytes

Database mounted.

Connect to the primary database and set the remote archiving path to enable

SQL> conn sys/oracle@orcl_p.1 _ tns as sysdba

Connected.

SQL> alter system set log_archive_dest_state_2 = enable;

System altered.

View archive acceptance

SQL> select max (sequence #) from v $ archived_log;

MAX (SEQUENCE #)

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

4

SQL> conn sys/oracle@orcl_s.2 _ tns as sysdba

Connected.

SQL> select max (sequence #) from v $ archived_log;

MAX (SEQUENCE #)

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

4

Check whether archive logs from the source database are stored in the archive path of standby.

SQL>! Ls/u01/arch2

20174_840520047.dbf

10. Insert primary data to test whether the standby database can be accepted normally.

Create a table on the primary side and insert data

SQL> conn scott/tiger

Connected.

SQL> create table DG_TEST (ID VARCHAR2 (10 ));

Table created.

SQL> insert into DG_TEST

2 values ('dg _ test ')

3/

1 row created.

SQL>/

1 row created.

SQL>/

1 row created.

SQL>/

1 row created.

SQL> select * from DG_TEST;

ID

----------

DG_TEST

DG_TEST

DG_TEST

DG_TEST

SQL> commit;

Commit complete.

Switch to archive logs to archive the current logs

SQL> conn/as sysdba

Connected.

SQL> alter system switch logfile;

System altered.

Standby starts the redo application (this is also the working method of the physical standby, so the recovery operation is performed from the obtained archive to keep consistent with the source database)

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

Database altered.

You must stop the redo application before opening the database.

SQL> alter database recover managed standby database cancel;

Database altered.

Open standby data to check whether the new data is inserted in the source database.

SQL> select * from scott. DG_TEST;

ID

----------

DG_TEST

DG_TEST

DG_TEST

DG_TEST

The data is successfully transferred to the standby database. The physical DG is successfully built!

-------------------------------------- Split line --------------------------------------

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

-------------------------------------- Split line --------------------------------------