Shulou(Shulou.com)06/01 Report--

When building a DG in this way, the downtime of the main library is very small, and you only need to restart it to make the parameters take effect. You can also use this method for DB migration. Just set up the DG and activate the repository. In this way, downtime is relatively short throughout the migration.

Pyhsical standby of Oracle 11g supports apply and Real-time query under open read only. Therefore, there are physical standby stability and logical standby report query capabilities.

Oracle: 11.2.0.1

OS: redhat 5.5

Primary IP: 192.168.2.42

DB_NAME=sanfu

Standby IP: 192.168.2.43

DB_NAME=sanfu

one。 Primary operation:

1. Set Archive Mod

This production library is all in this mode.

SQL > archive log list

SQL > shutdown immediate

SQL > startup mount

SQL > alter database archivelog

SQL > archive log list

2. Primary sets force logging

SQL > alter database force logging

SQL > select force_logging from v$database

FORCE_LOG

-

YES

3. Configure Oracle Net

Both the Primary library and Standby need to be modified. You can also use the netca and netmgr commands to configure.

Note: remember to restart listener after modification.

Listener.ora

[oracle@qs-dmm-rh2 admin] $cat listener.ora

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = localhost6.localdomain6) (PORT = 1521))

)

)

ADR_BASE_LISTENER = / u01/app/oracle

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = orcl)

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

(SID_NAME = orcl)

)

)

-- configure static registration

Tnsname.ora

[oracle@qs-dmm-rh2 admin] $cat tnsnames.ora

Sanfus =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = orcl)

)

)

Sanfup =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = orcl)

)

)

4. Add data guard parameter

Create a pfile and add the following files:

SQL > create pfile from spfile

* .db_name='orcl'

* .db_unique_name='sanfup'

* .log_archive_config='dg_config= (sanfup,sanfus)'

* .log_archive_dest_1='location=/u01/archivelog valid_for= (all_logfiles,all_roles) db_unique_name=sanfup'

* .log_archive_dest_2='service=sanfus reopen=120 lgwr async valid_for= (online_logfiles,primary_role) db_unique_name=sanfus'

* .log_archive_dest_state_1=enable

* .log_archive_dest_state_2=enable

* .standby_file_management='auto'

* .fal_server='sanfus'

* .fal_client='sanfup'

Note:

In the Data Guard of Oracle 11g, the standby_archive_dest parameter has been cancelled.

The location of the Standby archive files is based on the following rules:

(1) when valid_for= (all_logfiles,all_roles) is set for LOG_ARCHIVE_DEST_n, Oracle selects the LOG_ARCHIVE_DEST_n parameter as the archive target when the standby_archive_dest parameter is not defined.

(2) if the LOG_ARCHIVE_DEST_n parameter is independently set to the valid_for= (standby_logfile,*) attribute while the first step is set, then any LOG_ARCHIVE_DEST_n value will be automatically selected when the compatible parameter is greater than 10.0.

(3) if LOG_ARCHIVE_DEST_n is not set, the default location is:

$ORACLE_HOME/dbs.

However, the default values of the valid_for parameter are all_logfiles and all_roles. So as long as the local archive location is set, the remote archive files will also be placed in this directory.

5. Restart the main library with the new pfile

SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL > create spfile from pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora'

File created.

SQL > startup

ORACLE instance started.

Total System Global Area 422670336 bytes

Fixed Size 1336960 bytes

Variable Size 310380928 bytes

Database Buffers 104857600 bytes

Redo Buffers 6094848 bytes

Database mounted.

Database opened.

SQL >

two。 Standby side settings:

1. Create a related directory structure

[oracle@qs-dmm-rh3 trace] $mkdir-p / u01/app/oracle/oradata/dave

The directory we created here is different from the Target library. We need to convert it in the parameter file.

two。 Create a password file for standby

[oracle@qs-dmm-rh3 trace] $orapwd file=?/dbs/orapwdave password=oracle

3. Create initialization parameters for standby:

* .compatible='11.2.0.4.0'

* db_name=orcl

* .Applet'/ u01qapplet control_files='/u01/app/oracle/oradata/dave/control01.ctl', 'oradataUnix DaveUniver control02.ctl'

'/ u01applink oracleandoradataUniverse control l03.ctl'

* .db_unique_name='sanfus'

* .log_archive_config='dg_config= (sanfup,sanfus)'

* .log_archive_dest_1='location=/u01/archivelog valid_for= (standby_logfile, standby_role) db_unique_name=sanfus'

* .log_archive_dest_2='service=sanfup reopen=120 lgwr async valid_for= (online_logfiles,primary_role) db_unique_name=sanfup'

* .log_archive_dest_state_1=enable

* .log_archive_dest_state_2=enable

* .standby_file_management='auto'

* .fal_server='sanfup'

* .fal_client='sanfus'

* .log_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/dave'

* .db_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/dave'

4. Start standby to the nomount state with pfile:

SQL > startup nomount pfile=?/dbs/initorcl.ora

ORACLE instance started.

Total System Global Area 146472960 bytes

Fixed Size 1335080 bytes

Variable Size 92274904 bytes

Database Buffers 50331648 bytes

Redo Buffers 2531328 bytes

5. Start duplicate

[oracle@qs-dmm-rh3 dbs] $rman target sys/oracle@sanfup auxiliary sys/oracle@sanfus

Recovery Manager: Release 11.2.0.1.0-Production on Tue Mar 8 16:10:25 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to target database: ORCL (DBID=1272955137)

Connected to auxiliary database: ORCL (not mounted)

RMAN > duplicate target database for standby from active database

Starting Duplicate Db at 08-MAR-11

Using target database control file instead of recovery catalog

Allocated channel: ORA_AUX_DISK_1

Channel ORA_AUX_DISK_1: SID=20 device type=DISK

Contents of Memory Script:

{

Backup as copy reuse

Targetfile'/ u01 targetfile'/ u01qapapworcl1 / u01apapworcl` auxiliary format

'/ u01apapworcl` / u01apapworcl'

-- the password file is copied when you create a standby with duplicate

}

Executing Memory Script

Starting backup at 08-MAR-11

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=45 device type=DISK

Finished backup at 08-MAR-11

Contents of Memory Script:

{

Backup as copy current controlfile for standby auxiliary format'/ u01qapplash oracleUnixoradataUniverse DaveUniver control01.ctl'

Restore clone controlfile to'/ u01 from

'/ u01 apprenticeship oradataUniverse control 01.ctl'

Restore clone controlfile to'/ u01qapplash oracleUniple oradata from DaveUniver control03.ctl'

'/ u01 apprenticeship oradataUniverse control 01.ctl'

-- create control files

}

Executing Memory Script

Starting backup at 08-MAR-11

Using channel ORA_DISK_1

Channel ORA_DISK_1: starting datafile copy

Copying standby control file

Output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f tag=TAG20110308T161152 RECID=4 STAMP=745258313

Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

Finished backup at 08-MAR-11

Starting restore at 08-MAR-11

Using channel ORA_AUX_DISK_1

Channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 08-MAR-11

Starting restore at 08-MAR-11

Using channel ORA_AUX_DISK_1

Channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 08-MAR-11

Contents of Memory Script:

{

Sql clone 'alter database mount standby database'

-- launch the slave library to mount standby

}

Executing Memory Script

Sql statement: alter database mount standby database

Contents of Memory Script:

{

Set newname for tempfile 1 to

"/ u01/app/oracle/oradata/dave/temp01.dbf"

Switch clone tempfile all

Set newname for datafile 1 to

"/ u01/app/oracle/oradata/dave/system01.dbf"

Set newname for datafile 2 to

"/ u01/app/oracle/oradata/dave/sysaux01.dbf"

Set newname for datafile 3 to

"/ u01/app/oracle/oradata/dave/undotbs01.dbf"

Set newname for datafile 4 to

"/ u01/app/oracle/oradata/dave/users01.dbf"

Backup as copy reuse

Datafile 1 auxiliary format

"/ u01/app/oracle/oradata/dave/system01.dbf" datafile

2 auxiliary format

"/ u01/app/oracle/oradata/dave/sysaux01.dbf" datafile

3 auxiliary format

"/ u01/app/oracle/oradata/dave/undotbs01.dbf" datafile

4 auxiliary format

"/ u01/app/oracle/oradata/dave/users01.dbf"

Sql 'alter system archive log current'

-- datafile convert to another directory

}

Executing Memory Script

Executing command: SET NEWNAME

Renamed tempfile 1 to / u01/app/oracle/oradata/dave/temp01.dbf in control file

Executing command: SET NEWNAME

Executing command: SET NEWNAME

Executing command: SET NEWNAME

Executing command: SET NEWNAME

Starting backup at 08-MAR-11

Using channel ORA_DISK_1

Channel ORA_DISK_1: starting datafile copy

Start copy datafile. If the data file is large, this will be slow.

Input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf

Output file name=/u01/app/oracle/oradata/dave/system01.dbf tag=TAG20110308T161204

Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55

Channel ORA_DISK_1: starting datafile copy

Input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf

Output file name=/u01/app/oracle/oradata/dave/sysaux01.dbf tag=TAG20110308T161204

Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35

Channel ORA_DISK_1: starting datafile copy

Input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf

Output file name=/u01/app/oracle/oradata/dave/undotbs01.dbf tag=TAG20110308T161204

Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

Channel ORA_DISK_1: starting datafile copy

Input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf

Output file name=/u01/app/oracle/oradata/dave/users01.dbf tag=TAG20110308T161204

Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 08-MAR-11

Sql statement: alter system archive log current

Contents of Memory Script:

{

Switch clone datafile all

}

Executing Memory Script

Datafile 1 switched to datafile copy

Input datafile copy RECID=4 STAMP=745258432 file name=/u01/app/oracle/oradata/dave/system01.dbf

Datafile 2 switched to datafile copy

Input datafile copy RECID=5 STAMP=745258432 file name=/u01/app/oracle/oradata/dave/sysaux01.dbf

Datafile 3 switched to datafile copy

Input datafile copy RECID=6 STAMP=745258432 file name=/u01/app/oracle/oradata/dave/undotbs01.dbf

Datafile 4 switched to datafile copy

Input datafile copy RECID=7 STAMP=745258432 file name=/u01/app/oracle/oradata/dave/users01.dbf

Finished Duplicate Db at 08-MAR-11

RMAN >

The DG copy to this step has been completed. But there are still some details to deal with.

Two points to note:

(1) if you are using a non-catalog, add the nocatalog keyword to the rman connection, such as:

DG2:/home/oracle > rman targetsys/oracle@DG_PD auxiliarysys/oracle@DG_ST nocatalog

Recovery Manager: Release 11.2.0.3.0-Production on Fri Oct 28 18:46:46 2011

Copyright (c) 1982, 2011, Oracle and/or itsaffiliates. All rights reserved.

Connected to target database: DG (DBID=1679060044)

Using target databasecontrol file instead of recovery catalog

Connected to auxiliary database: DG (notmounted)

Otherwise, the following error will be reported:

DBGSQL: TARGET > begin: fhdbi: = dbms_rcvcat.getDbid; end

DBGSQL: sqlcode = 6550

DBGSQL: B: fhdbi = 32767

RMAN-00571: =

RMAN-00569: = ERROR MESSAGESTACK FOLLOWS =

RMAN-00571:===

RMAN-03002: failure of Duplicate Db commandat 10/28/2011 17:53:04

RMAN-05501: aborting duplication of targetdatabase

RMAN-03015: error occurred in stored scriptMemory Script

ORA-06550: line 1, column 17:

PLS-00201: identifier'DBMS_RCVCAT.GETDBID' must be declared

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

(2) when executing duplicate, if the source and target database directories are the same, then you need to add nofilenamecheck to duplicate, as follows:

RMAN > duplicate target database for standby from active database dorecover nofilenamecheck

In the above example, the directory structure is different, so this parameter is not used. If the directory is the same and the parameter is not added, the following error will be reported:

RMAN > duplicate target database forstandby from active database dorecover nofilenamecheck

RMAN-05501: aborting duplication of targetdatabase

RMAN-05001: auxiliary file name/u01/app/oracle/oradata/dg/users01.dbf conflicts with a file used by the targetdatabase

RMAN-05001: auxiliary file name/u01/app/oracle/oradata/dg/undotbs01.dbf conflicts with a file used by thetarget database

RMAN-05001: auxiliary file name/u01/app/oracle/oradata/dg/sysaux01.dbf conflicts with a file used by thetarget database

RMAN-05001: auxiliary file name/u01/app/oracle/oradata/dg/system01.dbf conflicts with a file used by thetarget database

three。 Follow-up work

1. The main library has already used spfile, but the backup library is still using the previous pfile:

Primary:

SQL > show parameter pfile

NAME TYPE VALUE

-

Spfile string / u01/app/oracle/product/11.2.0

Standby:

SQL > show parameter pfile

NAME TYPE VALUE

-

Spfile string

Pfile contains some basic parameters that we set up. However, the repository is redundant, so it is recommended to use the pfile copy of the main library, and then modify the relevant parameters before creating the spfile. In this way, even if it is switched, it will have little impact on DB.

