Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Oracle11g dataguard complete manual

2025-01-26 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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

1. Preface:

There are many articles about the configuration of dataguard on the network, but many articles with oracle11g can only run on 9 / 10, for example, FAL_CLIENT has been abandoned in 11g, but now the articles on the network do not mark this point. And for the specific meaning is vague, for the novice can only know it but do not know why. In this article, I want people like me to know not only how to configure dataguard, but also why it is needed.

This article not only records how to configure, but also explains why this is so, as well as the main points to note, and I hope this article can be used as a reference manual for dataguard configuration.

II. Premise

1. The main library is in archive mode:

If we don't know why it's archiving mode, then we shouldn't know what dataguard is for. Through many embellishments of the official language, we need to make it clear that DG (dataguard abbreviation) is actually used for high availability. The implementation principle is to obtain data from the master database to the slave database, and when an exception occurs in the master library, the slave library will take over the master library to complete the identity change. Can be a master library, up to 9 slave libraries. It is divided into logical standby and physical standby at the same time. Here we are discussing physical standby.

Once the standby is created and configured, dg is responsible for transferring the primary database redo data to the standby database, and the standby database receives the redo data through the application to keep the transaction consistent with the primary database.

It is clear that the master and slave libraries need to be consistent, and archive log and redo log need to be transferred to the slave libraries. If it is not for the archiving mode, the data consistency of the master and slave libraries cannot be guaranteed.

two。 The slave library only needs to install the database software, and the data is transferred from the master library.

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

4. The master and slave library hardware had better be consistent. The oracle database version needs to be consistent.

(1) memory check item:

# grep MemTotal / proc/meminfo

Swap partition check: if the memory is 1-2G magnetic swap 1.5 times; 2-16G magnetic 1 times; more than 16G, set to 16G.

# grep SwapTotal / proc/meminfo

View shared memory size:

# df-h / dev/shm

(2) View the system processor architecture, which is consistent with the oracle installation package.

# uname-m

(3) Space / tmp must be greater than 1G

# df-h / tmp

5. Configuration environment database user must have sysdba permission

6. The following environment: main library 192.168.215.101 database instance name: orcl db_unique_name:orcl

Slave database 192.168.215.102 database instance name: orcl db_unique_name:orcldg

III. Configuration

1. Determine if DG is installed:

Select * from v$option where parameter = 'Oracle Data Guard'

If it is true, it means that it has been installed and can be configured, otherwise the corresponding components need to be installed.

two。 Set the main library to force logging.

By default, database operations record redo log, but in some specific cases you can use nologging not to generate redo information

(1) batch INSERT of the table (prompted by / * + APPEND * / to use "direct path insertion". Or use SQL*Loader direct path to load). 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, indexes on this table generate redo! ).

(2) LOB operation (updates to large objects do not need to generate logs).

(3) create tables through CREATE TABLE AS SELECT

(4) various ALTER TABLE operations, such as MOVE and SPLIT

(5) in some table migrations and tablespace migrations, you can use alter table a nologging; or alter tablespace snk nologging; to change back to the logging state after the operation is completed.

Here, you need to say that if you use nologging to import a large number of data, future modifications to these data will be in redo or archive log, but the benchmark data is not available, so once the media is damaged, it cannot be fully recovered. You must make a full or level 0 backup after switching back to logging using nologging.

(1) mandatory logging: sql > alter database force logging

(2) check status (YEs is mandatory): sql > select name,force_logging from v$database

(3) if you need to add or delete data files in the main database, these files will also 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)

The slave library uses standby log files to save the redo logs received from the master library. Since it is mainly used by the slave library, why do you need to build it on the master library?

Standby log files? There are two main reasons: one is that the main library may be converted into a standby library, and the other is that the standby library needs to have standby log files.

If standby log files is established, the repository will be created automatically.

If you set up standby, you should pay attention to the following:

Standby log files is the same size as redo log files.

Query redo log files file size (default is 3 50m dint): select group#,bytes/1024/1024 as M from v$log

In general, the number of standbyredo log file groups is at least one more than the number of online redo log file groups in the primary database.

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 rac in the rac structure

Number of nodes.

There is a recommended formula for reference: (log groups per thread + 1) * maximum number of threads

Assuming that there are 1 nodes now, then = (3-1) * 1-4

If it is two nodes, then = (3: 1) * 2: 8

Here we create four standby logfile:

Also: it is not recommended that the group number group# is next to the redo, because the subsequent redo may be adjusted. Here we start by establishing a standby logfile from 11 to 14.

?

