Home > DGCA > DG常用的几个场景

DG常用的几个场景

 

一、Using a Physical Standby Database for Read/Write Testing and Reporting

流程图:
Description of Figure 12-7 follows

1.确认备库开启flashback
–std
SQL> select database_role,flashback_on from v$database;

DATABASE_ROLE    FLASHBACK_ON
—————- ——————
PHYSICAL STANDBY YES

2.DG保护模式
–pri
SQL> select database_role,protection_level,protection_mode from v$database;

DATABASE_ROLE    PROTECTION_LEVEL     PROTECTION_MODE
—————- ——————– ——————–
PRIMARY          MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

3.备库停止恢复,创建还原点
–std
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> create restore point before_open_rw_0317 guarantee flashback database;

Restore point created.

4.主库将该备库地址defer
–pri
SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=DEFER;

System altered.

5.备库open read write
SQL> alter database activate standby database;

Database altered.

SQL> alter database set standby database to maximize performance;

Database altered.

SQL> alter database open;

Database altered.

SQL>  select database_role,protection_level,protection_mode from v$database;

DATABASE_ROLE    PROTECTION_LEVEL     PROTECTION_MODE
—————- ——————– ——————–
PRIMARY          MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

6.read write备库
SQL> create table johnny_test (id number,name varchar2(50));

Table created.

SQL> insert into johnny_test select 1,’landrover’ from dual;

1 row created.

SQL> commit;

Commit complete.

SQL> select * from johnny_test;

        ID NAME
———- ————————————————–
         1 landrover

7.flashback 备库到还原点,继续DG的一份子recover
SQL> startup mount force;
ORACLE instance started.

Total System Global Area  268435456 bytes
Fixed Size                  1218892 bytes
Variable Size              88082100 bytes
Database Buffers          176160768 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> flashback database to restore point BEFORE_OPEN_RW_0317;

Flashback complete.

SQL> select database_role from v$database;

DATABASE_ROLE
—————-
PRIMARY

SQL> alter database convert to physical standby;

Database altered.

SQL>  select database_role from v$database;
select database_role from v$database
                           *
ERROR at line 1:
ORA-01507: database not mounted

SQL> select status from v$instance;

STATUS
————
STARTED

SQL> startup mount force;
ORACLE instance started.

Total System Global Area  268435456 bytes
Fixed Size                  1218892 bytes
Variable Size              88082100 bytes
Database Buffers          176160768 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL>  select database_role from v$database;

DATABASE_ROLE
—————-
PHYSICAL STANDBY

SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.

SQL> select database_role,protection_level,protection_mode from v$database;

DATABASE_ROLE    PROTECTION_LEVEL     PROTECTION_MODE
—————- ——————– ——————–
PHYSICAL STANDBY MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

二、Using RMAN Incremental Backups to Roll Forward a Physical Standby Database

简洁版:
1. Physical Standby Database Lags Far Behind the Primary Database

–pri
SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=DEFER; –模拟丢失
RMAN> backup incremental from scn 498822 database format ‘/tmp/forstd%U’ tag ‘forstd’;
RMAN> exit
[oracle@localhost tmp]$ scp /tmp/forstd0* 192.168.220.129:/tmp
RMAN> backup current controlfile for standby format ‘/tmp/forstd.ctl’;
RMAN> exit
[oracle@localhost tmp]$ scp /tmp/forstd.ctl 192.168.220.129:/tmp/

–std
SQL> recover managed standby database using current logfile disconnect;
RMAN> catalog start with ‘/tmp/forstd0′;
RMAN> recover database noredo;
RMAN> shutdown immediate;
RMAN> startup nomount;
RMAN> restore standby controlfile from ‘/tmp/forstd.ctl’;
RMAN> shutdown immediate;
RMAN> startup mount
SQL> recover managed standby database using current logfile disconnect;

2. Physical Standby Database Has Nologging Changes On a Subset of Datafiles

类似于情况1,只不过备份跟恢复的时候选择datafile xxx 而不是整个database。还有种方法就是将出现nologging的文件在主库做个备份,然后copy到备库即可。这种方法操作起来比较简单:
–pri
SQL> select owner,TABLE_NAME,tablespace_name ,LOGGING from dba_tables where table_name=’T';

OWNER                          TABLE_NAME                     TABLESPACE_NAME                LOG
—————————— —————————— —————————— —
SYS                            T                              QIFENG                         NO
SQL>  insert /*+append*/ into t select * from t; 

24 rows created.

SQL> commit;

Commit complete.

SQL> select file#,NAME,UNRECOVERABLE_CHANGE#,FIRST_NONLOGGED_SCN from v$datafile;

     FILE# NAME                                                         UNRECOVERABLE_CHANGE# FIRST_NONLOGGED_SCN
