Creating
Logical Standby
Create a Physical Standby
Database
Ensure the physical standby
database is caught up to the primary database.
Preparing the Primary Database Before
creating the logical database, some tasks will have to be performed and the
primary database will have to be prepared for the Data Guard setup.
The steps for preparing the Primary Database
for Logical Standby Database creation are given below:
1. Check for unsupported
Data type and Tables
It
is important to identify unsupported database objects on the primary database
before you create a logical standby database. This is because changes made to
unsupported datatypes, table, sequences, or views on the primary database will
not be propagated to the logical standby database. Moreover, no error message
will be returned.
Unsupported Datatypes
Unsupported Tables, Sequences, and Views
- Most
schemas that ship with the Oracle database are skipped by SQL Apply
- Tables
with unsupported datatypes
- Tables
using table compression
Use the
following query on the primary database to list the schema and table names of
primary database tables that are not supported by logical standby databases:
SELECT
* FROM DBA_LOGSTDBY_UNSUPPORTED_TABLE;
OR
SELECT DISTINCT
OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED
ORDER BY OWNER,
TABLE_NAME;
OWNER TABLE_NAME
------------------------------
---------------------
IX
AQ$_ORDERS_QUEUETABLE_G
IX AQ$_ORDERS_QUEUETABLE_H
IX
AQ$_ORDERS_QUEUETABLE_I
OE CATEGORIES_TAB
OE CUSTOMERS
OE WAREHOUSES
PM ONLINE_MEDIA
PM PRINT_MEDIA
SH MVIEW$_EXCEPTIONS
To view the column name and data type for one
of the tables listed in the previous query-
SELECT
COLUMN_NAME, DATA_TYPE FROM DBA_LOGSTDBY_UNSUPPORTED
WHERE
OWNER=<owner name> AND TABLE_NAME=<table name>;
SELECT
COLUMN_NAME,DATA_TYPE FROM DBA_LOGSTDBY_UNSUPPORTED
WHERE OWNER='OE'
AND TABLE_NAME = 'CUSTOMERS';
COLUMN_NAME DATA_TYPE
------------------------------
---------------------------------
CUST_ADDRESS CUST_ADDRESS_TYP
PHONE_NUMBERS PHONE_LIST_TYP
CUST_GEO_LOCATION SDO_GEOMETRY
SELECT
COLUMN_NAME,DATA_TYPE FROM DBA_LOGSTDBY_UNSUPPORTED
WHERE OWNER='IX'
AND TABLE_NAME = 'AQ$_ORDERS_QUEUETABLE_G';
COLUMN_NAME DATA_TYPE
------------------------------
---------------------------------------
MSGID RAW
SUBSCRIBER# NUMBER
NAME VARCHAR2
ADDRESS# NUMBER
SIGN OBJECT
DBS_SIGN OBJECT
Unsupported Schemas Dropped
DROP USER OR
CASCADE;
DROP USER PM
CASCADE;
DROP USER SH
CASCADE;
DROP USER IX
CASCADE;
If
the primary database contains unsupported tables, log apply services
automatically exclude these tables when applying redo data to the logical
standby database.
If any of the critical tables in the primary database are
unsupported then using a Physical Standby Database might be a better option.
By default, all SQL statements except those in the following
list are applied to a logical standby database if they are executed on a
primary database:
2. Ensure the table rows
in the Primary Database can be Uniquely Identified.
Because
the ROWIDs on a logical standby database might not be the same as the ROWIDs on
the primary database, a different mechanism must be used to match the updated
row on the primary database to its corresponding row on the logical standby
database. You can use one of the following to match up the corresponding rows:
Oracle recommends that you add a primary key or a unique
index to tables on the primary database, whenever appropriate and possible, to
ensure SQL Apply can efficiently apply data updates to the logical standby
database.
Query
the DBA_LOGSTDBY_NOT_UNIQUE view to identify tables in the primary database that do not
have a primary key or unique index with NOT
NULL columns.
SELECT *
FROM DBA_LOGSTDBY_NOT_UNIQUE;
OWNER TABLE_NAME B
------------------------------
------------------------------ -
SCOTT BONUS N
SCOTT SALGRADE N
SH SALES N
SH COSTS N
SH
SUPPLEMENTARY_DEMOGRAPHICS N
The
following query displays a list of tables that SQL Apply might not be able to uniquely identify:
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
------------------------------
------------------------------ -
TSMSYS SRS$ Y
SCOTT BONUS N
SCOTT SALGRADE N
HR COUNTRIES N
The
value of the BAD_COLUMN column will be either Y or N, as described in the
following list:
Indicates a table column is defined using an unbounded
datatype, such as CLOB or BLOB. SQL Apply attempts to maintain these tables,
but you must ensure the application provides uniqueness in bounded columns
only. Note that if two rows in the table match except for rows in the LOB
column, then the table cannot be maintained properly and SQL Apply will stop.
Indicates the table contains enough column information to
maintain the table in a logical standby database.
The physical
organization in a logical standby database is different from that of the
primary database, even though the logical standby database is created from a
backup copy of the primary database. The ROWIDs contained in the redo records
generated by the primary database cannot be used to identify the corresponding
row in the logical standby database.
Oracle
uses primary-key or unique-key supplemental logging to logically identify a
modified row in the logical standby database. When database-wide primary-key
and unique-key supplemental logging is enabled, each UPDATE statement also
writes the column values necessary in the redo log to uniquely identify the
modified row in the logical standby database.
·
If a table has a primary key defined, then
the primary key is logged as part of the UPDATE statement to identify the
modified row.
·
In the absence of a primary key, the shortest
non-null unique key is logged as part of the UPDATE statement to identify the
modified row.
·
In the absence of both a primary key and a
non-null unique key, all columns of bounded size are logged as part of the
UPDATE statement to identify the modified row. In other words, all columns
except those with the following types are logged: LONG, LOB, LONG ROW, object
type, and collections.
Oracle Corporation recommends that there
should be a
primary key or a non-null unique index to tables in the primary database,
whenever possible, to ensure that SQL Apply can efficiently apply redo data
updates to the logical standby database.
3. Add a Disabled Primary Key RELY Constraint
ALTER TABLE tsmsys.srs$ ADD
PRIMARY KEY(empid,ename) RELY DISABLE;
4. Stop Redo Apply on the Physical Standby
Database to be converted to Logical Standby
ALTER DATABASE RECOVER MANAGED
STANDBY DATABASE CANCEL
A Log Miner
dictionary must be built into the redo data so that the Log Miner component of
SQL Apply can properly interpret changes in the redo.
When you execute the DBMS_LOGSTDBY.BUILD
procedure, the LogMiner dictionary is built into redo data and supplemental
logging is automatically enabled for logging primary key and unique key
columns. Supplemental logging ensures
that each update contains enough information to logically identify the affected
row.
Supplemental
logging is automatically enabled in Oracle 11g
Execute the
procedure on the primary database.
Note:
The DBMS_LOGSTDBY.BUILD procedure waits for all existing transactions to complete so that long running transactions executing on the primary database will affect its operation.
EXECUTE DBMS_LOGSTDBY.BUILD;
6. Adjust
Initialization parameter LOG_ARCHIVE_DEST_n
on Logical Standby as well as
Primary Database to separate the local destinations for:
Primary Database to separate the local destinations for:
Archived redo log
files that store redo data generated by the logical standby database.
Archived redo log
files that store redo data received from the primary database.
ALTER SYSTEM SET
LOG_ARCHIVE_DEST_4='LOCATION=c:\delhi\logical_standby_log
VALID_FOR=(STANDBY_LOGFILES, STANDBY_ROLE) DB_UNIQUE_NAME=delhi' SCOPE=BOTH;
ALTER SYSTEM SET
LOG_ARCHIVE_DEST_STATE_4=ENABLE SCOPE = BOTH;
7. Transition
Physical Standby Database to Logical Standby Database
SHU IMMEDIATE
STARTUP MOUNT
ALTER DATABASE RECOVER TO
LOGICAL STANDBY DELHI;
This will
change spfile parameter DB_NAME
to DELHI
SHU IMMEDIATE
STARTUP MOUNT
ALTER DATABASE OPEN
RESETLOGS;
Database altered.
SELECT OPEN_MODE,
DATABASE_ROLE , SWITCHOVER_STATUS FROM V$DATABASE;
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
--------------------
---------------- --------------------
READ WRITE LOGICAL STANDBY NOT ALLOWED
ARCHIVE LOG LIST
Database log mode Archive Mode
Automatic archival Enabled
Archive destination C:\delhi\Logical_Standby_Log
Oldest online log
sequence 1
Next log sequence to
archive 2
Current log sequence 2
SELECT SEQUENCE#, APPLIED,
NAME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# APPLIED
NAME
---------- --------- -------------------------------------------------
1 NO C:\DELHI\ARCHIVELOG\ARC0000000001_0779717715.0001
2 NO C:\DELHI\ARCHIVELOG\ARC0000000002_0779717715.0001
8. Start SQL Apply.
Issue the following
statement to begin applying redo data to the logical standby database.
ALTER DATABASE START LOGICAL
STANDBY APPLY IMMEDIATE;
Database altered.
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
--------------------
---------------- --------------------
READ
WRITE LOGICAL STANDBY NOT ALLOWED
ALTER
SYSTEM REGISTER;
9. Verify the Logical Standby Database Is Performing Properly
SELECT
sequence#,applied,file_name FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#;
SEQUENCE#
APPLIED
FILE_NAME
----------
-------- -------------------------------------------------
91 YES
C:\DELHI\ARCHIVELOG\ARC0000000091_0779574141.0001
92 YES
C:\DELHI\ARCHIVELOG\ARC0000000092_0779574141.0001
93 YES
C:\DELHI\ARCHIVELOG\ARC0000000093_0779574141.0001
94 YES C:\DELHI\ARCHIVELOG\ARC0000000094_0779574141.0001
If
log is not shipped to logical standby location, restart the primary database.
10 Verify redo data is being applied correctly.
On the
logical standby database, query the
V$LOGSTDBY_STATS view to verify redo data is being applied correctly.
SELECT
NAME, VALUE FROM V$LOGSTDBY_STATS WHERE NAME = 'coordinator state';
NAME VALUE
--------------------------- --------------
coordinator
state INITIALIZING
When the coordinator
process is INITIALIZING, log
apply services are preparing to begin SQL Apply, but data from the archived
redo log files is not being applied to the logical standby database, it takes
time so wait. If it shows IDLE , then log has been
applied.
SELECT NAME,
VALUE FROM V$LOGSTDBY_STATS WHERE NAME = 'coordinator state';
NAME VALUE
---------------------- -----
coordinator
state IDLE
11. View the V$LOGSTDBY view to see current SQL Apply activity.
On the
logical standby database, query the
V$LOGSTDBY view to see a current snapshot of SQL Apply activity.
A text message describing the current activity of each process involved in
reading and applying changes is displayed.
SYS@delhi>
SELECT TYPE, HIGH_SCN, STATUS FROM V$LOGSTDBY;
TYPE HIGH_SCN STATUS
------------------------------
----------------------------------------
COORDINATOR 1219665 ORA-16116: no
work available
ANALYZER 1219643 ORA-16116: no
work available
APPLIER 1219643 ORA-16116:
no work available
APPLIER 1219640 ORA-16116:
no work available
APPLIER 1219576 ORA-16116:
no work available
APPLIER 1216010 ORA-16116:
no work available
APPLIER 1216498 ORA-16116:
no work available
READER 1219665 ORA-16242:
Processing log file
(thread# 1, sequence# 97)
(thread# 1, sequence# 97)
BUILDER 1219643 ORA-16116: no work
available
PREPARER 1219642 ORA-16116: no
work available
SELECT
SID,SERIAL#,SPID,TYPE,HIGH_SCN FROM V$LOGSTDBY_PROCESS;
SID
SERIAL# SPID TYPE HIGH_SCN
----------
---------- ------------ ------------------------------ ----------
44 7 3264 COORDINATOR 1225171
55 1 2516 ANALYZER 1225121
56 1 580 APPLIER 1223847
57 1 328 APPLIER 1221407
58 1 1388 APPLIER 1221470
59 1 2360 APPLIER 1225118
60 1 2160 APPLIER 1225121
39 7 2908 READER 1225171
42 5 572 BUILDER 1225121
43 2 2356 PREPARER 1225120
SELECT
APPLIED_SCN, LATEST_SCN FROM V$LOGSTDBY_PROGRESS;
APPLIED_SCN
LATEST_SCN
-----------
----------
1225398
1225402
12.
On shutdown of logical standby database, each time you have to restart SQL apply.
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
13. Securing Logical Standby Database
Configure the
database guard to control user access to table
ALTER DATABASE GUARD command keywords:
ALL Prevent
users from making changes to any data in the database.
STANDBY Prevent
users from making changes to any data maintained by SQL
APPLY
NONE Normal
security
The Data Guard level
is automatically set to ALL by the broker on the logical standby
database.
The Data Guard level
applies to all users except SYS
SELECT
GUARD_STATUS FROM V$DATABASE;
GUARD_STATUS
------------
ALL
Connect
scott/tiger
DELETE
FROM EMP WHERE DEPTNO=10;
delete
from emp where deptno=10
*
ERROR
at line 1:
ORA-16224:
Database Guard is enabled
connect sys/admin as sysdba
ALTER DATABASE GUARD NONE;
database altered.
SELECT GUARD_STATUS FROM
V$DATABASE;
GUARD_SATUS
-----------
NONE
Connect scott/tiger
DELETE FROM EMP WHERE
DEPTNO=10;
3 rows deleted.
END
No comments:
Post a Comment