建立物理standby可以用rman,也可以用冷备份,使用冷备份可以参照http://www.itpub.net/showthread.php?s=&threadid=666617

我的这个实验是用rman来建立standby

我主要是参考下面个文档进行的

http://hi.baidu.com/space6212/blog/item/249d5b230cfa4a4c9358071e.html

http://blog.tom.com/blog/read.php?bloggerid=133149&blogid=61256

一一定要主库和备库都安装好后再启动listener

并且tnsname中的service看初始化参数的server_names

我就写一下,我在安装physical standby的一些步骤

环境:

Sid_name_ cncimps

Cnc17: primary

Cnc18: standby

1. 当然是要让数据库处在force logging状态

Alter database force logging;

2. 当然要让数据库处在archive状态

Startup mount

Alter database archivelog

3. 在主库创建standby redo log在主库上建立standby log，大小与主库联机日志大小一样，组数至少大1（这个是可选，是为了角色切换方便）

alter database add standby logfile group 4 '/opt/oracle/oradata/cncimps/standbyredo04.log' size 50m;

alter database add standby logfile group 5 '/opt/oracle/oradata/cncimps/standbyredo05.log' size 50m;

alter database add standby logfile group 6 '/opt/oracle/oradata/cncimps/standbyredo06.log' size 50m;

alter database add standby logfile group 7 '/opt/oracle/oradata/cncimps/standbyredo07.log' size 50m;

这些日志查询v$log是看不到状态的,需要查询v$logfile才能发现

orapwd file=orapwcncimps password=oracle entries=10

4. 初始化参数:

cncimps.__db_cache_size=771751936

cncimps.__java_pool_size=16777216

cncimps.__large_pool_size=16777216

cncimps.__shared_pool_size=251658240

cncimps.__streams_pool_size=0

*.audit_file_dest='/opt/oracle/admin/cncimps/adump'

*.background_dump_dest='/opt/oracle/admin/cncimps/bdump'

*.COMPATIBLE='10.2.0.1'

*.control_files='/opt/oracle/oradata/cncimps/control01.ctl','/opt/oracle/oradata/cncimps/control02.ctl','/opt/oracle/oradata/cncimps/control03.ctl'

*.core_dump_dest='/opt/oracle/admin/cncimps/cdump'

*.db_block_size=8192

*.db_file_multiblock_read_count=8

*.db_recovery_file_dest_size=21474836480

*.dispatchers='(protocol=TCP)','(ADDRESS=(PROTOCOL=TCP)(DISPATCHERS=2))'

*.job_queue_processes=10*.open_cursors=600

*.pga_aggregate_target=254803968

*.processes=500

*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'

*.sessions=555

*.sga_max_size=1610612736

*.sga_target=1073741824

*.shared_servers=6

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/opt/oracle/admin/cncimps/udump'

*.local_listener='PRIMARY'

*.db_domain=''

*.FAL_CLIENT='primary'

*.FAL_SERVER='standby'

*.DB_UNIQUE_NAME='primary'

*.SERVICE_NAMES=primary ##指定了这个service_names,那么在tns里指定的server_name就只能是它,否则连接不上,当然也可以不指定,缺省是db_name+db_domain

*.db_name='cncimps'

*.STANDBY_FILE_MANAGEMENT='AUTO'

*.db_recovery_file_dest='/opt/oracle/flash_recovery_area'

*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'

##primary和standby是指主备里面指定的db_unique_name,如果有多台可以指定多个

*.LOG_ARCHIVE_DEST_1='LOCATION=/opt/oracle/archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary'

*.LOG_ARCHIVE_DEST_2='SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'

##注意这里的service是tnsnames.ora里面定义的能到达standby的tns配置

*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'

*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'

*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'

*.LOG_ARCHIVE_MAX_PROCESSES=4

*.log_file_name_convert='/opt/oracle/oradata/cncimps/','/opt/oracle/oradata/cncimps/'

这些粉红色的部分都是做standby特别需要注意的地方

5. rman备份数据库

$ORACLE_HOME/bin/rman target /

backup database format='/home/oracle/backup/%U_%s.bak';

sql "Alter System Archive Log Current";

Backup filesperset 10 ArchiveLog all format='/home/oracle/backup/arc%U_%s.bak';

在做rman备份之前最好删除过期的备份

crosscheck backupset

delete obsolete

delete expired backup;

change archivelog all crosscheck ;

delete archivelog all;

delete expired archivelog all;

6. 获取standby的控制文件

用sys用户进入数据库

alter database create standby controlfile as '/home/oracle/backup/standby.ctl';

7. 将控制文件复制到需要的位置

[oracle@cnc18 backup]$ cp standby.ctl /opt/oracle/oradata/cncimps/control01.ctl

[oracle@cnc18 backup]$ cp standby.ctl /opt/oracle/oradata/cncimps/control02.ctl

[oracle@cnc18 backup]$ cp standby.ctl /opt/oracle/oradata/cncimps/control03.ctl

8. 修改standby的init文件

*.DB_UNIQUE_NAME='standby' ##在从的指定从的unique name

*.SERVICE_NAMES=standby

*.FAL_CLIENT='standby'

*.FAL_SERVER='primary' ##这2个参数和主库的相反

*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'

*.LOG_ARCHIVE_DEST_1='LOCATION=/opt/oracle/archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'

*.LOG_ARCHIVE_DEST_2='SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'

*.STANDBY_FILE_MANAGEMENT =AUTO

*.log_file_name_convert='/opt/oracle/oradata/cncimps/','/opt/oracle/oradata/cncimps/'

9. 建立备库密码文件

orapwd file=orapwcncimps password=oracle entries=10

注意 : 这一步这个password一定要和主库的password一致,否则容易出现ora-12154的问题,如果主库的记不住,主库也可以重建,方法一样的

10. 然后创建相应的文件夹

mkdir -p /u01/oracle/admin/primary/{adump,bdump,cdump,udump}

11. 恢复数据库到备库里

启动备库到nomount状态

Sqlplus / as sysdba

Startup nomount

Alter database mount standby database

Rman恢复数据库

restore datatase;

restore archivelog all;

recover database; ##这步需要做,否则在alter database open的时候不能打开数据库,说system.dbf有问题

(

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-16004: backup database requires recovery

ORA-01152: file 1 was not restored from a sufficiently old backup

ORA-01110: data file 1: '/opt/oracle/oradata/cncimps/system01.dbf'

)

12. 分别在主备库里面修改tnsnames.ora

cat $ORACLE_HOME/network/admin/tnsnames.ora

primary =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 61.135.171.17)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = cncimps)

)

)

standby =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 61.135.171.19)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = standby)

)

)

13. 启动备库

startup nomount;

alter database mount standby database;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

也可以按照下面的方式来打开备库来进行数据查询,查询后按上面的方式启动备库,就又可以恢复了

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

alter database open;

14. 检查物理standby情况
1）在备库检查当前的archivelog
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

15. 然后可以在主库里插入数据来测试

16. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> ALTER DATABASE OPEN;