Oracle 19c Data Guard物理备库

最新推荐文章于 2024-06-21 19:10:13 发布

xwk19870212

最新推荐文章于 2024-06-21 19:10:13 发布

阅读量481

点赞数

分类专栏： oracle 文章标签： oracle 数据库 sql

本文链接：https://blog.csdn.net/xwk19870212/article/details/125739468

版权

oracle 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

Oracle 19c Data Guard物理备库

一、主库端：

1.开启记日志和归档模式

select log_mode,force_logging from v$database;
alter database force logging;
select log_mode,force_logging from v$database;

 archive log list;
 shutdown immediate;
 startup mount;
 alter database archivelog;
 archive log list;
 alter database open;

3.主库中添加附加日志

select  MEMBER from v$logfile;
select bytes/1024/1024 from v$log;
alter database add standby logfile group 4 '/u01/app/oracle/oradata/ORCL/redo04.log' size 200M;
alter database add standby logfile group 5 '/u01/app/oracle/oradata/ORCL/redo05.log' size 200M;
alter database add standby logfile group 6 '/u01/app/oracle/oradata/ORCL/redo06.log' size 200M;
alter database add standby logfile group 7 '/u01/app/oracle/oradata/ORCL/redo07.log' size 200M;

4.修改参数文件

alter system set DB_UNIQUE_NAME='primary' scope=spfile;
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/arch';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=standby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby';
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE ;
alter system set FAL_SERVER=standby;
alter system set FAL_CLIENT=primary;
alter system set STANDBY_FILE_MANAGEMENT=AUTO;
alter system set log_file_name_convert='/u01/app/oracle/oradata/standby','/u01/app/oracle/oradata/ORCL' scope=spfile;
--alter system set REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE' scope=spfile;
--alter system set log_file_name_convert='orcl','standby'
--alter system set db_file_name_convert='/u02/oradata/orcl/','/u03/oradata/orcl/'

col name format a25;
col VALUE format a25;
col DISPLAY_VALUE format a25;
select name,VALUE,DISPLAY_VALUE from v$parameter
where upper(name) in ('DB_UNIQUE_NAME','LOG_ARCHIVE_CONFIG'
,'LOG_ARCHIVE_DEST_1','LOG_ARCHIVE_DEST_2',
'SET FAL_SERVER','SET FAL_CLIENT','STANDBY_FILE_MANAGEMENT',
'DB_FILE_NAME_CONVERT','LOG_FILE_NAME_CONVERT');

5.配置监听和tnsname

cd $ORACLE_HOME/network/admin
vim listener.ora

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

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

ADR_BASE_LISTENER = /u01/app/oracle

vim tnsname.ora

primary =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.40)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )


standby =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.50)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

6.将主库中的网络文件和口令文件传输到备库

cd $ORACLE_HOME/network/admin
scp -r listener.ora tnsnames.ora 192.168.56.50:/u01/app/oracle/product/19.3.0/db_1/network/admin
scp -r /u01/app/oracle/product/19.3.0/db_1/dbs/orapworcl 192.168.56.50:/u01/app/oracle/product/19.3.0/db_1/dbs

二、备库端：

1.修改监听和tnsname

修改listener.ora中的主机名

cd $ORACLE_HOME/network/admin
vi listener.ora

修改tnsname，如果需要修改，根据实际情况修改即可。

vi tnsname

2.创建必要的目录

创建归档目录

mkdir /u01/arch

创建审计文件目录。

show parameter audit_file_dest：
mkdir -p /u01/app/oracle/admin/orcl/adump

创建数据文件目录：

mkdir -p /u01/app/oracle/oradata/ORCL

3.创建参数文件

cd $ORACLE_HOME/dbs
vi initorcl.ora
DB_NAME=orcl

4.启动辅助实例到nomount状态

startup nomount;

三、创建备库

1.复制备库

