oracle11g R2 "Single Instance fs→ Single instance FS"

oracle11g R2 "Single Instance fs→ Single instance FS"

Environment used in this demo case:

	Primary	Standby
OS Hostname	Pry	Std
OS Version	RHEL6.5	RHEL6.5
DB Version	11.2.0.4	11.2.0.4
Db_name	Stephen	Stephen
Db_unique_name	Stephen	Standby
Service_names	Stephen	Standby
Instance_name	Stephen	Standby

Primary database Configure1. Enable Primary Force logging

Sql> select force_logging from V$database;

sql> ALTER DATABASE force logging;

2. Configuring Redo Transport Authentication

(1) Tnsnames.ora

STEPHEN =

(DESCRIPTION =

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

(Connect_data =

(SERVER = dedicated)

(service_name = Stephen)

)

)

STANDBY =

(DESCRIPTION =

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

(Connect_data =

(SERVER = dedicated)

(service_name = standby)

)

)

(2) tnsping Command parses the network service name, the command must return OK.

At this point, the standby side is not configured to listen, can not be resolved, standby configuration and then test.

$ tnsping Standby

(2) password file

If there is no password file, create a password file. Format:orapw<oracle_sid>

CD $ORACLE _home/dbs

Orapwd file=orapwstephen password=oracle ignorecase=y force=y

3. Add Standby LogFiles

Requirements for adding standby logfile:

(1) Ensure that the log file size is the same on the primary and standby databases

(2) Determine the appropriate number of repository redo log file groups

Standby logfile Number = (number of logfile per thread +1) * Number of threads

(3) Check the maxlogfiles and Maxlogmembers parameters specified during CREATE database, which can be viewed by trace controlfile.

(4) RAC Environment Create standby logfile, specify thread# Create

Standby logfile per thread = number of logfile per thread + 1

Check the logfile of the current environment:

Set Lines pages 300

Col Member for A60

Select a.thread#,a.group#,b.member,b.type,a.bytes/1024/1024 MB from V$log a,v$logfile b where a.group#=b.group#

UNION ALL

Select a.thread#,a.group#,b.member,b.type,a.bytes/1024/1024 MB from V$standby_log a,v$logfile b where a.group#=b.group #;

Based on the query's logfile information, add standby logfile requirements above, add a reasonable standby logfile, and then use the SQL above to view the logfile of the current environment again:

sql> ALTER DATABASE Add standby logfile

Group 4 '/oradata/stephen/redo04.log ' size 50M,

Group 5 '/oradata/stephen/redo05.log ' size 50M,

Group 6 '/oradata/stephen/redo06.log ' size 50M,

Group 7 '/oradata/stephen/redo07.log ' size 50M;

4. Modify the primary parameter file

Backup SPFile:

Create pfile= '/tmp/pfile.bak ' from SPFile;

Modify the following parameters, where Stephen,standby is the db_unqiue_name/tns-alias of Primary,standby, see the Detailed Parameters section:

Alter system set log_archive_config= ' dg_config= (stephen,standby) ';

Alter system set log_archive_dest_1= ' Location=/oradata/arch valid_for= (all_logfiles,all_roles) db_unique_name= Stephen ';

Alter system set log_archive_dest_2= ' Service=standby lgwr async valid_for= (online_logfiles,primary_role) db_unique_ Name=standby ';

alter system set LOG_ARCHIVE_MAX_PROCESSES=30; # Adjust the number according to demand

Confirm the following parameter default values, if not the following parameter values, modify:

Alter system set remote_login_passwordfile=exclusive Scope=spfile;

# remote_login_passwordfile=exclusive/shared

alter system set log_archive_dest_state_1=enable;

alter system set log_archive_dest_state_2=enable;

The Log_archive_format parameter controls the format of the archive file name, with the default value ending with. DBF, which is proposed to be modified to the end of. Arc in order to differentiate it from datafile:

Alter system set log_archive_format= '%t_%s_%r.arc ' scope=spfile;

