DG 搭建

SELECT directory_name, grantee, privilege FROM user_tab_privs t, all_directories d   WHERE t.table_name(+)=d.directory_name   ORDER BY 1,2,3;


select * from dba_directories;


SQL> conn / as sysdba
SQL> drop user unticket cascade;


DG:
先在操作系统上安装上Oracle软件（版本要一致）


主库上配置好本地监听，使用dbca手工建库（注意建库的一些规划）
库建好要将数据库设置为归档模式，且为日志强制切换（最好重新制定归档日志目录）


alter database archivelog;
alter database force logging;
alter system set log_archive_dest_1='location=/arch/undb';
create pfile from spfile;
然后将参数文件和密码文件拷贝到从库，并改名：


从库上：
参数文件里面注意路径的修改，并且在末尾加上以下参数：同时要确保参数文件当中涉及到的目录创建（注意权限）


*.db_unique_name='undbpdg'
*.db_file_name_convert='/data01/oradata/undb','/data01/oradata/undbpdg'
*.log_file_name_convert='/data01/oradata/undb','/data01/oradata/undbpdg'
*.log_archive_config='dg_config=(undb,undbpdg)'
*.log_archive_dest_1='location=/arch/undbpdg valid_for=(all_logfiles,all_roles) db_unique_name=undbpdg'
*.log_archive_dest_2='service=undb lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=undb'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.log_archive_max_processes=10
*.fal_server=undb
*.fal_client=undbpdg
*.standby_file_management=auto




开始配置从库的本地监听以及网络监听（联系主库）（注意关闭防火墙，否则会影响到tnsping 连接）
netmgr（本地，确保本地监听可以正常打开） netca tnsping命令
在从库上创建二进制参数文件
create spfile from pfile='?/dbs/initundbpdg.ora';
然后将数据库启动到nomount状态




主库：
配置网络监听，确保主从之间可以互通
进入数据库，添加以下参数：


alter system set log_archive_config='dg_config=(undb,undbpdg)';
alter system set log_archive_dest_1='location=/arch/undb valid_for=(all_logfiles,all_roles) db_unique_name=undb';
alter system set log_archive_dest_2='service=undbpdg lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=undbpdg';
alter system set log_archive_dest_state_1=enable;
alter system set log_archive_dest_state_2=enable;
alter system set log_archive_max_processes=10;
alter system set fal_server=undbpdg;
alter system set fal_client=undb;
alter system set standby_file_management=auto;


alter system set db_unique_name='undb'scope=spfile;
alter system set db_file_name_convert='/data01/oradata/undbpdg','/data01/oradata/undb' scope=spfile;
alter system set log_file_name_convert='/data01/oradata/undbpdg','/data01/oradata/undb' scope=spfile;


使用rman 对主库进行备份：


rman target /
run
{
allocate channel d1 type disk format '/data01/backupset/%U';
backup database;
release channel d1;
}
注意备份目录的权限


备份成功之后开始创建控制文件，然后将其拷贝到从库相同的目录下，也要将全库备份的文件拷贝到从库（最好是目录相同，连同目录一起拷贝）。


alter database create standby controlfile as '/app/oracle/standby.ctl';
确保控制文件在从库存在，备份文件在从库相同的目录存在（控制文件生成一定要在备份之后）


然后开始克隆：
rman target / auxiliary sys/123456@undbpdg
run
{
allocate auxiliary channel d1 type disk;
duplicate target database for standby nofilenamecheck;
release channel d1;
}
克隆时注意观察控制文件的input、output


克隆完成后
从库：
观察从库的状态
select status from v$instance; 已到mount状态


开始创建备库的standby日志：（redo日志的大小为1G，3组）


alter database add standby logfile ('/data01/oradata/undbpdg/standby01.log') size 1024m;
alter database add standby logfile ('/data01/oradata/undbpdg/standby02.log') size 1024m;
alter database add standby logfile ('/data01/oradata/undbpdg/standby03.log') size 1024m;
alter database add standby logfile ('/data01/oradata/undbpdg/standby04.log') size 1024m;




