Oracle---dataguard

克隆安装oracle
groupadd oinstall
groupadd dba
useradd -g oinstall -G dba -m oracle
passwd oracle
su - oracle

vi .bashrc
---------------------------------------------------------
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export ORACLE_SID=orcl
export ORACLE_OWNER=oracle
export ORACLE_TERM=vt100
export THREADS_FLAG=native
export LANG=en_US
---------------------------------------------------------

su -
mkdir -p /u01/app/oracle
chown -R oracle.oinstall /u01/app

vi + /etc/security/limits.conf
---------------------------------------------
oracle soft nofile 65536
oracle hard nofile 65536
oracle soft nproc 16384
oracle hard nproc 16384

vi + /etc/sysctl.conf
---------------------------------------------
kernel.shmmax = 4046471168
kernel.shmmni = 4096
kernel.shmall = 2097152
kernel.sem = 250 32000 100 128
fs.file-max = 6815744
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
fs.aio-max-nr = 1048576
---------------------------------------------
使内核参数生效
sysctl -p

cd /u01
tar -cf - app | ssh oracle@172.25.6.12 'cd /u01/; tar -xf -'
###################################################################################
主库：primary 172.25.0.10 ; db_unique_name=demo
从库：standby 172.25.0.11 ; db_unique_name=aux1
主库与从库的db_name必须一样，主库与从库的db_unique_name必须不一样

1.主库要运行在归档模式
archive log list
shut immediate
startup mount
alter database archivelog;
alter database open;

2.主库要打开force logging:主库的所有操作都要产生日志！
select database_role,force_logging from v$database;
alter database force logging;

3.主库要打开dataguard开关：参数值在主从节点要一致
show parameter log_archive_config
alter system set log_archive_config='dg_config=(主库的唯一名,从库1的唯一名，从库2的唯一名，.....)';
alter system set log_archive_config='dg_config=(demo,aux1)';

4.修改主库的本地归档路径：
alter system set log_archive_dest_1='location=/home/oracle/arc_db01_dest1/ valid_for=(日志类型,数据库角色) db_unique_name=demo';

alter system set log_archive_dest_1='location=/home/oracle/arc_db01_dest1/ valid_for=(online_logfiles,primary_role) db_unique_name=demo';

5.激活主库的本地归档路径：
alter system set log_archive_dest_state_1='enable';

6.在主库启用远程归档：
alter system set log_archive_dest_3='service=连接从库的网络服务命名 valid_for=(online_logfiles,primary_role) db_unique_name=aux1';

alter system set log_archive_dest_3='service=aux1srv valid_for=(online_logfiles,primary_role) db_unique_name=aux1';

alter system set log_archive_dest_state_3='enable';

7.将主库的口令文件拷贝的从库
scp $ORACLE_HOME/dbs/orapw$ORACLE_SID oracle@172.25.0.11:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwaux1

8.为从库创建参数文件：使用主库参数文件直接修改
sqlplus / as sysdba
create pfile='/home/oracle/initaux1.ora' from spfile;
vi /home/oracle/initaux1.ora
--------------------------------------------------------------------------
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.db_block_size=8192
*.db_domain='example.com'
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.log_archive_config='dg_config=(demo,aux1)'
*.memory_target=843055104
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
audit_file_dest='/u01/app/oracle/admin/aux1/adump'
control_files='/u01/app/oracle/oradata/aux1/control01.ctl'
db_unique_name='aux1'
local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.25.0.11)(PORT=1521)))'
log_archive_dest_2='location=/home/oracle/arc_aux1_dest2/ valid_for=(standby_logfiles,standby_role) db_unique_name=aux1'
log_archive_dest_state_2='enable'
db_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/aux1/'
log_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/aux1/'
standby_file_management=auto
--------------------------------------------------------------------------
scp /home/oracle/initaux1.ora oracle@172.25.0.11:/u01/app/oracle/product/11.2.0/db_1/dbs/

9.在从库准备相关目录：
mkdir -p /u01/app/oracle/admin/aux1/adump
mkdir -p /home/oracle/arc_aux1_dest2
mkdir -p /u01/app/oracle/oradata/aux1/

10.修改从库的ORACLE_SID
export ORACLE_SID=aux1

11.将从库的pfile转换成spfile
sqlplus / as sysdba
SQL> create spfile from pfile;

12.启动从库的实例到nomount
startup nomount

13.在从库配置监听程序:使用静态注册
vi /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
--------------------------------------------------------------------
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.0.11)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = aux1.example.com)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = aux1)
    )
  )
--------------------------------------------------------------------

14.启动从库监听程序
$ lsnrctl start

15.在主库配置服务命名:
和主库的远程归档参数service=aux1srv保持一致
vi /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
--------------------------------------------------------------------
aux1srv =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.0.11)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = aux1.example.com)
    )
  )
--------------------------------------------------------------------

