Oracle-Data-Guard之创建物理备库

发表于 | 分类于

第一. Creating a Physical Standby Database

1.1 实验背景

此处输入图片的描述

  • You have two servers (physical or VMs) with an operating system and Oracle installed on them. In this case I’ve used Oracle Linux 5.6 and Oracle Database 11.2.0.2.
  • The primary server has a running instance.
  • The standby server has a software only installation.
数据库 IP DB_SID DB_NAME DB_UNIQUE_NAME Oracle Net Service Name
Primary 0.0.0.188 mmpdb3 mmpdb3 mmpdb3 mmpdb3
Physical standby 0.0.0.189 mmpdb3 mmpdb3 mmpdb3sby mmpdb3sby

1.2 Primary Server Setup

1.2.1 Logging

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
#Check that the primary database is in archivelog mode.
SELECT log_mode FROM v$database;
LOG_MODE
------------
NOARCHIVELOG
#If it is noarchivelog mode, switch is to archivelog mode.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
#Enabled forced logging by issuing the following command.
ALTER DATABASE FORCE LOGGING;

1.2.2 Initialization Parameters

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
#Check the setting for the DB_NAME and DB_UNIQUE_NAME parameters. In this case they are both set to "mmpdb3" on the primary database.
SQL> show parameter db_name
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      mmpdb3
SQL> show parameter db_unique_name
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      mmpdb3
#The DB_NAME of the standby database will be the same as that of the primary, but it must have a different DB_UNIQUE_NAME value. The DB_UNIQUE_NAME values of the primary and standby database should be used in the DG_CONFIG setting of the LOG_ARCHIVE_CONFIG parameter. For this example, the standby database will have the value "mmpdb3sby".
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(mmpdb3,mmpdb3sby)';
#Set suitable remote archive log destinations. In this case I'm using the fast recovery area for the local location, but you could specify an location explicitly if you prefer. Notice the SERVICE and the DB_UNIQUE_NAME for the remote location reference the standby location.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=mmpdb3sby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=mmpdb3sby';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
#The LOG_ARCHIVE_FORMAT and LOG_ARCHIVE_MAX_PROCESSES parameters must be set to appropriate values and the REMOTE_LOGIN_PASSWORDFILE must be set to exclusive.
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
#In addition to the previous setting, it is recommended to make sure the primary is ready to switch roles to become a standby. For that to work properly we need to set the following parameters. Adjust the *_CONVERT parameters to account for your filename and path differences between the servers.
ALTER SYSTEM SET FAL_SERVER=mmpdb3sby;
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='mmpdb3sby','mmpdb3' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='mmpdb3sby','mmpdb3'  SCOPE=SPFILE;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
#Remember, some of the parameters are not modifiable, so the database will need to be restarted before they take effect.
shutdown immediate;
startup;

1.2.3 Service Setup

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
[oracle@hzvscmdb admin]$ more tnsnames.ora
MMPDB3 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.188)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mmpdb3.qa.webex.com)
    )
  )
mmpdb3sby =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.189)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = dedicated)
      (SERVICE_NAME = mmpdb3.qa.webex.com)
    )
  )

1.2.4 Create Standby Controlfile and PFILE

1
2
3
4
5
6
7
8
9
10
11
#Create a controlfile for the standby database by issuing the following command on the primary database.
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/home/oracle/dbbak/rman/mmpdb3sby_20170321.ctl';
#Create a parameter file for the standby database.
CREATE PFILE='/home/oracle/dbbak/rman/pfile_mmpdb3.ora' FROM SPFILE;
#Amend the PFILE making the entries relevant for the standby database.
*.DB_UNIQUE_NAME='mmpdb3sby'
*.FAL_SERVER=mmpdb3
*.FAL_CLIENT='mmpdb3sby'
*.log_archive_dest_2='SERVICE=mmpdb3 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=mmpdb3'

1.2.5 Create Standby Controlfile and PFILE

1
2
3
4
#The DUPLICATE command automatically creates the standby redo logs on the standby. To make sure the primary database is configured for switchover, we must create the standby redo logs on the primary server.
ALTER DATABASE ADD STANDBY LOGFILE ('/home/oracle/app/oracle/oradata/mmpdb3/standby_redo01.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/home/oracle/app/oracle/oradata/mmpdb3/standby_redo02.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/home/oracle/app/oracle/oradata/mmpdb3/standby_redo03.log') SIZE 50M;

1.3 Standby Server Setup (DUPLICATE)

