–1、主库检查服务器托管网
检查修改主库的force logging 模式
sqlplus / as sysdba
SQL>select FORCE_LOGGING from v$database;
alter database force logging;
–主库remote_login_passwordfile 为EXCLUSIVE
show parameter remote_login_passwordfile
—2、主库参数
alter system set DB_UNIQUE_NAME=orcl scope=spfile;
alter system set log_archive_config=’DG_CONFIG=(orcl,adg)’ scope=both;
alter system set LOG_ARCHIVE_DEST_1=’LOCATION=+data/ORCL/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl’ scope=both;
alter system set LOG_ARCHIVE_DEST_2=’SERVICE=adgdg LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=adg’;
alter system set standby_file_management=’AUTO’ sid=’*’ scope=both;
alter system set fal_client=’orcldg’;
alter system set fal_server=’adgdg’;
alter system set db_file_name_convert=’+DATA’,’+DATA’ SCOPE=SPFILE;
ALTER SYSTEM SET log_file_name_convert=’+DATA’,’+DATA’ SCOPE=SPFILE;
–3、新增tnsname.ora
–主库和备库 1节点
orcldg =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.23)(PORT = 1521)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
adgdg =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.133)(PORT = 1521)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ADG)
)
)
–主库和备库 2节点
orcldg =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.12)(PORT = 1521)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
adgdg =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.134)(PORT = 1521)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = adg)
)
)
–4、备份主库 和 control file
rman target /
backup as compressed backupset database format ‘/tmp/bak/racdb%U’;
sqlplus / as sysdba
ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘/home/oracle/stb_ctl’;
–5、创建pfile
sqlplus / as sysdba
create pfile=’/home/oracle/pfile.ora’ from spfile;
more pfile
orcl1.__db_cache_size=855638016
orcl2.__db_cache_size=855638016
orcl1.__java_pool_size=16777216
orcl2.__java_pool_size=16777216
orcl1.__large_pool_size=33554432
orcl2.__large_pool_size=33554432
orcl2.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
orcl1.__pga_aggregate_target=872415232
orcl2.__pga_aggregate_target=872415232
orcl1.__sga_target=1275068416
orcl2.__sga_target=1275068416
orcl1.__shared_io_pool_size=0
orcl2.__shared_io_pool_size=0
orcl1.__shared_pool_size=352321536
orcl2.__shared_pool_size=352321536
orcl1.__streams_pool_size=0
orcl2.__streams_pool_size=0
*.aq_tm_processes=5
*.audit_file_dest=’/u01/app/oracle/admin/orcl/adump’
*.audit_trail=’db’
*.cluster_database=true
*.compatible=’11.2.0.4.0′
*.control_files=’+DATA/orcl/controlfile/current.261.995444255′,’+DATA/orcl/controlfile/current.260.995444255′
*.db_block_size=8192
*.db_create_file_dest=’+DATA’
*.db_domain=”
*.db_file_name_convert=’+DATA’,’+DATA’
*.db_name=’orcl’
*.db_recovery_file_dest=’+DATA’
*.db_recovery_file_dest_size=1073741824
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)’
*.enable_goldengate_replication=TRUE
*.fal_client=’orcldg’
*.fal_server=’adgdg’
orcl1.instance_number=1
orcl2.instance_number=2
*.log_archive_config=’DG_CONFIG=(orcl,adg)’
*.log_archive_dest_1=’LOCATION=+data/ORCL/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl’
*.log_archive_dest_2=’SERVICE=adg LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=adg’
*.log_archive_format=’%t_%s_%r.dbf’
*.log_file_name_convert=’+DATA’,’+DATA’
*.memory_target=2147483648
*.open_cursors=300
*.processes=150
*.remote_listener=’rac-scan:1521′
*.remote_login_passwordfile=’exclusive’
*.standby_file_management=’AUTO’
orcl2.thread=2
orcl1.thread=1
orcl2.undo_tablespace=’UNDOTBS2′
orcl1.undo_tablespace=’UNDOTBS1′
–6、拷贝到备库修改pfile
按照db_unique_name 不同修改，但要保持 db_name 相同，需要指定control file的位置
scp pfile.ora oracle@standby:/home/oracle/
more pfile.ora
adg1.__db_cache_size=855638016
adg2.__db_cache_size=855638016
adg1.__java_pool_size=16777216
adg2.__java_pool_size=16777216
adg1.__large_pool_size=33554432
adg2.__large_pool_size=33554432
adg2.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
adg1.__pga_aggregate_target=872415232
adg2.__pga_aggregate_target=872415232
adg1.__sga_target=1275068416
adg2.__sga_target=1275068416
adg1.__shared_io_pool_size=0
adg2.__shared_io_pool_size=0
adg1.__shared_pool_size=352321536
adg2.__shared_pool_size=352321536
adg1.__streams_pool_size=0
adg2.__streams_pool_size=0
*.aq_tm_processes=5
*.audit_file_dest=’/u01/app/oracle/admin/adg/adump’
*.audit_trail=’db’
*.cluster_database=true
*.compatible=’11.2.0.4.0′
*.control_files=’+DATA/adg/controlfile/control01.ctl’,’+DATA/adg/controlfile/control02.ctl’
*.db_block_size=8192
*.db_create_file_dest=’+DATA’
*.db_domain=”
*.db_file_name_convert=’+DATA’,’+DATA’
*.db_name=’orcl’
*.db_unique_name=’adg’
*.db_recovery_file_dest=’+DATA’
*.db_recovery_file_dest_size=1073741824
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=adgXDB)’
*.enable_goldengate_replication=TRUE
*.fal_client=’adgdg’
*.fal_server=’adgdg’
adg1.instance_number=1
adg2.instance_number=2
*.log_archive_config=’DG_CONFIG=(adg,adg)’
*.log_archive_dest_1=’LOCATION=+data/ORCL/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=adg’
*.log_archive_dest_2=’SERVICE=adg LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=adg’
*.log_archive_format=’%t_%s_%r.dbf’
*.log_file_name_convert=’+DATA’,’+DATA’
*.memory_target=2147483648
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=’exclusive’
*.standby_file_management=’AUTO’
adg2.thread=2
adg1.thread=1
adg2.undo_tablespace=’UNDOTBS2′
adg1.undo_tablespace=’UNDOTBS1′
–7、备库创建目前并以pfile起动
mkdir -p /u01/app/oracle/admin/adg/adump
export ORACLE_SID=adg1
sqlplus / as sysdba
startup nomount pfile=’/home/oracle/pfile.ora’;
这里可能会报内存空间不足，可能是由于shm不足引起，需要重新挂载shm
[root@rac2 ~]# more /etc/fstab
LABEL=/ / ext3 defaults 1 1
LABEL=/boot /boot ext3 defaults 1 2
tmpfs /dev/shm tmpfs defaults,size=1024M 0 0
devpts /dev/pts devpts gid=5,mode=620 0 0
sysfs /sys sysfs defaults 0 0
proc /proc proc defaults 0 0
LABEL=SWAP-sda2 swap swap defaults 0 0
增大到了3G，然后重新mount即可生效。
mount -o remount /dev/shm
–8、拉起备库，并调整参数
sqlplus / as sysdba
startup nomount
create spfile=’+data’ from pfile=’/home/oracle/pfile.ora’;
vi $ORACLE_HOME/dbs/initadg1.ora
spfile=’+DATA/adg/parameterfile/spfile.258.1004832971′
vi $ORACLE_HOME/dbs/initadg2.ora
spfile=’+DATA/adg/parameterfile/spfile.258.1004832971′
shutdown immediate;
startup mount
alter system set DB_UNIQUE_NAME=adg scope=spfile;
alter system set log_archive_config=’DG_CONFIG=(orcl,adg)’ scope=both;
alter system set LOG_ARCHIVE_DEST_1=’LOCATION=+data VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=adg’ scope=both;
alter system set LOG_ARCHIVE_DEST_2=’SERVICE=orcldg LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl’;
alter system set standby_file_management=’AUTO’ sid=’*’ scope=both;
alter system set fal_client=’adgdg’;
alter system set fal_server=’orcldg’;
alter system set db_file_name_convert=’+DATA’,’+DATA’ SCOPE=SPFILE;
ALTER SYSTEM SET log_file_name_convert=’+DATA’,’+DATA’ SCOPE=SPFILE;
–9、在备库还原control file和数据库
ASMCMD> cp /home/grid/stb_ctl control01.ctl
ASMCMD> cp /home/grid/stb_ctl control02.ctl
rman target /
catlog start with ‘/home/oracle/bak’;
restore database;
catlog start with ‘+data/adg/datafile’;
switch database to copy;
–10、在备库先添加standby redo，然后再在主库添加
alter database add standby logfile thread 1 group 51 ‘+data’ size 50m;
alter database add standby logfile thread 1 group 61 ‘+data’ size 50m;
alter database add standby log服务器托管网file thread 2 group 71 ‘+data’ size 50m;
alter database add standby logfile thread 2 group 81 ‘+data’ size 50m;
–11、起动验证
sqlplus / as sysdba
alter database recover managed standby database using current logfile disconnect from session；
–开启read only模式
alter database recover managed standby database cancel;
alter database open read only;
alter database recover managed standby database using current logfile disconnect from session ;
–12、拉起2节点
注意环境变量，tnsnames.ora、监听状态
sqlplus / as sysdba
startup
–13、注册数据库到CRS
srvctl add database -d adg -n orcl -o $ORACLE_HOME
srvctl add instance -d adg -i adg1 -n adg1
srvctl add instance -d adg -i adg2 -n adg2
–14、检查
–备库
col scn for 9999999999999999999
select current_scn from v$database;
–主库
select current_scn as scn from dual;
select group#,thread#, SEQUENCE#,ARCHIVED from v$log;
select PROCESS,THREAD#,SEQUENCE# from v$managed_standby;
服务器托管，北京服务器托管，服务器租用 http://www.fwqtg.net
相关推荐: oracle 数据库建集群式数据库的DBLINK的语法
根据需要修改以下红色字体的部分即可。 1、连接集群式数据库DBLINK语法 create public database link 自定义的dblink名字 connect to 连接对方数据库的用户名 identified by “密码” using ‘(D…
