搭建DG

梦里梦外; 2022-08-01 14:54 108阅读 0赞

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;