Logical standby creatation

miloluo

大部分来自官方文档 :)

4 Create Logical Standby
4.1 Prerequisite Conditions for Creating a Logical Standby Database

 

在搭建逻辑DG前， 需要确认两个事：

4.1.1 Determine Support for Data Types and Storage Attributes for Tables

4.1.2 Ensure Table Rows in the Primary Database Can Be Uniquely Identified

 

10g里不支持的数据类型有：

1. BFILE
   
2. Collections (including VARRAYSand nested tables)
   
3. Encrypted columns
   
4. Multimedia data types (including Spatial, Image, and Context)
   
5. ROWID, UROWID
   
6. User-defined types
   
7. XMLType

 

不支持的存储类型：

1. Segment compression storage type

 

不支持的pl/sql packages：

pl/sql 提供的更新系统元数据不支持SQL APPLY，因此他们的效果将不会在逻辑standby上出现。

例如，dbms_java, dbms_registry, dbms_alert, dbms_space_admin, dbms_refresh, dbms_redefinition, dbms_scheduler 和 dbms_aq。

 

特定的dbms_job有支持。job的暂停执行在逻辑standby数据库，并且job不能直接在standby数据库直接被定制。然而，在primary上提交的job恢复到standby数据库里。当出现switchover或者failover时，原来在原来primary执行的，将在新的primary上自动执行。

 

不支持的表，序列和视图：

非常重要的是在创建stadnby前，在primary数据库，去确定不支持的表，序列和视图。因为，这些不支持的数据类型，表，序列或视图在primary将自动被SQL APPLY跳过。此外，不会有报错。

 

一些Schema在将会被跳过SQL Apply.为了准确知道哪些schema被跳过，可以查询DBA_LOGSTDBY_SKIP视图。

 

1. Select owner from dba_logstdby_skip where statement_opt='INTERNAL SCHEMA';

 

为了查看primary数据库是否包含不支持对象，查看dba_logstdby_unsuported视图。

1. Select distinct owner, table_name from dba_logstdby_unsupported
   
2. 
   
3. Order by owner, table_name;
   
4. 
   
5.  
   
6. Select column_name, data_type from dba_logstdby_unsupported
   
7. 
   
8. Where owner='OE' and table_name='CUSTOMERS';

 

默认，下面的SQL语句将自动被SQL apply跳过：

1. ALTER DATABASE
   
2. ALTER MATERIALIZED VIEW
   
3. ALTER MATERIALIZED VIEW LOG
   
4. ALTER SESSION
   
5. ALTER SYSTEM
   
6. CREATE CONTROL FILE
   
7. CREATE DATABASE
   
8. CREATE DATABASE LINK
   
9. CREATE PFILE FROM SPFILE
   
10. CREATE MATERIALIZED VIEW
    
11. CREATE MATERIALIZED VIEW LOG
    
12. CREATE SCHEMA AUTHORIZATION
    
13. CREATE SPFILE FROM PFILE
    
14. DROP DATABASE LINK
    
15. DROP MATERIALIZED VIEW
    
16. DROP MATERIALIZED VIEW LOG
    
17. EXPLAIN
    
18. LOCK TABLE
    
19. SET CONSTRAINTS
    
20. SET ROLE
    
21. SET TRANSACTION

 

Statement Options for Skipping SQL DDL Statements

Statement Option	SQL Statements and Operations
ALTER SEQUENCE	ALTER SEQUENCE
ALTER TABLE	ALTER TABLE
COMMENT TABLE	COMMENT ON TABLEtable, view, materialized view COMMENT ON COLUMNtable.column, view.column, materialized_view.column
DELETE TABLE	DELETE FROMtable, view
EXECUTE PROCEDURE	CALL Execution of any procedure or function or access to any variable, library, or cursor inside a package.
GRANT DIRECTORY	GRANTprivilegeONdirectory REVOKEprivilegeONdirectory
GRANT PROCEDURE	GRANTprivilegeONprocedure, function, package REVOKEprivilegeONprocedure, function, package
GRANT SEQUENCE	GRANTprivilegeONsequence REVOKEprivilegeONsequence
GRANT TABLE	GRANTprivilegeONtable, view, materialized view REVOKEprivilegeONtable, view, materialized view
GRANT TYPE	GRANTprivilegeON TYPE REVOKEprivilegeON TYPE
INSERT TABLE	INSERT INTOtable, view
LOCK TABLE	LOCK TABLEtable, view
SELECT SEQUENCE	Any statement containing sequence.CURRVALor
SELECT TABLE	SELECT FROMtable, view, materialized view REVOKEprivilegeONtable, view, materialized view
UPDATE TABLE	UPDATEtable, view

 

4.1.2 Ensure Table Rows in the Primary Database Can Be Uniquely Identified

 

Step1 : Find tables without unique logical identifier in the primary database.

Query the DBA_LOGSTDBY_NOT_UNIQUE view to display a list of tables that SQL apply may not be able to uniquely identify. For example:

 

1. Select owner, table_name from dba_logstdby_not_unique
   
2. Where (owner, table_name) not in
   
3. (Select distinct owner, table_name from dba_logstdby_unsupoorted)
   
