靓靓小知识-Oracle的主备DG搭建

Oracle的主备DG搭建

1. 修改主机名,同步时间

主库IP：192.168.100.137
备库IP：192.168.100.138

配置主机名(主库)
Hostname zygjpdb
vim /etc/hosts
192.168.100.137 zygjpdb
192.168.100.138 zygjsdb

vim /etc/sysconfig/network
HOSTNAME=zygjpdb
------------------------------------------------------------------------------
配置主机名(备库）
Hostname zygjsdb
vim /etc/hosts
192.168.100.137 zygjpdb
192.168.100.138 zygjsdb

vim /etc/sysconfig/network
HOSTNAME=zygjsdb

同步时间，确保两台服务器的时间是一致的，这里我选用的ntpdate

有外网的情况，可使用ntp.aliyun.com
*/10 * * * * ntpdate ntp.aliyun.com

没外网的情况，需要自行搭建ntp服务器
*/10 * * * * ntpdate 192.168.100.130

2. 修改主库的配置

主库操作

su - oracle

sqlplus / as sysdba

SQL> create pfile from spfile;
SQL> shutdown immediate;
SQL> startup mount;
SQL> Alter database archivelog;
SQL> alter database force logging;--归档模式下修改
SQL> alter database open;
SQL> alter system set db_unique_name="pdb" scope=spfile; --修改完后，要重启数据库
SQL> Shutdown immediate;
SQL> startup
SQL> show parameter db_unique_name;--（检查是否已修改）
NAME                TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name      string      pdb

SQL> alter system set log_archive_config='dg_config=(pdb,sdb)';
SQL> ho mkdir /u01/archivelog; --创建归档目录
SQL> alter system set log_archive_dest_1='location=/u01/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=pdb';--归档文件的生成路径，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=pdb; --注意这里反着写的，表示切换后的主和备
SQL> alter system set standby_file_management=auto;

SQL> create pfile from spfile;

3.备库操作,安装软件,新建目录

装oracle软件(路径保持一致）,不需要装库

--创建这些目录，注意使用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

4.主库操作,拷贝配置文件到备库，等会在备库修改

scp /u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora oracle@192.168.100.138:/u01/app/oracle/product/11.2.0/db_1/dbs/ -------主库操作
--拷参数文件(配置文件)
scp /u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl oracle@192.168.100.138:/u01/app/oracle/product/11.2.0/db_1/dbs/ -------主库操作
--拷密码文件

--如果ssh端口有更改的，需要加scp -P port

5.备库操作,使密码文件生效

orapwd file=/u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl password=oracle force=y
--在备库重新密码文件，或者从主库scp过来也是一样
--注意，scp拷的时候请用oracle用户，否则用root用户拷过来，要修改权限

6.主库的监听配置文件

[oracle@zygjpdb~]$ cat /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = orcl)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = zygjpdb)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@zygjpdb~]$ cat /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = zygjpdb)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

PDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.137)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb)
    )
  )

SDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.138)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = sdb)
    )
  )

7.备库的监听配置文件

[oracle@zygjsdb~]$ cat /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = sdb)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = orcl)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = zygjsdb)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@zygjsdb ~]$ cat /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = zygjsdb)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

PDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.137)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb)
    )
  )

SDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.138)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = sdb)
    )
  )

8.主备库操作

两边都需要同样的操作，确保没有任何报错

lsnrctl reload
tnsping orcl
tnsping pdb
tnsping sdb
sqlplus sys/oracle@pdb as sysdba
sqlplus sys/oracle@sdb as sysdba

9.更改备库的启动文件

initorcl.ora 此文件是主库的配置文件，上一步scp过来的，下面的仅供参考，请根据实际情况修改

orcl.__db_cache_size=14831058944
orcl.__java_pool_size=469762048
orcl.__large_pool_size=536870912
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=6979321856
orcl.__sga_target=18186502144
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=2214592512
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'#Restore Controlfile
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=20737418240
*.db_unique_name='sdb'  --需修改
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.fal_client='SDB'  --需修改
*.fal_server='PDB'  --需修改
*.java_jit_enabled=TRUE
*.log_archive_config='dg_config=(sdb,pdb)'   ----需修改
*.log_archive_dest_1='location=/u01/arch/ valid_for=(all_logfiles,all_roles) db_unique_name=sdb'  --需修改
*.log_archive_dest_2='service=pdb lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=pdb'  --需修改
*.open_cursors=300
*.pga_aggregate_target=6949672960 --注意备库SGA和PGA的大小（根据机器配置） 
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=1655
*.sga_max_size=18161927680
*.sga_target=18161927680  --注意备库SGA和PGA的大小（根据机器配置） 
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

10.恢复备库，使之与主库同步

备库操作

[oracle@zygjsdb~]$ sqlplus / as sysdba
SQL> startup nomount pfile=/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora;
SQL> create spfile from pfile;
SQL> shutdown immediate;
SQL> startup nomount;

[oracle@zygjsdb~]$ rman target sys/oracle@pdb auxiliary sys/oracle@sdb
--开始恢复,最关键命令
RMAN>run { allocate channel c1 type disk;allocate channel c2 type disk;allocate channel c3 type disk;allocate AUXILIARY channel c4 type disk;allocate AUXILIARY channel c5 type disk;allocate AUXILIARY channel c6 type disk;DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK;release channel c1;release channel c2;release channel c3;release channel c4;release channel c5;release channel c6;}

11.恢复后，打开归档日志传递应用功能

11.1 恢复完成后，备库自动会由nomount状态到mount状态；现在退出rman，连接sqlplus查看状态

select status from v$instance;

select open_mode from v$database;

11.2 备库创建日志组

SQL> alter database add standby logfile group 10 '/u01/app/oracle/oradata/orcl/stredo10.log' size 500m;
SQL> alter database add standby logfile group 11 '/u01/app/oracle/oradata/orcl/stredo11.log' size 500m;
SQL> alter database add standby logfile group 12 '/u01/app/oracle/oradata/orcl/stredo12.log' size 500m;
SQL> alter database add standby logfile group 13 '/u01/app/oracle/oradata/orcl/stredo13.log' size 500m;

11.3 备库打开日志传递应用功能

--备库开启日志应用(非实时)，推荐使用
SQL> alter database recover managed standby database disconnect from session;

--备库开启实时日志应用
alter database recover managed standby database using current logfile disconnect from session;

--取消应用
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;    

11.4 至此，dg搭建完成，现在可以进行验证

--尝试在主库切换日志文件，查看备库日志
--主库操作
alter system switch logfile;

--备库日志
/u01/app/oracle/diag/rdbms/sdb/orcl/trace/alert_orcl.log

12.补充知识

12.1 pfile和spfile

create spfile from pfile；
--将编辑好的pfile文件转化为数据库启动默认加载的文件
create pfile from spfile; 
-- 将二进制文件转化为可编辑的pfile文件

initorcl.ora      就是pfile文件
spfileorcl.ora    就是spfile文件

12.2 备库打开read only；

SQL> startup nomount;
SQL>alter database mount standby database;
SQL>alter database open read only;
SQL>alter database recover managed standby database disconnect from session;