Oracle 19c物理備庫搭建

1.設定主庫

代碼如下（示例）：

SYS@orcl>startup mount;
SYS@orcl>alter database archivelog;
SYS@orcl>alter database open;
確認歸檔模式
SYS@orcl>archive log list;
查詢實體的歸檔模式和附加日志
SYS@orcl>select log_mode,force_logging from v$database;
開啟附加日志模式
alter database set standby nologging for data availability; --高可用模式
alter database set standby nologging for load performance； --性能模式
查詢redo日志數量
SYS@orcl>select group#,status,type,member from v$logfile;
查詢redo日志容量
SYS@orcl>select group# ,sequence#, bytes/104448,status from v$log;
給主庫創建stand by的redo日志組，方便角色切換為備庫時使用
容量要和redo相同，數量是redo當前數量+1
SYS@orcl>alter database add standby logfile group 4 ('/u01/app/oracle/oradata/ORCL/stredo04.log') size 200m;
Database altered.

SYS@orcl>alter database add standby logfile group 6 ('/u01/app/oracle/oradata/ORCL/stredo06.log') size 200m;

Database altered.

SYS@orcl>alter database add standby logfile group 7 ('/u01/app/oracle/oradata/ORCL/stredo07.log') size 200m;

Database altered.
確認standby redo logfile創建成功
SYS@orcl>select group#,status,used from v$standby_log;

2.設定備庫

代碼如下（示例）：

復制主庫密碼檔案和引數檔案到備庫改名為
initstddb.ora（create spfile的時候要把原來那個挪走，然后把這個改成initorcl.ora才能成功）
orapwstddb

備庫生成spfile并啟動實體到nomount
SYS@stddb> shutdown immediate;
SYS@stddb> create spfile from pfile;
SYS@stddb> startup nomount;

3.修改備庫的pfile

*.audit_file_dest='/u01/app/oracle/admin/stddb/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/stddb/control01.ctl','/u01/app/oracle/fast_recovery_area/stddb/control02.ctl'
*.db_block_size=8192
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=12732m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.enable_pluggable_database=true
*.local_listener='LISTENER_ORCL'
*.memory_target=1507m
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=3000
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'


*.db_unique_name='stddb'
*.log_archive_config='dg_config=(pridb,stddb)'
*.fal_client='stddb' --調換位置與主庫相反
*.fal_server='pridb'
本地歸檔檔案路徑
*.log_archive_dest_1='location=/u01/app/oracle/arch/stddb valid_for=(all_logfiles,all_roles) db_unique_name=stddb'
遠程歸檔檔案傳輸給的服務名和唯一庫ID-這里要填寫主庫的
*.log_archive_dest_2='service=pridb valid_for=(online_logfiles,primary_role) db_unique_name=pridb'
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
這里前面要填主庫的本機絕對路徑，后面為備庫的轉換路徑，格式要一致，要么都加'/'，要么都不加
*.log_file_name_convert='/u01/app/oracle/oradata/ORCL/','/u01/app/oracle/oradata/stddb/'
*.db_file_name_convert='/u01/app/oracle/oradata/ORCL/','/u01/app/oracle/oradata/stddb/'
STANDBY_FILE_MANAGEMENT=AUTO

4.創建備庫需要的檔案夾

mkdir -p /u01/app/oracle/admin/stddb/adump
mkdir -p /u01/app/oracle/oradata/stddb/
mkdir -p /u01/app/oracle/arch/stddb/
mkdir -p /u01/app/oracle/oradata/stddb/
mkdir -pv /u01/app/oracle/oradata/stddb/{orclpdb,pdbseed}

5.修改主庫和備庫的監聽

主庫listener.ora

LISTENER =
 (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = oracle19c)(PORT = 1521))
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
   )
 )
SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC =
     (GLOBAL_DBNAME = pridb.up.com)
     (ORACLE_HOME = /u01/app/oracle/product/19c/dbhome_1)
     (SID_NAME = orcl)
   )
 (SID_DESC =
     (GLOBAL_DBNAME = stddb.up.com)
     (ORACLE_HOME = /u01/app/oracle/product/19c/dbhome_1)
     (SID_NAME = orcl)
   )
 )

主庫tnsnames.ora

LISTENER_ORCL =
 (ADDRESS = (PROTOCOL = TCP)(HOST = oracle19c)(PORT = 1521))
ORCL =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = oracle19c)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = orcl)
   )
 )
ORCLPDB =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = oracle19c)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = orclpdb)
   )
 )

STDDB =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.0.201)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = stddb.up.com)
   )
 )
PRIDB =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.0.200)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = pridb.up.com)
   )
 )

備庫listener.ora

LISTENER =
 (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = oracle19c2)(PORT = 1521))
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
   )
 )
SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC =
     (GLOBAL_DBNAME = stddb.up.com)
     (ORACLE_HOME = /u01/app/oracle/product/19c/dbhome_1)
     (SID_NAME = orcl)
   )
 )

備庫tnsnames.ora

LISTENER_ORCL =
 (ADDRESS = (PROTOCOL = TCP)(HOST = oracle19c2)(PORT = 1521))
ORCL =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = oracle19c2)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = orcl)
   )
 )
ORCLPDB =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = oracle19c2)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = orclpdb)
   )
 )

STDDB =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.0.201)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = stddb.up.com)
   )
 )
PRIDB =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.0.200)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = pridb.up.com)
   )
 )

6.測驗網路互通性

[oracle@主庫 ~]$ tnsping stddb
[oracle@備庫 ~]$ tnsping pridb
測驗登錄
sqlplus sys/oracle@stddb as sysdba
sqlplus sys/oracle@pridb as sysdba

7.使用RMAN備份主庫

mkdir ~/dgback --建立備份目錄
rman target /
關閉主庫的控制檔案自動備份功能（19c默認開啟）
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP off;
指定備份目錄
configure channel device type disk format '/home/oracle/dgback/%d_%I_%s_%p.bkp';
執行備份
backup as compressed backupset database include current controlfile for standby plus archivelog;

8.restore備庫

拷貝資料檔案到備庫
[oracle@oracle19c ~/dgback]$ scp * 172.16.0.201:${PWD}

執行備庫恢復
備庫上需要把主庫拷貝來的pfile改名為當前實體initorcl.ora
然后在nomount模式下執行
create spfile from pfile
啟動RMAN 連接目標庫與輔助資料庫
rman target sys/oracle@pridb auxiliary sys/oracle@stddb
執行duplicate程序
RMAN> duplicate target database for standby;

9.recovery備庫

開啟備庫
SYS@orcl>alter database open;
查看資料庫角色
SYS@orcl>select database_role from v$database;
查看歸檔狀態
SYS@orcl>select sequence#,applied from v$archived_log order by sequence#;
開啟日志應用服務
alter database recover managed standby database disconnect from session;
在主庫切換幾次日志
SYS@pridb>alter system switch logfile;
然后回到備庫確認主庫的歸檔檔案已經在備庫參考
SYS@stddb>select sequence#,applied from v$archived_log order by sequence#;
查看資料庫狀態
SYS@stddb>select name,open_mode from v$database;
停止日志應用服務
alter database recover managed standby database cancel;
啟動備庫到OPEN狀態
SYS@stddb>alter database open;
查看備庫狀態，目前為只讀狀態
SYS@stddb>select name,open_mode from v$database;

備庫在OPEN狀態下開啟日志應用服務
SYS@orcl> alter database recover managed standby database using current logfile disconnect;
此時備庫可變為讀寫分離狀態下的只讀庫
SYS@orcl>select name,open_mode from v$database;

NAME      OPEN_MODE
ORCL      READ ONLY WITH APPLY