主库：切换几次日志
alter system switch logfile;


从库：
到/arch/undbpdg 下查看是否已有日志，有日志说明已经同步成功（注意日志的生成时间）


运行物理DG
alter database recover managed standby database using current logfile disconnect from session; --日志同步
select sequence#, applied from v$archived_log where applied='YES' order by sequence#;
查看状态，yes说明已经成功。




此时可以在主库上用普通用户创建一个表（注意提交），然后切换日志。
之后到从库上执行：
alter database recover managed standby database cancel;
alter database open;
此时可以正常在从库上用相应的用户查看到同样的信息


几个查询语句：
select open_mode,database_role,db_unique_name from v$database;
select switchover_status from v$database;
select status from v$instance;




DG身份的切换：
注意切换时，从库的状态应当是mount状态
从库：
shutdown immediate
startup nomount
alter database mount standby database;
SQL> select switchover_status from v$database;


SWITCHOVER_STATUS
--------------------
NOT ALLOWED


SQL> select open_mode,database_role,db_unique_name from v$database;


OPEN_MODE  DATABASE_ROLE DB_UNIQUE_
---------- -------------------- ----------
MOUNTED    PHYSICAL STANDBY undbpdg




主库：
SQL> select open_mode,database_role,db_unique_name from v$database;


OPEN_MODE     DATABASE_ROLE    DB_UNIQUE_NAME
-------------------- ---------------- ------------------------------
READ WRITE     PRIMARY      undb


SQL> select switchover_status from v$database;


SWITCHOVER_STATUS
--------------------
TO STANDBY


alter database commit to switchover to physical standby with session shutdown;
shutdown immediate
startup nomount
SQL> alter database mount standby database; ---即把主库切成备库
SQL> select status from v$instance;


STATUS
------------
MOUNTED


SQL> select switchover_status from v$database;  ----出现了该种状态


SWITCHOVER_STATUS
--------------------
RECOVERY NEEDED




从库：
select sequence#, applied from v$archived_log where applied='YES' order by sequence#;


 SEQUENCE# APPLIED
---------- ---------
3 YES
4 YES
5 YES
6 YES
7 YES


SQL> select switchover_status from v$database;


SWITCHOVER_STATUS
--------------------
NOT ALLOWED


SQL> select open_mode,database_role,db_unique_name from v$database;


OPEN_MODE  DATABASE_ROLE DB_UNIQUE_
---------- -------------------- ----------
MOUNTED    PHYSICAL STANDBY undbpdg


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


Database altered.


SQL> select open_mode,database_role,db_unique_name from v$database;


OPEN_MODE  DATABASE_ROLE DB_UNIQUE_
---------- -------------------- ----------
MOUNTED    PHYSICAL STANDBY undbpdg


SQL> alter database commit to switchover to primary with session shutdown;  ---即把从库切成主库


Database altered.


SQL> select open_mode,database_role,db_unique_name from v$database;


OPEN_MODE  DATABASE_ROLE DB_UNIQUE_
---------- -------------------- ----------
MOUNTED    PRIMARY undbpdg


SQL> alter database open;


SQL> select open_mode,database_role,db_unique_name from v$database;


OPEN_MODE  DATABASE_ROLE DB_UNIQUE_
---------- -------------------- ----------
READ WRITE PRIMARY undbpdg




主库：


alter database add standby logfile ('/data01/oradata/undb/standby01.log') size 1024m;
alter database add standby logfile ('/data01/oradata/undb/standby02.log') size 1024m;
alter database add standby logfile ('/data01/oradata/undb/standby03.log') size 1024m;
alter database add standby logfile ('/data01/oradata/undb/standby04.log') size 1024m;


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


