加入收藏 | 设为首页 | 会员中心 | 我要投稿 李大同 （https://www.lidatong.com.cn/）- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置： 首页 > 百科 > 正文

oracle 11g DG 搭建

发布时间：2020-12-12 15:58:25 所属栏目：百科 来源：网络整理
导读：groupadd oinstall groupadd dba groupadd oper groupadd asmadmin groupadd asmdba groupadd asmoper useradd -g oinstall -G dba,asmdba,asmadmin,asmoper grid useradd -g oinstall -G dba,oper,asmdba oracle passwd grid passwd oracle mkdir -p /u01/a

groupadd oinstall

groupadd dba

groupadd oper

groupadd asmadmin

groupadd asmdba

groupadd asmoper

useradd -g oinstall -G dba,asmdba,asmadmin,asmoper grid

useradd -g oinstall -G dba,oper,asmdba oracle


passwd grid

passwd oracle


mkdir -p /u01/app/11.2.0/grid

mkdir -p /u01/app/grid

mkdir -p /u01/app/oracle

chown grid:oinstall /u01/app/11.2.0/grid

chown grid:oinstall /u01/app/grid

chown -R oracle:oinstall /u01/app/oracle

chmod -R 775 /u01/

chown -R grid:oinstall /u01



sed -i 's/kernel.shmmax/#kernel.shmmax/g' /etc/sysctl.conf

sed -i 's/kernel.shmall/#kernel.shmall/g' /etc/sysctl.conf


vi /etc/sysctl.conf

添加以下内容：


fs.aio-max-nr = 1048576

fs.file-max = 6815744

kernel.shmall = 2097152

kernel.shmmax = 1073741824

kernel.shmmni = 4096

kernel.sem = 250 32000 100 128

net.ipv4.ip_local_port_range = 9000 65500

net.core.rmem_default = 262144

net.core.rmem_max = 4194304

net.core.wmem_default = 262144

net.core.wmem_max = 1048586


sysctl -p


vi /etc/security/limits.conf


添加如下内容：


oracle soft nproc 2047

oracle hard nproc 16384

oracle soft nofile 1024

oracle hard nofile 65536

oracle soft stack 10240

grid soft nproc 2047

grid hard nproc 16384

grid soft nofile 1024

grid hard nofile 65536

grid soft stack 10240


vi /etc/profile 写入如下内容：


if [ $USER = "oracle" ] || [ $USER = "grid" ]; then

if [ $SHELL = "/bin/ksh" ]; then

ulimit -p 16384

ulimit -n 65536

else

ulimit -u 16384 -n 65536

fi

umask 022

fi

export PATH=$PATH:/u01/app/11.2.0/grid/bin



配置节点rac1


grid 用户：


vi .bash_profile


export TMP=/tmp

export TMPDIR=$TMP

export ORACLE_HOSTNAME=

export ORACLE_SID=

export ORACLE_BASE=/u01/app/grid

export ORACLE_HOME=/u01/app/11.2.0/grid

export PATH=$ORACLE_HOME/bin:$PATH


oracle用户：


vi .bash_profile


export TMP=/tmp

export TMPDIR=$TMP

export ORACLE_HOSTNAME=

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1

export ORACLE_UNQNAME=

export ORACLE_SID=

export ORACLE_TERM=xterm

export PATH=/usr/sbin:$PATH

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib





1.分别安装完数据库软件

2.主库dbca建库

3.主备分别netca

4.主库执行：

select * from v$option where parameter = 'Managed Standby';

archive log list

alter database force logging;

alter database add standby logfile group 4 ('/u01/app/oracle/oradata/redo04.log') size 50m;

alter database add standby logfile group 5 ('/u01/app/oracle/oradata/redo05.log') size 50m;

alter database add standby logfile group 6 ('/u01/app/oracle/oradata/redo06.log') size 50m;

alter database add standby logfile group 7 ('/u01/app/oracle/oradata/redo07.log') size 50m;


create pfile from spfile;


show parameter name;


alter system set db_unique_name='ora11g_primary' scope=spfile;

