oracle11g R2 "Rac+asm→rac+asm"

oracle11g R2 "Rac+asm→rac+asm"

Environment used in this demo case: RAC+ASM+OMF

	Primary	Standby
OS Hostname	Node1,node2	Dgnode1,dgnode2
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	Stephen1,stephen2	Standby1,standby2

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 = DGNODE1-VIP) (PORT = 1521))

(ADDRESS = (PROTOCOL = TCP) (HOST = DGNODE2-VIP) (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 ignorecase=y 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 ' size 50M,

Group 6 ' +data ' size 50M,

Group 7 ' +data ' size 50M;

ALTER DATABASE ADD standby logfile thread 2

Group 8 ' +data ' size 50M,

Group 9 ' +data ' size 50M,

Group ' +data ' 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 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

-- confirm the following parameters and modify them if they are not the default values:

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 Standby_file_management=auto sid= ' * ';

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

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

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

When using OMF, only one ASM disk group can be used in the master and standby libraries, otherwise OMF is disabled:

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= ' * ';

5. Open Archive mode

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

Archive log list;

Srvctl Stop database-d Stephen

Srvctl Start database-d Stephen-o Mount

ALTER DATABASE Archivelog; -- execute at one node

ALTER DATABASE open; -- each node executes

6. Backup Primary Database

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 Configure pre-preparation

First install the Crs,rdbms software, configure the ASM,NETCA to create the listener.

1. Configure each node environment variable

Export Oracle_base=/oracle/app/oracle

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

Export oracle_sid=standby1 # based on node modification

Export path= $ORACLE _home/bin: $PATH

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

2. Configuring Redo Transport Authentication

(1) Tnsnames.ora

Configure the $oracle_home/network/admin/tnsnames.ora files for each node:

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 = DGNODE1-VIP) (PORT = 1521))

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

(Connect_data =

(SERVER = dedicated)

(service_name = standby)

)

)

Heavy load monitoring of each node:

Lsnrctl Reload

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

$ tnsping Stephen

(3) password file

Using the Master Vault's password file:

SCP $ORACLE _home/dbs/orapwstephen1 dgnode1: $ORACLE _home/dbs/orapwstandby1

SCP $ORACLE _home/dbs/orapwstephen1 dgnode2: $ORACLE _home/dbs/orapwstandby2

3. Create a Directory

Create related directories on each node:

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

To create an ASM directory:

Export ORACLE_SID=+ASM1

Sql> alter DiskGroup Data add directory ' +data/standby ';

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/initstandby1.ora

Modify the Pfile for the required standby pfile, paying particular attention to the following example parameter settings:

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

*.cluster_database_instances=2

*.cluster_database=true

*.db_create_file_dest= ' +data '

*.db_name= ' Stephen '

*.db_unique_name= ' standby '

*.service_names= ' standby '

Standby1.instance_name= ' Standby1 '

Standby2.instance_name= ' Standby2 '

standby2.instance_number=2

Standby1.instance_number=1

*.remote_listener= ' cluster-scan:1521 '

Standby1.local_listener= ' (address= (PROTOCOL=TCP) (HOST=DGNODE1-VIP) (port=1521)) '

Standby2.local_listener= ' (address= (PROTOCOL=TCP) (HOST=DGNODE2-VIP) (port=1521)) '

*.remote_login_passwordfile= ' Exclusive '

standby2.thread=2

Standby1.thread=1

*.undo_management= ' AUTO '

Standby1.undo_tablespace= ' UNDOTBS1 '

Standby2.undo_tablespace= ' UNDOTBS2 '

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

*.log_archive_dest_1= ' Location=+data 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_max_processes=30 # Adjust the number according to demand

*.remote_login_passwordfile=exclusive # exclusive/shared

*.log_archive_dest_state_1=enable

*.log_archive_dest_state_2=enable

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

*.fal_server=stephen

*.standby_file_management=auto

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

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

Starting the DB instance to the Nomount phase with pfile, you only need to start an instance for recovery:

sql> startup Nomount pfile= '?/dbs/initstandby1.ora ';

Note: The following recovery operations are done only on a single node.

5. Recovering standby Controlfile

sql> startup Nomount;

rman> restore standby controlfile to ' +data ' from '/backup/c_0aqn43be_1_1.bak ';

Modify the Control_files parameter to perform the Controlfile location:

Alter system set control_files= ' +data/standby/controlfile/current.257.896669437 ' scope=spfile sid= ' * ';

To restart an instance:

Shutdown immediate;

Startup Nomount pfile= '?/dbs/initstandby1.ora ';

ALTER DATABASE mount standby database;

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> run{

Set newname for datafile 1 to ' +data ';

Set newname for datafile 2 to ' +data ';

Set newname for DataFile 3 to ' +data ';

Set newname for datafile 4 to ' +data ';

Set newname for datafile 5 to ' +data ';

Set newname for Tempfile 1 to ' +data ';

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. Registering OCR

Register db,instance to OCR:

Srvctl add database-d standby-o $ORACLE _home

Srvctl add instance-d standby-i standby1-n dgnode1

Srvctl add instance-d standby-i standby2-n dgnode2

To view resource status:

Crsctl Status Res-t-W "TYPE = Ora.database.type"

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

Nametarget State SERVER State_details

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

Cluster Resources

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

Ora.standby.db

1 OFFLINE OFFLINE

2 OFFLINE OFFLINE

Enable DB following CRS boot:

Srvctl Enable database-d Standby

8. Create SPFile

sql> create spfile= ' +data ' from pfile= '?/dbs/initstandby1.ora ';

Close the DB instance, modify each node pfile, and then start the database with SPFile:

sql> shutdown Immediate

View the location of SPFile with the Asmcmd command

Asmcmd Find +data/standby spfile*

+data/standby/parameterfile/spfile.256.896665481

Modify each node Pfile:

CD $ORACLE _home/dbs

echo "spfile= ' +data/standby/parameterfile/spfile.256.896665481" >initstandby1.ora

echo "spfile= ' +data/standby/parameterfile/spfile.256.896665481" >initstandby2.ora

Start the instance with SPFile to the Mount stage (data Guard) or the open Read Only stage (Active Data Guard):

-- Boot to mount:

Srvctl Start database-d Standby-o Mount

-- boot to open Read only:

Srvctl start database-d standby-o ' Read Only '

To view resource status:

Crsctl Status Res-t-W "TYPE = Ora.database.type"

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

Nametarget State SERVER State_details

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

Cluster Resources

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

Ora.standby.db

1 OFFLINE OFFLINE Dgnode 1 open,readonly

2 OFFLINE OFFLINE Dgnode 2 open,readonly

If the status is displayed as open, this is an Oracle BUG, refer to MoS DOC 1622876.1

9. Application Log

The application log only needs to be executed on one node, and the log application can choose to apply archive log or redo 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;

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

Recover managed Standby database using current logfile disconnect;

10. 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 "Rac+asm→rac+asm"