———- ———————————————————— ——————— ——————-
         1 /data/oradata/johnnydb/SYSTEM01.DBF                                              0                   0
         2 /data/oradata/johnnydb/UNDOTBS01.DBF                                             0                   0
         3 /data/oradata/johnnydb/SYSAUX01.DBF                                              0                   0
         4 /data/oradata/johnnydb/USERS01.DBF                                               0                   0
         5 /data/oradata/johnnydb/QIFENG01.DBF                                         500715                   0

SQL> insert /*+append*/ into t select * from t; 

48 rows created.

SQL> commit;

Commit complete.

SQL> select file#,NAME,UNRECOVERABLE_CHANGE#,FIRST_NONLOGGED_SCN from v$datafile;

     FILE# NAME                                                         UNRECOVERABLE_CHANGE# FIRST_NONLOGGED_SCN
———- ———————————————————— ——————— ——————-
         1 /data/oradata/johnnydb/SYSTEM01.DBF                                              0                   0
         2 /data/oradata/johnnydb/UNDOTBS01.DBF                                             0                   0
         3 /data/oradata/johnnydb/SYSAUX01.DBF                                              0                   0
         4 /data/oradata/johnnydb/USERS01.DBF                                               0                   0
         5 /data/oradata/johnnydb/QIFENG01.DBF                                         500758                   0

SQL> alter system switch logfile;

System altered.

–std
SQL> select file#,STATUS,NAME,UNRECOVERABLE_CHANGE#,FIRST_NONLOGGED_SCN from v$datafile;

     FILE# STATUS  NAME                                                         UNRECOVERABLE_CHANGE# FIRST_NONLOGGED_SCN
———- ——- ———————————————————— ——————— ——————-
         1 SYSTEM  /data/oradata/johnnydb_dg/SYSTEM01.DBF                                           0                   0
         2 ONLINE  /data/oradata/johnnydb_dg/UNDOTBS01.DBF                                          0                   0
         3 ONLINE  /data/oradata/johnnydb_dg/SYSAUX01.DBF                                           0                   0
         4 ONLINE  /data/oradata/johnnydb_dg/USERS01.DBF                                            0                   0
         5 ONLINE  /data/oradata/johnnydb_dg/QIFENG01.DBF                                           0              500715

SQL> alter database open read only;

Database altered.

SQL> select count(*) from t;
select count(*) from t
                     *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 395)
ORA-01110: data file 5: ‘/data/oradata/johnnydb_dg/QIFENG01.DBF’
ORA-26040: Data block was loaded using the NOLOGGING option

SQL> alter database close;      

Database altered.

SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.
SQL> select file#,STATUS,NAME,UNRECOVERABLE_CHANGE#,FIRST_NONLOGGED_SCN from v$datafile;

     FILE# STATUS  NAME                                                         UNRECOVERABLE_CHANGE# FIRST_NONLOGGED_SCN
———- ——- ———————————————————— ——————— ——————-
         1 SYSTEM  /data/oradata/johnnydb_dg/SYSTEM01.DBF                                           0                   0
         2 ONLINE  /data/oradata/johnnydb_dg/UNDOTBS01.DBF                                          0                   0
         3 ONLINE  /data/oradata/johnnydb_dg/SYSAUX01.DBF                                           0                   0
         4 ONLINE  /data/oradata/johnnydb_dg/USERS01.DBF                                            0                   0
         5 ONLINE  /data/oradata/johnnydb_dg/QIFENG01.DBF                                           0              500715

SQL> shutdown immediate
–pri
SQL> alter tablespace qifeng begin backup;

Tablespace altered.

[oracle@localhost johnnydb]$ scp QIFENG01.DBF 192.168.220.129:/data/oradata/johnnydb_dg/

SQL> alter tablespace qifeng end backup;

Tablespace altered.

–std
SQL> startup mount
SQL>  select file#,STATUS,NAME,UNRECOVERABLE_CHANGE#,FIRST_NONLOGGED_SCN from v$datafile;

     FILE# STATUS  NAME                                                         UNRECOVERABLE_CHANGE# FIRST_NONLOGGED_SCN
———- ——- ———————————————————— ——————— ——————-
         1 SYSTEM  /data/oradata/johnnydb_dg/SYSTEM01.DBF                                           0                   0
         2 ONLINE  /data/oradata/johnnydb_dg/UNDOTBS01.DBF                                          0                   0
         3 ONLINE  /data/oradata/johnnydb_dg/SYSAUX01.DBF                                           0                   0
         4 ONLINE  /data/oradata/johnnydb_dg/USERS01.DBF                                            0                   0
         5 ONLINE  /data/oradata/johnnydb_dg/QIFENG01.DBF                                           0                   0

SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database open read only;

Database altered.

SQL> select count(*) from t;

  COUNT(*)
———-
        96

SQL>

3. Physical Standby Database Has Widespread Nologging Changes

可同情况1处理.

Categories: DGCA Tags:
  1. No comments yet.
  1. No trackbacks yet.