When primary switches to the standby role, the following parameters need to be modified, which is recommended to configure:

alter system set Fal_server=standby;

alter system set Standby_file_management=auto;

# Primary when the standby datafile and logfile storage paths are different, set the following parameters:

Alter system set db_file_name_convert= '/oradata/standby ', '/oradata/stephen ' scope=spfile;

Alter system set log_file_name_convert= '/oradata/standby ', '/oradata/stephen ' scope=spfile;

Note: The above parameters, specified SPFile modified, take effect after the instance restarts.

5. Open Archive mode

First verify that the current database is an archive mode, and if not, turn on archive mode.

Archive log list;

Shutdown immediate;

startup Mount;

ALTER DATABASE Archivelog;

Archive log list;

ALTER DATABASE open;

6. Backup Primary Database

You can use the previous backup, but you need to ensure that the archive has not been lost since the backup.

Rman Target/<<eof

Run{allocate channel C1 type disk;

Allocate channel C2 type disk;

Backup full database format '/backup/full_%u.bak ' plus archivelog;

SQL ' alter system switch logfile ';

Backup current controlfile for standby format '/backup/c_%u.bak ';

Release channel C1;

Release channel C2;

}

Eof

Standby database configure1. Configuring environment variables

Export Oracle_base=/oracle/app/oracle

Export Oracle_home=/oracle/app/oracle/product/11.2.0/db_1

Export Oracle_sid=standby

Export Nls_lang=american_america. Zhs16gbk

Export path= $ORACLE _home/bin: $PATH

Export ld_library_path= $ORACLE _home/lib # different platform variable names

2. Configuring Redo Transport Authentication

(1) Listener.ora

To configure static monitoring:

LISTENER =

(address_list=

(Address= (PROTOCOL=TCP) (HOST=STD) (port=1521))

)

Sid_list_listener=

(sid_list=

(sid_desc=

(Global_dbname=standby)

(Sid_name=standby)

(oracle_home=/oracle/app/oracle/product/11.2.0/db_1)

)

)

Enable monitoring:

Lsnrctl start

Lsnrctl status

(2) Tnsnames.ora

STEPHEN =

(DESCRIPTION =

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

(Connect_data =

(SERVER = dedicated)

(service_name = Stephen)

)

)

STANDBY =

(DESCRIPTION =

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

(Connect_data =

(SERVER = dedicated)

(service_name = standby)

)

)

(3) tnsping Command parses the network service name, the command must return OK.

At this point, the standby side is not configured to listen, can not be resolved, standby configuration and then test.

$ tnsping Stephen

(4) password file

Using the Master Vault's password file:

SCP pry: $ORACLE _home/dbs/orapwstephen $ORACLE _home/dbs/orapwstandby

3. Create a related directory

Mkdir-p $ORACLE _base/admin/standby/{adump,dpdump,pfile}

Mkdir-p/oradata/{standby,arch}

4. parameter file

Modified with primary pfile to ensure that some parameters are consistent with primary, note that the size of the main repository memory is appropriately adjusted for memory-related parameters.

SCP Pry:/tmp/pfile.bak $ORACLE _home/dbs/initstandby.ora

Modify the pfile to the desired standby pfile and create the SPFile. Pay particular attention to the following example parameter settings:

*.audit_file_dest= '/oracle/app/oracle/admin/standby/adump '

*.audit_trail= ' DB '

*.control_files= '/oradata/standby/control01.ctl '

*.db_name= ' Stephen '

*.db_unique_name= ' standby '

*.instance_name= ' standby '

*.service_names= ' standby '

*.remote_login_passwordfile= ' EXCLUSIVE '

*.undo_tablespace= ' UNDOTBS1 '

*.log_archive_format= '%t_%s_%r.arc '

*.log_archive_config= ' dg_config= (standby,stephen) '

*.log_archive_dest_1= ' Location=/oradata/arch valid_for= (all_logfiles,all_roles) Db_unique_name=standby '

