• SQL> alter database recover managed standby database parallel 2 using current logfile disconnect from session;
    ---开启redo日志应用-就是开启同步
  • SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
  • SQL> select open_mode from v$database;
  • SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; --停止redo应用
  • SQL> alter database recover managed standby database parallel 2 using current logfile disconnect from session; --开启应用
  • SQL> alter database open;

1.:主库开启归档

shutdown
··
··

2.开启强制附加日志:

select force_logging from v$database;
alter database force logging;
select force_logging from v$database;
select group#,bytes/1024/1024 from v$standby_log;

3.备份数据库主库,并包括控制文件

  • rman.sh

4.主库参数:

alter system set db_unique_name='ORCL' scope=spfile sid='*';
alter system set log_archive_config='dg_config=(ORCL,ORCLDG)' scope=spfile sid='*';
alter system set log_archive_dest_1='location=+ASM_ARC valid_for=(all_logfiles,all_roles) db_unique_name=ORCL' scope=spfile sid='*';
-- 压缩传输 启用*.log_archive_dest_3='SERVICE=shoudandg ASYNC LGWR  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) compression=enable  DB_UNIQUE_NAME=shoudandg'
alter system set log_archive_dest_2='service=ORCLDG lgwr async affirm valid_for=(online_logfiles,primary_role) db_unique_name=ORCLDG' scope=spfile sid='*';
alter system set log_archive_dest_state_1=enable scope=spfile sid='*';
alter system set log_archive_dest_state_2=enable scope=spfile sid='*';
alter system set log_archive_max_processes=5 scope=spfile sid='*';
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE scope=spfile;
--以下是主库切换为备库,充当备库角色时的一些参数设置,如果不打算做数据库切换就不用设置了
ALTER SYSTEM SET FAL_CLIENT = ORCL SCOPE=SPFILE; 
alter system set fal_server='ORCLDG' scope=spfile sid='*';
alter system set standby_file_management='auto' scope=spfile sid='*';
alter system set db_file_name_convert='/oradata/ORCL','+ASM_DATA/ORCL' scope=spfile sid='*';
alter system set log_file_name_convert='/oradata/Flash/ORCL','+ASM_UNDO/ORCL' scope=spfile sid='*';

convert 两行意思是从对方转换信息到本地。第一个位置写对方的路径。第二个写自己的路径。只有自己是备库的时候有用

5.主库:创建standby控制文件

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

6.主库:创建pfile文件

  • create pfile ='/u01/backup/standby.ora' from spfile;

7.主库:拷贝密码文件到备库。也可以在备库创建密码文件。密码与主密码相同

  • scp orapwCTCNDG1 oracle@10.109.68.236:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/
    注意:;RAC到单实例。需要改名字。 把1去掉或者创建密码文件:
  • orapwd file='$ORACLE_HOME/dbs/orapwCTCNDG' password=oracle entries=5 force=y;

7.1.创建备库redo log 为角色转换做准备,即当前的主库转换为备库是用standby redo log。

查看日志:

  • select group#,member from v$logfile;
    查看日志文件大小:
select group#,bytes/1024/1024 from v$log;
alter database add standby logfile thread 1 group 5 size 50M,group 6 size 50M,group 7 size 50M,group 8 size 50M;
##添加四个没有继续添加这个:alter database add standby logfile thread 2 group 9 size 50M,group 10 size 50M,group 11 size 50M,group 12 size 50M;

8.备库创建日志目录:

  • mkdir /oradata/DGADUMP

9.拷贝控制文件和参数文件到备库

然后修改init文件所有的路径位置

/oradata/ORCL/ORCLDG/controlfile/
/oradata/ARC/ORCL/ORCLDG/controlfile/

注意:控制文件要写好他的路径。两个路径。和名字。,然后恢复控制文件的时候就可以 自动创建了