1.3.1 Copy Files

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
#Create the necessary directories on the standby server.
$ mkdir -p /home/oracle/app/oracle/oradata/mmpdb3/
$ mkdir -p /home/oracle/app/oracle/fast_recovery_area/MMPDB3
$ mkdir -p /home/oracle/app/oracle/admin/mmpdb3/adump/
#Copy the files from the primary to the standby server
## Standby controlfile to all locations.
scp -r -p 22 oracle@0.0.0.188:/home/oracle/dbbak/rman/mmpdb3sby_20170321.ctl ./
cp mmpdb3sby_20170321.ctl /home/oracle/app/oracle/oradata/mmpdb3/control01.ctl
cp mmpdb3sby_20170321.ctl /home/oracle/app/oracle/fast_recovery_area/mmpdb3/control02.ctl
# Parameter file.
[oracle@hzvscmdb-stby dbs]$ pwd
/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs
scp -r -p 22 oracle@0.0.0.188:/home/oracle/dbbak/rman/pfile_mmpdb3.ora ./
# Remote login password file.
scp -r -p 22 oracle@0.0.0.188:/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/orapwmmpdb3 ./

1.3.2 Start Listener

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
#When using active duplicate, the standby server requires static listener configuration in a "listener.ora" file. In this case I used the following configuration.
[oracle@hzvscmdb-stby admin]$ more listener.ora
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hzvscmdb-stby.qa.webex.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = mmpdb3.qa.webex.com)
      (ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = mmpdb3)
    )
  )
ADR_BASE_LISTENER = /home/oracle/app/oracle
[oracle@hzvscmdb-stby admin]$ more tnsnames.ora
MMPDB3=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.188)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mmpdb3.qa.webex.com)
    )
  )
mmpdb3sby=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.189)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = dedicated)
      (SID = SERVICE_NAME = mmpdb3.qa.webex.com)
    )
  )
