Bugra Canbakal's Oracle Blog

  • Home
  • Contact
  • SITE MAP
You are here: Home / Oracle / Installation of Logical Standby

Installation of Logical Standby

11:46 AM By Bugra Canbakal Leave a Comment

1.PURPOSE AND SCOPE
At another article of mine ,I explained  installation of Physical standby and this article we are going to convert physical standby database to logical standby.

2.PRACTICE

2.1. At primary database we need to execute a query to see which tables are not supported by logical standby

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
SQL> Select * from dba_logstdby_unsupported;
 
 OWNER                 TABLE_NAME                    COLUMN_NAME         DATA_TYPE          
--------------------- --------------------------    ----------------    ---------------
OE    CATEGORIES_TAB    CATEGORY_ID                    Object Table        NUMBER
OE    CATEGORIES_TAB    PARENT_CATEGORY_ID            Object Table
 
OWNER                 TABLE_NAME                    COLUMN_NAME         DATA_TYPE          
--------------------- --------------------------    ----------------    ---------------
WMSYS                 WM$UDTRIG_INFO                TRIG_CODE           LONG
WMSYS                 WM$VERSIONED_TABLES           UNDO_CODE           WM$ED_UNDO_CODE_TABLE_TYPE

2.2. And also we need to find tables which hasnt got primary key or non-null unique indexes.We can execute query below for it.
N = It represent that it receive enough coloumn information for that table.
Y = It represent that LONG, CLOB or BLOB datatype at the table.We can bypass this problem using supplemental logging.

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
SQL>  SELECT OWNER, TABLE_NAME,BAD_COLUMN FROM DBA_LOGSTDBY_NOT_UNIQUE
     WHERE TABLE_NAME NOT IN (SELECT TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED);
 
OWNER                          TABLE_NAME                     B
------------------------------ ------------------------------ -
SCOTT                          BONUS                          N
SCOTT                          SALGRADE                       N
SH                             SALES                          N
SH                             COSTS                          N
SH                             SUPPLEMENTARY_DEMOGRAPHICS     N

2.3. Lets check supplemental logging.

Oracle PL/SQL
1
2
3
4
5
SQL> select supplemental_log_data_pk,supplemental_log_data_ui from v$database;
 
SUP SUP
--- ---
NO  NO

2.4. If it is closed we  need to open it for tables which hasnt got primary key or non-null unique indexes .

Oracle PL/SQL
1
2
3
4
5
6
7
SQL> ALTER  DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY,UNIQUE INDEX) COLUMNS;
 
Database altered.
 
SQL> ALTER SYSTEM SWITCH LOGFILE;
 
System altered.

2.5. We can also open parallelism. //  Optional.

Oracle PL/SQL
1
SQL> ALTER SYSTEM SET LOG_PARALLELISM=1 SCOPE=BOTH;

2.6.  We need to stop physcal standby.

Oracle PL/SQL
1
2
3
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
 
Database altered.

2.7. At primary database  log_archive_dest_1 parameter set to  All_LOGFILES and ALL_ROLES ,we need to change this to ONLINE_LOGFILES and ALL_ROLES .And we need to set  Log_archive_dest_3 and enable it.

Oracle PL/SQL
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
SQL> show parameter log
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      LOCATION=/u01/app/oracle/datab
                                                 ases/prim/redo/
                                                 VALID_FOR=(ALL_LOGFILES,ALL_RO
                                                 LES)
                                                 DB_UNIQUE_NAME=bugra
log_archive_dest_2                   string      service=stdby lgwr async noaff
                                                 irm valid_for=(online_logfiles
                                                 ,primary_role) db_unique_name=
                                                 stdby
log_archive_dest_3                   string
log_archive_dest_state_1             string      ENABLE
log_archive_dest_state_2             string      enable
log_archive_dest_state_3             string      enable
remote_login_passwordfile            string      EXCLUSIVE
sec_case_sensitive_logon             boolean     TRUE
sec_max_failed_login_attempts        integer     10
 
SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/databases/prim/redo/ VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES)  DB_UNIQUE_NAME=bugra'
scope=both;
 
System altered.
 
SQL> Alter system set LOG_ARCHIVE_DEST_3='LOCATION=/u01/app/oracle/databases/prim/redo2/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=bugra' scope=both;
 
System altered.
 
SQL> alter system set LOG_ARCHIVE_DEST_STATE_3=ENABLE scope=both;
 
System altered.

2.8. We need to create logminer for Redo log dictionary so with this we can use ONLINE_LOGFILE parameter which we set it to LOG_ARCHIVE_DEST_1.

Oracle PL/SQL
1
2
3
SQL> EXECUTE DBMS_LOGSTDBY.BUILD;  
 
PL/SQL procedure successfully completed.

2.9. So now redo logs are prepared for Logical standby. Lets staPhysical standby makirt the recover process at logical standby.

Oracle PL/SQL
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
SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY stdby;
 
Standby Alertloglari
 
Media Recovery Complete (stdby)
tkcrrxms: Killing 4 processes (all RFS)
RESETLOGS after incomplete recovery UNTIL CHANGE 904454
Resetting resetlogs activation ID 772557144 (0x2e0c4958)
Online log /u01/app/oracle/product/11.1.0/oradata/stdby/redogroup_1.262.720476831: Thread 1 Group 1 was previously cleared
Online log /u01/app/oracle/product/11.1.0/oradata/stdby/redogroup_1.263.720476839: Thread 1 Group 1 was previously cleared
Online log /u01/app/oracle/product/11.1.0/oradata/stdby/redogroup_2.264.720476843: Thread 1 Group 2 was previously cleared
Online log /u01/app/oracle/product/11.1.0/oradata/stdby/redogroup_2.265.720476851: Thread 1 Group 2 was previously cleared
Standby became primary SCN: 904452
Sat Sep 18 02:28:01 2010
Setting recovery target incarnation to 3
Converting standby mount to primary mount.
ACTIVATE STANDBY: Complete - Database mounted as primary (stdby)
*** DBNEWID utility started ***
DBID will be changed from 772585308 to new DBID of 1331092977 for database BUGRA
DBNAME will be changed from BUGRA to new DBNAME of STDBY
Starting datafile conversion
Setting recovery target incarnation to 1
Datafile conversion complete
Failed to find temporary file: /u01/app/oracle/product/11.1.0/oradata/stdby/data/temp.268.720476923
Database name changed to STDBY.
Modify parameter file and generate a new password file before restarting.
Database ID for database STDBY changed to 1331092977.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open with RESETLOGS option.
Succesfully changed database name and ID.
*** DBNEWID utility finished succesfully ***
Completed: ALTER DATABASE RECOVER TO LOGICAL STANDBY stdby
Sat Sep 18 02:28:14 2010
destination database instance is 'started' not 'mounted'

2.10. Our logical standby opened with nomount option and now we need to change log_archive_dest of  1,2 and 3.

Oracle PL/SQL
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
42
43
44
45
46
47
48
49
SQL> Show parameter log
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      location=/u01/app/oracle/produ
                                                 ct/11.1.0/oradata/stdby/arc1/
                                                 valid_for=(all_logfiles,all_ro
                                                 les) db_unique_name=stdby
log_archive_dest_2                   string      service=bugra lgwr async noaff
                                                 irm valid_for=(online_logfiles
                                                 ,primary_role) db_unique_name=
                                                 bugra
log_archive_dest_3                   string
log_archive_dest_state_1             string      enable
log_archive_dest_state_2             string      ENABLE
log_archive_dest_state_3             string      enable
 
SQL> alter system set LOG_ARCHIVE_DEST_1=
  'LOCATION=/u01/app/oracle/product/11.1.0/oradata/stdby/arc1/
   VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES)
   DB_UNIQUE_NAME=stdby'scope=both;
 
