oracle11g Dataguard Complete Brochure

First, preface:
Network on the configuration of Dataguard a lot of articles, but a lot of oracle11g articles are actually only 9 10 on the run, such as Fal_client in 11g has been obsolete, but now the network of articles are not labeled this point. And for the specific meaning of vague for beginners can only know it but do not know why. This article I want to let people like me for dataguard configuration not only know how to configure, but also to know why this configuration, this effect is the best.
This article is not just about documenting how it is configured, but also about why, and the attention points, I hope this article can be used as a reference manual for Dataguard configuration.

Second, the premise
1. The main library is the archive mode:
If we don't know why the archive mode is, then we should not be clear about what Dataguard is for. Through many of the official languages, we need to make clear that DG (Dataguard, post-same) is actually useful for high availability. The implementation of the principle is to get data from the main library from the library, in the main library when the exception, from the library to take over the main library, complete the identity changes. Can be a master library with a maximum of 9 from the library. Also divided into logical standby and physical standby here we are talking about physical standby.
Once created and configured as standby, the DG is responsible for transmitting the primary database redo data to the standby database, and the standby database maintains the same transaction with the redo database by applying the primary data received by the application.
This is clear, you need to ensure that the master-slave library is consistent, need to transfer archive log and redo log to the slave library, if not the archive mode can not guarantee the data of the master and slave library consistent.

2. From the library only need to install the database software, data from the main library after the completion of the transfer.

3. Many people say that 11g has active dataguard (ADG), logic standby is actually useless.

4. The master-slave library hardware is best consistent. The Oracle database version needs to be consistent.
(1) Memory Check entries:
# grep Memtotal/proc/meminfo
Swap partition Check: If the presence of 1-2g,swap is 1.5 times times, 2-16g,1 times, more than 16G, set to 16G.
# grep Swaptotal/proc/meminfo
To view the shared memory size:
# df-h/DEV/SHM

(2) View System processor architecture, consistent with Oracle installation package
# uname-m

(3) Space/tmp must be greater than 1G
# DF-H/tmp

5. Configure the Environment database user must have SYSDBA permissions
6. Back environment: Main Library 192.168.215.101 DB instance name: ORCL DB_UNIQUE_NAME:ORCL
192.168.215.102 DB instance name from library: ORCL DB_UNIQUE_NAME:ORCLDG

Third, the configuration
1. Determine if the DG has been installed:
SELECT * from v$option where parameter = ' Oracle Data Guard ';
If True indicates that the installation can be configured, the appropriate components will need to be installed.

