oracle10g R2 "rac+asm→ Single Instance FS"

oracle10g R2 "rac+asm→ Single Instance FS"

TenG R2 rac+asmà Single Instance FS DG, it is recommended to disable OMF.

Environment used in this demo case:

	Primary	Standby
OS Hostname	Node1,node2	Std
OS Version	RHEL5.8	RHEL5.8
DB Version	10.2.0.5	10.2.0.5
Db_name	Stephen	Stephen
Db_unique_name	Stephen	Standby
Service_names	Stephen	Standby
Instance_name	Stephen1,stephen2	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 = NODE1-VIP) (PORT = 1521))

(ADDRESS = (PROTOCOL = TCP) (HOST = NODE2-VIP) (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

(3) password file

If there is no password file, the password file is created on one node and copied to the remaining nodes. Format:orapw<oracle_sid>

CD $ORACLE _home/dbs

Orapwd file=orapwstephen1 password=oracle Force=y

SCP orapwstephen1 node2: $ORACLE _home/dbs/orapwstephen2

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:

ALTER DATABASE ADD standby logfile thread 1

Group 5 ' +data/stephen/onlinelog/standby05.log ' size 50M,

Group 6 ' +data/stephen/onlinelog/standby06.log ' size 50M,

Group 7 ' +data/stephen/onlinelog/standby07.log ' size 50M;

ALTER DATABASE ADD standby logfile thread 2

Group 8 ' +data/stephen/onlinelog/standby08.log ' size 50M,

Group 9 ' +data/stephen/onlinelog/standby09.log ' size 50M,

Group ' +data/stephen/onlinelog/standby10.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) ' sid= ' * ';

Alter system set log_archive_dest_1= ' Location=+data/stephen/archivelog valid_for= (all_logfiles,all_roles) db_unique _name=stephen ' sid= ' *;

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

Alter system set LOG_ARCHIVE_MAX_PROCESSES=30 sid= ' * '; # Adjust the number according to demand

-- Verify that the following parameter is the default value, or modify it if it is not:

Alter system set remote_login_passwordfile=exclusive scope=spfile sid= ' * ';

# remote_login_passwordfile=exclusive/shared

Alter system set log_archive_dest_state_1=enable sid= ' * ';

Alter system set log_archive_dest_state_2=enable sid= ' * ';

-- You can modify the archive log format to differentiate it from datafile:

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

-- To prepare the role parameters:

Alter system set Fal_server=standby sid= ' * ';

Alter system set Fal_client=stephen sid= ' * ';

Alter system set Standby_file_management=auto sid= ' * ';

Alter system set db_file_name_convert= '/oradata/standby ', ' +data/stephen/datafile ' scope=spfile sid= ' * ';

Alter system set log_file_name_convert= '/oradata/standby ', ' +data/stephen/onlinelog ' scope=spfile sid= ' * ';

# If you do not use OMF, you can disable OMF:

alter system reset db_create_file_dest sid= ' * ';

alter system reset DB_CREATE_ONLINE_LOG_DEST_n sid= ' * '; #n as a numeric variable

alter system reset db_recovery_file_dest sid= ' * ';

Specifies spfile modified parameters, which take effect after restarting the instance.

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;

ALTER DATABASE open;

6. Backup Primary Database

Rman Target/<<eof

Run{allocate channel C1 type disk;

Allocate channel C2 type disk;

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

SQL ' alter system switch logfile ';

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

Release channel C1;

Release channel C2;

}

Eof

Standby database configure1. Configuring environment variables

Export Oracle_base=/u01/app/oracle

Export Oracle_home= $ORACLE _base/product/10.2/db_1

Export Oracle_sid=standby

Export path= $ORACLE _home/bin: $PATH

Export ld_library_path= $ORACLE _home/lib #不同平台变量名不一样

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=/u01/app/oracle/product/10.2/db_1)

)

)

Enable monitoring:

Lsnrctl start

Lsnrctl status

(2) Tnsnames.ora

STEPHEN =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = NODE1-VIP) (PORT = 1521))

(ADDRESS = (PROTOCOL = TCP) (HOST = NODE2-VIP) (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.

$ tnsping Stephen

...

OK (0 msec)

(4) password file

Using the Master Vault's password file:

SCP Node1: $ORACLE _home/dbs/orapwstephen1 $ORACLE _home/dbs/orapwstandby

3. Create a related directory

Mkdir-p $ORACLE _base/admin/standby/{adump,bdump,cdump,dpdump,pfile,udump,hdump}

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 Node1:/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= '/u01/app/oracle/admin/standby/adump '

*.background_dump_dest= '/u01/app/oracle/admin/standby/bdump '

*.core_dump_dest= '/u01/app/oracle/admin/standby/cdump '

*.user_dump_dest= '/u01/app/oracle/admin/standby/udump '

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

*.cluster_database=false

*.instance_number=1

*.local_listener= ' (address= (PROTOCOL=TCP) (HOST=STD) (port=1521)) '

*.thread=1

*.undo_management= ' AUTO '

*.undo_tablespace= ' UNDOTBS1 '

*.db_name= ' Stephen '

*.db_unique_name= ' standby '

*.instance_name= ' standby '

*.service_names= ' standby '

*.fal_client= ' standby '

*.fal_server= ' Stephen '

*.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_2= ' Enable '

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

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

*.remote_login_passwordfile= ' exclusive ' #exclusive/shared

*.standby_file_management= ' AUTO '

*.db_file_name_convert= ' +data/stephen/datafile ', '/oradata/standby '

*.log_file_name_convert= ' +data/stephen/onlinelog ', '/oradata/standby '

#  Cancel the following parameter settings:

#*.cluster_database_instances

#*.remote_listener

# if not used, do not set the following parameters to disable OMF:

#*.db_create_file_dest

#*.db_create_online_log_dest_n #n as a numeric variable

#*.db_recovery_file_dest

Create SPFile:

Create SPFile from Pfile;

5. Recovering standby Controlfile

sql> startup Nomount;

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

6. Recovering Data files

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/undotbs1.dbf ';

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

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

Set newname for datafile 5 to '/oradata/standby/users01.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;

Real-time application redo logfile:

Recover managed Standby database using current logfile disconnect;

To cancel the application log:

Recover managed standby database cancel;

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#;

oracle10g R2 "rac+asm→ Single Instance FS"