2013年12月13日 星期五

Oracle DB - Data Guard, Create Physical Standby Database

1. DB Migration Procedure v4.doc

2. DailyWorkLog.txt

3. 05_DBA_Project.xlsx

4. C:\Oracle Database 11g Release 2 Documentation\index.htm

5. C:\Oracle Database 10g Release 2 Documentation\index.htm

6. Internet Document
http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/ha/dataguard/physstby/physstdby.htm?cid=4024&ssid=106308955970538

http://shivanandarao-oracle.com/2012/03/10/creating-physical-standby-database-on-oracle-11g/

http://learnwithme11g.wordpress.com/2011/12/07/creating-a-physical-standby-database-11gr2-3/


5. Download files from rue1rdb



‧Preparing the Primary Database for Standby Database Creation
--Enable Forced Logging
SQL> ALTER DATABASE FORCE LOGGING;
SQL> SELECT force_logging FROM v$database;

--Create a Password File
Depends...

--Configure a Standby Redo Log
A standby redo log is required for the maximum protection and maximum availability modes and the LGWR ASYNC transport mode is recommended for all databases.
The size of the current standby redo log files must exactly match the size of the current primary database online redo log files. For example, if the primary database uses two online redo log groups whose log files are 200K, then the standby redo log groups should also have log file sizes of 200K.
Minimally, the configuration should have one more standby redo log file group than the number of online redo log file groups on the primary database.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 01 '/oradata/ERP/srl01.log' SIZE 200M;
or
SQL> ALTER DATABASE ADD STANDBY LOGFILE SIZE 200M;

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

--Set Primary Database Initialization Parameters
Example Primary Database: Primary Role Initialization Parameters
DB_NAME=taipei
DB_UNIQUE_NAME=taipei
LOG_ARCHIVE_CONFIG='DG_CONFIG=(taipei,shuzou)'
CONTROL_FILES='/arch1/taipei/control1.ctl', '/arch2/taipei/control2.ctl'
LOG_ARCHIVE_DEST_1=
 'LOCATION=/arch1/taipei/
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=taipei'
LOG_ARCHIVE_DEST_2=
 'SERVICE=shuzou LGWR ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=shuzou'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30

Example Primary Database: Standby Role Initialization Parameters
FAL_SERVER=shuzou
FAL_CLIENT=taipei
DB_FILE_NAME_CONVERT='shuzou','taipei'
LOG_FILE_NAME_CONVERT=
 '/arch1/shuzou/','/arch1/taipei/','/arch2/shuzou/','/arch2/taipei/'
STANDBY_FILE_MANAGEMENT=AUTO


--Enable Archiving
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

‧Step-by-Step Instructions for Creating a Physical Standby Database
--Create a Backup Copy of the Primary Database Datafiles
Backup database using RMAN.
Backup database using cold backup.

--Create a Control File for the Standby Database
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'constdby01.ctl';

--Prepare an Initialization Parameter File for the Standby Database
SQL> CREATE PFILE='/tmp/initERP.ora' FROM SPFILE;
SQL> SHUTDOWN IMMIEDATE

