primary 库	standby 库
Ip	10.8.98.103	10.8.98.104
hostname	dbserver01	dbserver02
Oracle_sid	prod1	prod1
数据库版本	11.2.0.4	11.2.0.4
Db_name	prod	prod
Db_unique_name	prodpri	proddg
tnsnames	tns_primary	tns_standby

主机安装oracle，备机只需要安装数据库软件，不需要建库。

1、主机设置归档模式

SQL> archive log list;
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       /oracle/app/oracle/oradata/prod/arch
Oldest online log sequence     60
Next log sequence to archive   62
Current log sequence	       62

主机开启force logging 模式

SQL> alter database force logging;

2、主机添加standby log

alter database add standby logfile group 21('/oracle/app/oracle/oradata/prod/redo21_standby.log') size 50M;
alter database add standby logfile group 22('/oracle/app/oracle/oradata/prod/redo22_standby.log') size 50M;
alter database add standby logfile group 23('/oracle/app/oracle/oradata/prod/redo23_standby.log') size 50M;

3、创建主备库的参数文件，lisenter.ora,tnsnames.ora,并拷贝主机上密码文件到备机

主库

[oracle@dbserver01 dbs]$ cat initprod1.ora 
prod1.__db_cache_size=1358954496
prod1.__java_pool_size=16777216
prod1.__large_pool_size=603979776
prod1.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environment
prod1.__pga_aggregate_target=1610612736
prod1.__sga_target=2415919104
prod1.__shared_io_pool_size=0
prod1.__shared_pool_size=402653184
prod1.__streams_pool_size=0
*.audit_file_dest='/oracle/app/oracle/admin/prod/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/oracle/app/oracle/oradata/prod/control01.ctl','/oracle/app/oracle/oradata/prod/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='prod'
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prod1XDB)'
*.memory_target=4017094656
*.open_cursors=300
*.processes=1500
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=1655
*.undo_tablespace='UNDOTBS1'
#dg add
*.DB_UNIQUE_NAME=prodpri
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(prodpri,proddg)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/oracle/app/oracle/oradata/prod/arch  valid_for=(all_logfiles,all_roles)  db_unique_name=prodpri'
*.log_archive_format='ARC_%T_%S_%R.arc'
*.LOG_ARCHIVE_DEST_2='SERVICE=tns_standby LGWR  SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=proddg'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER=tns_standby
*.FAL_CLIENT=tns_primary
*.STANDBY_FILE_MANAGEMENT=AUTO
*.log_archive_max_processes=30
*.db_file_name_convert='/oracle/app/oracle/oradata/prod/','/oracle/app/oracle/oradata/prod/'
*.log_file_name_convert='/oracle/app/oracle/oradata/prod/','/oracle/app/oracle/oradata/prod/'

lisenter.ora

[oracle@dbserver01 admin]$ cat listener.ora 
# listener.ora Network Configuration File: /oracle/app/oracle/product/11.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
  (SID_LIST =
     (SID_DESC =
      (GLOBAL_DBNAME = prod)
      (ORACLE_HOME = /oracle/app/oracle/product/11.2.0)
      (SID_NAME = prod1))
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.8.98.103)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
ADR_BASE_LISTENER = /oracle/app/oracle
[oracle@dbserver01 admin]$

tnsnames.ora( 主机备机相同)

[oracle@dbserver01 admin]$ cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /oracle/app/oracle/product/11.2.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
tns_primary =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.8.98.103)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prod)
    )
  )
tns_standby =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.8.98.104)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prod)
    )
  )
[oracle@dbserver01 admin]$

备机

初始化参数

