Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article, I will teach you to Changing Parameters From SQLPLUS While Broker Is Enabled.
Changing parameters from SQLPLUS while the broker is ENABLE means that the broker does not work properly / cannot perform monitoring operations.
We are testing.
[Commands run from DGMGRL are all run from Primary-1 by connecting to DGMGRL]1. We connect to the broker and question STATUS.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | DGMGRL> connect sys/Passw0rd4 Connected. DGMGRL> show configuration Configuration - Broker _Configuration Protection Mode: MaxPerformance Databases: primary - Primary database standby - Physical standby database logical - Logical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS DGMGRL> show database logical Database - logical Role: LOGICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 0 seconds ago) Apply Lag: 0 seconds (computed 0 seconds ago) Apply Rate: 0 Byte/s Instance(s): primary1 primary2 (apply instance) Database Status: SUCCESS |
2. We query the tables of the TEST scheme to be tested.
1 2 3 4 5 | [Primary-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ EMPLOYEES_YEDEK |
1 2 3 4 5 | [Logical-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ EMPLOYEES_YEDEK |
1 2 3 4 5 | [Physical-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ EMPLOYEES_YEDEK |
3. We connect to SQLPLUS and stop Redo-Apply in Logical Standby Database.
1 2 3 | [Logical-2] SQL> alter database stop logical standby apply; Database altered. |
[Logical-2]—> ALERT LOG
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | Sat Jan 14 23:20:29 2017 alter database stop logical standby apply Sat Jan 14 23:20:30 2017 LOGSTDBY Apply process AS02 server id=2 pid=67 OS id=19645 stopped Sat Jan 14 23:20:30 2017 LOGSTDBY Analyzer process AS00 server id=0 pid=65 OS id=19635 stopped Sat Jan 14 23:20:30 2017 LOGSTDBY Apply process AS01 server id=1 pid=66 OS id=19639 stopped Sat Jan 14 23:20:30 2017 LOGSTDBY Apply process AS04 server id=4 pid=69 OS id=19656 stopped Sat Jan 14 23:20:30 2017 LOGSTDBY Apply process AS05 server id=5 pid=72 OS id=19660 stopped Sat Jan 14 23:20:30 2017 LOGSTDBY Apply process AS03 server id=3 pid=68 OS id=19651 stopped Sat Jan 14 23:20:30 2017 LOGMINER: session#=1 (Logical_Standby$), preparer MS02 pid=62 OS id=19630 sid=143 stopped Sat Jan 14 23:20:30 2017 LOGMINER: session#=1 (Logical_Standby$), builder MS01 pid=60 OS id=19626 sid=24 stopped Sat Jan 14 23:20:30 2017 LOGMINER: session#=1 (Logical_Standby$), reader MS00 pid=57 OS id=19622 sid=83 stopped Sat Jan 14 23:20:30 2017 LOGSTDBY status: ORA-16128: User initiated stop apply successfully completed Completed: alter database stop logical standby apply |
4. We query the configuration from the broker.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | DGMGRL> show configuration Configuration - Broker _Configuration Protection Mode: MaxPerformance Databases: primary - Primary database standby - Physical standby database logical - Logical standby database Error: ORA-16810: multiple errors or warnings detected for the database Fast-Start Failover: DISABLED Configuration Status: ERROR |
5. We create a table under the TEST scheme to see if the change made from SQLPLUS is activated, although the configuration fails in the broker.
1 2 3 4 5 6 7 8 9 10 | [Primary-1] SQL> create table test.locations_yedek as select * from hr.locations; Table created. [Primary-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ EMPLOYEES_YEDEK LOCATIONS_YEDEK |
1 2 3 4 5 6 | [Physical-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ EMPLOYEES_YEDEK LOCATIONS_YEDEK |
1 2 3 4 5 | [Logical-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ EMPLOYEES_YEDEK |
6. We start Redo-Apply by connecting from SQLPLUS.
1 2 3 | [Logical-2] SQL> alter database start logical standby apply immediate; Database altered. |
[Logical-2]—>ALERT LOG
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 | Sat Jan 14 23:25:45 2017 alter database start logical standby apply immediate ALTER DATABASE START LOGICAL STANDBY APPLY (primary2) with optional part IMMEDIATE Attempt to start background Logical Standby process Sat Jan 14 23:25:45 2017 LSP0 started with pid=57, OS id=22149 Completed: alter database start logical standby apply immediate LOGMINER: Parameters summary for session# = 1 LOGMINER: Number of processes = 3, Transaction Chunk Size = 201 LOGMINER: Memory Size = 30M, Checkpoint interval = 150M LOGMINER: SpillScn 5599813, ResetLogScn 925702 LOGMINER: summary for session# = 1 LOGMINER: StartScn: 0 (0x0000.00000000) LOGMINER: EndScn: 0 (0x0000.00000000) LOGMINER: HighConsumedScn: 5599799 (0x0000.00557237) LOGMINER: session_flag: 0x1 LOGMINER: Read buffers: 16 LOGMINER: Memory LWM: limit 10M, LWM 24M, 80% LOGMINER: Memory Release Limit: 1M Sat Jan 14 23:25:46 2017 LOGMINER: session#=1 (Logical_Standby$), reader MS00 pid=60 OS id=22156 sid=26 started Sat Jan 14 23:25:46 2017 LOGMINER: session#=1 (Logical_Standby$), builder MS01 pid=62 OS id=22160 sid=142 started Sat Jan 14 23:25:46 2017 LOGMINER: session#=1 (Logical_Standby$), preparer MS02 pid=65 OS id=22164 sid=91 started LOGMINER: Begin mining logfile for session 1 thread 1 sequence 550, +DATA/logical/onlinelog/group_6.274.932894839 LOGMINER: Begin mining logfile for session 1 thread 2 sequence 308, +DATA/logical/onlinelog/group_9.277.932894841 Sat Jan 14 23:25:46 2017 LOGSTDBY Analyzer process AS00 started with server id=0 pid=66 OS id=22168 Sat Jan 14 23:25:46 2017 LOGSTDBY Apply process AS03 started with server id=3 pid=69 OS id=22184 Sat Jan 14 23:25:46 2017 LOGSTDBY Apply process AS04 started with server id=4 pid=72 OS id=22188 Sat Jan 14 23:25:46 2017 LOGSTDBY Apply process AS05 started with server id=5 pid=76 OS id=22192 Sat Jan 14 23:25:46 2017 LOGSTDBY Apply process AS02 started with server id=2 pid=68 OS id=22180 Sat Jan 14 23:25:46 2017 LOGSTDBY Apply process AS01 started with server id=1 pid=67 OS id=22174 |
7. We check whether the table is created in Logical Standby.
1 2 3 4 5 6 | [Logical-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ EMPLOYEES_YEDEK LOCATIONS_YEDEK |
8. We check whether the error is gone by querying the STATUS of the configuration from the broker.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | DGMGRL> connect sys/Passw0rd4 Connected. DGMGRL> show configuration Configuration - Broker _Configuration Protection Mode: MaxPerformance Databases: primary - Primary database standby - Physical standby database logical - Logical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS |
