Logical Standby Database

Logical Standby Database Creation Procedure

We have to first create physical standby database after we configure physical standby database to logical standby database.

Step:1
For logical standby we have to first create PHYSICAL STANDBY DATABASE and sure physical standby database working properly.

Go through below link for PHYSICAL STANDBY DATABASE CREATION
http://dbataj.blogspot.com/2007/03/standby-database.html

Step:2
Prepare the Primary Database to Support a Logical Standby Database


1. Ensure Supplemental Logging Is Enabled

SQL> alter database add supplemental log data
( primary key, unique index) columns;

Database altered.

If you enable supplemental logging on a primary database in a Data Guard configuration that also contains physical standby databases, then you must issue the ALTER DATABASE ADD SUPPLEMENTAL LOG DATA statement on each physical standby database to ensure future switchovers work correctly.

On Physical Standby Database

SQL> alter database add supplemental log data
2 (PRIMARY KEY, UNIQUE INDEX) columns;

Database altered.

Step:3
Prepare the Primary Database for Role Transitions


Primary Database: Logical Standby Role Initialization Parameters

Create new folder for archive_orcl1 archive log on Primary Database.

SQL> alter system set
2 log_archive_dest_3=
3 'LOCATION=c:\oracle\product\10.1.0\archive_orcl1
4 valid_for=(standby_logfiles,standby_role)
5 db_unique_name=orcl';

System altered.


SQL> alter system set
2 LOG_ARCHIVE_DEST_STATE_3=ENABLE;

System altered.

SQL> alter system set undo_retention=3600;

System altered.

Step:3


Prepare to Transition to a Logical Standby Database



Ensure Supplemental Logging Is Enabled

On Physical Standby DB

SQL> ed
Wrote file afiedt.buf

1 SELECT SUPPLEMENTAL_LOG_DATA_PK AS PK_LOG,
2 SUPPLEMENTAL_LOG_DATA_UI AS UI_LOG
3* FROM V$DATABASE
SQL> /

PK_ UI_
--- ---
YES YES

Create new folder for archive_orclstdy1 archive log on Physical standby DB

SQL> alter system set
2 log_archive_dest_3=
3 'LOCATION=c:\oracle\product\10.1.0\archive_orclstdy1
4 valid_for=(standby_logfiles,standby_role)
5 db_unique_name=orclstdy';

System altered.


SQL> alter system set
2 LOG_ARCHIVE_DEST_STATE_3=ENABLE;

System altered.

SQL> alter system set undo_retention=3600;

System altered.


Shut down the logical standby database.



SQL> SHUTDOWN IMMEDIATE;


Step:4


Create a Control File for the Logical Standby Database




SQL> alter database create logical standby controlfile
2 as 'c:\oracle\product\10.1.0\oradata\orclstdy\controlstdy01.ctl';

Database altered.

Note: Above Created control file transfer logical standby database if logical standby database is remote site. otherwise directly create control file in logical standby folder in some host. like above example :ORCLSTDY: is logical/physical standby database folder.

Step:5


Start the Logical Standby Database

On Logical Standby Database
Start and mount the logical standby database.



SQL> startup mount ;
ORACLE instance started.

Total System Global Area 356515840 bytes
Fixed Size 789400 bytes
Variable Size 330036328 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.

Must sure your able to connect primary database through TNS_ENTRY
eg: scott/tiger@ORCL


On Logical Standby Database
Prepare for SQL Apply


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;


On Logical Standby Database
Activate the logical standby database.

SQL> alter database activate standby database;

Database altered.

Reset the database name of the logical standby database.

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.


SQL> startup mount;
ORACLE instance started.

Total System Global Area 356515840 bytes
Fixed Size 789400 bytes
Variable Size 330036328 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.
SQL>


C:\>nid TARGET=sys/oracle@orclstdy DBNAME=orclstdy
DBNEWID: Release 10.1.0.5.0 - Production
Copyright (c) 2001, 2004, Oracle. All rights reserved.

Connected to database ORCL (DBID=1152774966)

Connected to server version 10.1.0

Control Files in database:
C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCLSTDY\CONTROLSTDY01.CTL

Change database ID and database name ORCL to ORCLSTDY? (Y/[N]) => Y


Proceeding with operation
Changing database ID from 1152774966 to 3530778477
Changing database name from ORCL to ORCLSTDY
Control File C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCLSTDY\CONTROLSTDY01.CTL - m
odified
Datafile C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCLSTDY\SYSTEM01.DBF - dbid chang
ed, wrote new name
Datafile C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCLSTDY\UNDOTBS01.DBF - dbid chan
ged, wrote new name
Datafile C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCLSTDY\SYSAUX01.DBF - dbid chang
ed, wrote new name
Datafile C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCLSTDY\USERS01.DBF - dbid change
d, wrote new name
Datafile C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCLSTDY\EXAMPLE01.DBF - dbid chan
ged, wrote new name
Datafile C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCLSTDY\SYSTEM02.DBF - dbid chang
ed, wrote new name
Control File C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCLSTDY\CONTROLSTDY01.CTL - d
bid changed, wrote new name
Instance shut down

Database name changed to ORCLSTDY.
Modify parameter file and generate a new password file before restarting.
Database ID for database ORCLSTDY changed to 3530778477.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.


Create New PASSWORD files


C:\>ORAPWD FILE=C:\ORACLE\PRODUCT\10.1.0\DB_1\DATABASE\PWDORCL.ORA PASSWORD=ORAC
LE ENTRIES=5 FORCE=Y


C:\>ORAPWD FILE=C:\ORACLE\PRODUCT\10.1.0\DB_1\DATABASE\PWDORCLSTDY.ORA PASSWORD=
ORACLE ENTRIES=5 FORCE=Y


Chnage DB_NAME parameter in "INITORCLSTDY.ORA" file


C:\>set oracle_sid=orclstdy

C:\>sqlplus sys as sysdba

SQL*Plus: Release 10.1.0.5.0 - Production on Sun Jun 17 18:39:42 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter password:
Connected to an idle instance.

SQL> create pfile from spfile;

File created.


SQL> --modify db_name parameter >>> orcl to orclstdy <<<
SQL> create spfile from pfile;

File created.

Restart the logical standby database

SQL>startup mount

SQL> alter database open resetlogs;

Database altered.


Change the logical standby database global name


SQL> alter database rename global_name to orclstdy;

Database altered.


Create a new temporary file for the logical standby database.


SQL> select tablespace_name from dba_tablespaces
2 where contents = 'TEMPORARY';

TABLESPACE_NAME
------------------------------
TEMP

SQL> select * from v$tempfile;

no rows selected


SQL> alter tablespace temp
2 add tempfile 'c:\oracle\product\10.1.0\oradata\orclstdy\temp01.dbf'
3 size 40m reuse;

Tablespace altered.


Start SQL Apply.



SQL> alter database start logical standby apply;

Database altered.

Step:6

Verify the Logical Standby Database Is Performing Properly



Verify the archived redo log files were registered.

SQL> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';

Session altered.

SQL> select sequence#, first_time, next_time, dict_begin,dict_end
2 from dba_logstdby_log
3 order by 1;


SEQUENCE# FIRST_TIME NEXT_TIME DIC DIC
---------- -------------------- -------------------- --- ---
337 18-jun-2007 23:58:54 19-jun-2007 00:01:05 NO NO
338 19-jun-2007 00:01:05 19-jun-2007 00:39:07 NO NO
339 19-jun-2007 00:39:07 19-jun-2007 01:55:34 NO NO
340 19-jun-2007 01:55:34 19-jun-2007 03:14:26 NO NO
341 19-jun-2007 03:14:26 19-jun-2007 04:44:27 NO NO
342 19-jun-2007 04:44:27 19-jun-2007 06:13:28 NO NO
343 19-jun-2007 06:13:28 19-jun-2007 07:29:38 NO NO
344 19-jun-2007 07:29:38 19-jun-2007 08:54:20 NO NO



Send redo data to the standby database.


SQL> conn sys@ORCL as sysdba
Enter password:
Connected.
SQL> --On primary database issue below command
SQL> alter system archive log current;

System altered.

SQL> alter system archive log current;

System altered.


Query the DBA_LOGSTDBY_LOG view again.



SQL> conn sys@ORCLSTDY as sysdba
Enter password:
Connected.
SQL> --On logical standby database

SQL> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';

Session altered.

SQL> select sequence#, first_time, next_time, dict_begin,dict_end
2 from dba_logstdby_log
3 order by 1;


SEQUENCE# FIRST_TIME NEXT_TIME DIC DIC
---------- -------------------- -------------------- --- ---
337 18-jun-2007 23:58:54 19-jun-2007 00:01:05 NO NO
338 19-jun-2007 00:01:05 19-jun-2007 00:39:07 NO NO
339 19-jun-2007 00:39:07 19-jun-2007 01:55:34 NO NO
340 19-jun-2007 01:55:34 19-jun-2007 03:14:26 NO NO
341 19-jun-2007 03:14:26 19-jun-2007 04:44:27 NO NO
342 19-jun-2007 04:44:27 19-jun-2007 06:13:28 NO NO
343 19-jun-2007 06:13:28 19-jun-2007 07:29:38 NO NO
344 19-jun-2007 07:29:38 19-jun-2007 08:54:20 NO NO

345 19-jun-2007 08:54:20 19-jun-2007 10:14:30 NO NO
346 19-jun-2007 10:14:30 19-jun-2007 10:14:35 NO NO
347 19-jun-2007 10:14:35 19-jun-2007 10:15:13 NO NO



Verify redo data is being applied correctly.



SQL> column name format a30
SQL> column value format a30
SQL> select name,value from v$logstdby_stats where name = 'coordinator state';

NAME VALUE
------------------------------ ------------------------------
coordinator state APPLYING



View the V$LOGSTDBY view to see current SQL Apply activity.



SQL> column status format a50
SQL> column type format a12
SQL> select type,high_scn,status from v$logstdby;

TYPE HIGH_SCN STATUS
------------ ---------- --------------------------------------------------
COORDINATOR 1824043 ORA-16116: no work available
READER 1824043 ORA-16116: no work available
BUILDER 1824028 ORA-16116: no work available
PREPARER 1824027 ORA-16116: no work available
ANALYZER 1824013 ORA-16116: no work available
APPLIER 1823998 ORA-16116: no work available
APPLIER 1824013 ORA-16116: no work available
APPLIER 1823949 ORA-16116: no work available
APPLIER 1823972 ORA-16116: no work available
APPLIER 1823606 ORA-16116: no work available

10 rows selected.