Create Oracle's Data Guard and simple maintenance procedures

sponsored links
Maximum protection: zero data loss. Transactions need to occur simultaneously to the primary database and standby redo log write data.
To ensure the data is not lost, if a transaction can not find redo logs to the backup data write, then the primary database will automatically shut down.
Maximum availability: available in the main database to ensure the maximum extent under the circumstances to ensure the data is not lost. With
Maximum protection is the same time as writing to the owners and standby database redo log. Maximum protection with a different time when found not to write to the standby database redo log will not shut down the database, instead of using the Maximum
performance of the model management until the repair faults.
When an error occurs the second time at least to be able to write to a standby redo log database to ensure zero data loss.
(The case of many backup library. If it is a standby database error occurred will be lost once the data)
Maximum performance: Oracle default this way. Without affecting the performance of the main library where the maximum extent to ensure data is not lost. While the former two methods to prepare libraries to write redo log owners and this will adversely affect system performance. When the transaction will be immediately submitted to the data written to the local online log. Master database redo data stream to be written to at least one from the library, but this is not the main library simultaneously.
If you use enough bandwidth, then this model provides data protection to ensure maximum availability, while the least the main library's performance.
Check the database whether to support Data Guard, Data Guard Enterprise Edition only supports
SQL> select * from v $ option where parameter = 'Managed Standby';
PARAMETER VALUE
-------------------- ----------
Managed Standby TRUE
SQL>
Steps to create the physical standby database:
A mandatory generate log main library, because libraries are prepared using archive log restore, if not be impossible to restore archived logs.
SQL> ALTER DATABASE FORCE LOGGING;
Second, create a password file prepared by the library
D:> orapwd file = D: \ oracle \ product \ 10.2.0 \ db_1 \ database \ PWDauxdb.ora password = oracle
entries = 5;
Create an instance of
D:> oradim-NEW-SID auxdb-INTPWD D: \ oracle \ product \ 10.2.0 \ db_1 \ database \ PWDauxdb.ora
-STARTMODE manual
Third, modify the primary database parameter file, and sync to the spfile
orcl.__db_cache_size = 88080384
orcl.__java_pool_size = 4194304
orcl.__large_pool_size = 4194304
orcl.__shared_pool_size = 67108864
orcl.__streams_pool_size = 0
*. Audit_file_dest = 'D: \ oracle \ product \ 10.2.0 \ admin \ orcl \ adump'
*. Background_dump_dest = 'D: \ oracle \ product \ 10.2.0 \ admin \ orcl \ bdump'
*. Compatible = '10 .2.0.1.0 '
*. Control_files = 'D: \ oracle \ product \ 10.2.0 \ oradata \ orcl \ CONTROL01.CTL', 'D: \ oracle \ product \ 10.2.
0 \ oradata \ orcl \ CONTROL02.CTL ',' D: \ oracle \ product \ 10.2.0 \ oradata \ orcl \ CONTROL03.CTL '
*. Core_dump_dest = 'D: \ oracle \ product \ 10.2.0 \ admin \ orcl \ cdump'
*. Db_block_size = 8192
*. Db_domain =''
*. Db_file_multiblock_read_count = 16
*. Db_name = 'orcl'
*. Db_recovery_file_dest_size = 2147483648
*. Db_recovery_file_dest = 'd: \ oracle \ product \ 10.2.0 \ flash_recovery_area'
*. Db_unique_name = 'orcl'
*. Dispatchers = '(PROTOCOL = TCP) (SERVICE = orclXDB)'
*. Job_queue_processes = 10
*. Log_archive_config = 'dg_config = (orcl, auxdb)'
*. Log_archive_dest_1 = 'location = D: \ oracle \ product \ 10.2.0 \ oradata \ orcl \ archive
VALID_FOR = (ALL_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME = orcl '
*. Log_archive_dest_2 = 'service = AUX VALID_FOR = (ONLINE_LOGFILES, PRIMARY_ROLE)
DB_UNIQUE_NAME = auxdb '
*. LOG_ARCHIVE_DEST_STATE_1 = ENABLE
*. LOG_ARCHIVE_DEST_STATE_2 = ENABLE
*. Log_archive_max_processes = 1
*. Open_cursors = 300
*. Pga_aggregate_target = 16777216
*. Processes = 150
*. Remote_login_passwordfile = 'EXCLUSIVE'
*. Sga_target = 167772160
*. Undo_management = 'AUTO'
*. Undo_tablespace = 'UNDOTBS1'
*. User_dump_dest = 'D: \ oracle \ product \ 10.2.0 \ admin \ orcl \ udump'
*. Fal_service = 'aux'
*. Fal_client = 'orcl'
*. Standby_archive_dest = 'D: \ oracle \ product \ 10.2.0 \ oradata \ orcl \ standbyarchive'
*. Standby_file_management = auto
Fourth, create a data file prepared by the library file list view the master database data
SQL> select file #, name from v $ datafile;
FILE # NAME
----- --------------------------------------------- -------
1 D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ SYSTEM01.DBF
2 D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ UNDOTBS01.DBF
3 D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ SYSAUX01.DBF
4 D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ USERS01.DBF
5 D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ EXAMPLE01.DBF
6 D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ TEST01.DBF
Have chosen to line 6.
1), the master database data file copy to the path from the library
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'd: \ bak \ standby.ctl';
Then close the database file 1,2,3,4,5,6 copy to d: \ bak next.
Or use the following method:
2), using rman to copy data files to prepare the library
C: \ Documents and Settings \ Administrator.XY> rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Thursday March 15 14:42:25 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to target database: ORCL (DBID = 1140649303, not open)
RMAN> copy datafile 1 to 'd: \ bak \ system01.dbf', datafile 2 to 'd: \ bak \ undotbs01.d
bf ', datafile 3 to' d: \ bak \ sysaux01.dbf ', datafile 4 to' d: \ bak \ users01.dbf ', dataf
ile 5 to 'd: \ bak \ example01.dbf', datafile 6 to 'd: \ bak \ test01.dbf', current contr
olfile for standby to 'd: \ bak \ standby.ctl';
2>
Start backup on 15-3 -07 months
Using target database control file instead of recovery catalog allocated channel: ORA_DISK_1
Channel ORA_DISK_1: sid = 155 devtype = DISK
Channel ORA_DISK_1: start the data copy. . .
Channel ORA_DISK_1: data file is completed, elapsed time: 00:00:01
Channel ORA_DISK_1: start copying the data file backup control file copy of the output file name = D: \ BAK \ STANDBY.CTL tag = TAG20070315T144240 recid = 40 stamp =
61
7294620
Channel ORA_DISK_1: data file is completed, elapsed time: 00:00:01
-07 Months to complete backup in the 15-3
RMAN>
Open the database
SQL> ALTER DATABASE OPEN;
5, prepared in preparation to create a library database redo logs
1), maximum protection and maximum availability mode must be asked to prepare database redo log. Data transfer mode recommended LGWR SYNC.
Data Guard can be more prepared to use the library to restore redo logs, not just the use of archive log restore.
2) to ensure that the redo log from the library size and the size of the primary database redo log exactly.
3) to create the appropriate number of redo log group.
Redo log group from the library at least one more group than the main library.
Usually corresponds to an instance of a thread.
Recommended the following formula to calculate the number of redo log group.
(Maximum number of logfiles for each thread + 1) * maximum number of threads
This is the method used to reduce redo logs as prepared by library can not be assigned to lead the main library of the log reader process (LGWR) blocking.
For example: the main library there are two log groups per thread, then the library should be prepared 6 redo log group.
Add log from the library group.
SQL> alter database add standby logfile thread 1 'D: \ oracle \ product \ 10.2.0 \ oradata \ orcl \ redo04.log'
size 50m;
SQL> alter database add standby logfile thread 1 'D: \ oracle \ product \ 10.2.0 \ oradata \ orcl \ redo05.log'
size 50m;
SQL> alter database add standby logfile thread 1 'D: \ oracle \ product \ 10.2.0 \ oradata \ orcl \ redo06.log'
size 50m;
SQL> alter database add standby logfile thread 1 'D: \ oracle \ product \ 10.2.0 \ oradata \ orcl \ redo07.log'
size 50m;
4) Check whether the standby redo log to create a successful run in the main library
SQL> alter system switch logfile;
And from the database queries
SQL> SELECT GROUP #, THREAD #, SEQUENCE #, ARCHIVED, STATUS FROM
V $ STANDBY_LOG;
GROUP # THREAD # SEQUENCE # ARC STATUS
---------- ---------- ---------- --- ----------
4 1 0 NO ACTIVE
5 1 0 YES UNASSIGNED
6 1 0 YES UNASSIGNED
7 1 0 YES UNASSIGNED
6, configured listener.ora and tnsnames.ora.
listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D: \ oracle \ product \ 10.2.0 \ db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = auxdb)
(ORACLE_HOME = D: \ oracle \ product \ 10.2.0 \ db_1)
(SID_NAME = auxdb)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = D: \ oracle \ product \ 10.2.0 \ db_1)
(SID_NAME = orcl)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.199) (PORT = 1521))
)
)
tnsnames.ora
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.199) (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)
)
)
aux =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.199) (PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = auxdb)
)
)
7, modified from the database pfile, spfile and it synchronized to the
From the Library: auxdb
initauxdb.ora
*. Db_name = orcl
*. Db_unique_name = auxdb
*. DB_FILE_NAME_CONVERT = ('d: \ oracle \ product \ 10.2.0 \ oradata \ orcl', 'd: \ bak')
*. LOG_FILE_NAME_CONVERT = ('d: \ oracle \ product \ 10.2.0 \ oradata \ orcl', 'd: \ bak')
*. Control_files = 'D: \ bak \ standby.CTL'
*. REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE
*. Compatible = '10 .2.0.1.0 '
*. Db_block_size = 8192
*. Sga_target = 250000000
*. Background_dump_dest = d: \ bak \ bdump
*. Core_dump_dest = d: \ bak \ cdump
*. User_dump_dest = d: \ bak \ udump
*. Standby_archive_dest = 'd: \ bak \ standbyarchive'
*. Fal_server = 'orcl'
*. Fal_client = 'aux'
*. Standby_file_management = 'AUTO'
*. Log_archive_config = 'dg_config = (orcl, auxdb)'
*. Log_archive_dest_1 = 'location = D: \ bak \ archive VALID_FOR = (ALL_LOGFILES, ALL_ROLES)
DB_UNIQUE_NAME = auxdb '
*. LOG_ARCHIVE_DEST_2 = 'SERVICE = orcl LGWR ASYNC
VALID_FOR = (ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME = orcl '
SQL> create spfile from pfile;
File has been created.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 251658240 bytes
Fixed Size 1248380 bytes
Variable Size 83886980 bytes
Database Buffers 163577856 bytes
Redo Buffers 2945024 bytes
Completion of the loading database.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT
FROM SESSION;
Database has changed.
Verify success:
Main Library:
SQL> ALTER SYSTEM SWITCH LOGFILE;
System has been changed.
SQL>
From the Library:
SQL> SELECT SEQUENCE #, FIRST_TIME, NEXT_TIME FROM V $ ARCHIVED_LOG
ORDER BY SEQUENCE #;
SEQUENCE # FIRST_TIME NEXT_TIME
---------- -------------- --------------
115-3 -0715-3 month -07 months
...
2515-3 -0715-3 months -07 months
Has selected 17 lines.
Main Library:
SQL> ALTER SYSTEM SWITCH LOGFILE;
System has been changed.
From the Library:
SQL> SELECT SEQUENCE #, FIRST_TIME, NEXT_TIME, APPLIED FROM
V $ ARCHIVED_LOG ORDER BY SEQUENCE #
SEQUENCE # FIRST_TIME NEXT_TIME APP
---------- -------------- -------------- ---
115-3 -0715-3 month month -07 NO
. . .
2415-3 dated month -0715-3 -07 YES
2515-3 dated month -0715-3 -07 YES
2615-3 dated month -0715-3 -07 YES
Has selected 18 lines.
Thus creating a successful eight, following the switch to standby database test.
1, see the main library, there is no set standby_archive_dest / standby_file_management If not, will need to set
SQL> alter system set
standby_archive_dest = 'D: \ oracle \ product \ 10.2.0 \ oradata \ orcl \ standbyarchive' scope = both;
SQL> alter system set standby_file_management = 'auto' scope = both;
System has been changed.
SQL> show parameter standby
NAME TYPE VALUE
------------------------------------ ----------- --- ---------------------------
standby_archive_dest string D: \ oracle \ product \ 10.2.0 \ orada
ta \ orcl \ standbyarchive
standby_file_management string AUTO
2, view switch status, the normal should be TO STANDBY
SQL> SELECT SWITCHOVER_STATUS FROM V $ DATABASE;
SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE
SQL>
There needs to be some deal with both those who view the session is active
SQL> SELECT SID, PROCESS, PROGRAM FROM V $ SESSION WHERE TYPE = 'USER'
AND SID <>
(SELECT DISTINCT SID FROM V $ MYSTAT);
SID PROCESS PROGRAM
---- ---------- ---------------
148 2660:2244 plsqldev.exe
154 2660:2244 plsqldev.exe
158 2660:2244 plsqldev.exe
SQL>
1), as is the plsql connection, we can turn it off
SQL> SELECT SID, PROCESS, PROGRAM FROM V $ SESSION WHERE TYPE = 'USER'
AND SID <>
(SELECT DISTINCT SID FROM V $ MYSTAT);
Line is not selected
SQL> SELECT SWITCHOVER_STATUS FROM V $ DATABASE;
SWITCHOVER_STATUS
--------------------
TO STANDBY
SQL>
At this point you can switch up.
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
2), other methods to change job_queue_processes 0
SQL> show parameter job_queue_processes
NAME TYPE VALUE
------------------------------------ ----------- --- ---------------------------
job_queue_processes integer 10
SQL> alter system set job_queue_processes = 0;
SQL> SELECT SID, PROCESS, PROGRAM FROM V $ SESSION WHERE TYPE = 'USER'
AND SID <>
(SELECT DISTINCT SID FROM V $ MYSTAT);
SID PROCESS PROGRAM
---- ---------- ---------------
148 2660:2244 plsqldev.exe
154 2660:2244 plsqldev.exe
158 2660:2244 plsqldev.exe
SQL> SELECT SWITCHOVER_STATUS FROM V $ DATABASE;
SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE
Usually affect the process of switching solutions with the process were described
CJQ0 job queue process will JOB_QUEUE_PROCESSES dynamic to 0,
But do not change spfile
QMN0 Advanced Queue Time Manager AQ_TM_PROCESSES dynamic to 0, but
Do not change spfile
DBSNMP oem agent to stop agent emctl stop agent implementation must perform the following statement at this time to switch.
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH
SESSION SHUTDOWN;
3, the master data switch to standby database
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH
SESSION SHUTDOWN;
Database has changed.
4, close the database and to start the database mount mode
SQL> shutdown immediate
ORA-01507: database not loaded
ORACLE routines have been shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 75498852 bytes
Database Buffers 88080384 bytes
Redo Buffers 2945024 bytes
Completion of the loading database.
5, view the database state after switch
SQL> select t.PROTECTION_MODE, t.DATABASE_ROLE from v $ database t;
PROTECTION_MODE DATABASE_ROLE
-------------------- ----------------
MAXIMUM PERFORMANCE PHYSICAL STANDBY
6, will switch to the master database backup data
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
Database has changed.
SQL> ALTER DATABASE OPEN;
Database has changed.
SQL> SELECT SWITCHOVER_STATUS FROM V $ DATABASE;
SWITCHOVER_STATUS
--------------------
TO STANDBY
SQL> select t.PROTECTION_MODE, t.DATABASE_ROLE from v $ database t;
PROTECTION_MODE DATABASE_ROLE
-------------------- ----------------
MAXIMUM PERFORMANCE PRIMARY
SQL>
Thus the main switch has been successfully prepared the database.
9, from MAXIMUM PERFORMANCE to MAXIMUM PROTECTION switch.
SQL> select protection_mode from v $ database;
PROTECTION_MODE
--------------------
MAXIMUM PERFORMANCE
SQL> shutdown immediate;
SQL> startup mount
SQL> alter system set log_archive_dest_2 = 'SERVICE = aux LGWR SYNC AFFIRM
VALID_FOR = (ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME = auxdb '
scope = both;
SQL> alter database set standby database to maximize protection;
SQL> alter database open;
SQL> select protection_mode from v $ database;
PROTECTION_MODE
--------------------
MAXIMUM PROTECTION
Mode switching of the full syntax is as follows:
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE (PROTECTION |
AVAILABILITY | PERFORMANCE)
Frequently encountered in the switch test problems:
ARC0 started with pid = 15, OS
LGWR: Primary database is in MAXIMUM PROTECTION mode
LGWR: Destination LOG_ARCHIVE_DEST_2 is using asynchronous network I / O
LGWR: Destination LOG_ARCHIVE_DEST_1 is not standby database serviced by LGWR
LGWR: Minimum of 1 synchronous standby database required
Thu Mar 22 15:01:48 2007
Errors in file d: \ oracle \ product \ 10.2.0 \ admin \ orcl \ bdump \ orcl_lgwr_2392.trc:
ORA-16072: a minimum of one standby database destination is required
ORA-16086: standby database does not contain available standby log files
The error occurs because:
1, primary database of the model is the MAXIMUM PROTECTION
Can be changed to maximum performance
2, standby missing standby redo
3, LOG_ARCHIVE_DEST_2 parameter set incorrectly
*. Log_archive_dest_2 = 'service = aux OPTIONAL LGWR SYNC AFFIRM
VALID_FOR = (ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME = auxdb '
Particular attention is the SYNC or ASYNC, these two completely different, one asynchronous synchronization one.
MAXIMUM PROTECTION mode, primary, standby database to switch between
1, view the orcl database information
orcl database:
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 75498852 bytes
Database Buffers 88080384 bytes
Redo Buffers 2945024 bytes
Completion of the loading database.
Database has been opened.
SQL> select protection_mode from v $ database;
PROTECTION_MODE
--------------------
MAXIMUM PROTECTION
SQL> alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby
*
Error Line 1:
ORA-01093: ALTER DATABASE CLOSE only permitted when not connected to a session using the wrong reason: orcl database at this time there are connected session, you can turn it off.
SQL> alter database commit to switchover to physical standby;
Database has changed.
SQL> SELECT SWITCHOVER_STATUS FROM V $ DATABASE;
SELECT SWITCHOVER_STATUS FROM V $ DATABASE
*
Error Line 1:
ORA-01507: database not loaded
SQL> shutdown immediate
ORA-01507: database not loaded
ORACLE routines have been shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 79693156 bytes
Database Buffers 83886080 bytes
Redo Buffers 2945024 bytes
Completion of the loading database.
View orcl database state, has successfully switched to a standby database.
SQL> select t.PROTECTION_MODE, t.DATABASE_ROLE from v $ database t;
PROTECTION_MODE DATABASE_ROLE
-------------------- ----------------
MAXIMUM PROTECTION PHYSICAL STANDBY
SQL>
2, see auxdb library information
auxdb Library:
Ready to switch to the main database state.
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
*
Error Line 1:
ORA-16139: media recovery required
SQL> alter database recover managed standby database;
Database has changed.
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
Database has changed.
Successfully switched to the main database state.
SQL> select t.PROTECTION_MODE, t.DATABASE_ROLE from v $ database t;
PROTECTION_MODE DATABASE_ROLE
-------------------- ----------------
MAXIMUM PROTECTION PRIMARY
Open Database Error
SQL> alter database open;
alter database open
*
Error Line 1:
ORA-03113: end of file communication channel to modify the parameters log_archive_dest_2
*. Log_archive_dest_2 = 'SERVICE = orcl OPTIONAL LGWR SYNC AFFIRM
VALID_FOR = (ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME = orcl '
SQL> conn / as sysdba
Is connected to an idle instance.
SQL> conn / as sysdba
Is connected to the free routine.
SQL> create spfile from pfile;
File has been created.
SQL> startup
ORACLE instance started.
Total System Global Area 251658240 bytes
Fixed Size 1248380 bytes
Variable Size 83886980 bytes
Database Buffers 163577856 bytes
Redo Buffers 2945024 bytes
Completion of the loading database.
ORA-03113: end of file communication channel is still an error, the error is as follows:
alert_auxdb.log
Errors in file d: \ bak \ bdump \ auxdb_lgwr_2040.trc:
ORA-16086: standby database does not contain available standby log files
Thu Mar 22 16:37:37 2007
LGWR: Error 16086 verifying archivelog destination LOG_ARCHIVE_DEST_2
Thu Mar 22 16:37:37 2007
Destination LOG_ARCHIVE_DEST_2 is UNSYNCHRONIZED
LGWR: Error 16086 disconnecting from destination LOG_ARCHIVE_DEST_2 standby host
'Orcl'
LGWR: Continuing ...
LGWR: Minimum of 1 applicable standby database required
Thu Mar 22 16:37:41 2007
Errors in file d: \ bak \ bdump \ auxdb_lgwr_2040.trc:
ORA-16072: a minimum of one standby database destination is required
Because of these experiences can determine from the library without standby redo
3, in the library to create standby redo orcl
orcl database:
SQL> alter database add standby logfile 'd: \ oracle \ product \ 10.2.0 \ oradata \ orcl \ r
edo04.log 'size 50m;
Database has changed.
SQL> alter database add standby logfile 'd: \ oracle \ product \ 10.2.0 \ oradata \ orcl \ r
edo05.log 'size 50m;
Database has changed.
SQL> alter database add standby logfile 'd: \ oracle \ product \ 10.2.0 \ oradata \ orcl \ r
edo06.log 'size 50m;
Database has changed.
SQL> alter database add standby logfile 'd: \ oracle \ product \ 10.2.0 \ oradata \ orcl \ r
edo07.log 'size 50m;
Database has changed.
SQL>
4, open auxdb Library
auxdb Library:
SQL> conn / as sysdba
Is connected to the free routine.
SQL> create spfile from pfile;
File has been created.
SQL> startup
ORACLE instance started.
Total System Global Area 251658240 bytes
Fixed Size 1248380 bytes
Variable Size 83886980 bytes
Database Buffers 163577856 bytes
Redo Buffers 2945024 bytes
Completion of the loading database.
Database has been opened.
SQL> select instance_name from v $ instance;
INSTANCE_NAME
----------------
auxdb
SQL> select t.PROTECTION_MODE, t.DATABASE_ROLE from v $ database t;
PROTECTION_MODE DATABASE_ROLE
-------------------- ----------------
MAXIMUM PROTECTION PRIMARY
SQL>
At this point switch was successful.
10, Parameters:
COMPATIBLE = '10 .2.0.1.0 ': database version number, the main library and from library to be uniform, or else they might redo data can not be sent to from the main library from the library.
DB_FILE_NAME_CONVERT = master database data file address, the address data from the database: for the Lord from the library on the same machine or master data file from the library where the path of inconsistency
DB_UNIQUE_NAME =: Database unique name. Recommended, if used
LOG_ARCHIVE_CONFIG, then there must be change parameters.
FAL_CLIENT =, point of service from the library name, in this case aux
FAL_SERVER point to the main library of the service name, in this case orcl
LOG_ARCHIVE_CONFIG = 'DG_CONFIG = (main library db_unique_name, from the library's db_unique_name)'
LOG_ARCHIVE_DEST_n: log file address, at least two, a link to the main library and one point from the library
LOG_ARCHIVE_DEST_STATE_n = (ENABLE | DEFER | ALTERNATE | RESET) specify: enable or
disable to decide whether to transmit redo data to from the library.
LOG_FILE_NAME_CONVERT: with DB_FILE_NAME_CONVERT
STANDBY_ARCHIVE_DEST: specify the path to hold the received transmission came from the main library's archive log.
STANDBY_FILE_MANAGEMENT = (AUTO | MANUAL): AUTO when the main library to add or reduce the data files will automatically sync from the library without the need for manual intervention.
11, often encountered an error:
Error 1:
ORA-16057: DGID from server not in Data Guard configuration
Cause: The main library is not set parameters log_archive_config
Solution *. log_archive_config = 'dg_config = (orcl, auxdb)'
alter system set log_archive_config = 'dg_config = (orcl, auxdb)' scope = both;
Error 2:
PING [ARC0]: Heartbeat failed to connect to standby 'aux'. Error is 1031.
ORA-01031: insufficient privileges
Problem-solving ideas: 1, check the sys password is correct, most of the reason.
2, Oracle user has write permissions error standby_archive_dest 3:
ORA-16025: parameter LOG_ARCHIVE_DEST_2 contains repeated or conflicting attributes
The error occurs because LOG_ARCHIVE_DEST_2 = 'SERVICE = orcl LGWR ASYNC
VALID_FOR = (ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME = orcl '
db_unique_name before one less space due to 12, maintenance troubleshooting:
Fault 1
As the Internet and other causes not fully transferred to the archive log from the library, which we need manual intervention.
Common factors: from the library closed, network failure, from the lack of space.
To maintain the usual steps; Close: first turn off the main reservoir from the library, start: first start from the library and then start the main library.
On the log transfer control can be MANDATORY, REOPEN, MAX_FAILURE to control
MANDATORY REOPEN = 5 MAX_FAILURE = 3 every 5 seconds and try again, the maximum allowable number of errors is 3 times
If you try 3 times still not successful, then the master database log transport services will stop.
*. Log_archive_dest_2 = 'service = AUX VALID_FOR = (ONLINE_LOGFILES, PRIMARY_ROLE)
MANDATORY REOPEN = 5 MAX_FAILURE = 3 DB_UNIQUE_NAME = auxdb '
1, find the log is not in standby.
SQL> SELECT MAX (R. SEQUENCE #) LAST_SEQ_RECD, MAX (L. SEQUENCE #)
LAST_SEQ_SENT FROM
2> V $ ARCHIVED_LOG R, V $ LOG L WHERE
3> R. DEST_ID = 2 AND L. ARCHIVED = 'YES';
LAST_SEQ_RECD LAST_SEQ_SENT
------------- -------------
7 10
2, the path where to find primary
SQL> SELECT NAME FROM V $ ARCHIVED_LOG WHERE THREAD # = 1 AND DEST_ID = 1
AND
2> SEQUENCE # BETWEEN 7 AND 10;
NAME
-------------------------------------------------- ------------------------------
/ Primary/thread1_dest/arcr_1_7.arc
/ Primary/thread1_dest/arcr_1_8.arc
/ Primary/thread1_dest/arcr_1_9.arc
3, copy the log to the standby's STANDBY_ARCHIVE_DEST, will
STANDBY_ARCHIVE_DEST under the log copy to LOG_ARCHIVE_DEST
4
SQL> STARTUP MOUNT
SQL> ALTER DATABASE RECOVER AUTOMATIC STANDBY DATABASE;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT
FROM SESSION;
To this recovery was successful.
Fault 2:
Archive logs frequently arise between the gap, not understanding the difference and Fault 1
1, to confirm whether the missing archive logs between
SQL> SELECT THREAD #, LOW_SEQUENCE #, HIGH_SEQUENCE # FROM
V $ ARCHIVE_GAP;
THREAD # LOW_SEQUENCE # HIGH_SEQUENCE #
---------- ------------- --------------
19092
2, the missing archived log copy to a standby database and then to their respective registered under standby_archive_dest
ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';
3, restoration of archive logs
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;
4, then steps can be switched by switching.
  • del.icio.us
  • StumbleUpon
  • Digg
  • TwitThis
  • Mixx
  • Technorati
  • Facebook
  • NewsVine
  • Reddit
  • Google
  • LinkedIn
  • YahooMyWeb

Related Posts of Create Oracle's Data Guard and simple maintenance procedures

  • Set up a typical web RubyOnRails (1)

    EDITORIAL: First, write serial ah hope in themselves, and assist you See Crown a bit , know that many RubyOnRails development of the situation on it. Be able to assist a bit, and this is the reason insist. ^ _ ^ Encyclopedia of Life: www.eol.org RoR ...

  • Dash: from FiveRuns a new application metrics Ruby Service

    Dash , FiveRuns Provided a new measure of monitoring services, has been moved to individual beta version (you can apply), to allow interested developers to participate in assessment in a new way to monitor your application. This comes from the new se ...

  • dwr.util use

    util.js contains some useful function of function, for the client page to call, it can and DWR separate, independent business for your system. Main function is as follows: 1, $ () access to the page parameter value 2, addOptions and removeAllOptions initi

  • Ext.Ajax

    In fact, the use of Ext.Ajax not difficult, but first I have been unclear how to obtain its return value, Depressed for a while do , the following is a relatively simple example 1Ext.Ajax.request (( 2 url: 'Register.aspx', 3 params: ( 4 oper: ...

  • DWR util.js

    DWR util.js Study Notes /********************/ /********************/ util.js contains some useful function function, used to call on the client page. Main function is as follows: Code 1, $ () access to the page parameter value 2, addOptions and removeAll

  • Practical struts with ajax when the parameters of the solution to Chinese

    Problems encountered today, a look at the Internet search and found this solution, recorded to prevent the next find. Should not find the bar, I have to remember this has been, huh, huh. . . . At ajax with struts used, the parameters with the Chinese ...

  • Ask! Hibernate project to do when to do what the strategy is necessary to optimize the system performance, that is, how to improve the retrieval performance strategy?

    Ask! Hibernate project to do when to do what the strategy is necessary to optimize the system performance, that is, how to improve the retrieval performance strategy? I had not thought about it, only know about the cache settings, please show Big Bro ...

  • Hibernate's cache management

    Carried ] [http ://www.cnblogs.com/eflylab/archive/2007/01/11/617276.html Cache is the cache, it is often to improve system performance are the most important means to serve as a reservoir of data and the role of a buffer. Cache for the substantial r ...

  • javascript: history.go () and History.back () the difference between collections

    <input type=button value= Refresh > <input type=button value= Forward > <input type=button value= Back > <input type=button value= Forward > <input type=button value= Back > Back + refresh <input type=button value= Ba ...

  • Software Performance

    1.1 Software Performance In general, the performance is an indicator that the software system or component to its timeliness requirements in line with the level; followed by software performance, are an indicator can be used to measure time. Performa ...

blog comments powered by Disqus
Recent
Recent Entries
Tag Cloud
Random Entries