2. Set the main library to force logging.
Database Operations log redo log by default, but in some specific cases you can use nologging to not generate redo information
(1) Bulk INSERT (using direct path insertion via the/*+append */hint). or with Sql*loader direct path loading). Table data does not generate redo, but
All index modifications generate redo, but all index modifications generate redo (although the table does not generate logs, the indexes on the table generate redo! ）。
(2) LOB operations (updates to large objects do not have to generate a log).
(3) Creating tables with CREATE TABLE as select
(4) Various ALTER TABLE operations, such as Move and split
(5) In some table migration and table space migrations, ALTER TABLE A nologging can be used; or alter Tablespace SNK Nologging, and then modify back to logging state after the operation is complete.
need to say more here, if you use nologging to import large quantities of data, the subsequent changes to the data will be in the redo or archive log, but the baseline data is not, so once the media damage is not fully recoverable, A full or level 0 backup must be done after switching back to logging with nologging.

(1) Mandatory logging: Sql>alter database force logging;
(2) Check status (yes mandatory): Sql>select name,force_logging from V$database;
(3) If you need to add or delete data files in the main library, these files will be added or deleted in the backup, using the following:
Sql>alter system set standy_file_management= ' AUTO ';
By default this parameter is manual manual sql>show parameter standby

3. Create standby log files (alternate log file)
Use standby log from library Files to save the redo logs received from the main library. Since it is primarily used from the library, why do I need to create the
standby log files on the main library? The main reason is two: one is that the main library may be converted to a standby, and the repository is required to have standby log files if the main library
establishes standby log The files repository is automatically created.
Establish standby if you want to pay attention to the following points:
<1>standby log files are the same size as redo log files.
Query redo log files file size (default 50m,3): Select group#,bytes/1024/1024 as M from V$log
<2> Generally, the number of Standbyredo log filegroups is greater than The primary database has at least one number of online redo log file groups. The
recommended number of Standbyredo log groups is based on the number of threads in the primary database (the number of threads here can be understood as the number of RAC
nodes in the RAC structure). The
has a recommended formula for reference: (number of log groups per thread +1) * Maximum number of threads
assuming that the node is now 1, then = (3+1) *1=4
If it is two nodes = (3+1) *2=8
Here we create 4 standby logfile:
Another: Do not suggest that the group number group# next to redo, because the subsequent redo is possible to adjust, here we from the establishment from 11 to 14 standby logfile

1
2
3
4
5
6
7
8
# cd $ORACLE _base/oradata/orcl/
# mkdir DG
# chown ORACLE:DBA DG
sql> ALTER DATABASE Add standby logfile Group One '/opt/oracle/oradata/orcl/dg/standby11.log ' size 50M;
sql> ALTER DATABASE add standby logfile Group '/opt/oracle/oradata/orcl/dg/standby12.log ' size 50M;
sql> ALTER DATABASE add standby logfile Group/opt/oracle/oradata/orcl/dg/standby13.log ' size 50M;
sql> ALTER DATABASE add standby logfile Group '/opt/oracle/oradata/orcl/dg/standby14.log ' size 50M;

4. Creation and transmission of password files and control files
(1) The general database by default has a password file, stored in $oracle_home/dbs/orapwsid here for ORAPWORCL
If there is no sql>orapwd file= $ORACLE _HOME/DBS/ORAPWORCL password=oracle
(2) Check whether the Remote_login_passwordfile value is EXCLUSIVE
Sql>show parameter Remote_login_passwordfile
If the value is not exclusive, then: Alter system set remote_login_passwordfile=exclusive Scope=spfile;
(3) Password file requires SCP to slave library
# SCP ORAPWORCL [email protected]:/opt/oracle/11.2/dbs prompt Input Yes
(4) Control file:
11g control File Two copies, the same content, a copy in the $oracle_base/oradata/orcl/control01.ctl
A copy of the/opt/oracle/flash_recovery_area/orcl/control02.ctl

To generate the standby control file:

1
2
3
4
Sql>shutdown Immediate
Sql>startup Mount
Sql>alter Database Create standby controlfile as '/tmp/standby_control01.ctl ';
Sql>startup Open;

Then create the corresponding directory in the repository and authorize
mkdir ORCL---chown oracle:oinstall (or DBA) ORCL

SCP Control01.ctl [Email PROTECTED]:/OPT/ORACLE/ORADATA/ORCL
SCP Control02.ctl [Email protected]:/opt/oracle/flash_recovery_area/orcl/

5.db_name and Db_unique_name
The default db_name and Db_unique_name and instance names are the same, this is ORCL
It is important to note that the db_unique_name of the master and slave libraries in DG are inconsistent and need to be separated.
Here we set the db_unique_name of the main library to ORCL, from the library to the ORCLDG
Sql>show parameter Db_unique_name
Settings: Alter system set DB_UNIQUE_NAME=ORCL Scope=spfile;
-Note that although the default Db_unique_name and db_name are consistent, they need to be set explicitly, otherwise this parameter is not in SPFile

6. Flash back to the database:
It is strongly recommended to turn on the database flashback feature. Flashback allows you to restore a database to a previous point in time. This feature is useful when a failover occurs,
It allows you to flash the old Main library back to the fault before converting it to a standby. If the Flashback feature is not enabled, you will have to rebuild the repository, which means copying the data file again.
In addition to this benefit, flash pensive can in some cases allow you to avoid recovering data from a backup.

(1) Fast recovery zone (flash/fast Recovery area), default is configured, but need to confirm that the area of the disk is large enough, at least 300G (default 3G)
Sql>show parameter Db_recovery_file_dest
Location can be modified: Sql>alter system set db_recovery_file_dest= ' New path ';
Change size: Sql>alter system set db_recovery_file_dest_size=400g;

(2) Check if enabled, default is not turned on
Sql>select flashback_on from V$database;
Open: Sql>alter database flashback on;
If you encounter ORA-01153 error, it must be in the standby library to do this. You need to cancel the Redo log app first, enable the flashback log, and then re-enable the log app.
In the main library, the flashback log is enabled and the standby is not synchronized. You must manually enable the flashback log on both the master and the standby libraries.
If you do not enable the flashback log, when a failover occurs, you will need to completely restart the creation of a standby library.

7.sql*net settings
(1) Configuring the main library for monitoring
Although can be configured through NETCA, but in addition to this default, we also need a static registration Sid_list_listener, if there is no this from the parameter and
Dataguard boot sequence is incorrect, the main library will be reported ping[arc1]:heartbeat failed to connect to standby ' * * *. Error is 12514 causes archive not to complete
Configured as follows

Sid_list_listener=
(Sid_list =
(Sid_desc =
(Global_dbname = ORCL)
(Oracle_home =/opt/oracle/11.2)
(Sid_name = ORCL)
)
)
LISTENER =
(Description_list =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = primarydb) (PORT = 1521))
)
)