rman TARGET sys/oracle@primary AUXILIARY sys/oracle@standby
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
nofilenamecheck DORECOVER using backupset
SPFILE
SET DB_UNIQUE_NAME='standby'
SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'
SET LOG_ARCHIVE_DEST_1='LOCATION=/u01/arch'
SET LOG_ARCHIVE_DEST_2='SERVICE=primary ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'
SET FAL_SERVER='primary'
SET FAL_CLIENT='standby'
set log_file_name_convert='/u01/app/oracle/oradata/ORCL','/u01/app/oracle/oradata/standby'
SET STANDBY_FILE_MANAGEMENT='AUTO';

#alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
#alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE;
#SET AUDIT_FILE_DEST="/u01/app/oracle/admin/orcl/adump"
#SET CONTROL_FILES='/u01/app/oracle/oradata/ORCL/control01.ctl','/u01/arch/control02.ctl'
#SET DB_FILE_NAME_CONVERT="primary","standby" 
#SET LOG_FILE_NAME_CONVERT="primary","standby"

2.备库添加standby logfile

select  MEMBER from v$logfile;
select bytes/1024/1024 from v$log;
alter database add standby logfile group 4 '/u01/app/oracle/oradata/standby/redo04.log' size 200M;
alter database add standby logfile group 5 '/u01/app/oracle/oradata/standby/redo05.log' size 200M;
alter database add standby logfile group 6 '/u01/app/oracle/oradata/standby/redo06.log' size 200M;
alter database add standby logfile group 7 '/u01/app/oracle/oradata/standby/redo07.log' size 200M;

四、验证

1.备库应用日志

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; 
--取消日志应用
alter database recover managed standby database disconnect from session;  
--应用日志

2.查看主备日志应用情况

SELECT l.NAME,l.SEQUENCE#,l.ARCHIVED,l.applied FROM v$archived_log l
where l.sequence#>65;
alter system switch logfile;
SELECT l.NAME,l.SEQUENCE#,l.ARCHIVED,l.applied FROM v$archived_log l
where l.sequence#>58;

3.排错

select process, sequence#, status, delay_mins from v$managed_standby;
SELECT STATUS,DESTINATION, ERROR FROM V$ARCHIVE_DEST;
select error from v$archive_dest where target='STANDBY';

4.主备切换测试。

原主库端：
1.查看主库的switchover状态

SQL>  select database_role,switchover_status from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PRIMARY          TO STANDBY

附：
A:switchover_status出现session active/not allowed
当出现session active的时候表示还有活动的session,则运行
Alter database commit to switchover to physical standby with session shutdown;
当出现not allowed时，在官方文档说转换会不成功，但是我测试的时候成功了。
B.ora- 01153: an incompatible media recovery is active
运行下面代码
Alter database recover managed standby database finish;
或者Alter database recover managed standby database finish force;
Alter database recover managed standby database disconnect from session;
C.如果出现：RESOLVABLE GAP
说明从库的日志还没有应用完，等从库日志应用完之后再查询。

2.将主库切换成备库

SQL> Alter database commit to switchover to physical standby with session shutdown;

Database altered.

3.重启原主库
startup

备库端
4.查看原备库端的switchover状态

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY TO PRIMARY

若出现：
ORA-16139: media recovery required
若出现：SWITCHOVER LATENT
是因为没有执行：
alter database recover managed standby database disconnect from session;
若出现：SWITCHOVER PENDING
执行下面语句
alter database recover managed standby database disconnect from session;
alter database commit to switchover to physical standby with session shutdown;
如果出现not allow，有可能是主库还没有转为备库，需要先将主库转为备库。

若出现：SESSIONS ACTIVE
执行下面语句
SQL> alter database commit to switchover to physical standby with session shutdown;
不过我执行之后看到的状态还是SESSIONS ACTIVE，最后看到还有一个sqlplus窗口没有退出，退出后再次查询可以看到TO PRIMARY了。
如果还是不行查询一下检查一下连接数据库的应用是否已经关闭。
是否还有正在连接的会话杀掉就可以了。

5.备库切换成主库

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Database altered.

6.重启原备库（新主库）

SQL> shutdown immediate;

ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1577056040 bytes
Fixed Size                  9135912 bytes
Variable Size             419430400 bytes
Database Buffers         1140850688 bytes
Redo Buffers                7639040 bytes
Database mounted.
Database opened.

7.按照四、验证验证主备是否正常。