修改如下
ERP.__db_cache_size=5838471168
ERP.__java_pool_size=16777216
ERP.__large_pool_size=16777216
ERP.__oracle_base='/u01/oracle'#ORACLE_BASE set from environment
ERP.__pga_aggregate_target=1828716544
ERP.__sga_target=6442450944
ERP.__shared_io_pool_size=0
ERP.__shared_pool_size=520093696
ERP.__streams_pool_size=0
*.archive_lag_target=0
*.audit_file_dest='/u01/oracle/admin/ERP/adump'
*.audit_trail='NONE'
*.compatible='11.2.0.0.0'
*.control_files='/oradata/ERP/constdby01.ctl','/oradata/ERP/constdby02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='ERP'
*.db_recovery_file_dest='/oradata/arch'
*.db_recovery_file_dest_size=53687091200
*.db_unique_name='ERP_S_US'
*.dg_broker_start=TRUE
*.diagnostic_dest='/u01/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ERPXDB)'
*.fal_client='ERP_S_US'
*.fal_server='erp_p'
*.job_queue_processes=1000
*.log_archive_config='dg_config=(ERP_P,ERP_S,ERP_S_US)'
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST','valid_for=(ALL_LOGFILES, ALL_ROLES)'
*.log_archive_dest_2=''
*.log_archive_dest_3=''
*.log_archive_dest_4=''
*.log_archive_dest_state_2='ENABLE'
*.log_archive_dest_state_3='ENABLE'
*.log_archive_dest_state_4='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
ERP.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=4
*.log_archive_min_succeed_dest=1
ERP.log_archive_trace=0
*.open_cursors=300
*.pga_aggregate_target=1824522240
*.processes=600
*.remote_login_passwordfile='EXCLUSIVE'
*.sec_case_sensitive_logon=FALSE
*.sessions=665
*.sga_max_size=8589934592
*.sga_target=6442450944
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

--Copy Files from the Primary System to the Standby System

1. Datafiles and logifles
select * from v$datafile;
select * from v$logfile;

2. Standby control file
Copy the $ORACLE_HOME/dbs/constdby01.ctl to Standby site /oradata/ERP then duplicate a constdby02.ctl from constdby01.ctl

3. Initialization parameter file

4. Copy the remote login password file from the primary database system to the standby database system
$ORACLE_HOME/dbs/orapwERP file

--Set Up the Environment to Support the Standby Database
1. Set Standby site tnsnames.ora
2. Add ERP_S_US tnsnames.ora information to ERP_P and ERP_S site

% lsnrctl stop
% lsnrctl start

SQL> CREATE SPFILE FROM PFILE='/u01/oracle/product/11.2.0/dbs/initERP.ora';


--Start the Physical Standby Database
SQL>startup nomount;
SQL>alter database mount standby database;
接下來參考Excel file

or <參考原廠手冊>
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
(SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;)


To start Redo Apply in the foreground, issue the following SQL statement:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

To start Redo Apply in the background, include the DISCONNECT keyword on the SQL statement. For example:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
This statement starts a detached server process and immediately returns control to the user.

To start real-time apply, include the USING CURRENT LOGFILE clause on the SQL statement. For example:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;


--Verify the Physical Standby Database Is Performing Properly
Test archival operations to the physical standby database.
In this example, the transmission of redo data to the remote standby location does not occur until after a log switch. A log switch occurs, by default, when an online redo log file becomes full. To force a log switch so that redo data is transmitted immediately, use the following ALTER SYSTEM statement on the primary database. For example:
SQL> ALTER SYSTEM SWITCH LOGFILE;

On the standby database, query the V$ARCHIVED_LOG view to identify existing files in the archived redo log. For example:
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;


On the standby database, query the V$ARCHIVED_LOG view to verify the archived redo log files were applied.
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Primary:
SQL> select max(sequence#) from v$archived_log;

Standby:
SQL> select max(sequence#) from v$archived_log where applied='YES';

To check if the archive logs are successfully applied on standby, use the below query :
SQL> select max(al.sequence#) "Last Seq Received", max(lh.sequence#) "Last Seq Applied" from v$archived_log al, v$log_history lh;



‧Post-Creation Steps

--Configure DGMGRL
$ dgmgrl
DGMGRL> connect sys
DGMGRL> add database 'ERP_S_US' as connect identifier is ERP_S_US;
DGMGRL> show configuration;
DGMGRL> show database verbose 'ERP_S_US';
DGMGRL> ENABLE DATABASE 'ERP_S_US';

--Config Online and Standby redo log files
Handling ORL and SRL (Resize) on Primary and Physical Standby in Dataguard Environment (Doc ID 1532566.1)




沒有留言:

張貼留言