[oracle@hzvscmdb-stby admin]$ lsnrctl start
[oracle@hzvscmdb-stby admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 23-MAR-2017 01:08:46
Copyright (c) 1991, 2010, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hzvscmdb-stby.qa.webex.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                16-MAR-2017 02:42:48
Uptime                    6 days 22 hr. 25 min. 58 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /home/oracle/app/oracle/diag/tnslsnr/hzvscmdb-stby/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hzvscmdb-stby.qa.webex.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "mmpdb3.qa.webex.com" has 1 instance(s).
  Instance "mmpdb3", status UNKNOWN, has 1 handler(s) for this service...
Service "mmpdb3sby.qa.webex.com" has 1 instance(s).
  Instance "mmpdb3", status READY, has 1 handler(s) for this service...
The command completed successfully

1.3.3 Create Standby Using DUPLICATE

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
#Start the auxillary instance on the standby server by starting it using the temporary "init.ora" file.
$ export ORACLE_SID=mmpdb3
$ sqlplus / as sysdba
SQL> STARTUP NOMOUNT PFILE='/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/pfile_mmpdb3.ora';
#Connect to RMAN, specifying a full connect string for both the TARGET and AUXILLARY instances. DO not attempt to use OS authentication.
rman TARGET sys/pass@mmpdb3 AUXILIARY sys/pass@mmpdb3sby
DUPLICATE TARGET DATABASE
  FOR STANDBY
  FROM ACTIVE DATABASE
  DORECOVER
  SPFILE
    SET db_unique_name='mmpdb3sby' COMMENT 'Is standby'
    SET LOG_ARCHIVE_DEST_2='SERVICE=mmpdb3 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=mmpdb3'
    SET FAL_SERVER='mmpdb3' COMMENT 'Is primary'
  NOFILENAMECHECK;
  • FOR STANDBY: This tells the DUPLICATE command is to be used for a standby, so it will not force a DBID change.
  • FROM ACTIVE DATABASE: The DUPLICATE will be created directly from the source datafile, without an additional backup step.
  • DORECOVER: The DUPLICATE will include the recovery step, bringing the standby up to the current point in time.
  • SPFILE: Allows us to reset values in the spfile when it is copied from the source server.
  • NOFILENAMECHECK: Destination file locations are not checked.

1.4 Start Apply Process

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
#on the primary server
 SQL> select OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS from v$database;
OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- ---------------- --------------------
READ WRITE           PRIMARY          RESOLVABLE GAP
#on the standby server
SQL> select SEQUENCE#, APPLIED, STATUS from v$archived_log;
 SEQUENCE# APPLIED   S
---------- --------- -
      3024 YES       A
      3025 YES       A
      3026 YES       A
      3027 YES       A
      3028 YES       A
      3029 YES       A
      3030 NO        A
      3031 NO        A
      3032 NO        A
      3033 NO        A
      3034 NO        A
      3035 NO        A      
##Start the apply process on standby server.
##Background redo apply. Control is returned to the session once the apply process is started.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
SQL> select SEQUENCE#, APPLIED, STATUS from v$archived_log;
 SEQUENCE# APPLIED   S
---------- --------- -
      3024 YES       A
      3025 YES       A
      3026 YES       A
      3027 YES       A
      3028 YES       A
      3029 YES       A
      3030 YES       A
      3031 YES       A
      3032 YES       A
      3033 YES       A
      3034 YES       A
      3035 IN-MEMORY A
SQL> select SEQUENCE#, APPLIED, STATUS from v$archived_log;
 SEQUENCE# APPLIED   S
---------- --------- -
      3024 YES       A
      3025 YES       A
      3026 YES       A
      3027 YES       A
      3028 YES       A
      3029 YES       A
      3030 YES       A
      3031 YES       A
      3032 YES       A
      3033 YES       A
      3034 YES       A
      3035 YES       A
SQL> select OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS from v$database;
OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- ---------------- --------------------
MOUNTED              PHYSICAL STANDBY NOT ALLOWED
#on the primary server
SQL> select OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS from v$database;
OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- ---------------- --------------------
READ WRITE           PRIMARY          TO STANDBY

1.5 Test Log Transport

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
#On the primary server, check the latest archived redo log and force a log switch.
SQL> ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> SELECT sequence#, first_time, next_time
FROM   v$archived_log
ORDER BY sequence#;
 SEQUENCE# FIRST_TIME           NEXT_TIME
---------- -------------------- --------------------
      3035 22-MAR-2017 22:00:16 23-MAR-2017 00:00:40
      3035 22-MAR-2017 22:00:16 23-MAR-2017 00:00:40
SQL> alter system switch logfile;
System altered.
SQL> SELECT sequence#, first_time, next_time
FROM   v$archived_log
ORDER BY sequence#;
 SEQUENCE# FIRST_TIME           NEXT_TIME
---------- -------------------- --------------------
      3035 22-MAR-2017 22:00:16 23-MAR-2017 00:00:40
      3035 22-MAR-2017 22:00:16 23-MAR-2017 00:00:40
      3036 23-MAR-2017 00:00:40 23-MAR-2017 01:32:02
      3036 23-MAR-2017 00:00:40 23-MAR-2017 01:32:02
      
#Check the new archived redo log has arrived at the standby server and been applied.      
SQL> SELECT sequence#, first_time, next_time
FROM   v$archived_log
ORDER BY sequence#;
 SEQUENCE# FIRST_TIME           NEXT_TIME            APPLIED
---------- -------------------- -------------------- ---------
      3035 22-MAR-2017 22:00:16 23-MAR-2017 00:00:40 YES
      3036 23-MAR-2017 00:00:40 23-MAR-2017 01:32:02 YES

1.6 Test data standby database read only

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
#on the primary server check the data
SQL> select count(*) from mp.tlog;
  COUNT(*)
----------
  11000002
#on the primary server insert data
SQL> select count(*) from mp.tlog;
  COUNT(*)
----------
  12000002
SQL> select max(SEQUENCE#) from v$archived_log;
MAX(SEQUENCE#)
--------------
          3043
          
#需要等一会才能归档生成完
SQL> select max(SEQUENCE#) from v$archived_log;
MAX(SEQUENCE#)
--------------
          3045
#on the standby server check the data
SQL> SELECT sequence#, first_time, next_time, applied
FROM   v$archived_log
ORDER BY sequence#;
 SEQUENCE# FIRST_TIME           NEXT_TIME            APPLIED
---------- -------------------- -------------------- ---------
      3041 23-MAR-2017 01:39:52 23-MAR-2017 01:40:10 YES
      3042 23-MAR-2017 01:40:10 23-MAR-2017 01:40:28 YES
      3043 23-MAR-2017 01:40:28 23-MAR-2017 01:40:46 YES
      3044 23-MAR-2017 01:40:46 23-MAR-2017 01:54:44 IN-MEMORY
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open;
Database altered.
SQL> select OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS from v$database;
OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- ---------------- --------------------
READ ONLY            PHYSICAL STANDBY NOT ALLOWED
SQL> select count(*) from mp.tlog;
  COUNT(*)
----------
  11000002
  
SQL> SELECT sequence#, first_time, next_time, applied
FROM   v$archived_log
ORDER BY sequence#;
 SEQUENCE# FIRST_TIME           NEXT_TIME            APPLIED
---------- -------------------- -------------------- ---------
      3041 23-MAR-2017 01:39:52 23-MAR-2017 01:40:10 YES
      3042 23-MAR-2017 01:40:10 23-MAR-2017 01:40:28 YES
      3043 23-MAR-2017 01:40:28 23-MAR-2017 01:40:46 YES
      3044 23-MAR-2017 01:40:46 23-MAR-2017 01:54:44 IN-MEMORY
      
#发现SEQUENCE# 3044 还没有applied,
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS from v$database;
OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- ---------------- --------------------
READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED
SQL> SELECT sequence#, first_time, next_time, applied
FROM   v$archived_log
ORDER BY sequence#;
 SEQUENCE# FIRST_TIME           NEXT_TIME            APPLIED
---------- -------------------- -------------------- ---------
      3043 23-MAR-2017 01:40:28 23-MAR-2017 01:40:46 YES
      3044 23-MAR-2017 01:40:46 23-MAR-2017 01:54:44 YES
      3045 23-MAR-2017 01:54:44 23-MAR-2017 01:54:49 YES
      
SQL> select count(*) from mp.tlog;
  COUNT(*)
----------
  12000002