The following is the configuration steps for the Logical Standby Database.
(The location of some of the files used below is temporary, but also have certain modifications)
Place the primary database to Force Logging mode. Do the following:
SQL> ALTABASE force Logging;
Confirm that the primary database is archived and defined locally archive. As follows:
SQL> ALTER SESTEM SET log_archive_dest_1 = 'location = E: / Oracle / ORADATA / ORCL / Archive Mandatory' Scope = Both;
Confirm that the log_paallelism initializes the parameter value, the value of this parameter is 1, which is a default. If the found parameter value is 1, it is not necessary to make changes. If it is not a value, use the alter system set command to modify it. The specific operation is as follows:
SQL> Show parameter log_parameter ------ View the current value of the parameter
SQL> ALTER SESTEM SET LOG_PARALLISM = 1 Scope = Both;
Turn off the database after modification and restart this parameter is valid.
SQL> Shutdown Immediate
SQL> Startup
Confirm supported data types and tables:
Some data types and tables are supported while establishing Logical Standby Database. Some are not supported.
Supported data types are:
Charr
Nchar
Varchar2 and varchar
NVARCHAR2
Number
Date
Timestamp
TimeStamp with Time Zone
TimeStamp with local time zone
Interval year to Month
Interval day to second
Raw
Clob
BLOB
Unsupported data types are:
NClob
Long
Long Raw
Bfile
RowID
UroWID
User-defined Types
Object Types Refs
Varrays
NESTED TABLES
The object types that are not supported are:
User-defined Tables and SEQUENCES in The Sys Schema
Tables with unsupported DataTypes
Tables Using Data Segment Compression
Index-Organized Tables
Confirm that the primary data contains an unsupported object to query the data dictionary table DBA_LOGSTDBY_UNSUPPORTED:
SQL> SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPPPPORTED
2> ORDER BY OWNER, TABLE_NAME;
View the field names and data types of the tables drawn above with the following ways:
SQL> SELECT Column_name, Data_Type from DBA_LOGSTDBY_UNSUPPPPPPPPorted
2> WHERE OWNER = 'OE' and Table_name = 'Customers';
Logical Standby Database will filter the SQL statement:
Alter Database
Alter session
ALTER SNAPSHOT
Alter Snapshot log
Alter System Switch log
Create Control File
Create Database
Create Database Link
Create Pfile from SPFILE
Create Schema Authorization
Create snapshot
Create Snapshot Logcreate SPFile from Pfile
Create Table As SELECT FROM A Cluster Table
DROP DATABASE LINK
Drop snapshot
Drop snapshot log
EXPLAIN
Lock Table
Rename
SET Constraints
Set role
SET Transaction
Make sure that the table line in the primary database can be unique (ie there is a primary key in the table)
Find the table with no primary key:
SQL> SELECT OWNER, TABLE_NAME, BAD_COLUMN FROM DBA_LOGSTDBY_NOT_UNIQUE
2> WHERE TABLE_NAME NOT IN (SELECT TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED);
Owner Table_name B
------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------
OUTLN OL $ HINTS N
OUTLN OL $ NODES N
System Mview $ _ADV_BaseTable N
System mview $ _adv_sqldepend n
System mview $ _adv_filterinstance n
System Def $ _ORIGIN N
System repcat $ _SNAPGROUP N
System repcat $ _DDL N
System repcat $ _RESOLUTION_STATISTICS N
System repcat $ _Runtime_Parms Y
System mview $ _adv_index n
Select it to see the BAD_COLUMN field value. If this value is n to indicate that this table has enough column information that is not the primary key field. If this value is y, the information is not enough to add a Disable Rely constraint to make this form. Log on this table can apply to the logical spare library.
Select only the table repcat $ _Runtime_parms under the system user needs to be modified, then
SQL> DESC System. Repcat $ _Runtime_parms View the structure of this table. Then make the modified as follows:
SQL> ALTER TABLE SYSTEM. RepCAT $ _RUNTIME_PARMS Add Primary Key (Runtime_Parm_ID, Parameter_name) Rely Disable;
After the modification is finished, do the above query to confirm that there is no table needs to be modified:
SQL> SELECT OWNER, TABLE_NAME, BAD_COLUMN FROM DBA_LOGSTDBY_NOT_UNIQUE
2> WHERE TABLE_NAME NOT IN (SELECT TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED); OWNER TABLE_NAME B
------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------
OUTLN OL $ HINTS N
OUTLN OL $ NODES N
System Mview $ _ADV_BaseTable N
System mview $ _adv_sqldepend n
System mview $ _adv_filterinstance n
System Def $ _ORIGIN N
System repcat $ _SNAPGROUP N
System repcat $ _DDL N
System repcat $ _RESOLUTION_STATISTICS N
System mview $ _adv_index n
Confirm that the primary database can make up the logging (Supplemental Logging)
SQL> SELECT Supplemental_Log_Data_PK, Supplemental_Log_Data_UI from V $ Database;
SUP SUP
--- -----
NO no (no value means that the main database is not supported)
Make the primary database to make up the log
SQL> ALTABASE ADD Supplemental Log Data (Primary Key, UNIQUE INDEX) Columns; (this statement will be related to the unique information modified by the main data) and can pass the log to the alternate database and do log applications
Do new log switching in the primary database. Because after you finish Enable Supplemental Logging. The current log may contain a complementary log and the log applly of the logical standby database, is not supported. Log. So you want to ensure that all logs to be transferred from the primary database to the standby database before you create a logical alternate database.
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
Confirm that the primary database is supported at this time.
SQL> SELECT Supplemental_Log_Data_PK, Supplemental_Log_Data_UI from V $ Database;
SUP SUP
--- -----
YES YES (YES value means that the primary database is support for complement log)
Supplemental_log_data_pk: Support for complementing logs, its information is Primary Key
Supplemental_log_data_ui: Support for complementing logs, its information is Unique Index
Create an alternating table space in the primary database. And use dbms_logmnr_d.set_tablespace to put some tables in this table space. Since there are some tables in the logical alternate database belong to Sys and System users, these are placed in the table space. These tables may have slightly recorded after a period of time. In order to cause the SYSTEM table space to cause the database DOWN machine, establish this alternating table space to store these tables. SQL> Create TableSpace logmnrts DataFile '/ disk1 / Oracle /dbs/logmnrts.dbf '
2> Size 25M AutoExtend on MaxSize 2048M
3> segment space management auto;
SQL> EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE ('Logmnrts');
(Step on the top EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE ('logmnrts'); can be maintained in the future maintenance)
Create a logical standby database
Confirm the data files and log files for the primary database
Get the location of the data file by query V $ datafile in the primary database
SQL> SELECT NAME FROM V $ datafile;
Name
-------------------------------------------------- ---------
E: /oracle/ora92/orcl/system01.dbf
E: /oracle/ora92/orcl/undotbs01.dbf
E: /oracle/ora92/orcl/example01.dbf
E: /oracle/ora92/orcl/indx01.dbf
E: /oracle/ora92/orcl/tools01.dbf
E: /oracle/ora92/orcl/users01.dbf
E: /oracle/ora92/orcl/logmnrts.dbf
E: /oracle/ora92/orcl/xdb01.dbf
Get the location of the log file by query V $ logfile in the primary database
SQL> COL MEMBER FORMAT A35
SQL> SELECT Group #, TYPE, MEMBER from V $ logfile;
Group # Type MEMBER
---------- --------------------------------------- -
1 Online E: /oracle/ora92/orcl/redo01.log
2 Online E: /oracle/ora92/orcl/redo02.log
3 Online E: /oracle/ora92/orcl/redo03.log
Make a cold backup for the primary database (full backup, copy all required files: data files, control files, parameter files) The specific practice is as follows:
Close Database: SQL> Shutdown Immediate
Copy the previous data file to a temporary position f: / oracle
Start the database to MOUNT state: SQL> Startup Mount
Create a backup control file for the logical alternate database:
SQL> ALTER DATABASE Backup ControlFile To
2> 'f: /oracle/bkcontrol.ora';
Place the primary database in the Restriccted Session mode (when the database is turned on to avoid DML and DDL operations for the database): SQL> ALTER System Enable Restricted Session;
Establishing the LogMiner Dictionary (Logical Standby Database) Requires the logminer tool when decomposing Redo log for SQL statements, and this tool needs to create a Logminer dictionary before use):
SQL> ALTABASE OPEN;
SQL> EXECUTE DBMS_LOGSTDBY.BUILD; Cancel the RESTRICTED SESSION mode of the primary database allows users to perform DML and DDL statements:
SQL> ALTER SYSTEM DISABLE RESSICITED Session;
View the nearest archive log (need to be used during the creation of the logical alternate database)
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT; (Archive for the current log)
SQL> SELECT NAME FROM V $ Archived_log
2> Where (Sequence # = (Sequence Max (Sequence #) from V $ Archived_log
3> Where dictionary_begin = 'yes' and standby_dest = 'NO'));
Name
-------------------------------------------------- ---------------
E: /oracle/oradata/orcl/archive/1_19.dbf
The above query where the WHERE clause Dictionary_begin indicates that the new dictionary is to be found, StandBy_Dest indicates that the local archive directory is not a remote archive.
Create an initialization parameter for the alternate database:
SQL> CREATE PFILE = 'f: /oracle/initstdby.ora' from spfile;
Copy the data files obtained from the previous step from the main database from the server of the server from the server of the primary database. (Copy to the f: / oracle directory)
The value after the rendezed database server modifies the initialization parameter (the original copy of the initialization parameter file) is probably:
* .aq_tm_processes = 1
* .BACKGROUND_DUMP_DEST = 'E: / Oracle / Admin / Standby / BDump'
* .compatible = '9.2.0.0.0'
* .control_files = 'e: /oracle/oradata/controlfile/bkcontrol.ora'
* .CORE_DUMP_DEST = 'E: / Oracle / Admin / StandByl / CDump'
* .db_block_size = 16384
* .db_cache_size = 137363456
* .db_domain = ''
* .db_file_multiblock_read_count = 16
* .db_name = 'orcl'
* .dispatchers = '(protocol = tcp) (service = orclxdb)'
* .fast_start_mttr_target = 300
* .hash_join_ENABED = TRUE
* .instance_name = 'standby'
* .java_pool_size = 33554432
* .job_queue_processes = 10
* .lage_pool_size = 27262976
* .log_archive_dest_1 = 'location = E: / Oracle / ORADATA / Archive Mandatory'
* .log_archive_dest_state_1 = 'enable'
* .log_archive_format = 'Arch% D_% s.dbf'
* .log_archive_start = true
* .Open_cursors = 300
* .pga_aggregate_target = 80000000 * .processes = 150
* .query_rewrite_enabled = 'false'
* .remote_login_passwordfile = 'Exclusive'
* .shared_pool_size = 45088768
* .sort_area_size = 524288
* .SQL_TRACE = FALSE
* .STAR_TRANSFORMATION_ENABLED = 'false'
* .timed_statistics = true
* .undo_management = 'auto'
* .undo_retention = 10800
* .undo_tablespace = 'undotbs1'
* .user_dump_dest = 'E: / Oracle / Admin / Standby / UDUMP'
* .workarea_size_policy = 'auto'
* .lock_name_space = standby
* .standby_file_management = 'auto'
* .standby_archive_dest = 'E: / Oracle / ORADATA / STDARCH'
* .log_parallelism = 1
Create a new instance at one end of the alternate database. The following:
C: /> ORADIM -NEW -SID Standby -startmode Auto
Configure listner.ora, tnsnames.ora, and SQLNET.ORA files in the main data. After configuring, the following:
Listener =
(Description_List =
(Description_List =
(Description =
(Address = (protocol = TCP) (Host = 10.100.0.122) (port = 1521)))
)
)
(Description_List =
(Description =
(Address = (protocol = ipc) (key = extproc1)))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSEXTPROC)
(Oracle_Home = E: / Oracle / ORA92)
(Program = extproc)
)
(SID_DESC =
(Global_dbname = orcl)
(Oracle_Home = E: / Oracle / ORA92)
(SID_NAME = ORCL)
)
)
TNSNames.ora file is:
ORCL =
(Description =
(Address_list =
(Address = (protocol = TCP) (Host = 10.100.0.122) (port = 1521)))
)
(Connect_data =
(Server = DEDICATED)
(Service_name = orcl)
)
)
STANDBY =
(Description =
(Address_list =
(Address = (protocol = TCP) (Host = 10.100.0.22) (port = 1521)))
)
(Connect_Data = (Server = DEDICATED)
(Service_name = orcl)
)
)
EXTPROC_CONNECTION_DATA =
(Description =
(Address_list =
(Address = (protocol = ipc) (key = extproc1)))
)
(Connect_data =
(SID = PLSEXTPROC)
(Presentation = RO)
)
)
SQLNET.ORA file is:
SQLNET.AUTHENTICATION_SERVICES = (NTS)
Names.directory_path = (Hostname, TNSNames, Onames)
Configure Listener.ora, TnsNames.ora and SQLNET.ORA in the spare database. The configuration after the configuration is as follows:
Where parameter SQLNET.EXPIRE_TIME in the SQLNET.Or file is Enable Dead Connection Detection
Listener =
(Description_List =
(Description =
(Address_list =
(Address = (protocol = IPC) (key = extproc))
)
(Address_list =
(Address = (protocol = TCP) (Host = 10.100.0.22) (port = 1521)))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(Global_dbname = orcl2)
(Oracle_Home = E: / Oracle / ORA92)
(SID_NAME = ORCL)
)
(SID_DESC =
(Program = extproc)
(SID_NAME = PLSEXTPROC)
(Oracle_Home = E: / Oracle / ORA92)
)
)
TNSNames.ora file is:
ORCL =
(Description =
(Address_list =
(Address = (protocol = TCP) (Host = 10.100.0.122) (port = 1521)))
)
(Connect_data =
(Server = DEDICATED)
(Service_name = orcl)
)
)
STANDBY =
(Description =
(Address_list =
(Address = (protocol = TCP) (Host = 10.100.0.22) (port = 1521)))
)
(Connect_data =
(Server = DEDICATED)
(Service_name = orcl)
)
)
EXTPROC_CONNECTION_DATA =
(Description =
(Address_list =
(Address = (protocol = IPC) (key = extproc0)))
)
(Connect_data =
(SID = PLSEXTPROC)
(Presentation = RO)
)
)
SQLNET.ORA file is:
SQLNET.AUTHENTICATION_SERVICES = (NTS)
Names.directory_path = (Hostname, TNSNames, Onames)
SQLNET.EXPIRE_TIME = 2
Start and close the listener with LSNRCTL Start and LSNRCTL Stop in the primary database and alternate database. Then in the primary database with TNSPing Standby and try two libraries in the standby database.
Start the database to the MOUNT state with the initialization parameter file.
SQL> Startup Mount Pfile = f: /oracle/admin/standby/pfile/initstdby.ora;
Rename Data File. If the location of the copy of the data file is different from the position in the main library. And now we use the control file from the main library backup, so you have to put the data file rename to the new location. Do the following Operation (if the location is the same, you can do it):
SQL> ALTER DATABASE RENAME FILE 'E: /Oracle/ora92/orcl/system01.dbf'
2> To 'f: /oracle/standby/system01.dbf';
SQL> ALTER DATABASE RENAME FILE 'E: / Oracle/ora92/orcl/undotbs01.dbf'
2> To 'f: / oracle / standardby / undotbs01.dbf';
SQL> ALTER DATABASE RENAME FILE 'E: / Oracle/ora92/orcl/example01.dbf'
2> To 'f: / oracle / standby / example01.dbf';
SQL> ALTABASE RENAME FILE 'E: /Oracle/ora92/orcl/indx01.dbf'
2> to 'f: / oracle / standardby / index01.dbf';
SQL> ALTER DATABASE RENAME FILE 'E: / Oracle/ora92/orcl/tools01.dbf'
2> To 'f: / oracle / standardby / tools01.dbf';
SQL> ALTABASE RENAME FILE 'E: /Oracle/ora92/orcl/users01.dbf'
2> To 'f: / oracle / standardby / users01.dbf';
SQL> ALTER DATABASE RENAME FILE 'E: / Oracle/ora92/orCl/xdb01.dbf'
2> To 'f: / oracle / standardBy / xdb01.dbf';
SQL> ALTER DATABASE RENAME FILE 'E: / Oracle/ora92/orcl/logmnrts.dbf'
2> To 'f: / oracle / standardby / logmnrts.dbf';
Rename log file. This step only changes the location information of the log file in the control file. There is no real log file to generate, and there is no copy log file from the primary library to the standby library. Do as follows:
SQL> ALTER DATABASE RENAME FILE 'E: /Oracle/ora92/orcl/redo01.log'
2> To 'e: /oracle/radata/redo01.log';
SQL> ALTABASE RENAME FILE 'E: /Oracle/ora92/orcl/redo02.log'
2> To 'E: / Oracle / Radata /Redo02.log';sql> ALTER DATABASE RENAME FILE' E: / Oracle/ora92/orcl/redo03.log '
2> To 'e: /oracle/radata/redo03.log';
Open Data Guard in the standby database (User users can't do update operations in Logical Standby Database), do the following:
SQL> ALTER DATABASE Guard ALL;
SQL> ALTER DATABASE OPEN RESETLOGS;
Rederate the database name of the Logical Standby Database (this step is not fully understood)
Use the DBNewID (NID) tool to reset the database name. To turn off the database first, start the database to mount status:
SQL> Shutdown Immediate;
SQL> Startup Mount Pfile = f: /oracle/initstdby.ora;
With the DBNewid tool, the operation is as follows (this is on the command line):
Nid target = sys / password @ orcl dbname = standby
Then close the database: SQL> Shutdown Immediate
Change initialization parameters: db_name = standby
Start the database to mount status: SQL> Startup Mount;
Create a Server Parameter file for the standby database:
SQL> CREATE SPFILE from Pfile = f: /oracle/initstdby.ora;
Turn off the database and restart the database with SPFile:
SQL> Shutdown Immediate
SQL> Startup Mount
SQL> ALTER DATABASE OPEN RESETLOGS;
Create a new temporary file (a temporary table space) for the spare database
First look at whether there is a temporary file in the spare library, if you don't create it directly, if you have to delete recreated. (Because the temporary file from the backup of the main library is not used in the standby library, so rebuild) The operation is as follows:
SQL> SELECT * from V $ TEMPFILE; ------- View if there is a temporary file
No rows selected
SQL> ALTER DATABASE TEMPFILE 'TEMPFILENAME' DROP; ------ Delete temporary file before rebuilding
SQL> SELECT TABLESPACE_NAME FROM DBA_TABLESSPACES WHERE
2> Contents = 'temporary'; -------- View the temporary table space name belonging to the temporary file
TableSpace_name
--------------------------------
Temp
SQL> ALTER TABLESPACE TEMP Add Tempfile
2> 'f: /oacle/standby/temp01.dbf'
3> size 40m reuse; -------- Rebuild a temporary file to this temporary table space
Register an archive log and launch SQL Apply Services
SQL> ALTABASE Register Logical Logfile
2> 'E: /Oracle/oradata/orcl/arc0004.001'; ------ Register the nearest archive log to Logical Standby Database. The E: / ORACLE / ORADATA / STDARCH / ARCH1069826922_21.DBF is Previously obtained.
SQL> Alter Database Start Logical Standby Apply Initial; ----- Start Redo Logs Apply, Apply Redo Logs with executing SQL statements. It is SQL Apply Services. To INITIAL is the first time to start SQL Apply Services . The following can be used directly SQL> ALTER DATABASE STOP Logical Standby Apply; ---- Stop
SQL> ALTABASE Start Logical Standby Apply; ---- Startup
Configure remote archiving at the primary database.
SQL> ALTER SESTEM SET LOG_ARCHIVE_DEST_3 = 'Service = payroll3' scope = Both;
-------- Configure Remote Archive Directory
SQL> ALTER SESTEM SET LOG_ARCHIVE_DEST_STATE_3 = enable scope = Both;
-------- Configure the status of this archive directory is available
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT; ---- When archiving the current log, launch remote archive.
Check Logical Standby Database
After building a good Logical Standby Database, the Log Transport Service and Log Apply Service are also launched. You need to check if the log is passing by the primary database to the alternate database and Apply. To do this, use the following steps.
Check if the log has been registered into the Logical Standby Database. Connect to the Alternate Database and query the DBA_LOGSTDBY_LOG view.
SQL> ALTER Session Set NLS_DATE_FORMAT = 'DD-MON-YY HH24: MI: SS'
Session altered.
SQL> SELECT SEQUENCE #, First_TIME, Next_TIME, DICT_BEGIN, DICT_END
2> from dba_logstdby_log order by sequence #;
Sequence # first_TIME NEXT_TIME DIC DIC
---------- ------------------------------------- ---
22 23- November -04 14:00:17 23- November -04 14:26:58 no no
1 rows selected.
Connect to the primary database and archive some logs.
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
SYSTEM altered.
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
SYSTEM altered.
Query the DBA_LOGSTDBY_LOG view.
SQL> ALTER Session Set NLS_DATE_FORMAT = 'DD-MON-YY HH24: MI: SS'
Session altered.
SQL> SELECT SEQUENCE #, First_TIME, Next_TIME, DICT_BEGIN, DICT_END
2 from dba_logstdby_log order by sequence #;
Sequence # first_TIME NEXT_TIME DIC DIC
---------- ------------------------------------- ---
22 23- November -04 14:00:17 23- November -04 14:26:58 No23 23- November -04 14:26:58 23- November -04 14:33:33 No NO
2 rows selected.
(You can see a new archive log)
Check if the data in the log is apply to the logical standby database
Query the DBA_LOGSTDBY_STATS view in the logical standby database to check if the data in the log is being properly appped.
SQL> Column Name Format A30
SQL> Column Value Format A30
SQL> SELECT NAME, VALUE from V $ logStdby_stats where name = 'coordinator stat
Name Value
------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------
Coordinator State Initializing
In the above output, the Coordinator process is initializing, which indicates that the Log Apply Service is preparing Apply SQL. But the data in the log has not started to be in the Applied to the logical standby database.
Check V $ logStdby to see the current SQL Apply active status. Connect to the logical standby database, query the V $ logStdby view.
SQL> Column Status Format A50
SQL> Column Type Format A12
SQL> SELECT TYPE, HIGH_SCN, STATUS from V $ logStdby;
Type high_scn status
------------ ---------- ---------------------------------------------------------------------------------------------------------------------------------- ----------------------
Coordinator ORA-16115: Loading Log Miner Dictionary Data
Reader ORA-16127: Stalled Waiting for Additional Transact
Ions to be coplied
Builder ORA-16117: Processing
Preparer ORA-16116: No Work Available
SQL> SELECT TYPE, HIGH_SCN, STATUS from V $ logStdby;
Type high_scn status
------------ ---------- ---------------------------------------------------------------------------------------------------------------------------------- ----------------------
Coordinator ORA-16126: Loading Table or Sequence Object Number
Reader ORA-16116: No Work Available
Builder ORA-16116: No Work Available
Preparer ORA-16116: No Work Available
If the Redo Data starts Apply to the alternate database, the status of the Coordinator process in the V $ logStdby view will display Applying.
Check that the data in the log is Apply to complete it in the logical standby database from the DBA_LOGSTDBY_PROCESS view.
SQL> SELECT Applied_scn, newest_scn from dba_logstdby_progress; applied_scn newest_scn
----------------------
180702 180702
Among them, if the value of the application_scn and newest_scn is the same, it means that the Coordinator Process process has been applied. The data in the log is also completed.
After log_archive_dest_n initial parameters, specify delay = n (minutes) means that the Redo Log Data transmitted by Primary Database to Standby Database will only start Apply to Standby Database after n minutes. Default is Nodelay, if delay is specified without specified value The words are default for 30 minutes.
