查看primary目录

SQL> set linesize 200

SQL> col name for a20

SQL> col value for a60

select name ,value from v$parameter where name in

('audit_file_dest','background_dump_dest','control_files','core_dump_dest','user_dump_dest') ORDER BY name ASC;

NAME VALUE

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

audit_file_dest /u01/app/oracle/admin/orcl/adump

background_dump_dest /u01/app/oracle/diag/rdbms/orcl/orcl/trace

control_files /u01/app/oradata/orcl/control01.ctl, /u01/app/oradata/orcl/control02.ctl

core_dump_dest /u01/app/oracle/diag/rdbms/orcl/orcl/cdump

user_dump_dest /u01/app/oracle/diag/rdbms/orcl/orcl/trace

备库创建目录

$ mkdir -p /u01/app/oracle/admin/orcl/adump

$ mkdir -p /u01/app/oradata/orcl/

primary

SQL> show parameter remote_login_passwordfile;

SQL> alter system set remote_login_passwordfile=EXCLUSIVE scope=spfile;

1.密码文件要从主库生成

cd $ORACLE_HOME/dbs

$orapwd file='$ORACLE_HOME/dbs/orapw$ORACLE_SID' password=123456 force=y

或

alter user sys identified by oracle;

$ scp orapworcl oracle@orastandby:/u01/app/oracle/product/11.2.0/db_1/dbs

$mv orapworcl orapwstandby

2.primary listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME =orcl)

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

(SID_NAME = orcl)

)

LISTENER =

(DESCRIPTION =

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

)

ADR_BASE_LISTENER = /u01/app/oracle

standby listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME =standby)

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

(SID_NAME = orcl)

)

LISTENER =

(DESCRIPTION =

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

)

ADR_BASE_LISTENER = /u01/app/oracle

primary tnsnames.ora

standby=

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = standby)

)

standby tnsnames.ora

orcl=

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

primary to standby test connect

$rman target sys/123456@orcl auxiliary sys/123456@standby

primary

为以后方便主、备切换，需要在主库添加standby redo log。主库添加后，复制时会复制到备库。

添加standby logfile，如果原来有三组联机日志，那么添加4组standby日志。

select group#,type,member from v$logfile;

select group#,members,bytes/1024/1024,status from v$log;

--设置备库日志文件组，数量一般为目标库日志文件组+1

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

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

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

SQL> alter database add standby logfile group 7 size 50m;

SQL> alter database add logfile group 8 '+data,+fra' size 50m;

SELECT GROUP#, BYTES/1024/1024 M FROM V$STANDBY_LOG;

--开启强制日志模式

alter database force logging;

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,standby)' scope=both sid='*'; 日志传输的库，或者说dg环境有几个库

alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl' scope=both sid='*';

use_db_recovery_file_dest的值是db_recovery_file_dest,有gi的时候需要设置成+FRA

alter system set LOG_ARCHIVE_DEST_2='SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby' scope=both sid='*';

注意service是服务名和db_unique_name不同

alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE ;

alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE ;

alter system set fal_server='standby'; 当出现间隙，获取丢失的归档日志

FAL_SERVER: Specify an Oracle Net Service Name (TNS-Alias or Connect Descriptor) that points to the Database from where the missing ArchiveLog(s) should be requested. This can either be the Primary Database, but also another Standby-, ArchiveLog Repository- or Far Sync Standby (> Oracle 12.1.0) Database inside the Data Guard Configuration. It is possible to specify multiple Service Names (Comma separated). FAL will then sequentially attempt those Databases to resolve the Gap.

alter system set fal_client='orcl'; 从11gr2开始废弃

alter system set DB_FILE_NAME_CONVERT='+datadg/standby','+datadg/orcl' scope=spfile;

alter system set LOG_FILE_NAME_CONVERT='+datadg/standby','+datadg/orcl' scope=spfile;

注意log和db_file的路径不一致，不能一并写成+datadg/standby/datafile

alter system set STANDBY_FILE_MANAGEMENT=auto scope=both;

值为auto说明，主库创建任何文件，备库也会自动创建，修改成manual的话就是手动copy到备库

sho parameter db_create_file_dest

开启OMF

alter system set db_create_file_dest='/home/oracle';

这个参数用于指定Oracle数据库服务器创建以下文件的缺省路径：

■ Datafiles■ Tempfiles■ Redo log files■ Control files■ Block change tracking files

