資料庫環境oracle 11.1.0.6
作業系統環境 linux redhat update 4 32位
ORACLE_BASE為/u01/app/oracle
ORACLE_HOME為/u01/app/oracle/product/11.1.6/db_1
主庫資料檔案存放目錄/oradata/asm11g/下
備庫資料檔案存放目錄/oradata/standby/下

1.配置主資料庫為歸檔模式和強制使用日誌模式
SQL>startup force mount;
SQL>alter database archivelog;
SQL>alter database open;
SQL> ALTER DATABASE FORCE LOGGING;

2.設定主庫spfile引數如下:
DB_NAME=asm11g
DB_UNIQUE_NAME=asm11g
LOG_ARCHIVE_CONFIG='DG_CONFIG=(asm11g,standby)'
CONTROL_FILES='/oradata/asm11g/controlfile/control1.ctl'
LOG_ARCHIVE_DEST_1=
'LOCATION=/u01/app/oracle/archivelog
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=asm11g'
LOG_ARCHIVE_DEST_2=
'SERVICE=standby ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=standby'
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

3.新建備庫存放目錄
cd $ORACLE_BASE
CD admin
mkdir standby
cd standby
mkdir adump
mkdir dpdump
mkdir pfile
cd /oradata
mdkir standby

4.備份主庫資料檔案用rman工具
rman target /
backup database format '/oradata/%U.bak';

5.在主庫生成備庫控制檔案
SQL>startup force mount;
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/oradata/standby/control01.ctl';
SQL> ALTER DATABASE OPEN;

6.從主庫建立引數檔案用於備庫
SQL> CREATE PFILE='/oradata/standby/initstandby.ora' FROM SPFILE;

修改備庫引數檔案如下 vi initstandby.ora:
DB_NAME=asm11g
DB_UNIQUE_NAME=standby
LOG_ARCHIVE_CONFIG='DG_CONFIG=(asm11g,standby)'
CONTROL_FILES='/oradata/standby/control1.ctl'
DB_FILE_NAME_CONVERT='asm11g','standby'
LOG_FILE_NAME_CONVERT='asm11g','standby'
LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
LOG_ARCHIVE_DEST_1=
'LOCATION=/oradata/standby/archivelog.
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=standby'
LOG_ARCHIVE_DEST_2=
'SERVICE=asm11g ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=asm11g'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=asm11g
FAL_CLIENT=standby

7.備庫建立密碼檔案注意要和主庫密碼一致
cd $ORACLE_HOME/dbs
orapwd file=orapwstandby password=abcdefg entries=10 ignorecase=y

8.配置主庫和備庫的listener 採用靜態註冊如下所示:
LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = asm11g.localdomain)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
)
SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/11.1.6/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_NAME =asm11g)
      (ORACLE_HOME = /u01/app/oracle/product/11.1.6/db_1)
      (SID_NAME =asm11g)
    )
    (SID_DESC =
      (GLOBAL_NAME =standby)
      (ORACLE_HOME = /u01/app/oracle/product/11.1.6/db_1)
      (SID_NAME =standby)
    )
)
#lsnrctl stop
#lsnrctl start

9.配置tnsnames.ora檔案
[oracle@asm11g admin]$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.1.6/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ASM11G =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = asm11g.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = asm11g)
    )
)
standby =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = asm11g.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME =standby)
    )
)
tnsping 驗證
[oracle@asm11g admin]$ tnsping asm11g

TNS Ping Utility for Linux: Version 11.1.0.6.0 - Production on 25-JUL-2008 13:35:21

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = asm11g.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = asm11g)))
OK (20 msec)
[oracle@asm11g admin]$ tnsping standby

TNS Ping Utility for Linux: Version 11.1.0.6.0 - Production on 25-JUL-2008 13:35:24

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = asm11g.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =standby)))
OK (10 msec)
[oracle@asm11g admin]$

10.啟動standby 資料庫
export ORACLE_SID=standby
sqlplus / as sysdba
create spfile from pfile='/oradata/standby/initstandby.ora';
startup nomount;
alter database mount standby database;
host
恢復主庫的資料檔案到備庫
rman target /
restore database;
exit;
新增standby logfile 啟動到恢復管理模式
idle> alter database add standby logfile '/oradata/standby/redo04.log' size 50M;

Database altered.

Elapsed: 00:00:01.06
idle> alter database add standby logfile '/oradata/standby/redo05.log' size 50M;

Database altered.

Elapsed: 00:00:01.18
idle> alter database add standby logfile '/oradata/standby/redo06.log' size 50M;

Database altered.

Elapsed: 00:00:00.85

idle> recover managed standby database disconnect from session;
Media recovery complete.
..

11.驗證歸檔是否正常傳送
主庫:
alter system switch logfile;
備庫:是否傳送過來
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
2> FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
是否已經apply
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG
2 ORDER BY SEQUENCE#;

檢查主庫連到備庫的引數檔案是否正常
select * from v$archive_dest_status;

在配置完成後發現歸檔日誌不能傳送到備庫
select * from v$archive_dest_status;
發現log_archive_dest_2的status為error
error報ORA-16191: Primary log shipping client not logged on standby
檢查資料庫日誌也發現下面錯誤

Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191
------------------------------------------------------------
Error 16191 connecting to destination LOG_ARCHIVE_DEST_2 standby host 'standby'
Error 16191 attaching to destination LOG_ARCHIVE_DEST_2 standby host 'standby'
ORA-16191: Primary log shipping client not logged on standby

解決方法:重建主庫和備庫的密碼檔案使密碼一致
加上ignorecase引數為y

最後重新啟動下主庫
再查
select * from v$archive_dest_status;
log_archive_dest_2的status為valid

搭建11g 單機 linux standby 操作文件

相關文章