一、備份 1、數據備份（主庫上執行） rman>backup full database format '/u01/rmanbak/port_full_backup%U.bk'; 2、資料庫軟體備份（主庫上執行） $ cd /home/oracle/product/10.2.0/ $ tar cvf /u01/oraclehomebak/product.tar db_1 3、資料庫參數備份（主庫上執行） sqlplus "/as sysdba" sql>create pfile='/home/oracle/initport1.ora' from spfile; 二、主庫實施（階段一） 1、參數修改 ALTER DATABASE FORCE LOGGING; ALTER SYSTEM SET DB_UNIQUE_NAME=port scope=spfile; ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(port,standby)' scope=both; --主庫歸檔目的地 ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/home/oracle/archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=port' scope=both; --當該庫充當主庫角色時,設置物理備庫redo data的傳輸目的地 ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby' scope=spfile; --許redo傳輸服務傳輸數據到目的地,默認是enable ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=spfile; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=spfile; --exclusive or shared,所有庫sys密碼要一致,默認是exclusive ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE scope=spfile; -- ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' scope=spfile; ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=4 scope=spfile; --配置網路服務名,fal_server拷貝丟失的歸檔文件到這裡 ALTER SYSTEM SET FAL_CLIENT=port SCOPE=SPFILE; --配置網路服務名,假如轉換為備庫角色時,從這裡獲取丟失的歸檔文件 ALTER SYSTEM SET FAL_SERVER=STANDBY SCOPE=SPFILE; --auto后當主庫的datafiles增刪時備庫也同樣自動操作,且會把日誌傳送到備庫standby_archive_dest參數指定的目錄下, --確保該目錄存在,如果你的存儲採用文件系統沒有問題,但是如果採用了裸設備,你就必須將該參數設置為manual ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=SPFILE;--前為切換后的主庫路徑,後為切換后的備庫路徑,如果主備庫目錄結構完全一樣,則無需設定 ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/u01/oracle/oradata/port/','/u01/oracle/oradata/port/' SCOPE=SPFILE; --同上,這兩個名字轉換參數是主備庫的路徑映射關係,可能會是路徑全名,看情況而定 ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/u01/oracle/oradata/port/','/u01/oracle/oradata/port/' SCOPE=SPFILE; --一般和LOG_ARCHIVE_DEST_1的位置一樣,如果備庫採用ARCH傳輸方式,那麼主庫會把歸檔日誌傳到該目錄下 ALTER SYSTEM SET STANDBY_ARCHIVE_DEST='/home/oracle/archivelog/' scope=spfile; 2、重啟主庫 shutdown immediate; startup; 3、主庫的tnsnames.ora添加內容 STANDBY = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =172.16.3.56)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = standby) )) 4、備份資料庫 run{ allocate channel c1 device type disk format '/u01/rmanbak/forstandby/%U' connect sys/password@port; backup database; backup archivelog all; } 5、生成備庫控制文件 方法一： SQL>alter database create standby controlfile as '/u01/rmanbak/forstandby/ctl/controlfile.ctl'; 方法二： run{ allocate channel c1 device type disk format '/u01/rmanbak/forstandby/ctl/CON_%U'; backup current controlfile for standby; } 6、如果主庫沒有密碼文件則建立密碼文件,從而可以OS驗證的方式登陸 $ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=iamwangnc entries=5 三、備庫實施（階段一） 1、獲取主庫的密碼文件 方法一：FTP cd /home/oracle/product/10.2.0/db_1/dbs ftp 172.16.3.46 cd /home/oracle/product/10.2.0/db_1/dbs/ bin get orapwport 方法二：SCP -bash-3.2$ scp -r root@172.16.3.46:/home/oracle/product/10.2.0/db_1/dbs/orapwport /home/oracle/product/10.2.0/db_1/dbs/orapwport root@172.16.3.46's password:orapwport 100% 1536 1.5KB/s 00:00 -bash-3.2$ orapwd file=orapwport password=oracle entries=5(未執行) 2、檢查相應目錄和許可權 /home/oracle/admin /home/oracle/admin/port /home/oracle/admin/port/adump /home/oracle/admin/port/bdump /home/oracle/admin/port/cdump /home/oracle/admin/port/udump 3、配置tnsnames.ora和listener.ora ############################################# tnsnames.ora文件 #############################################port = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.3.46)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = port) (INSTANCE_NAME = port) ) ) STANDBY = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =172.16.3.56)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = standby) ) ) ################## listener.ora文件 ################## SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /home/oracle/product/10.2.0/db_1) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = standby) (ORACLE_HOME = /home/oracle/product/10.2.0/db_1) (SID_NAME = port) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.3.56)(PORT = 1521)) ) ) ) 4、啟動監聽 lsnrctl start 5、修改參數文件/home/oracle/product/10.2.0/db_1/dbs/initport.ora create pfile = '/home/oracle/product/10.2.0/db_1/dbs/initport.ora' from spfile; *.audit_file_dest='/home/oracle/admin/port/adump' *.background_dump_dest='/home/oracle/admin/port/bdump' *.core_dump_dest='/home/oracle/admin/port/cdump' *.user_dump_dest='/home/oracle/admin/port/udump' *.compatible='10.2.0.4.0' *.control_files='/u01/oracle/oradata/port/control01.ctl','/u01/oracle/oradata/port/control02.ctl','/u01/oracle/oradata/port/control03.ctl' *.db_block_size=8192 *.db_domain='' *.db_file_multiblock_read_count=32 *.db_name='port' *.db_unique_name='standby' *.dispatchers='' *.fal_client='STANDBY'*.fal_server='port' *.job_queue_processes=10 *.log_archive_config='DG_CONFIG=(port,standby)' *.log_archive_dest_1='LOCATION=/home/oracle/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=standby' *.log_archive_dest_2='service=port lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=port' *.log_archive_dest_state_1='ENABLE' *.log_archive_dest_state_2='ENABLE' *.log_archive_max_processes=4 *.log_file_name_convert='/u01/oracle/oradata/port/','/u01/oracle/oradata/port/' *.db_file_name_convert='/u01/oracle/oradata/port/','/u01/oracle/oradata/port/' *.open_cursors=1000 *.pga_aggregate_target=1092957696 *.processes=1000 *.remote_login_passwordfile='EXCLUSIVE' *.sessions=885 *.sga_max_size=4589934592 *.sga_target=4589934592 *.shared_servers=0 *.standby_file_management='AUTO' *.undo_management='AUTO' *.undo_tablespace='UNDOTBS1' 6、啟動到nomount狀體 shutdown immediate; startup nomount pfile='/home/oracle/product/10.2.0/db_1/dbs/initport.ora'; 7、從主庫拷貝備份到備庫上--注意rman備份的文件在主備庫主機上目錄要一致 scp -r root@172.16.3.46:/u01/rmanbak/forstandby/ /u01/rmanbak/forstandby/ 四、主庫實施（階段二） 1、利用auxiliary恢復備庫 rman target / auxiliary sys/password@standby duplicate target database for standby nofilenamecheck; RMAN> run{ allocate channel c1 device type disk format '/u01/rmanbak/forstandby/%U' connect sys/password@port; allocate auxiliary channel ac1 device type disk format '/u01/rmanbak/forstandby/%U';duplicate target database for standby; } 如果新創建備庫的資料庫文件路徑名稱和主庫一樣,oracle為了避免錯誤的覆蓋,會拋出RMAN-05001錯誤, 需要先執行如下語句即可成功,不做資料庫文件的名稱正確性檢查 RMAN>duplicate target database for standby nofilenamecheck; 2、把備庫至於恢復狀態(備庫) sqlplus / as sysdba alter database recover managed standby database disconnect from session; 3、在備庫添加standby redo log alter database recover managed standby database cancel; ALTER DATABASE ADD STANDBY LOGFILE group 4 ('/u01/oracle/oradata/port/redo04.log') SIZE 50M; ALTER DATABASE ADD STANDBY LOGFILE group 5 ('/u01/oracle/oradata/port/redo05.log') SIZE 50M; ALTER DATABASE ADD STANDBY LOGFILE group 6 ('/u01/oracle/oradata/port/redo06.log') SIZE 50M; ALTER DATABASE ADD STANDBY LOGFILE group 7 ('/u01/oracle/oradata/port/redo07.log') SIZE 50M; standby redolog的組數參考公式：(online redolog組數 1) * 資料庫線程數；單機線程數為1,RAC一般為2. standby redolog的組成員數和大小也盡量和online redolog一樣. 4、把備庫至於恢復狀態 alter database recover managed standby database disconnect from session; 5、查看備庫日誌應用狀態 select sequence#,applied from v$archived_log ; 6、測試方案 建表 create table test as select * from dba_users; 插入數據 insert into test select * from test; commit; 切換日誌 alter system switch logfile; 啟動備機到只讀模式 recover managed standby database cancel; alter database open; 查看test是否同步 select * from test; 7.回退方案 若在實施過程中出現故障,並會影響到業務的情況,就執行回退機制.具體的操作如下： 去除DataGurd 斷開主機傳輸到DataGuard備機的日誌功能 還原force logging模式 停止資料庫： Shutdown immediate 啟動資料庫到mount狀態修改強制日誌： $sqlplus /nolog startup mount; alter database archivelog; alter database no force logging; alter database open;
本文出自 「麥地塢」 博客,請務必保留此出處http://yunlongzheng.blog.51cto.com/788996/775195
