Oracle 11g DG Construction

Environment: centos 5.6 oracle 11g r2

Primary: 192.168.8.132 str1.example.com str1 master database sid primary

Standby: 192.168.8.131 str2.example.com str2 standby database sid standby

Oracle 11g software installation http://yangcheng.blog.51cto.com/1388193/1209421

Primary Configuration

1. Configure the Silent Installation File

[Root @ str1 ~] # Vi/u01/response/d. rsp

[GENERAL]

RESPONSEFILE_VERSION = "11.2.0"

OPERATION_TYPE = "createDatabase"

[CREATEDATABASE]

GDBNAME = "primary"

SID = "primary"

TEMPLATENAME = "General_Purpose.dbc"

[CreateTemplateFromDB]

SOURCEDB = "myhost: 1521: primary"

SYSDBAUSERNAME = "system"

TEMPLATENAME = "My Copy TEMPLATE"

[CreateCloneTemplate]

SOURCEDB = "primary"

TEMPLATENAME = "My Clone TEMPLATE"

[DELETEDATABASE]

SOURCEDB = "primary"

[GenerateScripts]

TEMPLATENAME = "New Database"

GDBNAME = "primary"

[CONFIGUREDATABASE]

[ADDINSTANCE]

DB_UNIQUE_NAME = "primary"

NODELIST =

SYSDBAUSERNAME = "sys"

[DELETEINSTANCE]

DB_UNIQUE_NAME = "primary"

INSTANCENAME = "primary"

SYSDBAUSERNAME = "sys"

2. Set oracle environment variables

[Root @ str1 ~] # Vi/home/oracle/. bash_profile

#. Bash_profile

# Get the aliases and functions

If [-f ~ /. Bashrc]; then

.~ /. Bashrc

Fi

# User specific environment and startup programs

PATH = $ PATH: $ HOME/bin

Export PATH

Umask 022

Export ORACLE_BASE =/u01/app

Export ORACLE_HOME = $ ORACLE_BASE/oracle/product/11.2.0/dbhome_1

Export ORACLE_SID = primary

Export PATH = $ PATH: HOME/bin: $ ORACLE_HOME/bin

3. Install primary Columns

$ ORACLE_HOME/bin/dbca-silent-responsefile/u01/response/d. rsp

1. Open the database and modify Database Properties

[Oracle @ str1 ~] $ Sqlplus/as sysdba;

1) modify the attributes of the master database

SQL> startup mount

SQL> alter database force logging;

2) view the status

SQL> select FORCE_LOGGING from v $ database;

3) change the database to archive mode.

SQL> alter system set log_archive_dest_1 = 'location =/u01/app/oradata/primary/'scope = both;

SQL> alter database archivelog;

4) add standby logfile (not included)

At least one backup log should be greater than the redo log

Select GROUP #, MEMBERS, BYTES/1024/1024 from v $ log;

Select GROUP #, MEMBER from v $ logfile;

Add standby logfile

Alter database add standby logfile

Group 4 ('/u01/app/oradata/primary/redo04.log') size 50 m,

Group 5 ('/u01/app/oradata/primary/redo05.log') size 50 m,

Group 6 ('/u01/app/oradata/primary/redo06.log') size 50 m,

Group 7 ('/u01/app/oradata/primary/redo07.log') size 50 m;

--- If you want to delete the standby logfile

--- Deletion is also simple: SQL> alter database drop standby logfile group 4;

5) create a pfile

SQL> create pfile = '/u01/app/backup/initprimary. ora' from spfile;

2. Modify initprimary. ora

Add content

Vi/u01/app/backup/initprimary. ora

DB_UNIQUE_NAME = 'primary'

Log_archive_config = 'dg _ CONFIG = (primary, standby )'

Log_archive_dest_1 = 'location =/u01/app/oradata/primary/archivelog VALID_FOR = (ALL_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME = primary'

LOG_ARCHIVE_DEST_2 = 'service = standby lgwr async VALID_FOR = (ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME = standby'

LOG_ARCHIVE_DEST_STATE_1 = ENABLE

