Thông tin máy chủ
=====thủ tục chi tiết Dựng standby database cho CSDL RAC dbavn==========
Thêm thông tin TNSNAME
Thêm đoạn sau vào file tnsname.ora
Trên máy nguồn:
dbavndg =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.149)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.150)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dbavndg)
)
)
Trên máy đích
dbavn=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS=(PROTOCOL = TCP) (HOST=192.168.10.52) (PORT=1521))
(ADDRESS=(PROTOCOL = TCP) (HOST=192.168.10.55) (PORT=1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dbavn)
)
)
Kiểm tra kết nối từ máy nguồn tnsping sang db đích
bash-3.2$ tnsping dbavndg
====backup full db==============
Backup full database
=============================
Copy bản backup level0
và các file cần thiết sang des
$ sqlplus / as sysdba
Create stanby controlfile
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/dbavndgggggggg.ctl';
Copy stanby controlfile sang des /tmp/
$ cd $ORACLE_HOME/dbs
Copy password file sang cả 2 node bên des
Điều chỉnh lại các tham số DataGuard
SQLPLUS / AS SYSDBA
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(dbavn,dbavndg)'
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=dbavndg NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dbavndg';
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
SQL> ALTER SYSTEM SET FAL_SERVER=dbavndg;
SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT='+DATA','+DATA' SCOPE=SPFILE; --- nếu trước chưa có tham số này thì k cần edit
SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='+DATA', '+DATA' SCOPE=SPFILE; ------ nếu trước chưa có tham số này thì k cần edit
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
Tạo pfile
SQL> CREATE PFILE='/tmp/pfilebinhtv_guard.ora' FROM SPFILE;
SQL> exit
Copy pfile sang des
Cấu hình trên máy chủ des 100.149/100.150
Tạo thư mục lưu autdit trên des
/u01/app/oracle/11.2.0/db/rdbms/audit -- con nguồn
/u01/oracle/db/rdbms/audit -- con des
=====edit pfile guard============
*.archive_lag_target=0
*.audit_trail='OS'# changed to OS on Read-Only instance:Deprecate parameter:Deprecate parameter
*.cluster_database=TRUE
*.cluster_database_instances=2
*.compatible='11.2.0.3.0'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='+DATA','+DATA'
*.db_files=10000
*.db_name='dbavn'
*.db_recovery_file_dest='+RECO'
*.db_recovery_file_dest_size=1024G
*.db_unique_name='dbavndg'
*.dg_broker_start=TRUE
dbavndg1.dispatchers='(PROTOCOL=TCP) (SERVICE=dbavndgXDB)'
dbavndg2.dispatchers='(PROTOCOL=TCP) (SERVICE=dbavndgXDB)'
*.fal_client='dbavndg'
*.fal_server='dbavn'
*.global_names=FALSE
*.job_queue_processes=1000
*.local_listener=''
*.log_archive_config='dg_config=(dbavndg,dbavn)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'
dbavndg1.log_archive_dest_2='service=dbavndg NOAFFIRM ASYNC valid_for=(online_logfiles,primary_role) db_unique_name=dbavndg'
dbavndg2.log_archive_dest_2='service=dbavndg NOAFFIRM ASYNC valid_for=(online_logfiles,primary_role) db_unique_name=dbavndg'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
dbavndg2.log_archive_format='%t_%s_%r.dbf'
dbavndg1.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=4
*.log_archive_min_succeed_dest=1
*.log_archive_trace=0
dbavndg2.log_archive_trace=0
dbavndg1.log_archive_trace=0
*.log_buffer=10485760# log buffer update
*.log_file_name_convert='+DATA','+DATA'
*.max_dump_file_size='51200'
*.open_cursors=5000
*.optimizer_dynamic_sampling=2
*.optimizer_mode='ALL_ROWS'
*.parallel_max_servers=64
*.pga_aggregate_target=6G
*.plsql_warnings='DISABLE:ALL'# PL/SQL warnings at init.ora
*.processes=2000
*.query_rewrite_enabled='TRUE'
*.recyclebin='ON'
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_limit=TRUE
*.result_cache_max_size=146816K
*.resumable_timeout=3600
*.service_names='dbavn','dbavndg'
*.session_cached_cursors=200
*.sessions=3040
*.sga_max_size=28G# internally adjusted
*.sga_target=28G
*.skip_unusable_indexes=TRUE
*.standby_file_management='AUTO'
*.streams_pool_size=512M
dbavndg1.thread=1
dbavndg2.thread=2
*.transactions=1007
*.undo_management='AUTO'
*.undo_retention=4800
dbavndg1.undo_tablespace='UNDOTBS01'
dbavndg2.undo_tablespace='UNDOTBS2'
dbavndg1.user_dump_dest='/u01/oracle/diag/rdbms/dbavndg/dbavndg1/trace'#Deprecate parameter
dbavndg2.user_dump_dest='/u01/oracle/diag/rdbms/dbavndg/dbavndg2/trace'#Deprecate parameter
=========================
Standby: startup nomount
export ORACLE_UNQNAME =dbavndg
export ORACLE_SID=dbavndg1
sqlplus / as sysdba
startup nomount pfile='duong_dan_chua_pfile'
Standby: đưa control file vào Diskgroup +DATA
rman target /
RMAN> RESTORE standby CONTROLFILE FROM '/backup/binhtv/dbavndg.ctl';
Starting restore at 04-NOV-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2946 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+RECO/dbavn/controlfile/current.259.10
Finished restore at 05-NOV-20
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
SYS>create spfile ='+DATA' from pfile ='/backup/pfilebinhtv_guard.ora';
Standby: restore database
$ rman target /
run {
catalog start with '/backup/binhtv/full/' noprompt;
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
allocate channel c5 device type disk;
allocate channel c6 device type disk;
allocate channel c7 device type disk;
allocate channel c8 device type disk;
allocate channel c9 device type disk;
allocate channel c10 device type disk;
allocate channel c11 device type disk;
allocate channel c12 device type disk;
allocate channel c13 device type disk;
allocate channel c14 device type disk;
allocate channel c15 device type disk;
allocate channel c16 device type disk;
restore database;
recover database;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
release channel c7;
release channel c8;
release channel c9;
release channel c10;
release channel c11;
release channel c12;
release channel c13;
release channel c14;
release channel c15;
release channel c16;
}
Bỏ qua thông báo dưới:
released channel: c15
released channel: c16
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/04/2020 23:28:18
RMAN-06054: media recovery requesting unknown archived log for thread 2 with sequence 74857 and starting SCN of 13053814164036
Standby: Thêm standby logfile (stanbylog >= redo onlinelog)
Primary hiện tại có 10 onlinelog, 12 stanbylog size 1gb
Vậy cần tạo 12 stanby size 1GB
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 21 ('+DATA') SIZE 1G;
thực hiện tương tự 11 stanby còn lại
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
Standby: Bật apply log
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Standby: tắt đồng bộ
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
SQL> shutdown immediate ;
SQL> startup mount ;
Bật đồng bộ
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Đồng bộ xong thì chuyển db về open_mode: READ ONLY WITH APPLY
SQL> alter database open read only;
Cấu hình Broker
Trên máy nguồn
# su – oracle
export ORACLE_UNQNAME =dbavn
export ORACLE_SID=dbavn1
# sqlplus / as sysdba
SQL> alter system set dg_broker_start=true scope=both;
SQL> show parameter dg_broker;
SQL> exit;
Trên máy đích
# su – oracle
export ORACLE_UNQNAME =dbavndg
export ORACLE_SID=dbavndg1
# sqlplus / as sysdba
SQL> alter system set dg_broker_start=true scope=both;
SQL> show parameter dg_broker;
SQL> exit;
$ dgmgrl
DGMGRL> connect syscon
dgmgrl sys/ABCZXYZ…
DGMGRL> CREATE CONFIGURATION 'dbavn_TU' AS PRIMARY DATABASE IS 'dbavn' CONNECT IDENTIFIER IS 'dbavn';
DGMGRL> show configuration
DGMGRL> add database 'dbavndg' as connect identifier is dbavndg maintained as physical;
DGMGRL> show configuration
DGMGRL> enable configuration
DGMGRL> show configuration
DGMGRL> show database verbose 'dbavndg';
DGMGRL> show database verbose 'dbavn';
====DỌN dẹp sau khi dựng==
Xóa bản backup db trên guard copy từ Primary sang
Xóa các file tmp….
Bật lại backup db chính OK
Đặt job xóa archive định kỳ OK
Đặt job cảnh báo lag hàng ngày OK
dbavn_DR_RAC =
(DESCRIPTION =
(LOAD_BALANCE=yes)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.149)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.150)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = dedicated)
(SERVICE_NAME = dbavndg)
)
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile: 0902912888
⚡️ Skype: tranbinh48ca
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: http://bit.ly/ytb_binhoraclemaster
👨 Tiktok: https://www.tiktok.com/@binhoraclemaster?lang=vi
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhoracle
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội
#OracleTutorial #OracleDBA #OracleDatabaseAdministration #học oracle database #oca #ocp #tự học oracle