[oracle@dbserver02 dbs]$ cat initprod1.ora 
prod1.__db_cache_size=1929379840
prod1.__java_pool_size=16777216
prod1.__large_pool_size=33554432
prod1.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environment
prod1.__pga_aggregate_target=1610612736
prod1.__sga_target=2415919104
prod1.__shared_io_pool_size=0
prod1.__shared_pool_size=402653184
prod1.__streams_pool_size=0
*.audit_file_dest='/oracle/app/oracle/admin/prod/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/oracle/app/oracle/oradata/prod/control01.ctl','/oracle/app/oracle/oradata/prod/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='prod'
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prod1XDB)'
*.memory_target=4017094656
*.open_cursors=300
*.processes=1500
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=1655
*.undo_tablespace='UNDOTBS1'
#dg add
*.DB_UNIQUE_NAME=proddg
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(prodpri,proddg)'
*.log_archive_format='ARC_%T_%S_%R.arc'
*.LOG_ARCHIVE_DEST_1='LOCATION=/oracle/app/oracle/oradata/prod/arch  valid_for=(all_logfiles,all_roles)  db_unique_name=proddg'
*.LOG_ARCHIVE_DEST_2='SERVICE=tns_primary LGWR  SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prodpri'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER=tns_primary
*.FAL_CLIENT=tns_standby
*.STANDBY_FILE_MANAGEMENT=AUTO
*.log_archive_max_processes=30
*.db_file_name_convert='/oracle/app/oracle/oradata/prod/','/oracle/app/oracle/oradata/prod/'
*.log_file_name_convert='/oracle/app/oracle/oradata/prod/','/oracle/app/oracle/oradata/prod/'
[oracle@dbserver02 dbs]$

lisenter.ora

[oracle@dbserver02 admin]$ cat listener.ora 
# listener.ora Network Configuration File: /oracle/app/oracle/product/11.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
  (SID_LIST =
     (SID_DESC =
      (GLOBAL_DBNAME = prod)
      (ORACLE_HOME = /oracle/app/oracle/product/11.2.0)
      (SID_NAME = prod1))
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.8.98.104)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
ADR_BASE_LISTENER = /oracle/app/oracle
[oracle@dbserver02 admin]$

拷贝主机的密码文件到备机相应目录

主机备机根据参数文件initprod1.ora创建相应的文件夹

mkdir -p /oracle/app/oracle/admin/prod/adump
mkdir -p /oracle/app/oracle/oradata/prod/
mkdir -p /oracle/app/oracle/oradata/prod/arch

4、测试tnsping  ok，主备机都启动到nomount状态，均创建spfile

然后主机打开数据库，备机打开到nomount状态。

SQL> create spfile from pfile;

5、实施数据库克隆通过rman duplicate (在备机操作)

[oracle@dbserver02 prod]$ rman target sys/oracle@tns_primary auxiliary sys/oracle@tns_standby
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Aug 27 15:04:42 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: PROD (DBID=493196996)
connected to auxiliary database: PROD (not mounted)
RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK;
恢复完成之后，打开数据库，并且开启实时应用同步
SQL> alter database open;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.

主机查看状态

SQL> SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS,protection_level FROM V$DATABASE;
OPEN_MODE	     DATABASE_ROLE    SWITCHOVER_STATUS    PROTECTION_LEVEL
-------------------- ---------------- -------------------- --------------------
READ WRITE	     PRIMARY	      FAILED DESTINATION   MAXIMUM PERFORMANCE

如果报

SWITCHOVER_STATUS   为   FAILED DESTINATION

重启下主机的数据库服务

6、查看主机备机状态，确认dataguard搭建完成。

主机

SQL> SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS,protection_level FROM V$DATABASE;
OPEN_MODE	     DATABASE_ROLE    SWITCHOVER_STATUS    PROTECTION_LEVEL
-------------------- ---------------- -------------------- --------------------
READ WRITE	     PRIMARY	      TO STANDBY	   MAXIMUM PERFORMANCE

备机

SQL> SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS,protection_level FROM V$DATABASE;
OPEN_MODE	     DATABASE_ROLE    SWITCHOVER_STATUS    PROTECTION_LEVEL
-------------------- ---------------- -------------------- --------------------
READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED	   MAXIMUM PERFORMANCE

7、验证

主机创建一个tablespace，一个账户，一个表，插入一条数据，到备机查看

主机

SQL> create  tablespace test datafile '/oracle/app/oracle/oradata/prod/test01.dbf'  size 100m  autoextend on next 100m;
Tablespace created.
SQL> create user test identified by test default tablespace test;
User created.
SQL> grant dba to test;
Grant succeeded.
SQL> conn test
Enter password: 
Connected.
SQL> create table tt(id int);
Table created.
SQL> insert into tt values(10);
1 row created.
SQL> commit;
Commit complete.

备机查询

SQL> conn test
Enter password: 
Connected.
SQL> select * from tt;
	ID
----------
	10
SQL>

如果文章对你有用，给点个赞吧。