16.在主库测试与从库的网络连接：
SQL> conn sys/oracle@aux1srv as sysdba
SYS@ aux1srv> show parameter name

17.在主库启动rman复制从库：
rman target / auxiliary sys/oracle@aux1srv
RMAN> duplicate target database for standby from active database;

18.在从库增加standby log：standby log和主库的online log大小必须一致
SYS@ db01> select GROUP#,BYTES from v$log;

    GROUP#    BYTES
---------- ----------
     1   52428800
     2   52428800
     3   52428800

select * from v$standby_log;
alter database add standby logfile '/u01/app/oracle/oradata/aux1/redo04.log' size 52428800;
alter database add standby logfile '/u01/app/oracle/oradata/aux1/redo05.log' size 52428800;
alter database add standby logfile '/u01/app/oracle/oradata/aux1/redo06.log' size 52428800;

19.查看主库当前组的序列号：
select group#,sequence#,status from v$log;

20.查看从库的归档情况
select sequence#,applied from v$archived_log;

21.在主库切换日志
alter system switch logfile;

22.再查看从库的归档情况,看新的归档在从库是否出现
select sequence#,applied from v$archived_log;

23.如果从库的归档没有出现，查看主库的远程归档报错信息
select error from v$archive_dest where dest_id=3;

ERROR
-----------------------------------------------------------------
ORA-16058: standby database instance is not mounted

在主库重新激活远程归档，刷新字典消息：
alter system set log_archive_dest_state_3='enable';

24.打开从库：物理备用库打开模式只能是read only
alter database open;
select database_role,open_mode from v$database;

25.在从库启动管理恢复进程：回在从库激活一个进程MRP0
alter database recover managed standby database disconnect from session;

26.停止管理恢复进程：
alter database recover managed standby database cancel;

27.启用日志实时应用：
alter database recover managed standby database using current logfile disconnect from session;

28.在从库进行不完全恢复，找回主库误操作
alter database recover managed standby database cancel;

alter database recover managed standby database disconnect from session until change 2810381;

29.在从库启用日志的延迟应用(单位是分钟)
alter database recover managed standby database cancel;
alter database recover managed standby database disconnect from session delay 1;
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
30.在从库打开闪回数据库功能
alter database recover managed standby database cancel;
select flashback_on from v$database;
alter database flashback on;

31.在从库创建还原点:
CREATE RESTORE POINT test_active GUARANTEE FLASHBACK DATABASE;

32.增加online redolog的归档路径
mkdir -p /home/oracle/arc_aux1_dest1
alter system set log_archive_dest_1='location=/home/oracle/arc_aux1_dest1/ valid_for=(online_logfiles,primary_role) db_unique_name=aux1';

alter system archive log sequence 1 to '/home/oracle/arc_aux1_dest1/';

33.激活从库：临时激活目的是为了做测试
ALTER DATABASE ACTIVATE STANDBY DATABASE;

34.打开从库
alter database open;

35.查看从库打开模式
select database_role,open_mode from v$database;

35.在从库中进行数据修改测试

36.将从库闪回到还原点，使其角色回归standby
shut immediate
startup mount
FLASHBACK DATABASE TO RESTORE POINT test_active;

37.将角色重新转换成从库：
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
shut immediate
startup
select database_role,open_mode from v$database;
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
##################################################################################
系统重新起动后主从节点资源启动顺序：
1.启动从库监听
 lsnrctl start
2.启动从库(有可能不能open，那就在mount状态重新建立数据同步再open)
 export ORACLE_SID=aux1
 sqlplus / as sysdba
startup
3.启动主库
 sqlplus / as sysdba
 startup
如何校验主从是否同步？
在主库切换日志到从库查看归档是否出现！
##################################################################################
38.查看主库向从库传输日志的模式
SYS@ db01> select ASYNC_BLOCKS,AFFIRM from v$archive_dest where dest_id=3;

ASYNC_BLOCKS AFF
------------ ---
       61440 NO

ASYNC_BLOCKS --> 61440 代表主库向从库发送日志时可以延迟61440个日志块（网路IO异步）
AFFIRM --> NO 代表从库得到主库的日志并写盘后不需要通知主库
修改这两个选项，使主库向从库传输日志时使用网络IO同步，从库日志写盘成功给主库返回值

alter system set log_archive_dest_3='service=aux1srv sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=aux1';

39.查看当前主库的保护模式
select database_role,protection_mode,protection_level from v$database;

DATABASE_ROLE     PROTECTION_MODE      PROTECTION_LEVEL
---------------- -------------------- --------------------
PRIMARY      MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

40.升级对主库的保护模式:在主库修改
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE {AVAILABILITY | PERFORMANCE | PROTECTION};
升级为最高可用模式
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;
alter system switch logfile;
alter system checkpoint;

select database_role,protection_mode,protection_level from v$database;

DATABASE_ROLE     PROTECTION_MODE      PROTECTION_LEVEL
---------------- -------------------- --------------------
PRIMARY      MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

