DG安装sql命令

博主： Tehran
发布时间：2022 年 03 月 31 日
308 次浏览
暂无评论
12343字数
分类： Oracle

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 日

如果觉得我的文章对你有用，请随意赞赏

发表评论取消回复
# 请输入真实的邮箱地址便于接收回复

评论 *

私密评论

名称 *

🎲

邮箱 *

地址

DG安装sql命令

Tehran • 2022 年 03 月 31 日

<div class="tip share">请注意，本文编写于 616 天前，最后修改于 616 天前，其中某些信息可能已经过时。</div>

<ul><li>SQL&gt; alter database recover managed standby database parallel 2 using current logfile disconnect from session;<br>---开启redo日志应用-就是开启同步</li><li>SQL&gt;  SELECT SWITCHOVER_STATUS FROM V$DATABASE;</li><li>SQL&gt; select open_mode from v$database;</li><li>SQL&gt; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;         --停止redo应用</li><li>SQL&gt; alter database recover managed standby database parallel 2 using current logfile disconnect from session;  --开启应用</li><li>SQL&gt; alter database open;</li></ul><h3>1.：主库开启归档</h3><p>shutdown<br>··<br>··</p><h3>2.开启强制附加日志：</h3><pre><code>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;</code></pre><h3>3.备份数据库主库，并包括控制文件</h3><ul><li>rman.sh</li></ul><h3>4.主库参数：</h3><pre><code>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='*';</code></pre><p><strong>convert 两行意思是从对方转换信息到本地。第一个位置写对方的路径。第二个写自己的路径。只有自己是备库的时候有用</strong></p><h3>5.主库：创建standby控制文件</h3><ul><li>alter database create standby controlfile as '/u01/backup/standby.ctl';</li></ul><h3>6.主库：创建pfile文件</h3><ul><li>create pfile ='/u01/backup/standby.ora' from spfile;</li></ul><h3>7.主库：拷贝密码文件到备库。也可以在备库创建密码文件。密码与主密码相同</h3><ul><li>scp orapwCTCNDG1 oracle@10.109.68.236:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/<br><strong>注意</strong>：；RAC到单实例。需要改名字。 把1去掉或者创建密码文件：</li><li>orapwd file='$ORACLE_HOME/dbs/orapwCTCNDG' password=oracle entries=5 force=y;</li></ul><h4>7.1.创建备库redo log 为角色转换做准备，即当前的主库转换为备库是用standby redo log。</h4><p><strong>查看日志：</strong></p><ul><li>select group#,member from v$logfile;<br><strong>查看日志文件大小：</strong></li></ul><pre><code>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;</code></pre><h3>8.备库创建日志目录：</h3><ul><li>mkdir /oradata/DGADUMP</li></ul><h3>9.拷贝控制文件和参数文件到备库</h3><p><strong>然后修改init文件所有的路径位置</strong></p><pre><code>/oradata/ORCL/ORCLDG/controlfile/
/oradata/ARC/ORCL/ORCLDG/controlfile/</code></pre><p><strong>注意：控制文件要写好他的路径。两个路径。和名字。，然后恢复控制文件的时候就可以 自动创建了</strong></p><pre><code>*.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'</code></pre><p><strong>备库添加此信息</strong></p><h3>10.添加然后添加tns：</h3><pre><code>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)
    )
  )</code></pre><h3>11.恢复pfile文件</h3><ul><li>SQL&gt; startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/standby.ora';<br><strong>注意：pfile文件里面控制文件哪里要改为 新的控制文件的位置</strong></li></ul><h3>12.恢复控制文件 ：</h3><ul><li>restore standby controlfile from '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/standby.ctl';<br><strong>恢复成功之后</strong></li><li>alter database  mount;</li></ul><h3>13:恢复数据文件</h3><ul><li>catalog start with '/oradata/backup/';<br><strong>指定一下备份路径。如果源端和目标端备份路径不一样 指定一下</strong></li></ul><pre><code>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&gt; recover database;</code></pre><p><strong>恢复完之后可以看看日志和数据文件：--看情况需不需要创建日志</strong></p><pre><code>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;</code></pre><p><strong>临时表空间</strong></p><pre><code>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;</code></pre><h3>15：开启备库应用日志 ：</h3><ul><li>SQL&gt; alter database recover managed standby database parallel 2 using current logfile disconnect from session;</li></ul><h3>16.开启ADG</h3><pre><code>alter database recover managed standby database cancel;   ---停止redo应用
alter database open;        --打开数据库看
alter database recover managed standby database using current logfile disconnect from session;  --然后在开启redob应用</code></pre><h3>17:查看seq号是否一致</h3><ul><li>archive log list;</li></ul><h3>18:查看DG状态：</h3><pre><code>select DATABASE_ROLE,PROTECTION_MODE,PROTECTION_LEVEL from v$database;
1.最大保护模式
 maximize protection