指定这个参数后，就不需要在创建表空间时指定具体的datafile的文件路径和名称了

控制文件路径提前建

重点：参数更改完成后一定要确认，每个节点都要确认清楚。

配置standby库，备库需要开启到nomount

设置备库的参数：

1、将主库参数复制过来：

SQL> create pfile='/home/oracle/pfile' from spfile;

[oracle@oracle1 dbs]$ scp /home/oracle/pfile 192.168.5.66:/home/oracle/

2、参数文件添加如下内容：

*.db_name='orcl'

*.db_unique_name='standby'

*.fal_server='orcl'

*.log_archive_config='DG_CONFIG=(standby,orcl)'

*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'

*.log_archive_dest_2='SERVICE=orcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'

*.LOG_ARCHIVE_DEST_STATE_1=ENABLE

*.STANDBY_FILE_MANAGEMENT=AUTO

--当开启OMF功能时下面参数无需设置(前提是路径存在，且有权限），这里写上(如下是，开启到nomount可以直接开启OMF

*.log_file_name_convert='+datadg/orcl','+datadg/standby'

*.db_file_name_convert='+datadg/orcl','+datadg/standby'

$tnsping standby 主库执行

$tnsping orcl 备库执行

[oracle@dan admin]$ rman target sys/123456@orcl auxiliary sys/123456@standby

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Apr 9 22:32:27 2020

connected to target database: ORCL (DBID=1547652110)

connected to auxiliary database: STANDBY (not mounted)

当出现如上的情况即可开始跑duplicate

RMAN> duplicate target database for standby from active database dorecover nofilenamecheck;

备库上执行如下语句(是否开启到read only，如果不是直接最后一句）

alter database archivelog

alter database open read only; 其实备库直接alter database open之后就是只读的

archive log list;

alter database recover managed standby database disconnect from session;

切换归档测试：alter system switch logfile;

归档存放空间设置大点

归档删除策略（应用了之后就删除）

主库：CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY;

备库：CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

添加资源到gi

srvctl add database -d unique_name -o $ORACLE_HOME -p +PRNOVFDATA/PARAMETERS/spfile.2343 -n db_name -a DATADG,FRADG -i instance

srvctl add listener 或者srvctl add listener -s

登陆主库

$ sqlplus / as sysdba

SQL> select database_role from v$database;

登陆到备库

$ sqlplus / as sysdba

SQL> select database_role from v$database;

检查归档日志是否能正常传输

主备库：

SQL> select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG;

主备库正常切换

主库切换为物理备库

主库查询：

select database_role,switchover_status from v$database;

如果是SESSIONS ACTIVE（select program,type from v$session where type='USER';可通过此来查看有哪些会话在活动)。

其实也可以切换

TO STANDBY

主库可以直接进行切换

alter database commit to switchover to physical standby with session shutdown;

主库关闭数据库并重启到mount状态

STARTUP MOUNT;

select switchover_status from v$database;

SWITCHOVER_STATUS

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

RECOVERY NEEDED

将物理备库切换为主库

select switchover_status from v$database;

如果返回TO PRIMARY或SESSIONS ACTIVE便可进行切换。当返回SWITCHOVER LATENT或 SWITCHOVER PENDING，则需要去检查告警日志,如果是not allowed，直接switchover to primary 会报错ORA-16139: media recovery required

此时先

alter database recover managed standby database disconnect from session;

变成session active

alter database commit to switchover to primary with session shutdown;

SQL> alter database open;

SQL> select database_role from v$database;

新备

SQL> alter database open;

SQL> alter database recover managed standby database using current logfile disconnect from session;

故障切换

备库：

select * from v$archive_gap;

如果没有gap，直接备库如下语句

alter database recover managed standby database finishi;

alter database commit to switchover to primary with session shutdown;

如果切换到中途失败或者不切了，比如说

alter database commit to switchover to physical standby with session shutdown ;之后不想切换了或者此语句失败或者
alter database commit to switchover to primary with session shutdown;失败

可以直接如下操作,原主库

alter database commit to switchover to primary with session shutdown；

如果上面这句失败，可以在主库切换时产生的trc文件里面找到创建控制文件的部分，并将原主库开启到nomount，创建控制文件（注意可能会报错ORA-01503、ORA-01229、ORA-01110将NORESETLOGS改为resetlogs），并recover database until sequence 33; 然后open resetlogs，起了之后并不会影响原主备关系。

ITPub博客

11g has adg 搭建 + 切换

最新文章