Configuration of logical tracing uard

1. The Primary database generates the LogMiner dictionary information (before generation, make sure that the physical Standby to be converted stops the REDO application) ORCLPRI_LGexecutedbms_logstdby.build; PLSQLproceduresuccessfullycompleted.2. the logic Standby is -----

1. the Primary database generates LogMiner dictionary information (before generation, make sure that the physical Standby to be converted stops the REDO application) ORCLPRI_LG execute dbms_logstdby.build; PL/SQL procedure successfully completed. 2. logical Standby renamed ORCLSTD_LG show parameter db_name name type value -----

1. The Primary database generates LogMiner dictionary information (before generation, make sure that the physical Standby to be converted stops the REDO Application)
ORCLPRI_LG> execute dbms_logstdby.build;

PL/SQL procedure successfully completed.
2. Rename logical Standby
ORCLSTD_LG> show parameter db_name

NAME TYPE VALUE
-----------------------------------------------------------------------------
Db_name string orcl
ORCLSTD_LG> alter database recover to logical standby ORCLLDG;
Alter database recover to logical standby ORCLLDG
*
ERROR at line 1:
ORA-19953: database shocould not be open

ORCLSTD_LG> shutdown immediate
ORA-01507: database not mounted

ORACLE instance shut down.
ORCLSTD_LG> alter database recover to logical standby ORCLLDG;
Alter database recover to logical standby ORCLLDG
*
ERROR at line 1:
A ORA-01034: ORACLE not available

ORCLSTD_LG> startup mount
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 62916852 bytes
Database Buffers 100663296 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORCLSTD_LG> alter database recover to logical standby ORCLLDG;

Database altered.

ORCLSTD_LG> show parameter db_name

NAME TYPE VALUE
-----------------------------------------------------------------------------
Db_name string orcl
ORCLSTD_LG> shutdown immediate
ORA-01507: database not mounted

ORACLE instance shut down.
ORCLSTD_LG> startup mount
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 62916852 bytes
Database Buffers 100663296 bytes
Redo Buffers 2973696 bytes
Database mounted.
3. the restart takes effect.
ORCLSTD_LG> show parameter db_name

NAME TYPE VALUE
-----------------------------------------------------------------------------
Db_name string ORCLLDG
ORCLSTD_LG> select database_role from v $ database;

DATABASE_ROLE
----------------
LOGICAL STANDBY
4. Create local and remote archiving paths
Modify Primary spfile:
Orcl. _ db_cache_size = 92274688
Orcl. _ Java _pool_size = 4194304
Orcl. _ large_pool_size = 4194304
Orcl. _ shared_pool_size = 58720256
Orcl. _ streams_pool_size = 4194304
*. Audit_file_dest = '/u01/app/oracle/admin/orcl/adump'
*. Background_dump_dest = '/u01/app/oracle/admin/orcl/bdump'
*. Compatible = '10. 2.0.1.0'
*. Control_files = '/u01/app/oracle/oradata/orcl/control01.ctl', '/u01/app/oracle/oradata/orcl/control02.ctl ', '/u01/app/oracle/oradata/orcl/control03.ctl' # Restore Controlfile
*. Core_dump_dest = '/u01/app/oracle/admin/orcl/cdump'
*. Db_block_size = 8192
*. Db_domain =''
*. Db_file_multiblock_read_count = 16
*. Db_file_name_convert = '/u01/app/oracle/oradata/orcl', '/u01/app/oracle/oradata/orclstd', '/u01/rec_catalog ', '/u01/app/oracle/oradata/orclstd'
*. Db_name = 'orcl'
*. Db_recovery_file_dest = '/u01/app/oracle/flash_recovery_area'
*. Db_recovery_file_dest_size = 2147483648
*. Db_unique_name = 'orclpre'
*. Dispatchers = '(PROTOCOL = TCP) (SERVICE = orclXDB )'
*. Fal_client = 'orcl _ 192.168.1.222'
*. Fal_server = 'orcls _ 192.168.1.223'
*. Job_queue_processes = 10
*. Log_archive_config = 'dg _ config = (orclpre, orclldg )'
*. Log_archive_dest_1 = 'location =/u01/arch_orcl'
*. Log_archive_dest_2 = 'service = orcls_192.168.1.223 lgwr async valid_for = (online_logfiles, primary_role) db_unique_name = orclldg'
*. Log_archive_dest_state_2 = 'enable'
*. Log_archive_format = 'archive _ % t _ % s _ % r. arclog'
*. Log_file_name_convert = '/u01/app/oracle/oradata/orcl', '/u01/app/oracle/oradata/orclstd', '/u01/rec_catalog ', '/u01/app/oracle/oradata/orclstd'
*. Open_cursors = 300
*. Pga_aggregate_target = 16777216
*. Processses = 150
*. Remote_login_passwordfile = 'clusive'
*. Sga_target = 167772160
*. Standby_file_management = 'auto'
*. Undo_management = 'auto'
*. Undo_tablespace = 'undotbs1'
*. User_dump_dest = '/u01/app/oracle/admin/orcl/udump'