2.最大可用性模式
maximize availability；
3.最大性能模式
maximize performance；</code></pre><h3>未测试：</h3><p><strong>配置主备切换</strong></p><pre><code>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;</code></pre><p><strong>主备切换：RACc主备切换需要停止实例2</strong></p><pre><code>主库节点2：[grid@dm02db01 ~]$ srvctl stop instance -d racdg -i racdg1
备库节点2：[grid@dm01db01 ~]$ srvctl stop instance -d racdb -i racdb1</code></pre><p><strong>主库执行</strong></p><pre><code>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;</code></pre><p><strong>备库执行</strong></p><pre><code>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</code></pre><p><strong>检查，检查备库打开的模式open_mode</strong></p><pre><code>set linesize 600
select dbid,name,open_mode,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from v$database;</code></pre><p><strong>启停：UBSPRD_STBY:</strong><br><strong>停：停止备库应用日志：</strong></p><pre><code>SQL&gt;alter database recover managed standby database cancel;
SQL&gt;shutdown immediate;</code></pre><p><strong>启：</strong></p><pre><code>SQL&gt;startup mount;
SQL&gt;alter database recover managed standby database using current logfile disconnect from session;</code></pre><p><strong>检查状态命令：</strong></p><ul><li>select DEST_NAME,status,error from v$archive_dest; --查出有问题的归档<br><strong>解决问题：</strong></li></ul><pre><code>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;</code></pre><p><strong>查看数据库状态：：  查看当前数据库是主库还是备库，</strong></p><ul><li>select switchover_status from v$database;<br><strong>查看gap值</strong></li><li>select * from v$archive_gap;<br><strong>查看进程传输、收取、应用</strong></li></ul><pre><code>select process, client_process, sequence#, status from v$managed_standby;
SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;</code></pre><p><strong>查看数据库dbid、dbname、打开状态等</strong></p><ul><li>select dbid,name,open_mode,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from v$database;<br><strong>查看arclog可用状态</strong></li><li>select dest_name,status from v$archive_dest_status;</li></ul><p><strong>查看archlog的seq号及开始时间下一次应用时间</strong></p><ul><li>SELECT thread#, SEQUENCE#, applied, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;<br><strong>查看archlog的seq号及第一次变化和 下一次变化值</strong></li><li>SELECT thread#, SEQUENCE#, APPLIED, FIRST_CHANGE#, NEXT_CHANGE# FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;<br><strong>查看应用seq状态</strong></li><li>select thread#,sequence#,applied from v$archived_log;<br><strong>应用arc的状态：</strong></li><li>select dest_name,status,archived_thread#, archived_seq#, applied_thread#, applied_seq# from v$archive_dest_status;<br><strong>查看 最大历史log的seq号</strong></li><li>select thread#, max (sequence#) from v$log_history group by thread#;<br><strong>查看最大arclog的seq号</strong></li><li>select thread#, max (sequence#) from v$archived_log where APPLIED='YES' group by thread#;</li></ul><p><strong>查看数据库保护模式和主备状态：</strong></p><ul><li>select DATABASE_ROLE,PROTECTION_MODE,PROTECTION_LEVEL from v$database;<br><strong>查看应用进度  监控复制进度</strong></li><li>select * from v$recovery_progress;<br><strong>备库查看standby日志</strong></li><li>select f.group#,s.bytes,s.status,f.type,f.member from v$logfile f,v$standby_log s where f.group#=s.group#;</li><li>1）在 primary 数据库，LGWR 提交 redo 数据到 LNSn(LGWR Network Server process)进程(n&gt;0) ，LNSn 启动网络传输。</li><li>2）standby 数据库的 RFS(Remote File Server)将接收到的 redo 数据写入 standby redolog。特别注意，在此期间，primary 数据库的事务会一直保持，直到所有所有含 LGWR SYNC 属性的LOG_ARCHIVE_DEST_n指定路径均已完成接收。</li></ul><p><strong>切换最大可用性模式</strong></p><pre><code>主库启动至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;
在最大可用性模式,如果备库不可用,不会导致主库挂起.</code></pre><p><strong>切换最高性能模式(默认)</strong></p><pre><code>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;</code></pre>

DG安装sql命令

1.：主库开启归档

2.开启强制附加日志：

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

4.主库参数：

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

6.主库：创建pfile文件

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

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

8.备库创建日志目录：

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

10.添加然后添加tns：

11.恢复pfile文件

12.恢复控制文件：

13:恢复数据文件

15：开启备库应用日志：

16.开启ADG

17:查看seq号是否一致

18:查看DG状态：

未测试：

相关文章

发表评论取消回复
# 请输入真实的邮箱地址便于接收回复

handsome 换字体

Oracle 数据库常用安装包合集（包含基础版和最新补丁集202107）

本站typecho 主题版本修改的东西(8.40版本和typecho开发版1.2)

超全 Linux 安装包合集(Centos Redhat Oracle Linux)

影视资源站接口

ORACLE 字符集的坑

小型企业网络拓扑的搭建

ORA-06544: PL/SQL: internal error

linux 网络连通性检查shell脚本

【Linux从青铜到王者】第三篇:Linux开发工具编辑器vim

DG安装sql命令

1.：主库开启归档

2.开启强制附加日志：

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

4.主库参数：

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

6.主库：创建pfile文件

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

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

8.备库创建日志目录：

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

10.添加然后添加tns：

11.恢复pfile文件

12.恢复控制文件 ：

13:恢复数据文件

15：开启备库应用日志 ：

16.开启ADG

17:查看seq号是否一致

18:查看DG状态：

未测试：

相关文章

发表评论 取消回复 # 请输入真实的邮箱地址便于接收回复

DG安装sql命令

12.恢复控制文件：

15：开启备库应用日志：

发表评论取消回复
# 请输入真实的邮箱地址便于接收回复