4. And bad_column='Y';

 

Step 2: Add a disabled primary-key RELY constraint.

If your application ensures  the rows in a table are unique, you can create a disabled primary key RELY constraint on the table. This avoids the overhead of maintaining a primary key on the primary database.

To create a disabled RELY constraint on a primary database table, use the ALTER TABLE statement with a RELY DISABLE clause. The following example creates a disabled RELY constraint on a table named mytab, for which rows can be uniquely identified using the id and name columns:

1. ALERT TABLE mytab ADD PRIMARY KEY(id, name) RELY DISABLE;

When you specify the RELY constraint, the system will assume that rows are unique. Because you are telling the system to rely on the information, but are not validating it on every modification done to the table, you must be careful to select columns for the disabled RELY constraint that will uniquely identifiy each row in the table. If such uniqueness is not present, then SQL apply will not correctly maintain the table.

To improve the performance of SQL apply, add a unique-constraint/index to the columns to identify the row on the logical standby database. Failure to do so result in full table scans during UPDATE or DELETE statements carried out on the table by SQL apply.

 

4.2 Step-by-Step Instructions for Creating a Logical Standby Database

This section describes the tasks you perform to create a logical standby database.

Table 4-2 provides a checklist of the tasks that you perform to create a logical standby database and specifies on which database you perform each task. There is also a reference to the section that describes the task in more detail.

Table 4-2 Creating a Logical Standby Database

Reference	Task	Database
Section 4.2.1	Create a Physical Standby Database	Primary
Section 4.2.2	Stop Redo Apply on the Physical Standby Database	Standby
Section 4.2.3	Prepare the Primary Database to Support a Logical Standby Database	Primary
Section 4.2.4	Transition to a Logical Standby Database	Standby
Section 4.2.5	Open the Logical Standby Database	Standby
Section 4.2.6	Verify the Logical Standby Database Is Performing Properly	Standby

 

4.2.1 完成。

4.2.2 Stop Redo Apply on the physical Standby Database

1. SQL> alter database recover managed standby database cancel;
   
2. 
   
3. Database altered.

 

4.2.3 Prepare the Primary Database to Support a Logical Standby Database

 

4.2.3.1 Prepare the Primary Database for Role Transitions

primary unique name:

1. SYS@liuzhou> show parameter unique
   
2. 
   
3.  NAME TYPE VALUE
   
4. 
   
5. ------------------------------------ ----------- ------------------------------
   
6. 
   
7. db_unique_name string prim
   
8. 
   
9. SYS@liuzhou>

 

standby unique_name:

1. SQL> show parameter unique
   
2. 
   
3. NAME TYPE VALUE
   
4. ------------------------------------ ----------- ------------------------------
   
5. db_unique_name string stdby

 
 log_archive_dest in primary:

1. SYS@liuzhou> show parameter log_archive_dest
   
2. 
   
3. NAME TYPE VALUE
   
4. 
   
5. ------------------------------------ ----------- ------------------------------
   
6. 
   
7. log_archive_dest string
   
8. 
   
9. log_archive_dest_1 string location=/arch1/ valid_for=(al
   
10. 
    
11.                                                  l_logfiles,all_roles) db_uniqu
    
12. 
    
13.                                                  e_name=prim
    
14. 
    
15. log_archive_dest_10 string
    
16. 
    
17. log_archive_dest_2 string service=stdby lgwr async valid
    
18. 
    
19.                                                  _for=(online_logfiles,primary_
    
20. 
    
21.                                                  role) db_unique_name=stdby
    
22. 
    
23. log_archive_dest_3 string

 
Setting log_archive_dest_3 for logical standby:

1. SYS@liuzhou> alter system set log_archive_dest_3='location=/arch2/ valid_for=(standby_logfiles,standby_role) db_unique_name=stdby' scope=spfile;
   
2. 
   
3. System altered.
   
4. 
   
5. SYS@liuzhou> alter system set log_archive_dest_state_3=enable;
   
6. 
   
7. System altered.
   
8. 
   
9.  
   
10. 
    
11. SYS@liuzhou> r
    
12. 
    
13.   1* select name, value from v$parameter where name='log_archive_dest_3'
    
14. 
    
15. NAME VALUE
    
16. ------------------------------ ------------------------------
    
17. log_archive_dest_3
    
18. 
    
19.  
    
20. 
    
21. SYS@liuzhou> select name, value from v$spparameter where name='log_archive_dest_3';
    
22. 
    
23. NAME VALUE
    
24. ------------------------------ ------------------------------
    
25. log_archive_dest_3 location=/arch2/ valid_for=(st
    
26. 
    
27.                                andby_logfiles,standby_role) d
    
28. 
    
29.                                b_unique_name=stdby

 

4.2.3.2 Build a Dictionary in the Redo Data

A LogMiner dictionary must be built into the redo data so that the LogMiner component of SQL apply can properly interpret changes it sees in the redo. As part of building LogMiner Multiversioned Data Dictionary, supplemental logging is automatically set up to log primary key and unique-constraint/index columns. The supplemental logging information ensures each update contains enough information to logically identify each row that is modified by the statement.

To build the LogMiner dictionary, issue the following statement:

1. SYS@liuzhou> execute dbms_logstdby.build;
   
2. 
   
3. PL/SQL procedure successfully completed.

The dbms_logstdby.build procedure waits for all existing transactions to complete. Long-running transactions executed on the primary database will affect the timelines of this command.

The dbms_logstdby.build procedure uses flashback query to obtain a consistent snapshot of the data dictionary that is then logged in the redo stream. Oracle recommends setting the UNDO_RETENTION initialization parameter to 3600 on both the primary and logcial standby databases.

 

4.2.4 Transition to a Logcial Standby Database

4.2.4.1 Convert to a Logical standby Database

1. SQL> alter database recover to logical standby liuzhou;
   
2. 
   
3. Database altered.

 

4.2.3.3 Adjust initialization parameters for the logical standby database

1. SQL> alter system set log_archive_dest_3='location=/arch2/ valid_for=(standby_logfiles,standby_role) db_unique_name=prim' scope=spfile;
   
2. 
   
3. System altered.

 

1. SQL> alter system set log_archive_dest_state_3=enable;
   
2. 
   
3. System altered.

 

1. SQL> alter system set log_archive_dest_3='location=/arch2/ valid_for=(standby_logfiles,standby_role) db_unique_name=prim' scope=spfile;
   
2. 
   
3. System altered.
   
4. 
   
5. SQL> alter system set log_archive_dest_state_3=enable;
   
6. 
   
7. System altered.
   
8. 
   
9.  
   
10. 
    
11. SQL> alter database open resetlogs;
    
12. 
    
13. Database altered.
    
14. 
    
15. SQL>
    
16. 
    
17. SQL> alter database start logical standby apply immediate;
    
18. 
    
19. Database altered.

1. SQL> archive log list
   
2. 
   
3. Database log mode Archive Mode
   
4. 
   
5. Automatic archival Enabled
   
6. 
   
7. Archive destination /arch1/
   
8. 
   
9. Oldest online log sequence 0
   
10. 
    
11. Next log sequence to archive 1
    
12. 
    
13. Current log sequence 1
    
14. 
    
15.  
    
16. 
    
17. SQL> ed
    
18. 
    
19. Wrote file afiedt.buf
    
20. 
    
21. 224
    
22. 
    
23. q
    
24. 
    
25.  
    
26. 
    
27.   1 select local.thread#, local.sequence# from
    
28. 
    
