搭建DG

最新推荐文章于 2024-06-28 11:20:27 发布

原创最新推荐文章于 2024-06-28 11:20:27 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

ORACLE--OGG/DG/TT 专栏收录该内容

8 篇文章

订阅专栏

1数据库归档模式

srvctl stop database -d fyl -o immediate
SQL> alter database archivelog;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=+DATA1/FYL/ARCH1'  SID='fyl1';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=+DATA1/FYL/ARCH2'  SID='fyl2';
SQL> alter database force logging；
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u01/app/oracle/oradata/orcl/redo04.log') size 50M; 
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u01/app/oracle/oradata/orcl/redo04.log') size 50M; 
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u01/app/oracle/oradata/orcl/redo04.log') size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 8 ('/u01/app/oracle/oradata/orcl/redo04.log') size 50M;

2初始化参数

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(fyl,fyl_std)' SID='*';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=+DATA1/FYL/ARCH1  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)  DB_UNIQUE_NAME=fyl' SID='fyl1';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=+DATA1/FYL/ARCH2  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)  DB_UNIQUE_NAME=fyl' SID='fyl2';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=fyl_std LGWR ASYNC  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)  DB_UNIQUE_NAME=fyl_std' SID='*';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
--ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=3;
ALTER SYSTEM SET FAL_SERVER=fyl_std;
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/u02/app/oracle/oradata/fyl_std','+DATA1/fyl/datafile','/u02/app/oracle/oradata/fyl_std','+DATA1/fyl/tempfile' scope=spfile SID='*';
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/u02/app/oracle/oradata/fyl_std','+DATA1/fyl/onlinelog','/u02/app/oracle/oradata/fyl_std','+DATA1/fyl/onlinelog' scope=spfile SID='*';
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

3备份数据库

[oracle@db1 ~]$ more rman.sh 
. /home/oracle/.bash_profile 
rman target / <<EOF
run
{
allocate channel c1 type disk maxpiecesize=500M; 
allocate channel c2 type disk maxpiecesize=500M;
backup incremental level 0 database  
format '/u02/rman_fyl1/fyl_%p_%s_%u.dbf';  
sql 'alter system archive log current';  
backup archivelog all 
format '/u02/rman_fyl1/fyl_%p_%s_%u.arc';  
backup current controlfile for standby 
format '/u02/rman_fyl1/fyl_%p_%s.ctl';  
}
exit;
EOF
[oracle@db1 ~]$ nohup sh rman.sh > 1.log &

[oracle@db1 rman_fyl1]$ scp * 192.168.6.99:/u02/rman
oracle@192.168.6.99's password: 
fyl_1_1_01qak654.dbf                                                                                                                  100%  273MB  10.1MB/s   00:27    
fyl_1_2_02qak655.dbf                                                                                                                  100%  333MB   9.0MB/s   00:37    
fyl_1_3_03qak67k.dbf                                                                                                                  100%   18MB  17.7MB/s   00:01    
fyl_1_4_04qak67k.dbf                                                                                                                  100%   96KB  96.0KB/s   00:00    
fyl_1_5_05qak67u.arc                                                                                                                  100%   11MB  10.5MB/s   00:01    
fyl_1_6_06qak67u.arc                                                                                                                  100% 1295KB   1.3MB/s   00:00    
fyl_1_7_07qak680.arc                                                                                                                  100% 2512KB   2.5MB/s   00:00    
fyl_1_8.ctl                                                                                                                           100%   18MB  17.7MB/s   00:01  
传送密码文件--不然会报错
[oracle@db1 dbs]$ scp orapwfyl1 192.168.6.99:/u02/app/oracle/product/11.2.0/db_2/dbs/orapwfyl_std
oracle@192.168.6.99's password: 
orapwfyl1                                                                                  100% 1536     1.5KB/s   00:00

alter database create standby controlfile as '/home/oracle/control01.ctl';

4修改备库参数文件

create pfile='/home/oracle/initfyl_std.ora' from spfile

