redhat_7.2使用RMAN部署oracle_11.2.0.4 physical standby datagard

一、安装环境

--------------------主库-----------------------
操作系统 linux_redhat_7.2_64位
hostname oracle
数据库 oracle_11.2.0.4
IP 192.168.16.55
oracle_base /app/oracle
oracle_home /app/oracle/product/11.2.4/db_1
oracle_sid orcl
归档模式 archive
数据库安装 安装数据库软件，创建监听，建库

--------------------备库----------------------------
操作系统 linux_redhat_7.2_64位
hostname standby
数据库 oracle_11.2.0.4
IP 192.168.16.56
oracle_base /app/oracle
oracle_home /app/oracle/product/11.2.4/db_1
oracle_sid orcl
归档模式 noarchive
数据库安装 安装数据库软件，创建监听，不建库

由于SID区分大小写，所有的sid都是orcl //注意事项！！！
二、主库配置：

#开启归档并强制日志模式：
SQL> startup mount;
SQL> alter database archivelog;
SQL> archive log list;
SQL> alter database force logging;
SQL> select name,log_mode,force_logging from v$database;
SQL> alter database open;

#创建standby redolog日志组：
注意：

1：standby redo log的文件大小与primary 数据库online redo log 文件大小相同
2：standby redo log日志文件组的个数依照下面的原则进行计算：
Standby redo log组数公式>=(每个instance日志组个数+1)*instance个数;
假如只有一个节点，这个节点有三组redolog;
所以Standby redo log组数>=(3+1)*1 == 4,所以至少需要创建4组Standby redo log 。

#查看当前线程与日志组的对应关系及日志组的大小：
SQL> select thread#,group#,bytes/1024/1024 from vKaTeX parse error: Expected 'EOF', got '#' at position 6: log; #̲查看日志的存放目录 SQL> …logfile;

#创建standby redolog日志组
alter database add standby logfile group 4(’/app/oracle/oradata/orcl/standbyredo04.log’) size 50m;
alter database add standby logfile group 5(’/app/oracle/oradata/orcl/standbyredo05.log’) size 50m;
alter database add standby logfile group 6(’/app/oracle/oradata/orcl/standbyredo06.log’) size 50m;
alter database add standby logfile group 7(’/app/oracle/oradata/orcl/standbyredo07.log’) size 50m;

#查看standby 日志组的信息：
SQL> select group#,sequence#,status, bytes/1024/1024 from v$standby_log;

#创建主库密码文件：
[oracle@oracle ~]$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle force=y

#配置spfile文件：
SQL> show parameter spfile;
SQL> create pfile=’/tmp/initorcl.ora’ from spfile;
[oracle@oracle dbs]$ vi /tmp/initorcl.ora
orcl.__db_cache_size=348127232
orcl.__java_pool_size=4194304
orcl.__large_pool_size=8388608
orcl.__oracle_base=’/app/oracle’
orcl.__pga_aggregate_target=335544320
orcl.__sga_target=499122176
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=130023424
orcl.__streams_pool_size=0
*.audit_file_dest=’/app/oracle/admin/orcl/adump’
*.audit_trail=‘db’
*.compatible=‘11.2.0.4.0’
*.control_files=’/app/oracle/oradata/orcl/control01.ctl’,’/app/oracle/oradata/orcl/control02.ctl’
*.db_block_size=8192
*.db_domain=’’
*.db_name=‘orcl’ ----注意------
*.diagnostic_dest=’/app/oracle’
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=orclXDB)’
*.memory_target=831520768
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=‘EXCLUSIVE’
*.undo_tablespace=‘UNDOTBS1’
*.db_unique_name=‘orclpr’ //添加内容
*.fal_client=‘orclpr’ //添加内容
*.fal_server=‘orcldg’ //添加内容
*.standby_file_management=‘AUTO’ //添加内容
*.log_archive_config=‘DG_CONFIG=(orclpr,orcldg)’ //添加内容
*.log_archive_dest_1=‘location=/app/oracle/oradata/orcl/archivelog’ //添加内容
*.log_archive_dest_2=‘SERVICE=orcldg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg’ //添加内容
*.log_archive_dest_state_1=‘ENABLE’ //添加内容
*.log_archive_dest_state_2=‘ENABLE’ //添加内容

wq!
#创建归档目录
[oracle@oracle orcl]$ mkdir /app/oracle/oradata/orcl/archivelog

#由pfile生成新的spfile:
SQL> shutdown immediate;
[oracle@oracle dbs]$ cp spfileorcl.ora spfileorcl.ora.bak
SQL> create spfile from pfile=’/tmp/initorcl.ora’;
SQL> startup
#查看修改的内容是否成功
SQL> show parameter dest
SQL> show parameter name

#添加监听：
[oracle@oracle dbs]$ netca
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.16.55)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

ADR_BASE_LISTENER = /app/oracle
#添加静态监听
[oracle@oracle dbs]$ netmgr
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /app/oracle/product/11.2.4/db_1)
(SID_NAME = orcl)
)
)

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

ADR_BASE_LISTENER = /app/oracle
#重启监听服务：
lsnrctl stop
lsnrctl start
lsnrctl status

#编辑网络服务名配置文件tnsnames.ora：
[oracle@oracle admin]$ vi $ORACLE_HOME/network/admin/tnsnames.ora

ORCLPR =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.16.55)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)

ORCLDG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.16.56)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)

wq!

