Oracle12C--DG搭建配置

|

一,主库前期操作

两台服务器,一台主库,一台从库

01,配置主库hosts

cat /etc/hosts
192.168.0.31 node12c01
192.168.0.32 node12c02

02,主库启动FORCE LOGGING

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
SQL>  select name,open_mode from v$pdbs;

NAME
--------------------------------------------------------------------------------
OPEN_MODE
----------
PDB$SEED
READ ONLY

ORCLPDB
MOUNTED

SQL>  select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
NO

SQL> alter database force logging;

Database altered.

SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
YES

03,启动归档模式

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
101
102
103
104
105
106
107
SQL>  archive log list;
Database log mode	       No Archive Mode
Automatic archival	       Disabled
Archive destination	       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Current log sequence	       3
SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 ORCLPDB			  MOUNTED
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size		    8795904 bytes
Variable Size		  671090944 bytes
Database Buffers	 1828716544 bytes
Redo Buffers		    7979008 bytes
Database mounted.
SQL>  alter database archivelog;

Database altered.

SQL>oh /orcl/app/oracle/oradata/orcl/archivelog

SQL> alter system set log_archive_dest_1='location=/orcl/app/oracle/oradata/orcl/archivelog';

SQL> archive log list
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       /orcl/app/oracle/oradata/orcl/archivelog
Oldest online log sequence     1
Next log sequence to archive   3
Current log sequence	       3

SQL> show parameter recovery;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest		     string	 /orcl/app/oracle/fast_recovery
						 _area/orcl
db_recovery_file_dest_size	     big integer 12780M
recovery_parallelism		     integer	 0
remote_recovery_file_dest	     string
SQL> alter system set db_recovery_file_dest_size=1G;

System altered.

SQL> show parameter recovery;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest		     string	 /orcl/app/oracle/fast_recovery
						 _area/orcl
db_recovery_file_dest_size	     big integer 1G
recovery_parallelism		     integer	 0
remote_recovery_file_dest	     string
SQL>  select name,open_mode from v$pdbs;

NAME
--------------------------------------------------------------------------------
OPEN_MODE
----------
PDB$SEED
MOUNTED

ORCLPDB
MOUNTED

SQL> alter database open;

Database altered.

SQL> select name,open_mode from v$pdbs;

NAME
--------------------------------------------------------------------------------
OPEN_MODE
----------
PDB$SEED
READ ONLY

ORCLPDB
MOUNTED


SQL>  alter pluggable database all open;

Pluggable database altered.

SQL> select name ,open_mode from v$pdbs;

NAME
--------------------------------------------------------------------------------
OPEN_MODE
----------
PDB$SEED
READ ONLY

ORCLPDB
READ WRITE

04,添加redo日志

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
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> select  group#, members,  bytes  from v$log;

    GROUP#    MEMBERS	   BYTES
---------- ---------- ----------
	 1	    1  209715200
	 2	    1  209715200
	 3	    1  209715200

SQL>  select  member from  v$logfile;

MEMBER
--------------------------------------------------------------------------------
/orcl/app/oracle/oradata/orcl/redo03.log
/orcl/app/oracle/oradata/orcl/redo02.log
/orcl/app/oracle/oradata/orcl/redo01.log

SQL> alter database add standby logfile '/orcl/app/oracle/oradata/orcl/stdredo01.log' size 50M;

Database altered.

SQL> alter database add standby logfile '/orcl/app/oracle/oradata/orcl/stdredo02.log' size 50M;

Database altered.

SQL> alter database add standby logfile '/orcl/app/oracle/oradata/orcl/stdredo03.log' size 50M;

Database altered.

SQL> alter database add standby logfile '/orcl/app/oracle/oradata/orcl/stdredo04.log' size 50M;

Database altered.

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/orcl/app/oracle/oradata/orcl/redo03.log
/orcl/app/oracle/oradata/orcl/redo02.log
/orcl/app/oracle/oradata/orcl/redo01.log
/orcl/app/oracle/oradata/orcl/stdredo01.log
/orcl/app/oracle/oradata/orcl/stdredo02.log
/orcl/app/oracle/oradata/orcl/stdredo03.log
/orcl/app/oracle/oradata/orcl/stdredo04.log

7 rows selected.

05,备份主库

创建备份目录

SQL> ho mkdir /home/oracle/dgback/

备份

RMAN> configure channel device type disk format '/home/oracle/dgback/%d_%I_%s_%p.bkp';
RMAN> backup as compressed backupset database include current controlfile for standby plus archivelog;

06,配置监听

主库和从库配置并且能ping 通