#vi $ORACLE _home/network/admin/listener.ora To add the above content

(2) Configuration TNSNames
#vi $ORACLE _home/network/admin/tnsnames.ora

ORCL =
(DESCRIPTION =
(Address_list =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.215.101) (PORT = 1521))
)
(Connect_data =
(service_name = ORCL)
)
)

ORCLDG =
(DESCRIPTION =
(Address_list =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.215.102) (PORT = 1521))
)
(Connect_data =
(service_name = ORCLDG)
)
)

(3) Transfer to repository and modify Listener.ora and Tnsnames.ora

SCP $ORACLE _home/network/admin/listener.ora [email protected]:/opt/oracle/11.2/network/admin/
SCP $ORACLE _home/network/admin/tnsnames.ora [email protected]:/opt/oracle/11.2/network/admin/

--listener.ora:
Sid_list_listener
(Sid_list =
(Sid_desc =
(Global_dbname = ORCLDG)
(Oracle_home =/opt/oracle/11.2)
(Sid_name = ORCL)
)
)

LISTENER =
(Description_list =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = standbydb) (PORT = 1521))
)
)

Tnsnames.ora: No modification required

8. Redo Log Transport Configuration
(1) Configure the archive log location:
Query the archive path that has been set sql:archive log list or show parameter log_archive_dest_1

Sql> alter system set log_archive_dest_1= ' Location=/opt/oracle/oradata/orcl/archive1 valid_for= (All_logfiles, Primary_role) DB_UNIQUE_NAME=ORCL ' scope=spfile;

You can also use the Quick recovery area as an archive directory, such as Location=use_db_recovery_file_dest
The official documentation says using valid_for= (Online_logfiles, All_roles), which will cause the repository to be unable to archive alternate log files because they are not online logs.
However, if you use the All_logfiles option, the Master repository will be able to archive both online and standby logs.
You must use All_logfiles if you want to back up the repository and back up the archive log at the same time.

(2) Configure redo log to backup library:

Sql>alter system set log_archive_dest_2= ' Service=orcldg lgwr sync valid_for= (online_logfile,primary_role)
Db_unique_name=orcldg ';

(3) Note that the Standby_archive_dest parameter is not required and has been deprecated by the official. When this parameter is set, the database is started and only ORA-32004 is reported:
Obsolete or deprecated parameter (s) specified for RDBMS instance wrong.

