oracle 11g dg 部署rman方式要點記錄

1、環(huán)境介紹

2、修改hostname

hostnamectl set-hostname oraclep

3、db部署

主：創(chuàng)建庫，從：不創(chuàng)建數(shù)據(jù)庫
就是沒有dbca那一步，網(wǎng)絡(luò)和基礎(chǔ)環(huán)境還是需要的

4、主庫開啟歸檔并設(shè)置強制日志 force logging

SQL> shutdown immediate

停止數(shù)據(jù)庫操作

startup mount

啟動到mount狀態(tài)

alter database archivelog;

開啟歸檔

alter database force logging;

強制記錄日志，即對數(shù)據(jù)庫中的所有操作都產(chǎn)生日志信息，并將該信息寫入到聯(lián)機重做日志文件。

alter database open;

打開數(shù)據(jù)庫

archive log list;

想查看數(shù)據(jù)的歸檔模式

select force_logging from v$database;

確認是否為強制日志

5、主庫添加standby redo log

select member from v$logfile;

查看redo和standby redo

select * from v$log;

查看redo情況

alter database add standby logfile group 21 '/u01/app/oradata/yunhaip/standby21.log' size 50M;
alter database add standby logfile group 22 '/u01/app/oradata/yunhaip/standby22.log' size 50M;
alter database add standby logfile group 23 '/u01/app/oradata/yunhaip/standby23.log' size 50M;
alter database add standby logfile group 24 '/u01/app/oradata/yunhaip/standby24.log' size 50M;

增一組大小為50M的standby redo，這里的group號不得與online redo重復,正式環(huán)境文件大小需要調(diào)整

6、配置文件修改
6.1、主庫pfile創(chuàng)建，以便做出修改

SQL>create pfile from spfile;
SQL> host
[oracle@oraclep ~]$ cd $ORACLE_HOME/dbs
[oracle@oraclep dbs]$ pwd
/u01/app/oracle/product/11.2.0/db_1/dbs
cat >> /u01/app/oracle/product/11.2.0/db_1/dbs/inityunhaip.ora << "EOF"
*.db_unique_name='yunhaip'
*.fal_server='yunhais'
*.log_archive_config='dg_config=(yunhaip,yunhais)'
*.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles, all_roles) db_unique_name=yunhaip'
*.log_archive_dest_2='service=yunhais lgwr async valid_for=(online_logfile,primary_role) db_unique_name=yunhais'
*.log_archive_dest_state_1=ENABLE
*.log_archive_dest_state_2=ENABLE
*.standby_file_management='AUTO'
*.db_file_name_convert='/u01/app/oradata/yunhaip','/u01/app/oradata/yunhaip'
*.log_file_name_convert='/u01/app/oradata/yunhaip','/u01/app/oradata/yunhaip'
EOF

6.2、拷貝主庫的pfile到從庫，并修改如下內(nèi)容：

[oracle@oraclep dbs]$ pwd
/u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@oraclep dbs]$ scp inityunhaip.ora
192.168.56.117:/u01/app/oracle/product/11.2.0/db_1/dbs/

cat >> /u01/app/oracle/product/11.2.0/db_1/dbs/inityunhaip.ora << "EOF"
*.db_unique_name='yunhais'
*.fal_server='yunhaip'
*.log_archive_config='dg_config=(yunhaip,yunhais)'
*.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles, all_roles) db_unique_name=yunhais'
*.log_archive_dest_2='service=yunhaip lgwr async valid_for=(online_logfile,primary_role) db_unique_name=yunhaip'
*.log_archive_dest_state_1=ENABLE
*.log_archive_dest_state_2=ENABLE
*.standby_file_management='AUTO'
*.db_file_name_convert='/u01/app/oradata/yunhaip','/u01/app/oradata/yunhaip'
*.log_file_name_convert='/u01/app/oradata/yunhaip','/u01/app/oradata/yunhaip'
EOF

6.3、說明：

dg_config=(yunhaip,yunhais) 以外，其他情況主從的相關(guān)信息對調(diào)即可

6.4、創(chuàng)建新的主庫spfile文件，并重新啟動主庫

SQL> shutdown immediate
SQL> create spfile from pfile;
SQL> startup
SQL> ALTER USER SYS IDENTIFIED BY sys;

修改sys密碼為以后rman連接使用

6.5、 復制主庫的密碼文件到備庫

scp orapwyunhaip 192.168.56.117:/u01/app/oracle/product/11.2.0/db_1/dbs/

7、從庫創(chuàng)建相關(guān)目錄

strings spfileyunhaip.ora

