oracle_datagrard 之Create筆記1

已有的環境準備
機器一：IP: 10.0.10.85 已有普通的資料庫gsweb，準備做為主庫
機器二：IP: 10.0.10.55 準備新增一個備庫[@more@]

已有的環境準備
機器一：IP: 10.0.10.85 已有普通的資料庫gsweb，準備做為主庫
機器二：IP: 10.0.10.55 準備新增一個備庫
/** 預處理：將機器二上的原來的一個資料庫的資料檔案目錄和ADMIN目錄改名為gsweb2 保留了機器二的口令檔案。
否則需要從主庫機器上覆制一個口令檔案 */
--3.1.1 Enable Forced Logging 在主庫上啟用force logging模式
--Place the primary database in FORCE LOGGING mode ALTER DATABASE FORCE LOGGING;
--3.1.2 Enable Archiving and Define a Local Archiving Destination
--Ensure that the primary database is in ARCHIVELOG mode, that automatic archiving is enabled, and that you have defined a local archiving destination.
--Set the local archive destination using the following SQL statement:
-- 在主庫上檢查歸檔模式 SELECT DBID, NAME, LOG_MODE, FORCE_LOGGING FROM V$DATABASE;
ALTER DATABASE FORCE LOGGING;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 'LOCATION=d:oracleora92oradataarchive';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1 = ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_START = TRUE SCOPE = SPFILE;
SHUTDOWN IMMEDIATE STARTUP MOUNT ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN; SELECT DBID, NAME, LOG_MODE, FORCE_LOGGING FROM V$DATABASE;

-- 3.2.1 Identify the Primary Database Datafiles
-- 在主庫上確定資料檔案及日誌檔案位置 文件上說只要資料檔案
sql> SELECT NAME FROM V$DATAFILE UNION ALL SELECT NAME FROM V$TEMPFILE UNION ALL SELECT MEMBER FROM V$LOGFILE;

-- 3.2.2 Make a Copy of the Primary Database
-- 將主庫上的資料檔案複製到備庫上
Step 1 Shut down the primary database.
sql> SHUTDOWN IMMEDIATE;
Step 2 Copy the datafiles to a temporary location.
-- 我是透過10.0.10.85d$oracleora92oradatagsweb2進行複製
-- unix 下使用 cp /disk1/oracle/oradata/payroll/system01.dbf /disk1/oracle/oradata/payroll/standby/system01.dbf
Step 3 Restart the primary database.
-- 啟動資料庫後建立從庫所需的PFILE和CONTROLFILE：
sql> STARTUP;

-- 3.2.3 Create a Control File for the Standby Database
-- 在主庫上建立控制檔案給備庫使用
sql> alter database create standby controlfile as 'd:gsweb2.ctl';

-- 3.2.4 Prepare the Initialization Parameter File to be Copied to the Standby Database
-- 在主庫上建立初始化引數檔案給備庫使用
sql> create pfile='d:initgsweb2.ora' from spfile;

-- 3.2.5 Copy Files from the Primary System to the Standby System
--Backup datafiles created in Section 3.2.2
--Standby control file created in Section 3.2.3
--Initialization parameter file created in Section 3.2.4
-- 將上面的控制檔案、初始化檔案 和密碼檔案拷到備庫的指定位置：
-- 因為備庫上原來有口令檔案，所以我沒有拷，(在建立spfile startup nomount之前使用這個口令檔案)

-- 3.2.6 Set Initialization Parameters on a Physical Standby Database
-- 在備庫上修改初始化引數檔案
-- 新增以下內容
...
standby_archive_dest='d:oracleoradataarchive' -- 這裡指備庫上的archive目錄 db_file_name_convert=('d:oracleoradatagsweb','d:oracleoradatagsweb2') --前面上主庫的目錄，後面是備庫的目錄 log_file_name_convert=('d:oracleoradatagsweb','d:oracleoradatagsweb2') --同上 standby_file_management=auto remote_archive_enable=true instance_name=gsweb2 -- 此處要注意修改例項名
...