主库

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
[oracle@node12c01 ~]$ cat /orcl/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
# listener.ora Network Configuration File: /orcl/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /orcl/app/oracle/product/12.1.0/db_1/)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = node12c01)
      (ORACLE_HOME = /orcl/app/oracle/product/12.1.0/db_1)
      (SID_NAME = ORCL)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = node12c01_dgmgrl)
      (ORACLE_HOME = /orcl/app/oracle/product/12.1.0/db_1)
      (SID_NAME = ORCL)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = node12c)
      (ORACLE_HOME = /orcl/app/oracle/product/12.1.0/db_1)
      (SID_NAME = ORCL)
    )
  )

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

ADR_BASE_LISTENER = /orcl/app/oracle/
[oracle@node12c01 ~]$ cat /orcl/app/oracle/product/12.1.0/db_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /orcl/app/oracle/product/12.1.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.31)(PORT = 1521))

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.31)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = NODE12C01)
      (UR=A)
    )
  )
node12c01 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.31)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = NODE12C01)
      (UR=A)
    )
  )
node12c02 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.32)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = NODE12C02)
      (UR=A)
    )
  )

从库

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
[oracle@node12c02 ~]$ cat /orcl/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
# listener.ora Network Configuration File: /orcl/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /orcl/app/oracle/product/12.1.0/db_1/)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = node12c02)
      (SID_NAME = ORCL)
      (ORACLE_HOME = /orcl/app/oracle/product/12.1.0/db_1/)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = node12c01)
      (SID_NAME = ORCL)
      (ORACLE_HOME = /orcl/app/oracle/product/12.1.0/db_1/)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.32)(PORT = 1521))
    )
  )

[oracle@node12c02 ~]$ cat /orcl/app/oracle/product/12.1.0/db_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /orcl/app/oracle/product/12.1.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.32)(PORT = 1521))


ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.32)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME =  node12c02)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.31)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = node12c01)
    )
  )

相互ping 

tnsping node12c01
tnsping node12c02

07, 配置连接

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
SQL> show parameter name

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
cdb_cluster_name		     string	 NODE12C01
cell_offloadgroup_name		     string
db_file_name_convert		     string
db_name 			     string	 orcl
db_unique_name			     string	 NODE12C01
global_names			     boolean	 FALSE
instance_name			     string	 orcl
lock_name_space 		     string
log_file_name_convert		     string
pdb_file_name_convert		     string
processor_group_name		     string

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
service_names			     string	 NODE12C01

这些配置的话需要一 一对应
配置操作命令有下:

alter system set service_names='NODE12C02'; ---更改服务名
alter system set db_unique_name='NODE12C02' scope=spfile;
create spfile from pfile='/orcl/app/oracle/product/12.1.0/db_1/dbs/initorcl.ora'; --创建spfile
startup nomount pfile='/orcl/app/oracle/product/12.1.0/db_1/dbs/initorcl.ora'; --指定启动
要相互能连接上

[oracle@node12c02 admin]$ sqlplus sys/123456@NODE12C01 as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Apr 2 22:42:25 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[oracle@node12c01 admin]$ sqlplus sys/123456@NODE12C02 as sysdba

8,备库生成数据

1
2
3
4
5
6
7
8
9
10
11
[oracle@node12c02 admin]$ rman target sys/123456@NODE12C01 auxiliary sys/123456@NODE12C02

Recovery Manager: Release 12.2.0.1.0 - Production on Tue Apr 2 23:15:51 2019

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1532278336)
connected to auxiliary database: ORCL (not mounted)

RMAN>  
RMAN> duplicate target database for standby from active database nofilenamecheck;

没有错误显示就成功了

9,dgbroker

主备都开启

alter system set dg_broker_start=true

尝试进入主库

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
[oracle@node12c01 ecpect]$ dgmgrl sys/123456
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Tue Apr 2 23:22:55 2019

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected to "NODE12C01"
Connected as SYSDG.
DGMGRL>

把主库添加进dg配置 --->具体操作查看往期文章

DGMGRL> help create

Creates a broker configuration

Syntax:

  CREATE CONFIGURATION <configuration name> [AS]
    PRIMARY DATABASE IS <database name>
    CONNECT IDENTIFIER IS <connect identifier>;

DGMGRL> CREATE CONFIGURATION node12c as primary database is node12c01 connect identifier is node12c01;

Configuration "node12c" created with primary database "node12c01"

把备库添加进dg配置

DGMGRL> add database node12c02 as connect identifier is node12c02 maintained as physical;
DGMGRL> enable configuration  --启动配置