*.audit_file_dest='/u01/app/oracle/admin/ORCL/adump'
*.control_files='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/standby.ctl'
*.db_create_file_dest='/oradata/ORCL'
*.db_name='ORCL'
*.db_recovery_file_dest='/oradata/Flash/ORCL'
*.db_unique_name='ORCLDG'
*.diagnostic_dest='/u01/app/oracle'
*.fal_client='ORCLDG'
*.fal_server='ORCL1','ORCL2'
*.log_archive_config='dg_config=(ORCL,ORCLDG)'
*.log_archive_dest_1='location=/oradata/ARC/ORCL valid_for=(all_logfiles,all_roles) db_unique_name=ORCLDG'
*.log_archive_dest_2='service=ORCL lgwr async affirm valid_for=(online_logfiles,primary_role) db_unique_name=ORCL'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_max_processes=5
*.db_file_name_convert='+ASM_DATA/ORCL','/oradata/ORCL'
*.log_file_name_convert='+ASM_UNDO/ORCL','/oradata/Flash/ORCL'
*.remote_listener='orcl-scan:1521'
*.remote_login_passwordfile='EXCLUSIVE'

备库添加此信息

10.添加然后添加tns:

ORCL =
  (DESCRIPTION =
   (ADDRESS =(PROTOCOL = TCP)(HOST =10.109.68.230)(PORT =1521))
    (ADDRESS =(PROTOCOL = TCP)(HOST =10.109.68.231)(PORT =1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )

ORCL1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.109.68.230)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )


ORCL2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.109.68.231)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )
ORCLDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.109.68.236)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )

11.恢复pfile文件

  • SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/standby.ora';
    注意:pfile文件里面控制文件哪里要改为 新的控制文件的位置

12.恢复控制文件 :

  • restore standby controlfile from '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/standby.ctl';
    恢复成功之后
  • alter database mount;

13:恢复数据文件

  • catalog start with '/oradata/backup/';
    指定一下备份路径。如果源端和目标端备份路径不一样 指定一下
select 'SET NEWNAME FOR DATAFILE '|| file# ||' to ' ||''''|| name ||''''|| ';' from v$datafile;
run {
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
SET NEWNAME FOR DATAFILE 1 to '+DATA';
SET NEWNAME FOR DATAFILE 2 to '+DATA';
SET NEWNAME FOR DATAFILE 3 to '+DATA';
SET NEWNAME FOR DATAFILE 4 to '+DATA';
SET NEWNAME FOR DATAFILE 5 to '+DATA';
restore database;
switch datafile all;
release channel d4;
release channel d3;
release channel d2;
release channel d1;
}
RMAN> recover database;

恢复完之后可以看看日志和数据文件:--看情况需不需要创建日志

select group#,type,member from v$logfile;
select   file#  , name  from v$datafile; 
select thread#,group#,bytes/1024/1024 from v$log;
alter database add standby logfile thread 1 
group 1 '/oradata/frdbr/standby_redo1.log' size 256M,
group 2 '/oradata/frdbr/standby_redo2.log' size 256M,
group 3 '/oradata/frdbr/standby_redo3.log' size 256M 
group 4 '/oradata/frdbr/standby_redo4.log' size 256M,
group 5 '/oradata/frdbr/standby_redo5.log' size 256M,
group 6 '/oradata/frdbr/standby_redo6.log' size 256M,
group 7 '/oradata/frdbr/standby_redo7.log' size 256M,
group 8 '/oradata/frdbr/standby_redo8.log' size 256M,
group 9 '/oradata/frdbr/standby_redo9.log' size 256M,
group 10 '/oradata/frdbr/standby_redo10.log' size 256M;
alter database add standby logfile thread 2 group 11 '/oradata/frdbr/standby_redo11.log' size 256M;

临时表空间

select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;
select status,enabled, name, bytes/1024/1024 file_size from v$tempfile;

select * from dba_temp_files;
alter tablespace temp add tempfile '/sdoradata/orcldg/datafile/temp_02.dbf' size 20G;
 alter tablespace temp drop tempfile 201;

15:开启备库应用日志 :

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

16.开启ADG

alter database recover managed standby database cancel;   ---停止redo应用
alter database open;        --打开数据库看
alter database recover managed standby database using current logfile disconnect from session;  --然后在开启redob应用

17:查看seq号是否一致

  • archive log list;

18:查看DG状态:

select DATABASE_ROLE,PROTECTION_MODE,PROTECTION_LEVEL from v$database;
1.最大保护模式
 maximize protection
2.最大可用性模式
maximize availability;
3.最大性能模式
maximize performance;

未测试:

配置主备切换