-- 修改以下內容 備庫有gsweb2目錄
*.background_dump_dest='D:oracleadmingsweb2bdump'
*.control_files='D:oracleoradatagsweb2GSWEB2.CTL'
*.core_dump_dest='D:oracleadmingsweb2cdump'
*.log_archive_dest_1='LOCATION=d:oracleoradataarchive' -- 這裡指備庫上的archive目錄
*.user_dump_dest='D:oracleadmingsweb2udump' -- 文件上備註說如果在同一臺機器上安裝，則要增加以下引數
# The following parameter is required only if the primary and standby databases # are located on the same system.
lock_name_space=gsweb2

-- 3.2.7 Create a Windows Service 在備庫上增加一個服務
WINNT> oradim -NEW -SID gsweb2 -STARTMODE manual

-- 3.2.8 Configure Listeners for the Primary and Standby Databases
-- 在兩個庫上配置Listeners 在主庫上修改以下內容
-- 主庫的listener.ora 將127.0.0.1改為10.0.10.85
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.10.85)(PORT = 1521)) ) (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)) ) )
-- 主庫的tnsnames.ora 增加
GSWEB1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.10.85)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = gsweb) ) ) GSWEB2 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.10.55)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = gsweb) ) )
-- 從庫上的listener.ora
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)) ) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.10.55)(PORT = 1521)) ) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = gsweb) (ORACLE_HOME = D:oracleora92) (SID_NAME = gsweb2) ) )
-- 從庫上的tnsnames.ora 與主庫一致 非必要，但好看
GSWEB1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.10.85)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = gsweb) ) ) GSWEB2 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.10.55)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = gsweb) ) )
-- 兩臺機器上重啟lsnrctl
% lsnrctl stop
% lsnrctl start

--3.2.9 Enable Dead Connection Detection on the Standby System
--在備庫上啟用expire_time 引數
--Enable dead connection detection by setting the SQLNET.EXPIRE_TIME parameter to 2 in the SQLNET.ORA parameter file on the standby system. For example: SQLNET.EXPIRE_TIME=2

--3.2.10 Create Oracle Net Service Names 在上面已經做過，修改tnsnames.ora
--3.2.11 Create a Server Parameter File for the Standby Database
--On an idle standby database, use the SQL CREATE statement to create a server parameter file for the standby database from the text initialization parameter file that was edited in Section 3.2.6. For example:
-- 在備庫上登入後，建立spfile 如果不能登入 需要口令檔案
c:> set ORACLE_SID=gsweb2
c:> sqlplus /nolog
SQL> conn / as sysdba
SQL> CREATE SPFILE FROM PFILE='initpayroll2.ora';

--3.2.12 Start the Physical Standby Database
SQL> startup nomount
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

--3.2.13 Initiate Log Apply Services --On the standby database, start log apply services as shown in the following example:
-- 在備庫上啟用日誌應用服務
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION

-- 3.2.14 Enable Archiving to the Physical Standby Database
-- 在主庫上啟用“歸檔到備庫”
SQL> alter system set log_archive_dest_2='SERVICE=gsweb2' scope=both;
SQL> alter system set log_archive_dest_state_2=enable scope=both;

--Step 4 Start remote archiving.
SQL> alter system archive log current;

-- 3.3 Verifying the Physical Standby Database
-- 檢驗日誌能否同步
-- Step 1 Identify the existing archived redo logs.
-- 在備庫上查詢當前的日誌號
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME 2 FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIME NEXT_TIME
---------- ------------------ ------------------
10 11-JUL-02 17:50:58 11-JUL-02 17:51:03
-- Step 2 Archiving the current log.
-- 在主庫上切換歸檔日誌
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
-- Step 3 Verify that the new archived redo log was received.
-- 在備庫上檢查日誌號是否增加
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
2> FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIME NEXT_TIME
---------- ------------------ ------------------
10 11-JUL-02 17:50:58 11-JUL-02 17:51:03
11 11-JUL-02 17:51:03 11-JUL-02 18:34:11
The logs are now available for log apply services to apply redo data to the standby database.
Step 4 Verify that the new archived redo log was applied. On the standby database, query the V$ARCHIVED_LOG view to verify the archived redo log was applied.
-- 在備庫檢驗日誌是否已應用
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG 2 ORDER BY SEQUENCE#;
SEQUENCE# APP
--------- ---
10 YES 11 YES
-- 已應用 結束
------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------