分类： Oracle
2017-04-19 21:51:18
Oracle 11G集群系统dataguard安装指导
第一步：primary database的创建。
1、使用OUI安装数据库软件
2、使用DBCA创建数据库（注意查看数据库文件是否为设计的位置）,全局数据库名：db 实例名：orcl
4、以oracle身份登录系统，连接主库，启动到 mount状态，
5、进入oracle账号，把数据库改为归档和force logging模式
（节点1操作，节点2数据库需关闭，等节点1调整完成，再开启节点2的库。）
#su – oracle
SQL> Sqlplus / as sysdba
SQL> create pfile='/u01/app/oracle/initorcl20160616.ora' from spfile;
－－备份参数文件，指定目录备份，如果直接生成的话，会覆盖现有pfile，
现有pfile，内容只是指向ＡＭＳ里的pfile
SQL> Shutdown immediate;
SQL> Startup mount;
SQL> Alter database archivelog;
SQL> alter database open;
SQL> alter database force logging;--归档模式下修改
SQL>alter system set log_file_name_convert='/u01/app/oracle/oradata/orcl','+DATA/orcl/onlinelog' scope=spfile;
SQL>alter system set db_file_name_convert='/u01/app/oracle/oradata/orcl','+DATA/orcl/datafile' scope=spfile;
SQL>alter system set db_recovery_file_dest='+DATA' scope=spfile;
SQL>alter system set db_recovery_file_dest_size=600G scope=spfile;
SQL> Shutdown immediate;
SQL> startup
SQL> show parameter db_unique_name 默认是orcl
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string orcl
SQL> alter system set log_archive_config='dg_config=(orcl,sdb)';
SQL> alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST' ;
归档文件的生成路径，location代表本地机上，service指在另一台机器上
SQL> alter system set log_archive_dest_2='service=sdb lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=sdb';
对方的网络服务名
SQL> show parameter log_archive_dest_state_1
SQL> show parameter log_archive_dest_state_2 --确认这两个参数为enable的，激活定义的,可以先修改为defer 归档日志目录，允许redo 传输服务传输redo数据到指定的路径
SQL> show parameter log_archive_format
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_format string %t_%s_%r.dbf --格式默认就好
SQL> alter system set fal_server=sdb;
SQL> alter system set fal_client=orcl; --注意这里反着写的，表示切换后的主和备
SQL> alter system set standby_file_management=auto;
如果primary 数据库数据文件发生修改（如新建，重命名等）则按照本参数的设置在standby 中做相应修改。设为AUTO 表示自动管理。设为MANUAL表示需要手工管理
--至此，参数修改完毕,然后由修改后的spfile创建一个pfile文件，以便拷到备库修改使用。节点2的库到这可以开启了。查询两边的状态，是否归档都已打开。
SQL> create pfile='/u01/app/oracle/initorcl**.ora' from spfile;
2、rman备份(在某一个节点操作)
mkdir /u01/arch_back
[~]$Rman target sys/oracle
RMAN> backup format '/u01/arch_back/con_%U' database include current controlfile for standby;
3、 装oracle软件(路径保持一致）（备库操作）
mkdir /u01/archivelog -p
mkdir /u01/arch_back -p
mkdir /u01/app/oracle/product/11.2.0/db_1/dbs -p
mkdir /u01/app/oracle/oradata/orcl/ -p
mkdir /u01/app/oracle/admin/orcl/{a,b,u,dp,c}dump -p
mkdir /u01/app/oracle/diag/rdbms/orcl/orcl/ -p
mkdir /u01/app/oracle/fast_recovery_area/orcl/ -p
--创建这些目录，注意使用oracle用户去创建
[oracle@ljstopri ~]$scp /u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora :/u01/app/oracle/product/11.2.0/db_1/dbs/ -------主库操作
--拷参数文件，节点1和节点2统一使用asm里的spfile,只需拷贝一份。
[ ~]$ scp /u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl oracle@192.168.1.1:/u01/app/oracle/product/11.2.0/db_1/dbs/ -------主库操作
--拷密码文件 ，拷贝了节点1的密码文件。之后需要更改文件名。节点2 不需要拷贝。[ ~]$ scp /u01/arch_back/* oracle@192.168.1.1:/u01/arch_back/ -------主库操作
--拷控制文件和全备的rman备份
--注意，scp拷的时候请用oracle用户，否则用root用户拷过来，要修改权限
chown -R oracle:oinstall /u01 之后在修改备库密码
4、监听的配置
节点1
|
SDB = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.223)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) ) ORCL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = rac-cluster-scan)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) ) |
节点2
|
SDB = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.223)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) ) ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac-cluster-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) ) |
节点1和节点2各自添加了一个SDB。
备库
|
ORCL2 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.60)(PORT = 1521)) ) (CONNECT_DATA = (SID = orcl2) ) ) SDB = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.223)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) ) ORCL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.50)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) ) |
备库ORCL服务名，连接的SERVICE_NAME需要填写数据库名，而不是节点。
测试时
然后两边互相tnsping
tnsping orcl，orcl２，sdb
--上面都互相能tnsping通，最好在备库上远程连接一下主库，要求成功，才说明监听没问题
[ ~]$ sqlplus sys/as sysdba
2、参数文件
主库
|
orcl2.__db_cache_size=31004295168 orcl1.__db_cache_size=31138512896 orcl2.__java_pool_size=939524096 orcl1.__java_pool_size=939524096 orcl2.__large_pool_size=536870912 orcl1.__large_pool_size=536870912 orcl1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment orcl2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment orcl2.__pga_aggregate_target=9663676416 orcl1.__pga_aggregate_target=9663676416 orcl2.__sga_target=38654705664 orcl1.__sga_target=38654705664 orcl2.__shared_io_pool_size=0 orcl1.__shared_io_pool_size=0 orcl2.__shared_pool_size=5905580032 orcl1.__shared_pool_size=5771362304 orcl2.__streams_pool_size=0 orcl1.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/orcl/adump' *.audit_trail='db' orcl1.audit_trail='NONE' orcl2.audit_trail='NONE' *.cluster_database=true *.compatible='11.2.0.4.0' *.control_files='+DATA/orcl/controlfile/current.273.911996713' *.db_block_size=8192 *.db_create_file_dest='+DATA' *.db_domain='' *.db_file_name_convert='/u01/app/oracle/oradata/orcl','+DATA/orcl/datafile' *.db_name='orcl' *.db_recovery_file_dest='+DATA' *.db_recovery_file_dest_size=644245094400 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' *.fal_client='ORCL' *.fal_server='SDB' orcl2.instance_number=2 orcl1.instance_number=1 *.job_queue_processes=1000 *.log_archive_config='dg_config=(orcl,sdb)' *.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST' *.log_archive_dest_2='service=sdblgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=sdb' *.log_file_name_convert='/u01/app/oracle/oradata/orcl','+DATA/orcl/onlinelog' orcl1.memory_target=0 *.open_cursors=300 orcl1.pga_aggregate_target=9663676416 orcl2.pga_aggregate_target=9663676416 *.processes=1000 *.remote_listener='rac-cluster-scan:1521' *.remote_login_passwordfile='exclusive' *.resource_manager_plan='' *.sessions=1105 orcl1.sga_max_size=38654705664 orcl2.sga_max_size=38654705664 orcl1.sga_target=38654705664 orcl2.sga_target=38654705664 *.standby_file_management='AUTO' orcl2.thread=2 orcl1.thread=1 orcl2.undo_tablespace='UNDOTBS2' orcl1.undo_tablespace='UNDOTBS1' |
备库
|
orcl.__db_cache_size=7281311744 orcl.__java_pool_size=67108864 orcl.__large_pool_size=100663296 orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment orcl.__pga_aggregate_target=2952790016 orcl.__sga_target=8824815616 orcl.__shared_io_pool_size=0 orcl.__shared_pool_size=1308622848 orcl.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/orcl/adump' *.audit_trail='NONE' *.compatible='11.2.0.4.0' *.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'---此路径需要改为本库存放路径 *.db_block_size=8192 *.db_domain='' *.db_file_name_convert='+DATA/orcl/datafile','/u01/app/oracle/oradata/orcl' ---db_file_name_convert在备库需要把主备目录顺序对换一下，主在前，备在后 *.db_name='orcl' *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' db_recovery_file_dest改为本机路径 *.db_recovery_file_dest_size=4385144832 *.db_unique_name='sdb' 需要改成sdb *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' *.fal_client='sdb' *.fal_server='orcl' 进行更换 *.log_archive_config='dg_config=(sdb,orcl)' 顺序进行更换 *.log_archive_dest_1='location=/u01/archivelogvalid_for=(all_logfiles,all_roles) db_unique_name=sdb' -----Location在备库在此处修改成本地目录，db_unique_name也需要更改 *.log_archive_dest_2='service=orcllgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=orcl'需要修改 *.log_file_name_convert='+DATA/orcl/onlinelog','/u01/app/oracle/oradata/orcl' -------log_file_name_convert在备库需要把主备目录顺序对换一下，主在前，备在后 *.nls_language='SIMPLIFIED CHINESE' *.nls_territory='CHINA' *.open_cursors=300 *.pga_aggregate_target=1073741824------注意备库内存 *.processes=1000 *.remote_login_passwordfile='EXCLUSIVE' *.sessions=1105 *.sga_target=3221225472------注意备库内存 *.standby_file_management='AUTO' *.undo_tablespace='UNDOTBS1' |
5、恢复备库，使之和主库同步
[ ~]$ sqlplus / as sysdba
Connected to an idle instance.
SQL> startup nomountpfile='/u01/...../initorcl.ora'--启动到mount状态
SQL>create spfile from pfile;--备库是空闲实例状态时也可以创建spfile
SQL> shutdown
immediate;
SQL> startup nomount--启动到mount状态
--在备库上进行操作(恢复的时候，连接的某一个节点进行操作)
[ ~]$ rman target sys/oraclet@orcl auxiliary /
connected to target database: ORCL (DBID=1271897179)
connected to auxiliary database (not mounted)
--可以从连接信息看到是同时连到主和备（注意此时主库为open状态，备库为nomount状态）
RMAN> duplicate target database for standby nofilenamecheck;
--把拷过来的备份（全备和控制文件备份）进行恢复
[ ~]$ sqlplus / as sysdba--恢复完成后，备库自动会由nomount状态到mount状态；现在退出rman，连接sqlplus
Select status from v$instance; 查看状态
创建日志组
SQL>alter database add standby logfile thread 1 group 9 size 500m;
SQL>alter database add standby logfile thread 1 group 10 size 500m;
SQL>alter database add standby logfile thread 1 group 11 size 500m;
SQL>alter database add standby logfile thread 2 group 12 size 500m;
SQL>alter database add standby logfile thread 2 group 13 size 500m;
SQL>alter database add standby logfile thread 2 group 14 size 500m;
SQL>alter database recover managed standby database disconnect from session;
--打开日志传递应用功能
SQL>alter database recover managed standby database cancel;关闭日志传递
DATAGUARD的日常检查工作
1.确定主库，并查看当前的日志号
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PRIMARY
2：确定备用数据库，并查看当前应用的日志序号
SQL> select sequence#,applied,thread#,deleted from v$archived_log;
3.在备库上面确认相关的进程已经启动
SQL> select process,status,sequence# from v$managed_standby;