9. Configure Fal_server
This parameter specifies where to find the missing archive log when there is a problem with the log transfer. It is used when there is a gap between the redo logs received by the repository.
This can happen when a log transfer is interrupted, such as when you need to perform maintenance operations on the standby. There was no log transmission during the standby maintenance period, and the gap appeared.
With this parameter set, the repository will proactively look for the missing logs and require the main library to be transferred.
You are the main library, just fill in:
Fal_server= from Library
From the library, in turn:
Fal_server= Main Library
Note: The fal_client has been deprecated in 11g, although it can be configured but has no effect.

Sql>alter system set fal_server= ' ORCLDG ';

The name of another library in the 10.Data Guard configuration
Sql> alter system set Log_archive_config = ' dg_config= (ORCL,ORCLDG) ';

The above approach is that we use the ALTER system to modify the online, there is a more convenient way (but error-prone, so convenient and safe when not both)
Sql>create pfile from SPFile;
# Manual Modification Pfile
Sql>create SPFile from Pfile;
It then uses pfile to generate spfile and transfer pfile to generate SPFile from the library after modification
Note Manual additions:
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable

VI Initorcl.ora

orcl.__db_cache_size=180355072
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
Orcl.__oracle_base= '/opt/oracle ' #ORACLE_BASE set from environment
orcl.__pga_aggregate_target=264241152
orcl.__sga_target=494927872
Orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=289406976
orcl.__streams_pool_size=8388608
*.audit_file_dest= '/opt/oracle/admin/orcl/adump '
*.audit_trail= ' DB '
*.compatible= ' 11.2.0.0.0 '
*.control_files= '/opt/oracle/oradata/orcl/control01.ctl ', '/opt/oracle/flash_recovery_area/orcl/control02.ctl ' # Restore Controlfile
*.db_block_size=8192
*.db_domain= "
*.db_name= ' ORCL '
*.db_recovery_file_dest= '/opt/oracle/flash_recovery_area '
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest= '/opt/oracle '
*.dispatchers= ' (protocol=tcp) (SERVICE=ORCLXDB) '
*.fal_server= ' Orcldg '
*.job_queue_processes=1000
*.log_archive_config= ' dg_config= (ORCL,ORCLDG) '
*.log_archive_dest_1= ' location=/opt/oracle/oradata/orcl/archive1 valid_for= (all_logfiles,primary_role) db_unique _NAME=ORCL '
*.log_archive_dest_2= ' service=orcldg lgwr sync valid_for= (online_logfile,primary_role) Db_unique_name=orcldg '
*.log_archive_format= ' orcl_%t_%s_%r.dbf '
*.memory_target=756023296
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile= ' EXCLUSIVE '
*.standby_file_management= ' AUTO '
*.undo_tablespace= ' UNDOTBS1 '

SCP Initorcl.ora [Email protected]:/opt/oracle/11.2/dbs/
Modify the Initorcl.ora modification parameters of the repository as follows:

*.db_name= ' ORCL '
*.db_unique_name= ' orcldg '
*.fal_server= ' orcl '
*.log_archive_config= ' dg_config= ( ORCLDG,ORCL) '
*.log_archive_dest_1= ' location=/opt/oracle/oradata/orcl/archive1 valid_for= (All_logfiles, Primary_role) db_unique_name=orcldg '
*.log_archive_dest_2= ' service=orcl lgwr sync valid_for= (Online_logfile, Primary_role) DB_UNIQUE_NAME=ORCL '



then sql>create spfile from Pfile;

11. Transfer Master Library data to Repository
(1) scp-l 8192-rp/opt/oracle/oradata/orcl/[email protected]:/opt/oracle/oradata/
Note that the destination does not add ORCL the
-L is the limit limit for automatically creating the directory, so the maximum is 8192/8=1m speed, in order to resolve the stalled problem
-rp looping subdirectory files
(2) Creating the required directories in SPFile
such as/opt /oracle/admin/orcl/adump Dpdump pfile