Lgwr async: ensures asynchronous transmission from Primary to logical Standby

Modify Standby spfile:

Orcl. _ db_cache_size = 96468992
Orcls. _ db_cache_size = 100663296
Orclstd. _ db_cache_size = 50331648
Orcl. _ Java _pool_size = 4194304
Orcls. _ java_pool_size = 4194304
Orclstd. _ java_pool_size = 4194304
Orcl. _ large_pool_size = 4194304
Orcls. _ large_pool_size = 4194304
Orclstd. _ large_pool_size = 4194304
Orcl. _ shared_pool_size = 54525952
Orcls. _ shared_pool_size = 54525952
Orclstd. _ shared_pool_size = 104857600
Orcl. _ streams_pool_size = 4194304
Orcls. _ streams_pool_size = 0
Orclstd. _ streams_pool_size = 0
*. Audit_file_dest = '/u01/app/oracle/admin/orclstd/adump'
*. Background_dump_dest = '/u01/app/oracle/admin/orclstd/bdump'
*. Compatible = '10. 2.0.1.0'
*. Control_files = '/u01/app/oracle/oradata/orclstd/orclstd01.ctl', '/u01/app/oracle/oradata/orclstd/orclstd02.ctl ', '/u01/app/oracle/oradata/orclstd/orclstd03.ctl'
*. Core_dump_dest = '/u01/app/oracle/admin/orclstd/cdump'
*. Db_block_size = 8192
*. Db_domain =''
*. Db_file_multiblock_read_count = 16
*. Db_file_name_convert = '/u01/app/oracle/oradata/orcl', '/u01/app/oracle/oradata/orclstd ', '/u01/app/oracle/oradata/orclstd', '/u01/app/oracle/oradata/orcl', '/u01/rec_catalog ', '/u01/app/oracle/oradata/orclstd', '/u01/app/oracle/oradata/orclstd', '/u01/rec_catalog'
*. Db_name = 'orclldg' # db_name
*. Db_recovery_file_dest = '/u01/app/oracle/flash_recovery_area'
*. Db_recovery_file_dest_size = 2147483648
*. Db_unique_name = 'orclldg'
*. Dispatchers = '(PROTOCOL = TCP) (SERVICE = orclXDB )'
*. Fal_client = 'orcl _ 192.168.1.223'
*. Fal_server = 'orcls _ 192.168.1.222'
*. Job_queue_processes = 10
*. Log_archive_config = 'dg _ config = (orclpre, orclldg )'
*. Log_archive_dest_1 = 'location =/u01/arch_ldg valid_for = (online_logfiles, all_roles) db_unique_name = orclldg'
*. Log_archive_dest_2 = 'service = orcl_192.168.1.222 arch valid_for = (online_logfiles, primary_role) db_unique_name = orclpre'
*. Log_archive_dest_3 = 'location =/u01/arch_std valid_for = (standby_logfiles, standby_role) db_unique_name = orclldg'
*. Log_archive_dest_state_2 = 'enable'
*. Log_archive_format = 'archive _ % t _ % s _ % r. arclog'
*. Log_file_name_convert = '/u01/app/oracle/oradata/orcl', '/u01/app/oracle/oradata/orclstd ', '/u01/app/oracle/oradata/orclstd', '/u01/app/oracle/oradata/orcl', '/u01/rec_catalog ', '/u01/app/oracle/oradata/orclstd', '/u01/app/oracle/oradata/orclstd', '/u01/rec_catalog'
*. Open_cursors = 300
*. Pga_aggregate_target = 16777216
*. Processses = 150
*. Remote_login_passwordfile = 'clusive'
*. Sga_target = 167772160
*. Standby_file_management = 'auto'
*. Undo_management = 'auto'
*. Undo_tablespace = 'undotbs1'
*. User_dump_dest = '/u01/app/oracle/admin/orclstd/udump'

ORCLSTD_LG> create spfile from pfile = '/u01/pfile ';

File created.

ORCLSTD_LG> startup mount
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 62916852 bytes
Database Buffers 100663296 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORCLSTD_LG> alterd database open;
SP2-0734: unknown command beginning "alterd dat..."-rest of line ignored.
SQL> alter database open;
Alter database open
*
ERROR at line 1:
The ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

ORCLSTD_LG> alter database open resetlogs;

Database altered.
Start applying REDO data
ORCLSTD_LG> alter database start logical standby apply;

Database altered.

ORCLSTD_LG> alter database stop logical standby apply;

Database altered.

ORCLSTD_LG> select * from v $ logfile;

GROUP # STATUS TYPE
------------------------
MEMBER
--------------------------------------------------------------------------------
IS _
---
3 ONLINE
/U01/app/oracle/oradata/orclstd/redo03.log
NO

2 ONLINE
/U01/app/oracle/oradata/orclstd/redo02.log
NO