29.   2 (select thread#, sequence# from v$archived_log where dest_id=1)
    
30. 
    
31.   3 local where
    
32. 
    
33.   4 local.sequence# not in
    
34. 
    
35.   5* (select sequence# from v$archived_log where dest=2 and thread# = local.thread#)
    
36. 
    
37. SQL> l 5
    
38. 
    
39.   5* (select sequence# from v$archived_log where dest=2 and thread# = local.thread#)
    
40. 
    
41. SQL> c /dest/dest_id/
    
42. 
    
43.   5* (select sequence# from v$archived_log where dest_id=2 and thread# = local.thread#)
    
44. 
    
45. SQL> l
    
46. 
    
47.   1 select local.thread#, local.sequence# from
    
48. 
    
49.   2 (select thread#, sequence# from v$archived_log where dest_id=1)
    
50. 
    
51.   3 local where
    
52. 
    
53.   4 local.sequence# not in
    
54. 
    
55.   5* (select sequence# from v$archived_log where dest_id=2 and thread# = local.thread#)
    
56. 
    
57. SQL> r
    
58. 
    
59.   1 select local.thread#, local.sequence# from
    
60. 
    
61.   2 (select thread#, sequence# from v$archived_log where dest_id=1)
    
62. 
    
63.   3 local where
    
64. 
    
65.   4 local.sequence# not in
    
66. 
    
67.   5* (select sequence# from v$archived_log where dest_id=2 and thread# = local.thread#)
    
68. 
    
69.  
    
70. 
    
71. no rows selected
    
72. 
    
73.  
    
74. 
    
75. 查询数据库的角色：
    
76. 
    
77. SQL> select database_role from v$database;
    
78. 
    
79.  
    
80. 
    
81. DATABASE_ROLE
    
82. 
    
83. ----------------
    
84. 
    
85. LOGICAL STANDBY

 

后来，监控了一把，发现alert正常，切换日志也没问题。

primary:

1. Tue Sep 27 05:21:57 2011
   
2. alter database stop logical standby apply
   
3. Tue Sep 27 05:21:57 2011
   
4. ALTER DATABASE STOP LOGICAL STANDBY APPLY
   
5. Tue Sep 27 05:21:58 2011
   
6. LOGSTDBY status: ORA-16128: User initiated stop apply successfully completed
   
7. LOGSTDBY status: ORA-16112: log mining and apply stopping
   
8. Tue Sep 27 05:21:58 2011
   
9. Completed: alter database stop logical standby apply
   
10. 
    
11. 
    
12. 
    
13. 
    
14. Tue Sep 27 05:22:15 2011
    
15. alter database start logical standby apply
    
16. Tue Sep 27 05:22:15 2011
    
17. ALTER DATABASE START LOGICAL STANDBY APPLY (liuzhou)
    
18. Tue Sep 27 05:22:15 2011
    
19. No optional part
    
20. Attempt to start background Logical Standby process
    
21. LSP0 started with pid=19, OS id=7800
    
22. LOGSTDBY status: ORA-16111: log mining and apply setting up
    
23. Tue Sep 27 05:22:15 2011
    
24. LOGMINER: Parameters summary for session# = 1
    
25. LOGMINER: Number of processes = 3, Transaction Chunk Size = 201
    
26. LOGMINER: Memory Size = 30M, Checkpoint interval = 150M
    
27. Tue Sep 27 05:22:15 2011
28. Completed: alter database start logical standby apply

Standby:

1. Tue Sep 27 05:24:25 2011
   
2. Redo Shipping Client Connected as PUBLIC
   
3. -- Connected User is Valid
   
4. RFS[2]: Assigned to RFS process 7806
   
5. RFS[2]: Identified database type as 'logical standby'
   
6. Primary database is in MAXIMUM PERFORMANCE mode
   
7. Tue Sep 27 05:24:25 2011
   
8. RFS LogMiner: Client enabled and ready for notification
   
9. Primary database is in MAXIMUM PERFORMANCE mode
   
10. RFS[2]: Successfully opened standby log 4: '/u01/app/oracle/oradata/liuzhou/stdbylog04a.log'
    
11. Tue Sep 27 05:24:58 2011
    
12. RFS[1]: Successfully opened standby log 3: '/u01/app/oracle/oradata/liuzhou/stdbylog03a.log'
    
13. Tue Sep 27 05:24:59 2011
    
14. RFS LogMiner: Client enabled and ready for notification
    
15. Tue Sep 27 05:25:02 2011
    
16. RFS LogMiner: Registered logfile [/arch1/1_32_749293317.arch] to LogMiner session id [1]
    
17. Tue Sep 27 05:25:41 2011
    
18. RFS[1]: Archived Log: '/arc/1_30_749293317.arch'
    
19. Tue Sep 27 05:25:43 2011
    
20. RFS LogMiner: Registered logfile [/arc/1_30_749293317.arch] to LogMiner session id [1]
    
21. RFS[1]: Archived Log: '/arc/1_31_749293317.arch'
    
22. Tue Sep 27 05:25:43 2011
    
23. RFS LogMiner: Registered logfile [/arc/1_31_749293317.arch] to LogMiner session id [1]
    
24. LOGMINER: session# = 1, reader process P000 started with pid=22 OS id=7810
    
25. LOGMINER: session# = 1, builder process P001 started with pid=23 OS id=7812
    
26. LOGMINER: session# = 1, preparer process P002 started with pid=24 OS id=7814
    
27. Tue Sep 27 05:25:48 2011
    
28. LOGMINER: Begin mining logfile: /arc/1_30_749293317.arch
    
29. Tue Sep 27 05:25:48 2011
    
30. LOGMINER: Turning ON Log Auto Delete
    
31. Tue Sep 27 05:26:04 2011
    
32. LOGMINER: End mining logfile: /arc/1_30_749293317.arch
    
33. Tue Sep 27 05:26:04 2011
    
34. LOGMINER: Begin mining logfile: /arc/1_31_749293317.arch
    
35. Tue Sep 27 05:26:04 2011
    
36. LOGMINER: End mining logfile: /arc/1_31_749293317.arch
    
37. Tue Sep 27 05:26:04 2011
    
38. LOGMINER: Begin mining logfile: /arch1/1_32_749293317.arch
    
39. Tue Sep 27 05:26:08 2011
    
40. Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_ATTRCOL$ have been marked unusable
    
41. Tue Sep 27 05:26:08 2011
    
42. Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_CCOL$ have been marked unusable
    
43. Tue Sep 27 05:26:08 2011
    
44. Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_CDEF$ have been marked unusable
    
45. Tue Sep 27 05:26:08 2011
    
46. Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_COL$ have been marked unusable
    
47. Tue Sep 27 05:26:08 2011
    
48. Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_COLTYPE$ have been marked unusable
    
49. Tue Sep 27 05:26:08 2011
    
50. Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_ICOL$ have been marked unusable
    
51. Tue Sep 27 05:26:09 2011
    
52. Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_IND$ have been marked unusable
    
53. Tue Sep 27 05:26:09 2011
    
54. Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_INDCOMPART$ have been marked unusable
    
55. Tue Sep 27 05:26:09 2011
    
56. Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_INDPART$ have been marked unusable
    
57. Tue Sep 27 05:26:09 2011
    
58. Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_INDSUBPART$ have been marked unusable
    
59. Tue Sep 27 05:26:09 2011
    
60. Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_LOB$ have been marked unusable
    
61. Tue Sep 27 05:26:09 2011
    
62. Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_LOBFRAG$ have been marked unusable
    
63. Tue Sep 27 05:26:09 2011
    
64. Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_OBJ$ have been marked unusable
    
65. Tue Sep 27 05:26:09 2011
    
66. Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_TAB$ have been marked unusable
    
67. Tue Sep 27 05:26:09 2011
    
68. Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_TABCOMPART$ have been marked unusable
    
69. Tue Sep 27 05:26:09 2011
    
70. Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_TABPART$ have been marked unusable
    
71. Tue Sep 27 05:26:09 2011
    
72. Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_TABSUBPART$ have been marked unusable
    
73. Tue Sep 27 05:26:09 2011
    
74. Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_TS$ have been marked unusable
    
75. Tue Sep 27 05:26:09 2011
    
76. Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_TYPE$ have been marked unusable
    
77. Tue Sep 27 05:26:09 2011
    
78. Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_USER$ have been marked unusable
    
79. Indexes of table SYSTEM.LOGMNR_ATTRCOL$ have been rebuilt and are now usable
    
80. Indexes of table SYSTEM.LOGMNR_ATTRIBUTE$ have been rebuilt and are now usable
    
81. Indexes of table SYSTEM.LOGMNR_CCOL$ have been rebuilt and are now usable
    
82. Indexes of table SYSTEM.LOGMNR_CDEF$ have been rebuilt and are now usable
    
83. Indexes of table SYSTEM.LOGMNR_COL$ have been rebuilt and are now usable
    
84. Indexes of table SYSTEM.LOGMNR_COLTYPE$ have been rebuilt and are now usable
    
85. Indexes of table SYSTEM.LOGMNR_DICTIONARY$ have been rebuilt and are now usable
    
86. Indexes of table SYSTEM.LOGMNR_ICOL$ have been rebuilt and are now usable
    
87. Indexes of table SYSTEM.LOGMNR_IND$ have been rebuilt and are now usable
    
88. Indexes of table SYSTEM.LOGMNR_INDCOMPART$ have been rebuilt and are now usable
    
89. Indexes of table SYSTEM.LOGMNR_INDPART$ have been rebuilt and are now usable
    
90. Indexes of table SYSTEM.LOGMNR_INDSUBPART$ have been rebuilt and are now usable
    
91. Indexes of table SYSTEM.LOGMNR_LOB$ have been rebuilt and are now usable
    
92. Indexes of table SYSTEM.LOGMNR_LOBFRAG$ have been rebuilt and are now usable
    
93. Indexes of table SYSTEM.LOGMNR_OBJ$ have been rebuilt and are now usable
    
94. Indexes of table SYSTEM.LOGMNR_TAB$ have been rebuilt and are now usable
    
95. Indexes of table SYSTEM.LOGMNR_TABCOMPART$ have been rebuilt and are now usable
    
96. Indexes of table SYSTEM.LOGMNR_TABPART$ have been rebuilt and are now usable
    
97. Indexes of table SYSTEM.LOGMNR_TABSUBPART$ have been rebuilt and are now usable
    
98. Indexes of table SYSTEM.LOGMNR_TS$ have been rebuilt and are now usable
    
99. Indexes of table SYSTEM.LOGMNR_TYPE$ have been rebuilt and are now usable
    
100. Indexes of table SYSTEM.LOGMNR_USER$ have been rebuilt and are now usable
     
101. LSP2 started with pid=25, OS id=7817
     
102. Tue Sep 27 05:26:31 2011
     
103. LOGMINER: Begin mining logfile: /arc/1_30_749293317.arch
     
104. Tue Sep 27 05:26:31 2011
     
105. LOGMINER: Turning ON Log Auto Delete
     
106. Tue Sep 27 05:26:33 2011
     
107. LOGMINER: End mining logfile: /arc/1_30_749293317.arch
     
108. Tue Sep 27 05:26:33 2011
     
109. LOGMINER: Begin mining logfile: /arc/1_31_749293317.arch
     
110. Tue Sep 27 05:26:33 2011
     
111. LOGMINER: End mining logfile: /arc/1_31_749293317.arch
     
112. Tue Sep 27 05:26:33 2011
     
113. LOGMINER: Begin mining logfile: /arch1/1_32_749293317.arch
     
114. LOGSTDBY Analyzer process P003 started with pid=26 OS id=7819
     
115. LOGSTDBY Apply process P004 started with pid=27 OS id=7821
     
116. LOGSTDBY Apply process P005 started with pid=28 OS id=7823
     
117. LOGSTDBY Apply process P007 started with pid=29 OS id=7827
     
118. LOGSTDBY Apply process P008 started with pid=30 OS id=7829
     
119. LOGSTDBY Apply process P006 started with pid=25 OS id=7825
     
120. Tue Sep 27 05:26:40 2011
     
121. LOGMINER: End mining logfile: /arch1/1_32_749293317.arch
     
122. Tue Sep 27 05:26:43 2011
     
123. LOGMINER: Log Auto Delete - deleting: /arc/1_30_749293317.arch
     
124. Deleted file /arc/1_30_749293317.arch
     
125. Tue Sep 27 05:26:43 2011
     
126. LOGMINER: Log Auto Delete - deleting: /arc/1_31_749293317.arch
     
127. Deleted file /arc/1_31_749293317.arch

但是过了一会，发现standby报了很些错

～～～～～～～～～～～～

问题 1：session受限

～～～～～～～～～～～～

1. Tue Sep 27 07:02:23 2011
   
2. Errors in file /u01/app/oracle/admin/liuzhou/udump/liuzhou_rfs_6073.trc:
   
3. ORA-00604: error occurred at recursive SQL level 1
   
4. ORA-00018: maximum number of sessions exceeded
   
5. ORA-00604: error occurred at recursive SQL level 1
   
6. ORA-00018: maximum number of sessions exceeded
   
7. RFS LogMiner: Encountered error [604] while processing [STAT1] event
   
8. Tue Sep 27 07:02:23 2011
   
9. Errors in file /u01/app/oracle/admin/liuzhou/udump/liuzhou_rfs_6073.trc:
   
10. ORA-00604: error occurred at recursive SQL level 1
    
11. ORA-00018: maximum number of sessions exceeded
    
12. ORA-00604: error occurred at recursive SQL level 1
    
13. ORA-00018: maximum number of sessions exceeded
    
14. RFS LogMiner: Encountered error [604] while processing [GAP] event
    
15. Tue Sep 27 07:02:23 2011
    
16. Errors in file /u01/app/oracle/admin/liuzhou/udump/liuzhou_rfs_6073.trc:
    
17. ORA-00604: error occurred at recursive SQL level 1
    
18. ORA-00018: maximum number of sessions exceeded
    
19. ORA-00604: error occurred at recursive SQL level 1
    
20. ORA-00018: maximum number of sessions exceeded
    
21. Tue Sep 27 07:02:25 2011
    
22. ksvcreate: Process(m001) creation failed

Search了下，发现应该是sessions和processes的参数太小了。

1. do select * from v$resource_limit and paste here but you MUST have not shutdown the datatabse because the data in that v$ is from instance startup.
   
2. 
   
3. Default value for sessions is 1.1 * processes + 5
   
4. Increase the initialization parameter "sessions" in init.ora and restart the database.

改了下standby的两个参数，问题解决：

1. solution:
   
2. ~~~~~~~~~~~~~~~~~~~~~~~~~
   
3. 
   
4. 
   
5. SQL> show parameter session
   
6. 
   
7. NAME TYPE VALUE
   
8. ------------------------------------ ----------- ------------------------------
   
9. java_max_sessionspace_size integer 0
   
10. java_soft_sessionspace_limit integer 0
    
11. license_max_sessions integer 0
    
12. license_sessions_warning integer 0
    
13. logmnr_max_persistent_sessions integer 1
    
14. session_cached_cursors integer 20
    
15. session_max_open_files integer 10
    
16. sessions integer 49
    
17. shared_server_sessions integer
    
18. SQL>
    
19. SQL>
    
20. SQL>
    
21. SQL> alter database stop logical standby apply;
    
22. 
    
23. Database altered.
    
24. 
    
25. SQL> shutdown immediate;
    
26. Database closed.
    
27. Database dismounted.
    
28. ORACLE instance shut down.
    
29. SQL>
    
30. SQL>
    
31. SQL>
    
32. SQL> startup mount;
    
33. ORACLE instance started.
    
34. 
    
35. Total System Global Area 289406976 bytes
    
36. Fixed Size 1219016 bytes
    
37. Variable Size 234882616 bytes
    
38. Database Buffers 50331648 bytes
    
39. Redo Buffers 2973696 bytes
    
40. Database mounted.
    
41. SQL>
    
42. SQL>
    
43. 
    
44. SQL> alter system set sessions=60 scope=spfile;
    
45. 
    
46. System altered.
    
47. 
    
48. SQL> show parameter process
    
49. 
    
50. NAME TYPE VALUE
    
51. ------------------------------------ ----------- ------------------------------
    
52. aq_tm_processes integer 0
    
53. db_writer_processes integer 1
    
54. gcs_server_processes integer 0
    
55. job_queue_processes integer 0
    
56. log_archive_max_processes integer 2
    
57. processes integer 40
    
58. 
    
59. 
    
60. 
    
61. SQL> alter system set processes=60 scope=spfile;
    
62. 
    
63. System altered.
    
64. 
    
65. SQL>
    
66. SQL>
    
67. SQL> shutdown immedaite;
    
68. SP2-0717: illegal SHUTDOWN option
    
69. SQL> shutdown immediate;
    
70. ORA-01109: database not open
    
71. 
    
72. 
    
73. Database dismounted.
    
74. ORACLE instance shut down.
    
75. SQL> startup
    
76. ORACLE instance started.
    
77. 
    
78. Total System Global Area 289406976 bytes
    
79. Fixed Size 1219016 bytes
    
80. Variable Size 234882616 bytes
    
81. Database Buffers 50331648 bytes
    
82. Redo Buffers 2973696 bytes
    
83. Database mounted.
    
84. Database opened.
    
85. SQL>
    
86. SQL>
    
87. SQL>
    
88. SQL>
    
89. 
    
90. 
    
91. 
    
92. 
    
93. Testing:
    
94. 
    
95. Sat Sep 24 23:41:19 2011
    
96. Redo Shipping Client Connected as PUBLIC
    
97. -- Connected User is Valid
    
98. RFS[1]: Assigned to RFS process 8898
    
99. RFS[1]: Identified database type as 'logical standby'
    
100. Sat Sep 24 23:41:19 2011
     
101. RFS LogMiner: Client enabled and ready for notification
     
102. 
     
103. 
     
104. SYS@liuzhou> alter system archive log current;
     
105. 
     
106. System altered.
     
107. 
     
108. 
     
109. 
     
110. Sat Sep 24 23:45:45 2011
     
111. Redo Shipping Client Connected as PUBLIC
     
112. -- Connected User is Valid
     
113. RFS[2]: Assigned to RFS process 8903
     
114. RFS[2]: Identified database type as 'logical standby'
     
115. Primary database is in MAXIMUM PERFORMANCE mode
     
116. Sat Sep 24 23:45:45 2011
     
117. RFS LogMiner: Client enabled and ready for notification
     
118. Primary database is in MAXIMUM PERFORMANCE mode
     
119. RFS[2]: Successfully opened standby log 3: '/u01/app/oracle/oradata/liuzhou/stdbylog03a.log'
     
120. Sat Sep 24 23:47:25 2011
     
121. RFS[1]: Successfully opened standby log 4: '/u01/app/oracle/oradata/liuzhou/stdbylog04a.log'
     
122. Sat Sep 24 23:47:26 2011
     
123. RFS LogMiner: Client enabled and ready for notification
     
124. Sat Sep 24 23:47:28 2011
     
125. RFS LogMiner: Registered logfile [/arch1/1_33_749293317.arch] to LogMiner session id [1]

更严谨的做法是应该也把primary的相应参数和standby的统一。

～～～～～～～～～～～～～～

问题2： 日志挖掘未启用

～～～～～～～～～～～～～～

1. primary:
   
2. 
   
3. SYS@liuzhou> alter system archive log current;
   
4. 
   
5. System altered.
   
6. 
   
7. 
   
8. 
   
9. LNS1 started with pid=13, OS id=4382
   
10. Sun Sep 25 21:50:30 2011
    
11. Thread 1 advanced to log sequence 39
    
12.   Current log# 1 seq# 39 mem# 0: /u01/app/oracle/oradata/liuzhou/redo01.log
    
13. Sun Sep 25 21:50:32 2011
    
14. LNS: Standby redo logfile selected for thread 1 sequence 39 for destination LOG_ARCHIVE_DEST_2
    
15. Sun Sep 25 21:51:05 2011
    
16. sARC1: Standby redo logfile selected for thread 1 sequence 38 for destination LOG_ARCHIVE_DEST_2
    
17. 
    
18. 
    
19. 
    
20. 
    
21. ～～～～～～～～～
    
22. standby:
    
23. 
    
24. Mon Sep 26 19:27:07 2011
    
25. RFS[6]: Possible network disconnect with primary database
    
26. Redo Shipping Client Connected as PUBLIC
    
27. -- Connected User is Valid
    
28. RFS[7]: Assigned to RFS process 657
    
29. RFS[7]: Identified database type as 'logical standby'
    
30. Primary database is in MAXIMUM PERFORMANCE mode
    
31. Mon Sep 26 19:27:24 2011
    
32. RFS LogMiner: Client enabled and ready for notification
    
33. Primary database is in MAXIMUM PERFORMANCE mode
    
34. RFS[7]: Successfully opened standby log 4: '/u01/app/oracle/oradata/liuzhou/stdbylog04a.log'
    
35. Mon Sep 26 19:28:30 2011
    
36. RFS[1]: Successfully opened standby log 3: '/u01/app/oracle/oradata/liuzhou/stdbylog03a.log'
    
37. Mon Sep 26 19:28:33 2011
    
38. RFS LogMiner: Client enabled and ready for notification
    
39. Mon Sep 26 19:28:36 2011
    
40. RFS LogMiner: Registered logfile [/arch1/1_38_749293317.arch] to LogMiner session id [1]
    
41. 
    
42. 
    
43. 
    
44. SQL> alter database stop logical standby apply;
    
45. 
    
46. Database altered.
    
47. 
    
48. 
    
49. Mon Sep 26 19:33:16 2011
    
50. alter database stop logical standby apply
    
51. Mon Sep 26 19:33:16 2011
    
52. ALTER DATABASE STOP LOGICAL STANDBY APPLY
    
53. Completed: alter database stop logical standby apply
    
54. 
    
55. SQL> alter database start logical standby apply immediate;
    
56. 
    
57. Database altered.
    
58. 
    
59. 
    
60. Mon Sep 26 19:33:56 2011
    
61. alter database start logical standby apply immediate
    
62. Mon Sep 26 19:33:57 2011
    
63. ALTER DATABASE START LOGICAL STANDBY APPLY (liuzhou)
    
64. Mon Sep 26 19:33:57 2011
    
65. with optional part
    
66. IMMEDIATE
    
67. LSP0 started with pid=21, OS id=726
    
68. Mon Sep 26 19:33:57 2011
    
69. Attempt to start background Logical Standby process
    
70. LOGSTDBY Parameter: DISABLE_APPLY_DELAY =
    
71. LOGSTDBY Parameter: REAL_TIME =
    
72. Completed: alter database start logical standby apply immediate
    
73. Mon Sep 26 19:33:59 2011
    
74. LOGSTDBY status: ORA-16111: log mining and apply setting up
    
75. Mon Sep 26 19:33:59 2011
    
76. LOGMINER: Parameters summary for session# = 1
    
77. LOGMINER: Number of processes = 3, Transaction Chunk Size = 201
    
78. LOGMINER: Memory Size = 30M, Checkpoint interval = 150M
    
79. LOGMINER: session# = 1, builder process P001 started with pid=23 OS id=730
    
80. LOGMINER: session# = 1, preparer process P002 started with pid=24 OS id=732
    
81. LOGMINER: session# = 1, reader process P000 started with pid=22 OS id=728
    
82. Mon Sep 26 19:34:06 2011
    
83. LOGMINER: Begin mining logfile: /arch1/1_32_749293317.arch
    
84. Mon Sep 26 19:34:06 2011
    
85. LOGMINER: Turning ON Log Auto Delete
    
86. LOGSTDBY Analyzer process P003 started with pid=25 OS id=734
    
87. Mon Sep 26 19:34:12 2011
    
88. LOGMINER: End mining logfile: /arch1/1_32_749293317.arch
    
89. Mon Sep 26 19:34:12 2011
    
90. LOGMINER: Begin mining logfile: /arch1/1_33_749293317.arch
    
91. LOGSTDBY Apply process P005 started with pid=27 OS id=738
    
92. LOGSTDBY Apply process P007 started with pid=29 OS id=742
    
93. LOGSTDBY Apply process P004 started with pid=26 OS id=736
    
94. LOGSTDBY Apply process P006 started with pid=28 OS id=740
    
95. LOGSTDBY Apply process P008 started with pid=30 OS id=744
    
96. Mon Sep 26 19:34:14 2011
    
97. LOGMINER: End mining logfile: /arch1/1_33_749293317.arch
    
98. Mon Sep 26 19:34:14 2011
    
99. LOGMINER: Begin mining logfile: /arch1/1_34_749293317.arch
    
100. Mon Sep 26 19:34:24 2011
     
101. LOGMINER: Log Auto Delete - deleting: /arch1/1_32_749293317.arch
     
102. Deleted file /arch1/1_32_749293317.arch
     
103. Mon Sep 26 19:34:25 2011
     
104. LOGMINER: End mining logfile: /arch1/1_34_749293317.arch
     
105. Mon Sep 26 19:34:25 2011
     
106. LOGMINER: Begin mining logfile: /arch1/1_35_749293317.arch
     
107. Mon Sep 26 19:34:25 2011
     
108. LOGMINER: End mining logfile: /arch1/1_35_749293317.arch
     
109. Mon Sep 26 19:34:25 2011
     
110. LOGMINER: Begin mining logfile: /arch1/1_36_749293317.arch
     
111. Mon Sep 26 19:34:25 2011
     
112. LOGMINER: End mining logfile: /arch1/1_36_749293317.arch
     
113. Mon Sep 26 19:34:25 2011
     
114. LOGMINER: Begin mining logfile: /arch1/1_37_749293317.arch
     
115. Mon Sep 26 19:34:25 2011
     
116. LOGMINER: End mining logfile: /arch1/1_37_749293317.arch
     
117. Mon Sep 26 19:34:25 2011
     
118. LOGMINER: Begin mining logfile: /arch1/1_38_749293317.arch
     
119. Mon Sep 26 19:34:25 2011
     
120. LOGMINER: Log Auto Delete - deleting: /arch1/1_33_749293317.arch
     
121. Deleted file /arch1/1_33_749293317.arch
     
122. Mon Sep 26 19:34:25 2011
     
123. LOGMINER: Log Auto Delete - deleting: /arch1/1_34_749293317.arch
     
124. Deleted file /arch1/1_34_749293317.arch
     
125. Mon Sep 26 19:34:37 2011
     
126. LOGMINER: End mining logfile: /arch1/1_38_749293317.arch
     
127. Mon Sep 26 19:34:37 2011
     
128. LOGMINER: Begin mining logfile: /u01/app/oracle/oradata/liuzhou/stdbylog04a.log
     
129. 
     
130. 
     
131. 
     
132. 
     
133. 
     
134. 
     
135. 
     
136. 
     
137. SQL> select sequence#,first_change#,next_change#,timestamp,applied from dba_logstdby_log;
     
138. 
     
139.  SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP APPLIED
     
140. ---------- ------------- ------------ --------- --------
     
141.         32 349188 354354 27-SEP-11 CURRENT
     
142.         33 354354 357832 24-SEP-11 NO
     
143.         34 357832 370418 25-SEP-11 NO
     
144.         35 370418 371936 25-SEP-11 NO
     
145.         36 371936 371941 25-SEP-11 NO
     
146.         37 371941 371944 25-SEP-11 NO
     
147.         38 371944 390090 26-SEP-11 NO
     
148. 
     
149. 7 rows selected.
     
150. 
     
151. SQL> select sequence#,first_change#,next_change#,timestamp,applied from dba_logstdby_log;
     
152. 
     
153.  SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP APPLIED
     
154. ---------- ------------- ------------ --------- --------
     
155.         35 370418 371936 25-SEP-11 YES
     
156.         36 371936 371941 25-SEP-11 YES
     
157.         37 371941 371944 25-SEP-11 YES
     
158.         38 371944 390090 26-SEP-11 YES