[oracle@db1 ~]$ more initfyl_std.ora 
*.audit_file_dest='/u01/app/oracle/admin/fyl_std/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u02/app/oracle/oradata/fyl_std/control01.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='+DATA1/fyl/datafile','/u02/app/oracle/oradata/fyl_std','+DATA1/fyl/tempfile','/u02/app/oracle/oradata/fyl_std'
*.db_unique_name='FYL_STD'
*.db_name='fyl'
*.diagnostic_dest='/u01/app/oracle'
*.fal_server='FYL'
*.java_pool_size=0
*.log_archive_config='DG_CONFIG=(fyl,fyl_std)'
*.log_archive_dest_1='LOCATION=/u01/oracle/arch_fyl_std  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)  DB_UNIQUE_NAME=fyl_std'
*.log_archive_dest_2='SERVICE=fyl LGWR ASYNC  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)  DB_UNIQUE_NAME=fyl'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_file_name_convert='+DATA1/fyl/onlinelog','/u02/app/oracle/oradata/fyl_std','+DATA1/fyl/onlinelog','/u02/app/oracle/oradata/fyl_std'
*.memory_target=524288000
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='exclusive'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

5恢复数据库

RMAN> startup nomount;
RMAN> restore standby controlfile from '/u02/rman/fyl_1_8.ctl';
RMAN> alter database mount;
RMAN> catalog start with '/u02/rman';
RMAN> run {
2> allocate channel c1 type disk ;
3> allocate channel c2 type disk ;
4> restore database;
5> recover database;
6> }

6配置主备TNS

FYL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.31)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.32)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = fyl)
    )
  )

FYL_STD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.99)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = fyl_std)
    )
  )

7验证

备库应用日志
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNCET FROM SESSION;
主库
ALTER TABLESPACE USERS ADD DATAFILES ' +DATA1/fyl/datafile/users02.dbf' SIZE 5M AUTOEXTEND ON;
备库查看
SQL> select FILE_ID,FILE_NAME,BYTES/1024/1024/1024 GB,TABLESPACE_NAME,AUTOEXTENSIBLE from dba_data_files;

   FILE_ID FILE_NAME                                                                      GB TABLESPACE_NAME                AUT
---------- ---------------------------------------------------------------------- ---------- ------------------------------ ---
         1 /u02/app/oracle/oradata/fyl_std/system.266.883172679                    .68359375 SYSTEM                         YES
         2 /u02/app/oracle/oradata/fyl_std/sysaux.265.883172755                     .5859375 SYSAUX                         YES
         3 /u02/app/oracle/oradata/fyl_std/undotbs1.258.883172809                 .209960938 UNDOTBS1                       YES
         4 /u02/app/oracle/oradata/fyl_std/undotbs2.264.883172903                   .1953125 UNDOTBS2                       YES
         5 /u02/app/oracle/oradata/fyl_std/users.263.883172925                    .004882813 USERS                          YES
         6 /u02/app/oracle/oradata/fyl_std/users02.dbf                            .004882813 USERS                          YES

6 rows selected.

8过程错误
错误一

SQL> alter database recover managed standby database disconnect from session;
alter database recover managed standby database disconnect from session
*
ERROR at line 1:
ORA-01665: control file is not a standby control file

SQL> select controlfile_type from v$database; 

CONTROL
-------
BACKUP

原来使用
restore controlfile from '/u02/rman/fyl_1_8.ctl';
解决：使用如下命令
restore standby controlfile from '/u02/rman/fyl_1_8.ctl';

错误二

Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
      returning error ORA-16191
解决：copy密码文件

9备忘脚本

export ORACLE_SID=fyl_std
rman target / <<EOF
alter database nomount;
restore standby controlfile from '/u02/rman/fyl_1_8.ctl';
alter database mount;
catalog start with '/u02/rman' noprompt;
crosscheck backup;
run {
allocate channel c1 type disk ;
allocate channel c2 type disk ;
restore database;
recover database;
}
exit;
EOF

使用了db_file_name_convert log_file_name_convert参数可以省略下面信息
set newname for datafile 4  to  '/oracle/oradata/prod/users01.dbf';
set newname for datafile 5      to '/oracle/oradata/prod/undotbs02.dbf';
set newname for datafile 3      to '/oracle/oradata/prod/undotbs01.dbf';
set newname for datafile 1      to '/oracle/oradata/prod/system01.dbf';
restore database;
switch datafile all;
switch tempfile all;
release channel c1;
release channel c2;