GROUP # STATUS TYPE
------------------------
MEMBER
--------------------------------------------------------------------------------
IS _
---

1 ONLINE
/U01/app/oracle/oradata/orclstd/redo01.log
NO

ORCLSTD_LG> alter database add standby logfile group 4'/u01/app/oracle/oradata/orclstd/stdredo01.log 'size 50 m;

Database altered.

ORCLSTD_LG> alter database add standby logfile group 5'/u01/app/oracle/oradata/orclstd/stdredo02.log 'size 50 m;

Database altered.

ORCLSTD_LG> alter database add standby logfile group 6'/u01/app/oracle/oradata/orclstd/stdredo03.log 'size 50 m;

Database altered.
Re-Execute:
ORCLSTD_LG> alter database start logical standby apply immediate;

Database altered.

Test:

Primary:

SQL> select * from scott. test;

No rows selected

SQL> insert into scott. test values (1 );

1 row created.

SQL> insert into scott. test values (2 );

1 row created.

SQL> insert into scott. test values (3 );

1 row created.

SQL> commit;

Commit complete.

Standby:

SQL> select * from scott. test;

ID
----------
1
2
3

Logical Standby is created (converted ......

Summary:

1. Physical Standby, Logical Standby (Physical Standby and Logical Standby)

Physical standby directly accepts archived logs from the master database, and then performs block-based physical recovery (update or adjust the changed block). Therefore, Physical standby is equivalent to the master database at the physical file level. Physical standby recovery is only the underlying block apply, operating level work, database SCHEMA, including indexes are the same. It directly applies REDO or archive for synchronization. Does not involve temp, undo, etc. Possible modes of physical STANDBY: Read-only mode (open readonly) and RECOVERY mode (mananged recovery ).

In logical STANDBY, the logical information is the same, but the physical organization and data structure can be different. It is synchronized with the master database by converting the received REDOSQLAnd then execute SQL statement (SQL Apply) on STANDBY ). In addition to disaster recovery, logical STANDBY has other functions, suchReports.

Before 9i R2, the data guard server can only run in read-only or recover mode. A physical standby database is physically equivalent to the master database. When physical standby database is being restored, it cannot be opened for other purposes. The logical standby database is only equivalent to the schema to be restored in logical. Therefore, you can open the report (query action) at the same time during restoration ), you can also use different data objects from the master database, which greatly improves the utilization of the slave database.

Ii. Dataguard

All are Standby. Before Oracle 9i is called Standby, 9i or later Standby is renamed as Data guard. However, there are also many improvements and differences in functionality.

Iii. Standby LGWR/ARCH Transmission

View the database protection mode:

SQL> select DATABASE_ROLE, PROTECTION_MODE, PROTECTION_LEVEL from v $ database;

1. maximize performance: This is the default protection mode of data guard. Before the transaction commit on primay, you do not need to receive feedback from standby (the commit operation of the primary database is not waiting for STANDBY). In this mode, data may be lost when the primary fails, however, standby has the least impact on the performance of primary. You can use the lgwr async or ARCH transmission modes.

ARCH transmission mode: When the online redo log on the Primary DB is full or other conditions cause the redo log to be archived, the archived log file generated by the redo log is written to the local archive directory, the Standby archive directory is written. Only online redo log switching on Primary db does not have to wait until the write archiving action on Standby ends.

2. maximize availability: under normal circumstances, the maximum available mode is the same as the maximum protection mode. When standby is unavailable, the maximum available mode is automatically reduced to the maximum performance mode, therefore, the standby fault will not cause primay to be unavailable. After the problem is rectified, Standby and the primary database are re-synchronized. If there is at least one standby available, even if the primary is down, data will not be lost. (However, when the problem is fixed and FAILOVER is necessary before synchronization, some data may be lost ). Standby Redo log must be configured for the maximum availability mode Standby. It is recommended that the maximum availability mode be transmitted in lgwr async (asynchronous) mode.

The maximum available data guard mode is used. The master database transmits online redo log information to the slave database, and the online log information is written to the standby redo log of the slave database. After these standby redo logs are archived, archive logs in the slave database.

LGWR is also divided into lgwr async (asynchronous) and lgwr sync (synchronous.

	Maximum protection	Maximum availability	Maximum performance
Process	LGWR	LGWR	LGWR or ARCH
Network Transmission Mode	SYNC	SYNC	Set ASYNC in LGWR
Disk write operations	Affrem	Affrem	Noaffrem
Backup log	YES	Physical backup needs	Required for LGWR and physical backup
Backup Database Type	Physical Standby	Physical or logical	Physical or logical

Maximize protection: the highest protection mode. Transactions on primay must confirm that the redo has been passed to at least one standby before commit. If all standby is unavailable, primary will be suspended. This mode ensures zero data loss. For the maximum protection and maximum availability mode, Standby redo log must be configured for the standby database, and oracle recommends that all databases use the lgwr async mode for transmission.