*.log_archive_dest_2= ' Service=stephen lgwr async valid_for= (online_logfiles,primary_role) Db_unique_name=stephen '

*.log_archive_dest_state_1= ' Enable '

*.log_archive_dest_state_1= ' Enable '

*.log_archive_max_processes=30 # Adjust the number according to demand

*.fal_server= ' Stephen '

*.standby_file_management=auto

*.remote_login_passwordfile= ' EXCLUSIVE '

# The main, standby log files and data file storage paths are inconsistent, set the following parameters:

*.db_file_name_convert= '/oradata/stephen ', '/oradata/standby '

*.log_file_name_convert= '/oradata/stephen ', '/oradata/standby '

Create SPFile:

Create SPFile from Pfile;

5. Recovering standby Controlfile

sql> startup Nomount;

rman> restore standby controlfile from '/backup/c_05qlbira_1_1.bak ';

6. Recovering Data files

Start the database to the Mount stage and restore the data files.

(1) Standby and primary data file storage path consistent:

rman> SQL ' ALTER DATABASE mount standby database ';

Run

{Allocate channel C1 type disk;

Allocate channel C2 type disk;

Resotore database;

Recover database;

Release channel C1;

Release channel C2;

}

(2) Standby and primary data file storage path inconsistent:

Query the data file information for primary database:

Set Lines pages 300

Col name for A60

Col Member for A60

Select File#,name from V$datafile

UNION ALL

Select File#,name from V$tempfile;

Start the standby to mount stage and recover the data files:

rman> SQL ' ALTER DATABASE mount standby database ';

Run

{Set newname for datafile 1 to '/oradata/standby/system01.dbf ';

Set newname for datafile 2 to '/oradata/standby/sysaux01.dbf ';

Set newname for DataFile 3 to '/oradata/standby/undotbs1.dbf ';

Set newname for datafile 4 to '/oradata/standby/user01.dbf ';

Set newname for Tempfile 1 to '/oradata/standby/temp01.dbf ';

Restore database;

Switch datafile all;

Switch Tempfile all;

Recover database;

}

View Standby database log file:

Set Lines pages 300

Col Member for A60

Select a.thread#,a.group#,b.member,b.type,a.bytes/1024/1024 MB

From V$log A,v$logfile b where a.group#=b.group#

UNION ALL

Select a.thread#,a.group#,b.member,b.type,a.bytes/1024/1024 MB

From V$standby_log A,v$logfile b where a.group#=b.group#;

7. Application Log

Apply Archive log:

Recover managed standby Database disconnect;

Apply Redo logfile:

Recover managed Standby database using current logfile disconnect;

To cancel the application log:

Recover managed standby database cancel;

11g physical standby database can be opened to read only mode, which is often said to be active Dataguard. The business to migrate queries to the active DG reduces the pressure on the primary db.

Open the database to read only mode and start real-time replication:

ALTER DATABASE open read only;

Recover managed Standby database using current logfile disconnect;

8. Observing log Transfers

Follow the alert log of Primary,standby database to see if there is an error, and you can view information about the application log.

Verify that standby applies the log:

--primary end more than a few times log, observe alert log information.

alter system switch logfile;

alter system switch logfile;

-- Watch the Master repository log sequence number

Archive log list;

--primary End Query V$archived_log view to confirm that the log is applied:

Set Lines pages 300

Col name for A20

Select Name,dest_id,thread#,sequence#,standby_dest,applied,registrar,completion_time from V$archived_log

where standby_dest= ' YES '

Order BY thread#,sequence#;

--primary the maximum log sequence number for the Primary,standby-side query is consistent:

Select ' Primary: ' DB Role ', Thread#,max (sequence#)

From V$archived_log

where standby_dest= ' NO '

GROUP BY thread#

Union

Select ' Standby: ' DB Role ', Thread#,max (sequence#)

From V$archived_log

where standby_dest= ' yes ' and applied= ' yes '

GROUP BY thread#

Order BY thread#;

oracle11g R2 "Single Instance fs→ Single instance FS"