System altered.
 
SQL> Alter system set LOG_ARCHIVE_DEST_2=
  'SERVICE=bugra ASYNC
   VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
   DB_UNIQUE_NAME=bugra' scope=both;
 
System altered.
 
SQL> Alter system set LOG_ARCHIVE_DEST_3=
  'LOCATION=/u01/app/oracle/product/11.1.0/oradata/stdby/arc2/
   VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)
   DB_UNIQUE_NAME=stdby' scope=both;
 
System altered.
 
SQL> Alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;
 
System altered.
 
SQL> Alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;
 
System altered.
 
SQL> Alter system set LOG_ARCHIVE_DEST_STATE_3=ENABLE scope=both;
 
System altered.

2.11.  We are closing database and open it at mount stage and after of it we need to open database with resetlogs option.

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SQL> shutdown immediate;
ORA-01507: database not mounted
 
ORACLE instance shut down.
SQL> startup mount;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
 
Total System Global Area  146472960 bytes
Fixed Size                  1298472 bytes
Variable Size              92278744 bytes
Database Buffers           50331648 bytes
Redo Buffers                2564096 bytes
Database mounted.
SQL> ALTER DATABASE OPEN RESETLOGS;
 
Database altered.
 
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
 
Database altered.

2.12.  Lets check logical standby Alert log.

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Completed: alter database open
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE
ALTER DATABASE START LOGICAL STANDBY APPLY (stdby)
with optional part
IMMEDIATE
Attempt to start background Logical Standby process
Sat Sep 18 03:32:48 2010
LSP0 started with pid=24, OS id=32724
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 0, ResetLogScn 522753
Sat Sep 18 03:33:12 2010
LOGMINER: session#=1, reader MS00 pid=28 OS id=32739 sid=82 started
Sat Sep 18 03:33:14 2010
LOGMINER: session#=1, builder MS01 pid=29 OS id=32741 sid=79 started
Sat Sep 18 03:33:14 2010
LOGMINER: session#=1, preparer MS02 pid=30 OS id=32743 sid=78 started
LOGMINER: Begin mining logfile during dictionary load for session 1 thread 1 sequence 41, /u01/app/oracle/product/11.1.0/oradata/stdby/arc1/1_41_720476831.dbf
LOGMINER: Turning ON Log Auto Delete

2.13. Also we can change V$database .

Oracle PL/SQL
1
2
3
4
5
6
 
SQL> select log_mode,open_mode , database_role from v$database;
 
LOG_MODE     OPEN_MODE  DATABASE_ROLE
------------ ---------- ----------------
ARCHIVELOG   READ WRITE LOGICAL STANDBY

Share this:

  • LinkedIn
  • Twitter

Konuyla ilgili diğer yazılar

  • Data Guard Broker installation, configuration, monitoring and deactiving

Filed Under: Oracle, Standby - Dataguard Tagged With: dataguard, logical dataguard, logical standby

Leave a Reply Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

About Me



Language:

  • English
  • Turkish

Categories

  • Oracle
    • RAC – Real Application Cluster
    • RMAN
    • Single Node
    • Standby – Dataguard
  • OS
    • Linux

Blogroll

  • Emre Baransel
  • Gökhan Atıl
  • H. Tonguc Yılmaz
  • Kamil Türkyılmaz
  • Tanel Poder
  • Turkce Oracle
  • Turkish Oracle User Group
  • Uwe Hesse
  • Zekeriya Beşiroğlu

Archives

  • April 2014
  • March 2014
  • August 2013
  • June 2013
  • March 2013
  • January 2013
  • December 2012
  • November 2012
  • July 2012
  • June 2012
  • May 2012
  • January 2012
  • November 2011
  • October 2011
  • August 2011
  • July 2011
  • June 2011
  • February 2011
  • January 2011
  • December 2010
  • November 2010
  • June 2010
OCP
Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy

[footer_backtotop]

Copyright © 2010-2014 Bugra Canbakal. All rights reserved.