12. Enable the physical standby database
Sql>startup Nomount
Sql>alter database mount standby database;
(1) Start Redo Application
sql> ALTER database RECOVER MANAGED STANDBY database DISCONNECT from SESSION;
Launch real-time apps
sql> ALTER database RECOVER MANAGED STANDBY database USING current LOGFILE DISCONNECT from SESSION;
This command instructs the repository to start using an alternate log file for recovery. It also tells the repository command to complete and return to the command line interface

sql> ALTER database RECOVER MANAGED STANDBY database CANCEL;
At this time only temporarily redo the application, does not stop the Standby database, Standby will still remain received but will not be applied
Receive the archive until you start the Redo app again

(2) Stop standby
Under normal circumstances, first
sql> ALTER database RECOVER MANAGED STANDBY database CANCEL;
And then sql>shutdown immediate.
Of course, you can shutdown immediate directly.

(3) Management mode and read-only mode for standby server
<1> boot to admin mode

Sql>shutdown immediate;
Sql>startup Nomount;
Sql>alter database mount standby database;
Sql>alter database recover managed standby database disconnect from session;

<2> boot to read-only mode

Sql>shutdown immediate;
Sql>startup Nomount;
Sql>alter database mount standby database;
Sql>alter database open Read only;

<3> If you are in Managed recovery mode to read-only mode

sql> Recover managed standby database cancel;
Sql> ALTER DATABASE open read only;

At this time, you can add temporary data files to the database (this is not backed up during a hot backup).
such as Alter tablespace temp add tempfile '/u02/oradata/test/temp01.dbf ' size 100M;

<4>: From read-only mode to Administrative recovery mode
sql> Recover managed Standby database disconnect from session;

(4) Some monitoring of the application of physical reserve library
If there is a problem or we do not know the success is not, you can use the following method to detect

<1> Verify that the archive destination configuration in the Master repository is valid
Select dest_id, STATUS, DESTINATION, ERROR from V$archive_dest where dest_id<=2;
The destination state status should be displayed as VALID, and note that if the Redo app is not executed above there will be an error message

<2> confirm that redo logs are actually applied, and that the main library executes
Select sequence#, First_time, Next_time, applied, archived from v$archived_log where name = ' JED2 ' ORDER by First_time;
If the archive and log applications are normal, both the applied and archived columns should be yes. (if not applied redo,applied should be no)
Many tutorials let this query sort by sequence# columns, but I don't recommend them. If you sort by sequence# column, when you do a failover, the serial number starts again from 1,
With this query, you will not be able to see the latest records at the end of the results. I used to wonder why I couldn't find a new record because the new record didn't appear at the end,
I didn't see it. So, this query is sorted in first_time column.

<3> Check for redo log gaps on the main library
If you find that the log is not being applied, it is possible that the redo log has a gap, in which case the repository cannot be applied to the log. But if your fal_server parameter is set correctly, this should not be a problem
Select STATUS, gap_status from v$archive_dest_status where dest_id = 2;
If everything is OK, you should return to VALID and no gap. Remember to enable the Redo app to show no gap

<4> perform the following query on the Master repository to view the database status
SELECT * from V$dataguard_status order by TIMESTAMP;

<5> Check for success:
View Log shipping status on the main library:
Sql>select Dest_name,status,error from V$archive_dest;
Should log_archive_dest_1 and 2 States should be valid
Switch logs several times:
Sql>alter system switch logfile;
To view the log sequence number:
Sql>select sequence# from V$archived_log;
Repository Validation:
Sql>select sequence#,applied from V$archived_log;

13.dataguard Start-Off order
(1) Monitoring
Start from the library and start the main library
#lsnrctl start
(2) Start
First Boot from library:
Sql>startup Nomount
Sql>alter database mount standby database;
Sql>alter database recover managed standby database using current logfile disconnect from session;
In the main library
Sql>startup
(3) off: Just the opposite of opening
First Guan:
Sql>shutdown Immediate
Again Guan CONGCU:
Sql>alter database recover managed standby database cancel;
Sql>shutdown immediate;

This article transferred from: http://www.cnblogs.com/tippoint/archive/2013/04/18/3029019.html

oracle11g Dataguard Complete Brochure