alter system set log_archive_config='dg_config=(ora11g_primary,ora11g_standby)';

create pfile from spfile;

shutdown immediate;

cd /u01/app/oracle/product/11.2.0/db_1/dbs

查看initora11g.org db_unique_name='ora11g_primary'是否有

startup;


alter system set log_archive_dest_1='location=/u01/app/oracle/archivelog valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=ora11g_primary';

alter system set log_archive_dest_2='service=ora11g_standby LGWR SYNC valid_for=(ONLINE_LOGFILES,primary_role) db_unique_name=ora11g_standby';

alter system set log_archive_dest_state_1='enable';

alter system set log_archive_dest_state_2='enable';

alter system set REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE' scope=spfile;

alter system set FAL_SERVER='ora11g_standby' scope=spfile;

alter system set FAL_client='ora11g_primary' scope=spfile;

alter system set STANDBY_FILE_MANAGEMENT='AUTO' scope=spfile;


mkdir -p $ORACLE_BASE/archivelog


create pfile from spfile;

shutdown immediate；

验证参数是否都在

startup mount;

alter database create standby controlfile as '/u01/standby_ctl02.ctl';


cd /u01/app/oracle/product/11.2.0/db_1/network/admin

vim listener.ora

添加

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = ora11g)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

)

)


LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

)


ADR_BASE_LISTENER = /u01/app/oracle



vim tnsnames.ora

ORA11G_PRIMARY =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ora11g)

)

)

ORA11G_STANDBY =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ora11g)

)

)


关闭主数据库

SQL>shutdown immediate

生成spfile

SQL> create spfile from pfile;



5.在备库段执行：


mkdir -p $ORACLE_BASE/oradata/ora11g

mkdir -p $ORACLE_BASE/admin/ora11g

mkdir -p $ORACLE_BASE/admin/ora11g/adump

mkdir -p $ORACLE_BASE/admin/ora11g/bdump

mkdir -p $ORACLE_BASE/admin/ora11g/cdump

mkdir -p $ORACLE_BASE/admin/ora11g/dpdump

mkdir -p $ORACLE_BASE/admin/ora11g/pfile

mkdir -p $ORACLE_BASE/admin/ora11g/udump

mkdir -p $ORACLE_BASE/diag/rdbms

mkdir -p $ORACLE_BASE/diag/tnslsnr

mkdir -p $ORACLE_BASE/fast_recovery_area/ORA11G

mkdir -p $ORACLE_BASE/archivelog



下面的几个scp 在主库执行