SQL> select switchover_status from v$database;  ---需要等待一会儿状态才可改变


SWITCHOVER_STATUS
--------------------
RECOVERY NEEDED


select sequence#, applied from v$archived_log where applied='YES'order by sequence#;


 SEQUENCE# APPLIED
---------- ---------
1 YES
2 YES


此时可以在从库上切换几次日志


主库上操作：


alter database recover managed standby database cancel;
alter database open;


可以查询，至此，切换完成


然后可以再次切换回去：


主库：


SQL> select open_mode,database_role,db_unique_name from v$database;


OPEN_MODE     DATABASE_ROLE    DB_UNIQUE_NAME
-------------------- ---------------- ------------------------------
READ ONLY     PHYSICAL STANDBY undb


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


select open_mode,database_role,db_unique_name from v$database;   --------


OPEN_MODE     DATABASE_ROLE    DB_UNIQUE_NAME
-------------------- ---------------- ------------------------------
READ ONLY WITH APPLY PHYSICAL STANDBY undb


SQL> shutdown immediate
SQL> startup nomount
SQL> alter database mount standby database;---到mount状态（现在的从库）




从库：


SQL> select open_mode,database_role,db_unique_name from v$database;


OPEN_MODE  DATABASE_ROLE DB_UNIQUE_
---------- -------------------- ----------
READ WRITE PRIMARY undbpdg


SQL> select switchover_status from v$database;  ----此种状态（异常）


SWITCHOVER_STATUS
--------------------
FAILED DESTINATION


SQL> alter database commit to switchover to physical standby;


SQL> select switchover_status from v$database; ------


SWITCHOVER_STATUS
--------------------
RECOVERY NEEDED


shutdown immediate
SQL> startup nomount
SQL> alter database mount standby database;




主库：
重复前面操作，完成切换




逻辑DG的搭建：


从库：
SQL> shutdown immediate
SQL> startup nomount
SQL> alter database mount standby database;
SQL> select status from v$instance;


STATUS
------------
MOUNTED


SQL> select open_mode from v$database;


OPEN_MODE
--------------------
MOUNTED


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


SQL> alter database recover managed standby database cancel;  -----停止日志应用




主库：
mkdir -p /data01/oradata/undb/lgarchive


SQL> alter system set log_archive_dest_3='location=/data01/oradata/undb/lgarchive valid_for=(standby_logfiles,standby_role) db_unique_name=undb';


SQL> alter system set log_archive_dest_state_3=enable;


SQL> execute dbms_logstdby.build;


PL/SQL procedure successfully completed.




从库：
mkdir -p /data01/oradata/undbpdg/lgarchive
SQL> alter system set log_archive_dest_3='location=/data01/oradata/undbpdg/lgarchive valid_for=(standby_logfiles,standby_role) db_unique_name=undbpdg';


SQL> alter system set log_archive_dest_state_3=enable;


SQL> select * from v$tempfile;
如果没有信息，需要添加
alter tablespace temp add tempfile xxxxxx.


SQL> select status from v$instance;


STATUS
------------
MOUNTED


SQL> select database_role from v$database;


DATABASE_ROLE
----------------
PHYSICAL STANDBY


SQL> startup mount force 


SQL> alter database recover to logical standby undbpdg;  ----备库转移成了逻辑备库




SQL> select status from v$instance;


STATUS
------------
STARTED


SQL> startup mount force
SQL> select database_role from v$database;


DATABASE_ROLE
----------------
LOGICAL STANDBY


SQL> alter database open resetlogs;


SQL> show parameter db_name


NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name     string UNDBPDG




等上一会儿到主库：
alter system switch logfile;


查看undb库的alert信息
查看undbpdg的alert 信息


到从库的 /data01/oradata/undbpdg/lgarchive 查看日志（注意时间）


从库：
alter database start logical standby apply immediate;


此时从库的alert 日志里会产生大量信息


此时逻辑DG已经搭建好