三、备库配置：
#将主库中的密码文件、pfile文件、监听文件复制到备库中：
[oracle@oracle admin]$ cd $ORACLE_HOME/dbs
[oracle@oracle dbs]$ scp orapworcl 192.168.16.56:/app/oracle/product/11.2.4/db_1/dbs
[oracle@oracle dbs]$ scp /tmp/initorcl.ora 192.168.16.56:/tmp/
[oracle@oracle dbs]$ cd $ORACLE_HOME/network/admin
[oracle@oracle admin]$ scp listener.ora 192.168.16.56:/app/oracle/product/11.2.4/db_1/network/admin
[oracle@oracle admin]$ scp tnsnames.ora 192.168.16.56:/app/oracle/product/11.2.4/db_1/network/admin

#配置spfile文件：
[oracle@standby dbs]$ vi /tmp/initorcl.ora
orcl.__db_cache_size=348127232
orcl.__java_pool_size=4194304
orcl.__large_pool_size=8388608
orcl.__oracle_base=’/app/oracle’
orcl.__pga_aggregate_target=335544320
orcl.__sga_target=499122176
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=130023424
orcl.__streams_pool_size=0
*.audit_file_dest=’/app/oracle/admin/orcl/adump’
*.audit_trail=‘db’
*.compatible=‘11.2.0.4.0’
*.control_files=’/app/oracle/oradata/orcl/control01.ctl’,’/app/oracle/oradata/orcl/control02.ctl’
*.db_block_size=8192
*.db_domain=’’
*.db_name=‘orcl’ ----注意------
*.diagnostic_dest=’/app/oracle’
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=orclXDB)’
*.memory_target=831520768
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=‘EXCLUSIVE’
*.undo_tablespace=‘UNDOTBS1’
*.db_unique_name=‘orcldg’ ----注意修改----
*.fal_client=‘orcldg’ ----注意修改----
*.fal_server=‘orclpr’ ----注意修改----
*.standby_file_management=‘AUTO’
*.log_archive_config=‘DG_CONFIG=(orclpr,orcldg)’
*.log_archive_dest_1=‘location=/app/oracle/oradata/orcl/archivelog’ ----注意修改----
*.log_archive_dest_2=‘SERVICE=orclpr LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclpr’ ----注意修改----
*.log_archive_dest_state_1=‘ENABLE’
*.log_archive_dest_state_2=‘ENABLE’

#手工创建所需的目录：
su - oracle
[oracle@oracle orcl]$ mkdir -p /app/oracle/oradata/orcl/archivelog
[oracle@oracle orcl]$ mkdir -p /app/oracle/admin/orcl/adump
[oracle@oracle orcl]$ mkdir -p /app/oracle/admin/orcl/dpdump
[oracle@oracle orcl]$ mkdir -p /app/oracle/admin/orcl/pfile

#由pfile生成spfile:
SQL> create spfile from pfile=’/tmp/initorcl.ora’;
SQL> startup nomount;

#修改监听文件：
[oracle@standby ~]$ vi $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /app/oracle/product/11.2.4/db_1)
(SID_NAME = orcl)
)
)

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

ADR_BASE_LISTENER = /app/oracle

#编辑网络服务名配置文件tnsnames.ora：
[oracle@oracle admin]$ vi $ORACLE_HOME/network/admin/tnsnames.ora

ORCLPR =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.16.55)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)

ORCLDG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.16.56)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)

wq!
#启动监听服务
lsnrctl start

#tnsping测试： //主库和备库都互相ping
tnsping orclpr
tnsping orcldg

#启动备库到nomount状态，利用RMAN在备库上恢复主库：
rman target sys/oracle@orclpr auxiliary sys/oracle@orcldg

duplicate target database for standby from active database nofilenamecheck;

#复制结束后报下面的错误
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 1 thread 1: ‘/app/oracle/oradata/orcl/redo01.log’

RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 2 thread 1: ‘/app/oracle/oradata/orcl/redo02.log’

#解决方法：
方法#1：如果不考虑switchover（备库上不创建online reod log），那么可以忽略这个错误，因为这个错只是一个提示性的信息，不会影响备库的MRP的工作。
方法#2：如果考虑switchover，在备库上创建online reod log，并且设置log_file_name_convert参数： //后来的主备切换测试，发现redo log和standby log 会自动创建。

#恢复完成后数据库是mount状态
SQL> select status from v$instance;

SQL> alte database shutdown immediate;

SQL> alter database mount standby database;

SQL> alter database recover managed standby database disconnect from session;
SQL> select name,open_mode,database_role,protection_mode from v$database;
SQL> alter database recover managed standby database cancel;
SQL> alter database open read only;

SQL> recover managed standby database disconnect from session; //只读还原管理模式
SQL> select name,open_mode,database_role,protection_mode,switchover_status from v$database;

—测试看日志是否传送成功。
主库：
#先建立一个用户user1并授权
SQL> create user user1 identified by user1;
SQL> grant connect,resource to user1;
SQL> create table tab1(id number,name varchar2(20));
SQL> insert into tab1 values(1,‘Tom’);
SQL> commit;

#对日志进行切换
Sql> alter system switch logfile;
Sql> select max(SEQUENCE#) from v$archived_log;

备库：
select first_time,next_time, applied,sequence# from v$archived_log order by sequence#;

SELECT MAX(R.SEQUENCE#) LAST_SEQ_RECD, MAX(L.SEQUENCE#) LAST_SEQ_SENT FROM V$ARCHIVED_LOG R, V$LOG L WHERE R.DEST_ID=2 AND L.ARCHIVED=‘YES’;
检查STANDBY LOG分配情况
select GROUP#,THREAD#,SEQUENCE#,USED,ARCHIVED,STATUS from v$standby_log;

SQL> select username from dba_users;
SQL> conn user1/user1
SQL> select * from tab1;
ID NAME

---

 1 Tom