scp -r $ORACLE_BASE/oradata/ora11g/*.dbf 192.168.0.2:/$ORACLE_BASE/oradata/ora11g

scp -r $ORACLE_BASE/oradata/*.log 192.168.0.2:/$ORACLE_BASE/oradata

scp -r /u01/standby_ctl01.ctl 192.168.0.2:/$ORACLE_BASE/oradata

scp -r $ORACLE_HOME/dbs/initora11g.ora 192.168.0.2: $ORACLE_HOME/dbs/

scp -r /u01/app/oracle/product/11.2.0/db_1/dbs/orapwora11g 192.168.0.2:/u01/app/oracle/product/11.2.0/db_1/dbs/



$ cd $ORACLE_BASE/oradata/ora11g

$ mv standby_ctl01.ctl control01.ctl

$ cp control01.ctl control02.ctl


cd /u01/app/oracle/product/11.2.0/db_1/dbs

修改initora11g.ora


DB_UNIQUE_NAME=ora11g_standby

LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ora11g_standby'

LOG_ARCHIVE_DEST_2='SERVICE=ora11g_primary LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ora11g_primary'

FAL_SERVER=ora11g_primary

FAL_CLIENT=ora11g_standby


cd /u01/app/oracle/product/11.2.0/db_1/network/admin

vim listener.ora

添加

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = ora11g)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

)

)


LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

)


ADR_BASE_LISTENER = /u01/app/oracle



vim tnsnames.ora

ORA11G_PRIMARY =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ora11g)

)

)

ORA11G_STANDBY =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ora11g)

)

)


startup nomount

alter database mount standby database;




主库执行：

startup mount

alter database set standby database to maximize availability; (设置为最高可用性模式)

alter database open;


SQL>alter database recover managed standby database disconnect from session;

＃执行此命令后，备库会切换到自动恢复模式






一、功能切换

Switchover状态切换

首先在primary上操作：

(1).验证主库是否能执行角色转换到备库（原主库执行）

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

-----------------

TO STANDBY

1 row selected

(2)开始把物理主库改变为物理备库（原主库执行）

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;

或

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY with session shutdown;

注：如果有活动的session可以使用此选项，否则转换会遇到ORA-01093错误，也可以杀掉活动会话或等活动会话后进行转换

(3).关闭并重启主库（原主库执行）

SQL> shutdown immediate

SQL> startup nomount

SQL> alter database mount standby database; #让备库处于standby

SQL> alter database recover managed standby database using current logfile disconnect from session; #开始实时同步

然后在standby上操作：

(1).验证备库是否能执行角色转换到主库（原备库执行）

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

-----------------

TO_PRIMARY

1 row selected

(2).开始把物理备库转换成物理主库（原备库执行）

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

--如果报ORA-16139: media recovery required，可能是由于未应用日志引起，可先执行

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

(3)打开备库，然后关闭重启.（原备库执行）

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP;

(4)验证是否转换成功（原备库执行）

SQL> ALTER SYSTEM SWITCH LOGFILE;

启动日志应用

(5) 应用归档日志（原主库上执行)

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

小窍门：要想顺利的实现switchover，最好在每台server上都同时设置好primary和standby的一些初始化参数，虽然其中一些参数只有在primary或者standby其中之一上起作用。




4、启动和关闭顺序

启动顺序

(1).启从、主库的监听Listener

从库DG-Standby：

$lsnrctl start

主库DG-Primary：

$lsnrctl start

(2).启动备库数据库，执行如下：

$sqlplus /nolog

SQL>conn /as sysdba

SQL> startup nomount

SQL> alter database mount standby database; #让备库处于standby

SQL> alter database recover managed standby database using current logfile disconnect from session; #开始实时同步

(3).启动主库

$sqlplus /nolog

SQL>conn /as sysdba

SQL> startup

切换standby database到read only模式

SQL> alter database recover managed standby database cancel; #首先取消备库的自动恢复模式

SQL>alter database open read only;

如果要切换回recover manage模式（启动日志应用或者启动日志实时应用）

SQL> alter database recover managed standby database disconnect from session; 启动日志应用

SQL>alter database recover managed standby database using current logfile disconnect from session; 启动日志实时应用

(3).启动主库数据库(上述第二步执行完毕后，方可执行如下命令)：

SQL>startup


关闭顺序

关闭的时候正好相反，先关闭主库，然后关闭从库。

(1). 关闭主库

$su ? oracle

SQL>sqlplus /nolog

SQL>conn /as sysdba

SQL>shutdown immediate;

(2). 关闭从库

su ? oracle

SQL>sqlplus /nolog

SQL>conn /as sysdba

SQL>alter database recover managed standby database cancel; #停止同步

SQL>shutdown immediate



应急切换

（注：模拟主库由于故障无法正常switchover，需要执行failover，强制备库->pridb并接管业务）

(1).备库：

由于是failover，所以理解主库这时候已经无法正常使用，只需备库切换至pridb

停止应用恢复模式

alter database recover managed standby database finish;

转换standbydb为primary db

alter database commit to switchover to primary;


重启数据库，恢复正常业务

SQL>shutdown immediate

SQL>startup

select open_mode,database_role from v$database;


OPEN_MODE DATABASE_ROLE

---------- ----------------

OPEN PRIMARY

注:failover将破坏dataguard模式,需要重新配置dataguard，网上也说不需要配置，但是还没有测试成功



select dbid,name,protection_mode,protection_level from v$database;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
    相关内容
      推荐文章
        站长推荐
        热点阅读

          【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

          建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的最佳浏览效果

          Copygight © 2008-2022 https://www.lidatong.com.cn/ All Rights Reserved. 李大同