LOG_ARCHIVE_DEST_STATE_2 = ENABLE

FAL_SERVER = standby

FAL_CLIENT = primary

STANDBY_FILE_MANAGEMENT = AUTO

[Root @ str1 ~] # Vi/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames. ora

# Tnsnames. ora Network Configuration File:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames. ora

# Generated by Oracle configuration tools.

Primary =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVICE_NAME = primary)

)

)

Standby =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVICE_NAME = standby)

)

)

EXTPROC_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TPC) (KEY = EXTPROCO ))

)

(CONNECT_DATA =

(SID = PLSExtProc)

(PRESENTATION = RO)

)

)

3. Modify the listener

[Root @ str1 ~] # Vi/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener. ora

# Listener. ora Network Configuration File:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener. ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME =/u01/app/oracle/product/11.2.0/dbhome_1)

(PROGRAM = extproc)

)

(SID_DESC =

(GLOBAL_DBNAME = primary)

(ORACLE_HOME =/u01/app/oracle/product/11.2.0/dbhome_1)

(SID_NAME = primary)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1521 ))

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

)

)

4. Start with pfile and recreate spfile

Startup pfile = '/u01/app/backup/initprimary. ora'

Create spfile from pfile = '/u01/app/backup/initprimary. ora ';

5. Create a password file

Orapwd file = '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwprimary' password = letoula entries = 10

Vi. Backup Control Files

Alter database create standby controlfile as '/u01/app/backup/controlstb. ctl ';

7. Transmit pfile, password file, and control file to standby

[Root @ str1 ~] # Scp/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwprimary oracle@192.168.8.131:/u01/app/backup

[Root @ str1 ~] # Scp/u01/app/backup/controlstb. ctl oracle@192.168.8.131:/u01/app/backup

[Root @ str1 ~] # Scp/u01/app/backup/initprimary. ora oracle@192.168.8.131:/u01/app/backup

8. Transfer Data Files

[Root @ str1 ~] # Scp/u01/app/oradata/primary/* root@192.168.8.131:/u01/app/oradata/primary/-- standby create the primary directory

The master database has been configured temporarily.

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

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

Standby configuration

Oracle users log on to the standby server

1. modify oracle environment variables

[Root @ str2 primary] # vi/home/oracle/. bash_profile

#. Bash_profile

# Get the aliases and functions

If [-f ~ /. Bashrc]; then

.~ /. Bashrc

Fi

# User specific environment and startup programs

PATH = $ PATH: $ HOME/bin

Export PATH

Umask 022

Export ORACLE_BASE =/u01/app

Export ORACLE_HOME = $ ORACLE_BASE/oracle/product/11.2.0/dbhome_1

Export ORACLE_SID = standby

Export PATH = $ PATH: HOME/bin: $ ORACLE_HOME/bin

2. Copy the control file to the corresponding directory: cp/u01/app/backup/controlstb. ctl/u01/app/oradata/primary/control01.ctl

3, copy the password file to the corresponding directory: cp/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwprimary oracle@192.168.8.131: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwprimary

4. Create a directory for the backup server

Mkdir-p $ ORACLE_BASE/flash_recovery_area/primary

Mkdir-p $ ORACLE_BASE/oradata/primary

Cd $ ORACLE_BASE/admin/primary

[Oracle @ str2 standby] $ mkdir adump

M [oracle @ str2 standby] $ mkdir bdump

[Oracle @ str2 standby] $ mkdir cdump

[Oracle @ str2 standby] $ mkdir dpdump

[Oracle @ str2 standby] $ mkdir pfile

[Oracle @ str2 standby] $ mkdir udump

5. Modify initialization parameters

[Root @ str2 primary] # vi/u01/app/backup/initstandby. ora

Standby. _ db_cache_size = 197132288

Standby. _ java_pool_size = 4194304

Standby. _ large_pool_size = 4194304

Standby. _ oracle_base = '/u01/app' # ORACLE_BASE set from environment

Standby. _ pga_aggregate_target = 104857600

Standby. _ sga_target = 314572800

Standby. _ shared_io_pool_size = 0

