建立物理備庫

本文主要記錄了建立物理備庫的完整過程,以便實踐中參考.耐於機器配置,此實驗實現的是在本機上建立兩個資料庫TEST和STD_ASSG,TEST是主庫，STD_ASSG
是備庫，最後實現主庫發生日誌切換後，全部應用於物理備庫

一建立物理備庫需要的目錄
[oracle@dbsv admin]$ cd std_assg/
[oracle@dbsv std_assg]$ ls
[oracle@dbsv std_assg]$ mkdir arch
[oracle@dbsv std_assg]$ mkdir adump
[oracle@dbsv std_assg]$ mkdir bdump
[oracle@dbsv std_assg]$ mkdir cdump
[oracle@dbsv std_assg]$ mkdir udump
[oracle@dbsv std_assg]$ mkdir flash
[oracle@dbsv std_assg]$ cd $ORACLE_BASE/oradata
[oracle@dbsv oradata]$ mkdir STD_ASSG
二建立sys使用者需要的密碼檔案
[oracle@dbsv dbs]$ ls orapw*
orapwdBAKDB.pwd orapwdorcl orapworcl orapwstdby orapwTEST
[oracle@dbsv dbs]$ cp orapwTEST orapwSTD_ASSG
三建立備庫的日誌檔案
1 啟動已有主庫
[oracle@dbsv ~]$ export ORACLE_SID=TEST
[oracle@dbsv ~]$ sqlplus / as sysdba
2檢視備庫日誌情況
SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
GROUP#    THREAD# SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
         4          0          0 YES UNASSIGNED
         5          0          0 YES UNASSIGNED
         6          0          0 YES UNASSIGNED
3 建立備庫日誌
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 '/u01/app/oracle/oradata/STD_ASSG/std.redo' SIZE 50M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 8 '/u01/app/oracle/oradata/STD_ASSG/std2.redo' SIZE 50M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 9 '/u01/app/oracle/oradata/STD_ASSG/std3.redo' SIZE 50M;

Database altered.

四修改初始化引數，配置物理備庫需要的資訊
vi initTEST.ora
# for primary role
DB_NAME=TEST
DB_UNIQUE_NAME=TEST
LOG_ARCHIVE_CONFIG='DG_CONFIG=(TEST,STD_ASSG)'
#CONTROL_FILES='/u01/app/oracle/oradata/TEST/control1.ctl', '/u01/app/oracle/oradata/TEST/control2.ctl', '/u01/app/oracle/oradata/TEST/control3.ctl'
LOG_ARCHIVE_DEST_1=
'LOCATION=/u01/app/oracle/admin/TEST/arch/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=TEST'
LOG_ARCHIVE_DEST_2=
'SERVICE=STD_ASSG LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=STD_ASSG'
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

# for standby role
FAL_SERVER=STD_ASSG
FAL_CLIENT=TEST
DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/STD_ASSG/','/u01/app/oracle/oradata/TEST/'
LOG_FILE_NAME_CONVERT=
'/u01/app/oracle/oradata/STD_ASSG/','/u01/app/oracle/oradata/TEST/'
STANDBY_FILE_MANAGEMENT=AUTO

複製並修改主庫引數檔案，改為備庫可用引數檔案
1,$ s/TEST/STD_ASSG/g
# for primary role
DB_NAME=TEST
DB_UNIQUE_NAME=STD_ASSG
LOG_ARCHIVE_CONFIG='DG_CONFIG=(STD_ASSG,TEST)'
#CONTROL_FILES='/u01/app/oracle/oradata/STD_ASSG/control1.ctl', '/u01/app/oracle/oradata/STD_ASSG/control2.ctl', '/u01/app/oracle/oradata/STD_ASSG/control3.ctl'
LOG_ARCHIVE_DEST_1=
'LOCATION=/u01/app/oracle/admin/STD_ASSG/arch/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=STD_ASSG'
LOG_ARCHIVE_DEST_2=
'SERVICE=TEST LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=TEST'
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

# for standby role
FAL_SERVER=TEST
FAL_CLIENT=STD_ASSG
DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/TEST/','/u01/app/oracle/oradata/STD_ASSG/'
LOG_FILE_NAME_CONVERT=
'/u01/app/oracle/oradata/TEST/','/u01/app/oracle/oradata/STD_ASSG/'
STANDBY_FILE_MANAGEMENT=AUTO

五建立備庫的控制檔案
SQL> startup mount
ORACLE instance started.

Total System Global Area 452984832 bytes
Fixed Size                  1262308 bytes
Variable Size             230690076 bytes
Database Buffers          213909504 bytes
Redo Buffers                7122944 bytes
Database mounted.
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/u01/app/oracle/oradata/STD_ASSG/STDASSG.ctl';

Database altered.

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/u01/app/oracle/oradata/STD_ASSG/STDASSG2.ctl';

Database altered.

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/u01/app/oracle/oradata/STD_ASSG/STDASSG3.ctl';

Database altered.

六複製資料檔案到備庫相應目錄
[oracle@dbsv TEST]$ cp *.dbf ../STD_ASSG/
七配置網路
STD_RSSG =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = tcp)(HOST = dbsv)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = STD_ASSG)
    )
)
八配置備庫初始化引數檔案
[oracle@dbsv admin]$ export ORACLE_SID=STD_ASSG
[oracle@dbsv admin]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Sun Apr 27 18:26:55 2014

Connected to an idle instance.
SQL> create spfile from pfile='initSTD_ASSG.ora'
2 ;

File created.
啟動到控制檔案狀態
SQL> startup mount
ORACLE instance started.

Total System Global Area 452984832 bytes
Fixed Size                  1262308 bytes
Variable Size             138415388 bytes
Database Buffers          306184192 bytes
Redo Buffers                7122944 bytes
Database mounted.
九啟動日誌應用
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

十測試物理備庫是否可以使用
1 在主庫上
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIME          NEXT_TIME
---------- ------------------- -------------------
       …
47 2014-04-27:18:52:51 2014-04-27:18:56:08
        47 2014-04-27:18:52:51 2014-04-27:18:56:08
        48 2014-04-27:18:56:08 2014-04-27:20:38:43
        48 2014-04-27:18:56:08 2014-04-27:20:38:43
        49 2014-04-27:20:38:43 2014-04-27:20:39:36

SEQUENCE# FIRST_TIME          NEXT_TIME
---------- ------------------- -------------------
        49 2014-04-27:20:38:43 2014-04-27:20:39:36
        50 2014-04-27:20:39:36 2014-04-27:20:40:48
        50 2014-04-27:20:39:36 2014-04-27:20:40:48
        51 2014-04-27:20:40:48 2014-04-27:20:46:28
        51 2014-04-27:20:40:48 2014-04-27:20:46:28

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

2 在備庫上
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG
    ORDER BY SEQUENCE#;
2
SEQUENCE# APP
---------- ---
        44 YES
        45 YES
        46 YES
        47 YES
        48 YES
        49 YES
        50 YES
        51 YES

8 rows selected.
證明備庫日誌在備庫已被應用

dataguard-建立物理備庫全程解析

相關文章