alter system set log_archive_config='DG_CONFIG(racdb,racdg)';
alter system set fal_client='racdb';--指向自己
alter system set fal_server='racdg';--指向对端
alter database add standby logfile thread 1 group 5 size 50M,group 6 size 50M,group 7 size 50M;
alter database add standby logfile thread 2 group 8 size 50M,group 9 size 50M,group 10 size 50M;
alter system set db_file_name_convert='+DATA/racdg/','+DATA/racdb/' sid='*' scope=spfile;
alter system set log_file_name_convert='+DATA/racdg/','+DATA/racdb/' sid='*' scope=spfile;
set linesize 600
select dbid,name,open_mode,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from v$database;

主备切换:RACc主备切换需要停止实例2

主库节点2:[grid@dm02db01 ~]$ srvctl stop instance -d racdg -i racdg1
备库节点2:[grid@dm01db01 ~]$ srvctl stop instance -d racdb -i racdb1

主库执行

lsnrctl stop listener
alter database commit to switchover to physical standby with session shutdown;--会关闭实例
startup
alter database recover managed standby database using current logfile disconnect from session;

备库执行

lsnrctl stop listener
alter database recover managed standby database cancel;alter database commit to switchover to primary with session shutdown;
alter database open;
lsnrctl start listener

检查,检查备库打开的模式open_mode

set linesize 600
select dbid,name,open_mode,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from v$database;

启停:UBSPRD_STBY:
停:停止备库应用日志:

SQL>alter database recover managed standby database cancel;
SQL>shutdown immediate;

启:

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

检查状态命令:

  • select DEST_NAME,status,error from v$archive_dest; --查出有问题的归档
    解决问题:
alter system set LOG_ARCHIVE_DEST_STATE_2='defer' sid='*' scope=both;    ---设置为默认状态  
alter system set LOG_ARCHIVE_DEST_STATE_2='enable' sid='*' scope=both;  --再设置为可用状态,就可以再次同步  
alter system switch logfile;

查看数据库状态:: 查看当前数据库是主库还是备库,

  • select switchover_status from v$database;
    查看gap值
  • select * from v$archive_gap;
    查看进程传输、收取、应用
select process, client_process, sequence#, status from v$managed_standby;
SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

查看数据库dbid、dbname、打开状态等

  • select dbid,name,open_mode,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from v$database;
    查看arclog可用状态
  • select dest_name,status from v$archive_dest_status;

查看archlog的seq号及开始时间下一次应用时间

  • SELECT thread#, SEQUENCE#, applied, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
    查看archlog的seq号及第一次变化和 下一次变化值
  • SELECT thread#, SEQUENCE#, APPLIED, FIRST_CHANGE#, NEXT_CHANGE# FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
    查看应用seq状态
  • select thread#,sequence#,applied from v$archived_log;
    应用arc的状态:
  • select dest_name,status,archived_thread#, archived_seq#, applied_thread#, applied_seq# from v$archive_dest_status;
    查看 最大历史log的seq号
  • select thread#, max (sequence#) from v$log_history group by thread#;
    查看最大arclog的seq号
  • select thread#, max (sequence#) from v$archived_log where APPLIED='YES' group by thread#;

查看数据库保护模式和主备状态:

  • select DATABASE_ROLE,PROTECTION_MODE,PROTECTION_LEVEL from v$database;
    查看应用进度 监控复制进度
  • select * from v$recovery_progress;
    备库查看standby日志
  • select f.group#,s.bytes,s.status,f.type,f.member from v$logfile f,v$standby_log s where f.group#=s.group#;
  • 1)在 primary 数据库,LGWR 提交 redo 数据到 LNSn(LGWR Network Server process)进程(n>0) ,LNSn 启动网络传输。
  • 2)standby 数据库的 RFS(Remote File Server)将接收到的 redo 数据写入 standby redolog。特别注意,在此期间,primary 数据库的事务会一直保持,直到所有所有含 LGWR SYNC 属性的LOG_ARCHIVE_DEST_n指定路径均已完成接收。

切换最大可用性模式

主库启动至mount模式
alter system set log_archive_dest_2='service=standby OPTIONAL lgwr sync affirm valid_for=(online_logfiles,primary_roles) db_unique_name=orcl' scope=both;
alter database set standby database to maximize availability;
在最大可用性模式,如果备库不可用,不会导致主库挂起.

切换最高性能模式(默认)

alter system set log_archive_dest_2='service=standby OPTIONAL arch async noaffirm valid_for=(online_logfiles,primary_roles) db_unique_name=orcl' scope=both;
alter database set standby database to maximize performance;
最后修改:2022 年 03 月 31 日
如果觉得我的文章对你有用,请随意赞赏