two。 As long as the listening of the slave library is not restarted, the master library can still be recognized after restarting the slave library. If the listening of the standby library is restarted. Then the main library needs to be restarted.

3. The Standby at the end of the replication only starts to the state of mount standby. MRP's application archiving program is not started. So at this time to query the main and standby database, archiving is out of sync. The MRP process needs to be started manually.

SQL > alter database recover managed standby database disconnect from session

4. Prepare library Standby redo log questions:

After the end of duplicate, no standby redo log file was added to the slave library. But the main library uses logs sent by lgwr async. When the RFS process of the slave database receives the log and finds that the slave database does not have standby redo log, the slave database will automatically write it to the archive file with ARCH.

The following is the alert log of the repository:

Tue Mar 08 16:53:32 2011

Archived Log entry 9 added for thread 1 sequence 21 rlc 745174404 ID 0x4bdfd301 dest 2:

RFS [2]: Opened log for thread 1 sequence 22 dbid 1272955137 branch 745174404

Tue Mar 08 16:53:36 2011

Media Recovery Log / u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch2_21_745174404.dbf

Media Recovery Waiting for thread 1 sequence 22 (in transit)-in transit

Tue Mar 08 16:58:58 2011

Archived Log entry 10 added for thread 1 sequence 22 rlc 745174404 ID 0x4bdfd301 dest 2:

RFS [2]: Opened log for thread 1 sequence 23 dbid 1272955137 branch 745174404

Tue Mar 08 16:59:00 2011

Media Recovery Log / u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch2_22_745174404.dbf

Media Recovery Waiting for thread 1 sequence 23 (in transit)

Note that the archive directory here uses $ORACLE_HOME/dbs, and the default archive directory is also used when automatically converting to ARCH.

5. Add standby redo log to the slave library:

SQL > alter database add standby logfile'/ u01 size size 50m

Alter database add standby logfile'/ u01 size size 50m

*

ERROR at line 1:

ORA-01156: recovery or flashback in progress may need access to files

-- to add standby redo log to the slave database, you need to stop MRP first

SQL > alter database recover managed standby database cancel

Database altered.

SQL > alter database add standby logfile'/ u01 size size 50m

Database altered.

SQL > alter database add standby logfile'/ u01 size size 50m

Database altered.

SQL > alter database add standby logfile'/ u01 size size 50m

Database altered.

SQL > alter database add standby logfile'/ u01 size size 50m

Database altered.

SQL > alter database recover managed standby database disconnect from session

Database altered.

Take a look at the log again:

Tue Mar 08 17:47:39 2011

Archived Log entry 15 added for thread 1 sequence 27 ID 0x4bdfd301 dest 1:

RFS [2]: Selected log 4 for thread 1 sequence 28 dbid 1272955137 branch 745174404

Tue Mar 08 17:47:43 2011

Archived Log entry 16 added for thread 1 sequence 28 ID 0x4bdfd301 dest 1:

Media Recovery Log / u01/archivelog/1_27_745174404.dbf

RFS [2]: Selected log 4 for thread 1 sequence 29 dbid 1272955137 branch 745174404

Media Recovery Log / u01/archivelog/1_28_745174404.dbf

Media Recovery Waiting for thread 1 sequence 29 (in transit)

After we added standby redo log, the archive file became the parameter specified by Log_archive_dest_n that we specified.

6. Add standby redo log to the main library as well

SQL > alter database add standby logfile'/ u01 size size 50m

Database altered.

SQL > alter database add standby logfile'/ u01 size size 50m

Database altered.

SQL > alter database add standby logfile'/ u01 size size 50m

Database altered.

SQL > alter database add standby logfile'/ u01 size size 50m

Database altered.

7. Enable real-time apply to achieve real-time query:

SQL > alter database recover managed standby database cancel

Database altered.

SQL > ALTER DATABASE OPEN

Database altered.

SQL > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT

Database altered.

8. Verify real-time apply and real-time query:

Primary:

SQL > create table dave (id number,name varchar2 (20))

Table created.

SQL > insert into dave values (1)

1 row created.

SQL > commit

Commit complete.

Standby:

SQL > select open_mode from v$database

OPEN_MODE

-

READ ONLY WITH APPLY

SQL > select * from dave

ID NAME

--

1 tianlesoftware

Summary:

The physical Data Guard of 11gR2 is very powerful.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.