123456 cd $ORACLE_BASE/oradata/orcl/ # mkdir dg # chown oracle:dba dg sql > alter database add standby logfile group 11'/ opt/oracle/oradata/orcl/dg/standby11.log' size 50M; sql > alter database add standby logfile group 12'/ opt/oracle/oradata/orcl/dg/standby12.log' size 50M; sql > alter database add standby logfile group 13'/ opt/oracle/oradata/orcl/dg/standby13.log' size 50m; sql > alter database add standby logfile group 14'/ opt/oracle/oradata/orcl/dg/standby14.log' size 50m

4. Creation and transfer of password files and control files

(1) password files are available by default in general databases, which are stored in $ORACLE_HOME/dbs/orapwSID as orapworcl.

If there is no sql > orapwd file=$ORACLE_HOME/dbs/orapworcl password=oracle

(2) check whether the REMOTE_LOGIN_ PASSWORDFILER 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) the password file needs scp to slave library.

# scp orapworcl oracle@192.168.215.102:/opt/oracle/11.2/dbs prompts for yes

(4) Control documents:

There are two control documents for 11g, the same content, one at $ORACLE_BASE/oradata/orcl/control01.ctl

One at / opt/oracle/flash_recovery_area/orcl/control02.ctl

Generate the standby control file:

?

1234sql > shutdown immediate sql > startup mount sql > alter database create standby controlfile as'/ tmp/standby_control01.ctl'; sql > startup open

Then set up the corresponding directory in the standby library and authorize

Mkdir orcl--- chown oracle:oinstall (or dba) orcl

?

Scp control01.ctl oracle@192.168.215.102:/opt/oracle/oradata/orcl scp control02.ctl oracle@192.168.215.102:/opt/oracle/flash_recovery_area/orcl/

5.db_name and db_unique_name

The default db_name and db_unique_name are the same as the instance name, here is orcl

It should be noted that in DG, the db_unique_name of the master library and the slave library are not consistent and need to be distinguished.

Here we set the db_unique_name of the master library to orcl and the slave library to 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 the same, they need to be set explicitly, otherwise this parameter is not available in spfile

6. Flashback database:

It is strongly recommended to turn on the database flashback feature. Flashback allows you to restore the database to a previous point in time. This feature is very useful when a failover occurs

It allows you to flash the old main library back to before the failure, and then convert it into a backup library. If flashback is not enabled, you will have to rebuild the backup library, which means copying the data file again.

In addition to this benefit, flashbacks can also allow you to avoid restoring data from backups in some cases.

(1) Fast recovery area (Flash/Fast Recovery Area), which is configured by default, but you need to make sure that the disk in this area is large enough, at least 300g or more (default 3G)

Sql > show parameter db_recovery_file_dest

You can change the location: sql > alter system set db_recovery_file_dest=' new path'

Change the size: sql > alter system set db_recovery_file_dest_size=400G

(2) check whether it is enabled. It is disabled by default.

Sql > select flashback_on from v$database

Open: sql > alter database flashback on

If you encounter an error in ORA-01153, you must be doing this in the repository. You need to cancel the redo log application, enable flashback logging, and then re-enable the log application.

Enable flashback logging in the primary library, which is not synchronized with the standby library. You must manually enable flashback logs on both the primary and standby libraries.

If flashback logging is not enabled, you will need to start creating a standby library completely in the event of a failover.

7.SQL*NET Settin

(1) configure the monitoring of the main database

Although it can be configured through netca, in addition to this default, we also need a statically registered SID_LIST_LISTENER if there is no such slave parameter and

If the dataguard startup sequence is incorrect, the main library will report PING [Arc1]: Heartbeat failed to connect to standby'* * '.Error is 12514 causing the archive not to be completed

The configuration is as follows

?

SID_LIST_LISTENER= (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = orcl) (ORACLE_HOME = / opt/oracle/11.2) (SID_NAME = orcl) LISTENER= (DESCRIPTION_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = primaryDB) (PORT = 1521)))

# vi $ORACLE_HOME/network/admin/listener.ora add the above

(2) configure tnsnames

# vi $ORACLE_HOME/network/admin/tnsnames.ora

?