41.在从库启动日志实时应用校验数据
alter database recover managed standby database using current logfile disconnect from session;

42.升级为最大保护模式
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;

select database_role,protection_mode,protection_level from v$database;

DATABASE_ROLE     PROTECTION_MODE      PROTECTION_LEVEL
---------------- -------------------- --------------------
PRIMARY      MAXIMUM PROTECTION   MAXIMUM PROTECTION
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
switchover:必须是主库先变成从库，从库再变成主库

主库变从库需要进行哪些配置？
*增加standby log
alter database add standby logfile '/u01/app/oracle/oradata/orcl/redo04.log' size 52428800;
alter database add standby logfile '/u01/app/oracle/oradata/orcl/redo05.log' size 52428800;
alter database add standby logfile '/u01/app/oracle/oradata/orcl/redo06.log' size 52428800;
*增加为standby log保存归档的路径
mkdir -p /home/oracle/arc_db01_dest2
alter system set log_archive_dest_2='location=/home/oracle/arc_db01_dest2/ valid_for=(standby_logfiles,standby_role) db_unique_name=demo';
*增加standby_file_management
alter system set standby_file_management=auto scope=spfile;
*增加数据文件路径转换参数
alter system set db_file_name_convert='/u01/app/oracle/oradata/aux1/','/u01/app/oracle/oradata/orcl/' scope=spfile;
*增加日志文件路径转换参数
alter system set log_file_name_convert='/u01/app/oracle/oradata/aux1/','/u01/app/oracle/oradata/orcl/' scope=spfile;
*配置监听程序
vi $ORACLE_HOME/network/admin/listener.ora
--------------------------------------------------------------------
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.0.10)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = demo.example.com)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = db01)
    )
  )
--------------------------------------------------------------------
lsnrctl status
lsnrctl start
查看主库的角色转换状态：
select database_role,switchover_status from v$database;

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

开始角色转换：
TO STANDBY:
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
SESSION ACTIVE:
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
启动新的从库：
startup
SYS@ db01> select database_role from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
从库变主库需要进行哪些配置？
*online redolog需要由归档路径
alter system set log_archive_dest_1='location=/home/oracle/arc_aux1_dest1/ valid_for=(online_logfiles,primary_role) db_unique_name=aux1';

*为从库启用远程归档
alter system set log_archive_dest_3='service=db01srv sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=demo';

*配置服务命名
vi $ORACLE_HOME/network/admin/tnsnames.ora
------------------------------------------------------------------
db01srv =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.0.10)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = demo.example.com)
    )
  )
------------------------------------------------------------------
查看从库的角色转换状态：
select database_role,switchover_status from v$database;

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

将从库转换成主库：
ALTER DATABASE COMMIT TO SWITCHOVER TO primary;
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
使用broker实现快速故障转移:
前提条件:
主库和从库都要使用spfile
主库和从库都要配置连接自身和远程的服务命名
主从数据库都要配置local_listener参数
主库和从库都要打开闪回功能
alter database flashback on;
主库要运行在最高可用的保护模式
alter database set standby database to maximize AVAILABILITY;
主库和从库都要打开broker开关
172.25.0.11:
SQL> alter system set dg_broker_start=true;
172.25.0.10:
SQL> alter system set dg_broker_start=true;

在从库启动broker连接主库
dgmgrl
DGMGRL> connect sys/oracle@aux1srv
创建代理配置：
DGMGRL> create configuration 'testdg' as primary database is 'aux1' connect identifier is aux1srv;
将从库加入到代理配置
DGMGRL> add database 'demo' as connect identifier is db01srv maintained as physical;
查看broker配置
DGMGRL> show configuration verbose;
查看主从数据库配置
DGMGRL> show database verbose aux1;
DGMGRL> show database verbose demo;
启用broker代理配置
DGMGRL> enable configuration;
修改快速故障转移延迟时间
DGMGRL> edit configuration set property FastStartFailoverThreshold=10;
启动快速故障转移
DGMGRL> enable fast_start failover;
在从库启动监视器
dgmgrl
DGMGRL> connect sys/uplooking@aux1srv
DGMGRL> start observer;
在新的broker窗口查看broker配置
dgmgrl
DGMGRL> connect sys/uplooking@aux1srv
DGMGRL> show configuration verbose;

在新的主库修改归档路径状态将RESET变为enable
SQL> alter system set log_archive_dest_state_3='enable';
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
手工failover：
强制激活从库：
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;
shut immediate
startup
select database_role,protection_level from v$database;


将老的主库再转成从库：
在新的主库里面查询角色转换的scn
select STANDBY_BECAME_PRIMARY_SCN from v$database;
STANDBY_BECAME_PRIMARY_SCN
--------------------------
           1650349
将老的主库闪回到上面scn
startup mount
FLASHBACK DATABASE TO SCN 1650349;
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
shut immediate
startup
select database_role,protection_level from v$database;
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～