Standby. _ shared_pool_size = 100663296

Standby. _ streams_pool_size = 0

*. Audit_file_dest = '/u01/app/admin/primary/adump'

*. Audit_trail = 'db'

*. Compatible = '11. 2.0.0.0'

*. Control_files = '/u01/app/oradata/primary/control01.ctl', '/u01/app/flash_recovery_area/primary/control02.ctl'

*. Db_block_size = 8192

*. Db_domain =''

*. Db_name = 'primary'

*. Db_recovery_file_dest = '/u01/app/flash_recovery_area'

*. Db_recovery_file_dest_size = 4070572032

*. Diagnostic_dest = '/u01/app'

*. Dispatchers = '(PROTOCOL = TCP) (SERVICE = standbyXDB )'

*. Open_cursors = 300

*. Pga_aggregate_target = 104857600

*. Processses = 150

*. Remote_login_passwordfile = 'clusive'

*. Sga_target = 314572800

*. Undo_tablespace = 'undotbs1'

DB_UNIQUE_NAME = 'standby'

Log_archive_config = 'dg _ CONFIG = (primary, standby )'

Log_archive_dest_1 = 'location =/u01/app/oradata/primary/archivelog VALID_FOR = (ALL_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME = standby'

LOG_ARCHIVE_DEST_2 = 'service = primary lgwr async VALID_FOR = (ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME = primary'

LOG_ARCHIVE_DEST_STATE_1 = ENABLE

LOG_ARCHIVE_DEST_STATE_2 = ENABLE

FAL_SERVER = primary

FAL_CLIENT = standby

STANDBY_FILE_MANAGEMENT = AUTO

6. Modify the tns. ora listen. ora information of the backup server.

[Root @ str2 primary] # vi/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames. ora

# Tnsnames. ora Network Configuration File:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames. ora

# Generated by Oracle configuration tools.

Primary =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVICE_NAME = primary)

)

)

Standby =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVICE_NAME = standby)

)

)

EXTPROC_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TPC) (KEY = EXTPROCO ))

)

(CONNECT_DATA =

(SID = PLSExtProc)

(PRESENTATION = RO)

)

)

7. Restart the listener lsnrctl stop

Lsnrctl start

8. test whether the listener is normal (it is also a good idea to identify whether the password file is correctly transferred to the standby server to avoid permission issues ))

Use sqlplus sys/oracle @ standby as sysdba on the master database

Sqlplus sys/oracle @ primary as sysdba

9. Start the database to nomount with the created backup server Parameters

Startup pfile = '/u01/app/backup/initstandby. ora' nomount

Alter database mount standby database;

Create a spfile File

Create spfile from pfile = '/u01/app/backup/initstandby. ora ';

Modify the database to be archived

Alter database recover managed standby database disconnect from session;

Use the following statement to suspend the redo application.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Open a database in read-only mode

Alter database open read only;

Configuration complete

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

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

To determine whether the configuration is successful, check whether the sequence of the archive log of the primary database is consistent.

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

MAX (SEQUENCE #)

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

55

Execute alter system switch logfile on the master database; switch logs, you can see that the slave database will automatically apply logs transmitted through the master database.

SQL> alter system switch logfile;

Query database status SQL> select status from v $ instance;

It takes some time to query whether archive logs are applied.) SQL> select sequence #, applied from v $ archived_log where applied = 'yes' order by sequence #;

View the status of the master database DG:

SQL> select switchover_status from v $ database;

SWITCHOVER_STATUS

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

TO STANDBY

Log test:

Run the following command on the primary database:

SQL> conn scott/tiger

Connected.

SQL> create table y (y int );

Table created.

SQL> insert into y values (1 );

1 row created.

SQL> commit;

Commit complete.

SQL> conn/as sysdba;

Connected.

SQL> alter system switch logfile;

System altered.

Run the following command on the standby database:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Open a database in read-only mode

Alter database open read only;

SQL> conn scott/tiger

Connected.

SQL> select * from y;

Y

----------

1

This article from the "wow click" blog, please be sure to keep this source http://yangcheng.blog.51cto.com/1388193/1297625