ORCL = (DESCRIPTION = (ADDRESS_LIST = (PROTOCOL = TCP) (HOST = 192.168.215.101) (PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = orcl) ORCLDG = (DESCRIPTION = (ADDRESS_LIST = (PROTOCOL = TCP) (HOST = 192.168.215.102) (PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = orcldg)))

(3) transfer to slave database and modify listener.ora and tnsnames.ora

Scp $ORACLE_HOME/network/admin/listener.ora oracle@192.168.215.102:/opt/oracle/11.2/network/admin/scp $ORACLE_HOME/network/admin/tnsnames.ora oracle@192.168.215.102:/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 need to modify

8. Redo log transfer configuration

(1) configure the location of the archive log:

Query the archive path sql:archive log list or show parameter log_archive_dest_1 that has been set

?

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 a quick recovery area as an archive directory, such as LOCATION=use_db_recovery_file_dest

The official documentation says that valid_for= (online_logfiles, all_roles) is used, which will cause the repository to fail to archive alternate log files because they are not online logs.

However, if you use the all_logfiles option, both the primary and standby libraries will be able to archive online as well as standby logs.

If you want to back up in the repository and back up the archive log at the same time, you must use all_logfiles.

(2) configure redo log to backup database:

?

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 officially deprecated. If you start the database after setting this parameter, only ORA-32004 will be reported:

Obsolete or deprecated parameter (s) specified for RDBMS instance error.

9. Configure FAL_SERVER

This parameter specifies where to find the missing archive log if 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 log transfers are interrupted, such as when you need to maintain the repository. During the preparation and maintenance of the library, no logs are transmitted, and the gap occurs.

When this parameter is set, the standby library will take the initiative to find those missing logs and ask the main library to transfer them.

If you are the main library, fill in:

Fal_server= slave library

The reverse from the library:

Fal_server= main library

Note: FAL_CLIENT has been deprecated in 11g and although it can be configured, it no longer works.

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 method is that we use alter system to modify it online, and there is a more convenient way (but it is easy to make mistakes, so you can't have both convenience and security at any time)

Sql > create pfile from spfile

# manually modify pfile

Sql > create spfile from pfile

Then use pfile to generate spfile and transfer pfile to slave library to generate spfile after modification

Pay attention to the manual addition:

* .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'* .controllers = '11.2.0.0.0percent. Controlbacks filesystems. Controls. Control. '/ 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 oracle@192.168.215.102:/opt/oracle/11.2/dbs/

Modify the initorcl.ora modification parameters of the slave database 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 data from the main database to the standby database

(1) scp-l 8192-rp / opt/oracle/oradata/orcl/ oracle@192.168.215.102:/opt/oracle/oradata/

Be careful not to add orcl to the destination. The directory will be created automatically.

-l is the limit limit, so the maximum speed is 8192 pounds 1m, which is to solve the stalled problem.

-rp loop subdirectory file

(2) create the required directories in spfile

Such as / opt/oracle/admin/orcl/adump dpdump pfile

twelve。 Enable physical standby database

Sql > startup nomount

Sql > alter database mount standby database

(1) launch redo application

SQL > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION

Start real-time application

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

This command instructs the standby library to start using alternate log files for recovery. It also tells the standby library to return to the command line interface after the command is completed.

SQL > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

This is only a temporary redo application, not to stop the Standby database, standby will still keep receiving, but will not be applied again

Receive the archive until you start the redo application again

(2) stop standby

Normally, first of all,

SQL > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

Then sql > shutdown immediate

Of course, you can also shutdown immediate directly.

(3) Management mode and read-only mode of standby server

. Boot to administrative mode

?

SQL > shutdown immediate; SQL > startup nomount; SQL > alter database mount standby database; SQL > alter database recover managed standby database disconnect from session

. Boot to read-only mode

?

SQL > shutdown immediate; SQL > startup nomount; SQL > alter database mount standby database; SQL > alter database open read only

If you go to read-only mode under the administrative recovery 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 the hot backup).

For example, alter tablespace temp add tempfile'/ u02qoradata size TestUniverse temp01.dbf'Unim

. From read-only mode to management recovery mode

SQL > recover managed standby database disconnect from session

(4) several monitoring points of the application physics reserve database.

If there is a problem above or we do not know whether it is successful or not, we can use the following method to detect it.

Verify that the archive destination configuration in the main and standby database is valid.

Select DEST_ID, STATUS, DESTINATION, ERROR from V$ARCHIVE_DEST where DEST_IDselect dest_name,status,error from v$archive_dest

The log_archive_dest_1 and 2 states should be valid

Switch logs several times:

Sql > alter system switch logfile

View the log serial number:

Sql > select sequence# from v$archived_log

Prepare for library verification:

Sql > select sequence#,applied from v$archived_log

13.dataguard startup and shutdown sequence

(1) Monitoring

Start first from the library and then from the main library

# lsnrctl start

(2) start

First launch slave 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: opposite to opening

Close the main library first:

Sql > shutdown immediate

Close the slave library again:

Sql > alter database recover managed standby database cancel

Sql > shutdown immediate

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.

Views: 232

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report