创建Local Physical Standby  Oracle9i standby 数据库笔记

说明:已配置一个远程的Standby,现在再配置一个Local Standby

规划：
1 数据文件，日志文件转换路径：/opt/oracle/oradata/mydb --> /opt/oracle/oradata/standby

2 关闭Primary Instance 拷贝数据文件到既定目的地,不拷贝日志文件
shutdown immediate
cp /opt/oracle/oradata/mydb/*.dbf  /opt/oracle/oradata/standby/

3 为Standby 数据库创建控制文件
SQL> Startup
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/opt/oracle/oradata/standby/control01.ctl';

NOTE：先停库，将data files复制到standby，然后启动主库，创建standby的control files。(有什么不一样?)

4 编辑　Standby参数文件　
Primary: create pfile='/home/oracle/pfilestandby.ora' from spfile;

再更改，添加一些参数．
#备库instance_name=standby　与主库mydb可以不一样,但db_name必须一样  注意几个参数

*.control_files='/opt/oracle/oradata/standby/control01.ctl','/opt/oracle/oradata/standby/control02.ctl','/opt/oracle/oradata/standby/control03.ctl'
*.db_name='mydb'
*.instance_name='standby'
lock_name_space=standby
standby_file_management=AUTO
remote_archive_enable=TRUE
standby_archive_dest='/opt/oracle/standbyarchive'
db_file_name_convert=('/opt/oracle/oradata/mydb', '/opt/oracle/oradata/standby')
log_file_name_convert=('/opt/oracle/oradata/standby', '/opt/oracle/oradata/standby')
log_archive_dest_1=('LOCATION=/opt/oracle/standbyarchive')

5 生成密码文件，启动Standby

密码文件　
cp $ORACLE_HOME/dbs/orapwmydb  $ORACLE_HOME/dbs/orapwstandby

export ORACLE_SID=standby

sqlplus /nolog
SQL> connect / as sysdba
SQL> STARTUP NOMOUNT pfile='/home/oracle/pfilestandby.ora';
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

这里最好创建Standby Redo Log
alter database add standby logfile group 4 ('/opt/oracle/oradata/standby/redo04.log') size 10M;
alter database add standby logfile group 5 ('/opt/oracle/oradata/standby/redo05.log') size 10M;
alter database add standby logfile group 6 ('/opt/oracle/oradata/standby/redo06.log') size 10M;
alter database add standby logfile group 7 ('/opt/oracle/oradata/standby/redo07.log') size 10M;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

6 在Primary上激活到物理Standby数据库的归档
localstandby =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.230.17.34)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = standby)
    )
  )
 
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='SERVICE=localstandby LGWR' SCOPE=BOTH;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE SCOPE=BOTH;

说明用LOG_ARCHIVE_DEST_3的原因是LOG_ARCHIVE_DEST_2已被用来配置了一个Remote Physical Standby Database.

7 测试　

启动远程归档
Primary SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

在Primary 查询V$ARCHIVED_LOG,验证是否生成归档
(其实也可以直接到相关目录下查看Log是否创建):

SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG;

查询从Primary接收到的所有归档(Standby 端)
SQL> SELECT REGISTRAR, CREATOR, THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE# FROM V$ARCHIVED_LOG;

验证是否新的归档Redo日志已经被应用
SQL> SELECT  sequence#, applied FROM v$archived_log ORDER BY sequence#;

OK.表明我们还是成功的。暂时告一段落。