獲得目錄，我觀察的是主庫的

mkdir -p /u01/app/oracle
mkdir -p /u01/app/admin/yunhaip/{a,b,c,d,u}dump
mkdir -p /u01/app/oradata/yunhaip/
mkdir -p /u01/app/fast_recovery_area/yunhaip/

8、創(chuàng)建tnsnames.ora ，主從一致即可

cat >> /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora << "EOF"
yunhaip =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.118)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = yunhaip)
)
)

yunhais =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.117)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = yunhais)
)
)
EOF

9、修改備份庫的listener.ora

cat >> /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora << "EOF"
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = yunhais)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = yunhaip)
)
)

EOF

原因如下：
[oracle@oracles ~]$ rman target sys/sys@yunhaip auxiliary sys/sys@yunhais
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jul 3 15:43:07 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: YUNHAIP (DBID=665781658)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04006: error from auxiliary database: ORA-12528: TNS:listener: all appropriate instances are blocking new connections

10、嘗試啟動從庫到nomount

SQL> create spfile from pfile;
SQL> startup nomount

11、RMAN復制主庫到備庫
11.1、首先RMAN連接到主數(shù)據(jù)庫和備數(shù)據(jù)庫

rman target sys/sys@yunhaip auxiliary sys/sys@yunhais

如果有相關(guān)報錯，請注意9，6.4步驟

11.2、使用RMAN的duplicate命令進行復制，兩邊目錄結(jié)構(gòu)相同，需要添加nofilenamecheck參數(shù)

duplicate target database for standby from active database nofilenamecheck;

12、復制完畢，對從庫進行相關(guān)處理

select status from v$instance;

查詢從庫是否處于MOUNTED狀態(tài)

alter database recover managed standby database using current logfile disconnect from session;

在備庫開啟實時日志應(yīng)用

13、觀察主從正確狀態(tài)
13.1、觀察主庫alert日志

vim alert_yunhaip.log

Error 12154 received logging on to the standby發(fā)現(xiàn)這個錯誤

13.2、重啟主庫

SQL> shutdown immediate;
SQL> startup;

13.3觀察主庫狀態(tài)：

SQL> select switchover_status,database_role from v$database;

SWITCHOVER_STATUS DATABASE_ROLE

---

TO STANDBY PRIMARY

13.4、觀察從庫狀態(tài)

SQL> select switchover_status,database_role from v$database;

SWITCHOVER_STATUS DATABASE_ROLE

---

NOT ALLOWED PHYSICAL STANDBY

14、通過切換日志觀察同步情況
14.1、主庫切換

SQL> archive log list;
SQL> alter system switch logfile;
SQL> archive log list;

14.2、從庫觀察

SQL> archive log list;

15、從庫open，以便用戶能夠讀取

alter database recover managed standby database cancel;
alter database open;
alter database recover managed standby database using current logfile disconnect ;

[oracle@oracles ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 3 15:59:46 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect ;
Database altered.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

16、實驗過程
16.1、觀察從庫現(xiàn)有數(shù)據(jù)

[oracle@oracles ~]$ sqlplus test/test
SQLPlus: Release 11.2.0.3.0 Production on Wed Jul 3 16:01:59 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select from test;
ID NUMS

---

1 2
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracles ~]$ exit
登出
Connection to 192.168.56.117 closed.

16.2、主庫添加新數(shù)據(jù)

[oracle@oraclep trace]$ sqlplus test/test
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 3 16:02:19 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> insert into test values(2,2);
1 row created.
SQL> commit;
Commit complete.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

16.3、從庫觀察新數(shù)據(jù)

[oracle@oraclep trace]$ ssh 192.168.56.117
oracle@192.168.56.117's password:
Last login: Wed Jul 3 16:01:52 2019 from 192.168.56.118
[oracle@oracles ~]$ sqlplus test/test
SQLPlus: Release 11.2.0.3.0 Production on Wed Jul 3 16:02:36 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select from test;
ID NUMS

---

1 2
2 2

補充：
補充一、 只讀方式開始從庫

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area  768294912 bytes
Fixed Size                  2232312 bytes
Variable Size             452984840 bytes
Database Buffers          310378496 bytes
Redo Buffers                2699264 bytes
SQL> alter database mount standby database;

Database altered.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracles ~]$ sqlplus test/test   

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 4 09:32:48 2019

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select *from test;

    ID       NUMS
---------- ----------
     1          2
     2          2
     2          3
     2          4

SQL> select *from test;

    ID       NUMS
---------- ----------
     1          2
     2          2
     2          3
     2          4
     2          5

SQL>