oracle adg备库可以写吗,oracle

admin 管理员组

文章数量: 887021

oracle adg备库可以写吗,oracle

实验内容说明：

继上次主库关闭归档，使用基于SCN增量恢复主备关系后，测试关闭归档期间，主端新建表空间进行数据操作，操作完成后删除表空间，开启归档，备端是否能够不创建新表空间的情况下恢复主备关系，正常复制；

我们有一套同步正常的主备环境：

11.11.11.5 primary

11.11.11.6 standby

源端关闭归档，且创建新表空间，进行数据操作

[ora11@prim ~]$ sqlplus '/ as sysdba'

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  889389056

bytes

Fixed Size  2258360 bytes

Variable Size  281021000 bytes

Database Buffers  599785472

bytes

Redo Buffers  6324224

bytes

Database mounted.

SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> SELECT log_mode FROM v$database;

LOG_MODE

------------

NOARCHIVELOG

SQL> alter system switch logfile;

SQL> archive log list

Database log mode  No Archive Mode

Automatic archival  Disabled

Archive destination  /u01/app2/arch

Oldest online log sequence  70

Current log sequence  72

SQL> create tablespace tmpdata datafile

'/u01/app2/ora11/oradata/primary/tmpdata01.dbf' size 20M

extent management local segment space management

auto;

Tablespace created.

SQL> SELECT TABLESPACE_NAME,FILE_NAME FROM

dba_data_files;

TABLESPACE_NAME  FILE_NAME

--------------------

----------------------------------------------------------------------

SYSTEM  /u01/app2/ora11/oradata/primary/system01.dbf

SYSAUX  /u01/app2/ora11/oradata/primary/sysaux01.dbf

UNDOTBS1  /u01/app2/ora11/oradata/primary/undo.dbf

TMPDATA  /u01/app2/ora11/oradata/primary/tmpdata01.dbf

使用新表空间操作数据

SQL> SELECT count(*) FROM sun.tab1;

COUNT(*)

----------

10003

SQL> create table sun.tab2 tablespace tmpdata as select *

from sun.tab1 WHERE 1=2;

Table created.

SQL> SELECT tablespace_name FROM dba_segments WHERE

segment_name='TAB2';

TABLESPACE_NAME

--------------------

TMPDATA

SQL> truncate table  sun.tab1;

Table truncated.

SQL> insert into sun.tab1 select * from sun.tab2 ORDER BY

id,name;

10003 rows created.

SQL> commit;

Commit complete.

SQL>  drop table sun.tab2 purge;

Table dropped.

SQL> drop tablespace tmpdata including contents and

datafiles;

Tablespace dropped.

SQL> SELECT TABLESPACE_NAME,FILE_NAME FROM

dba_data_files;

TABLESPACE_NAME  FILE_NAME

--------------------

----------------------------------------------------------------------

SYSTEM  /u01/app2/ora11/oradata/primary/system01.dbf

SYSAUX  /u01/app2/ora11/oradata/primary/sysaux01.dbf

UNDOTBS1  /u01/app2/ora11/oradata/primary/undo.dbf

主端开启归档，恢复备库操作

--备库查询

Error 12514 received logging on to the standby

FAL[client, MRP0]: Error 12514 connecting to tnsp for fetching

gap sequence

23:46:52 SQL> select * from

v$archive_gap;

THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#

------- ------------- --------------

1

66

71

23:46:53 SQL> SELECT current_scn FROM v$database;

CURRENT_SCN

-----------

297662

23:48:04 SQL> select min(fhscn) from x$kcvfh;

MIN(FHSCN)

----------------

297663

--主库备份

RMAN> backup incremental from scn 297662

2>  tag incr_update database

format

3> '/u01/app2/backup/%U.db' ;

Starting backup at 05-JUL-17

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00002

name=/u01/app2/ora11/oradata/primary/sysaux01.dbf

input datafile file number=00001

name=/u01/app2/ora11/oradata/primary/system01.dbf

input datafile file number=00003

name=/u01/app2/ora11/oradata/primary/undo.dbf

channel ORA_DISK_1: starting piece 1 at 05-JUL-17

channel ORA_DISK_1: finished piece 1 at 05-JUL-17

piece handle=/u01/app2/backup/0hs8kgk0_1_1.db tag=INCR_UPDATE

comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time:

00:00:08

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

channel ORA_DISK_1: starting piece 1 at 05-JUL-17

channel ORA_DISK_1: finished piece 1 at 05-JUL-17

piece handle=/u01/app2/backup/0is8kgk8_1_1.db tag=INCR_UPDATE

comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time:

00:00:01

Finished backup at 05-JUL-17

SQL> alter database create standby controlfile as

'/u01/app2/backup/control.ctl';

Database altered.

[ora11@prim backup]$ scp * 11.11.11.6:/u01/app2/backup/

ora11@11.11.11.6's password:

Permission denied, please try again.

ora11@11.11.11.6's password:

0hs8kgk0_1_1.db  100% 4032KB  3.9MB/s

00:00

0is8kgk8_1_1.db  100%  20MB  9.9MB/s  00:02

control.ctl

--备库恢复增量备份

[ora11@stand ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jul 5 23:54:06

2017

Copyright (c) 1982, 2013, Oracle.  All rights

reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 -

64bit Production

With the Partitioning, OLAP, Data Mining and Real Application

Testing options

SQL>

SQL>

SQL> show parameter contr

NAME  TYPE  VALUE

------------------------------------ -----------

------------------------------

control_file_record_keep_time  integer

7

control_files  string  /u01/app2/ora11/oradata/standb

y/control01.ctl, /u01/app2/ora

11/oradata/standby/control02.c

tl

control_management_pack_access  string  DIAGNOSTIC+TUNING

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

[ora11@stand backup]$ cp  control.ctl

/u01/app2/ora11/oradata/standby/control01.ctl

[ora11@stand backup]$ cp  control.ctl

/u01/app2/ora11/oradata/standby/control02.ctl

SQL> startup mount

ORACLE instance started.

Total System Global Area  889389056

bytes

Fixed Size  2258360 bytes

Variable Size  281021000 bytes

Database Buffers  599785472

bytes

Redo Buffers  6324224

bytes

Database mounted.

SQL> SELECT current_scn FROM v$database;

CURRENT_SCN

-----------

299822

SQL> select min(fhscn) from x$kcvfh;

MIN(FHSCN)

----------------

297663

[ora11@stand backup]$

[ora11@stand backup]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Jul 5

23:56:16 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.

All rights reserved.

connected to target database: PRIMARY (DBID=1785505668, not

open)

RMAN> list backup;

using target database control file instead of recovery

catalog

specification does not match any backup in the

repository

RMAN>

RMAN> catalog start with '/u01/app2/backup/';

searching for all files that match the pattern

/u01/app2/backup/

List of Files Unknown to the Database

=====================================

File Name: /u01/app2/backup/0hs8kgk0_1_1.db

File Name: /u01/app2/backup/0is8kgk8_1_1.db

Do you really want to catalog the above files (enter YES or

NO)? YES

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: /u01/app2/backup/0hs8kgk0_1_1.db

File Name: /u01/app2/backup/0is8kgk8_1_1.db

RMAN> list backup

2> ;

List of Backup Sets

===================

BS Key  Type LV Size  Device Type Elapsed Time

Completion Time

------- ---- -- ---------- ----------- ------------

---------------

12  Incr  3.93M

DISK

00:00:00  05-JUL-17

BP Key: 12  Status: AVAILABLE

Compressed: NO  Tag:

INCR_UPDATE

Piece Name:

/u01/app2/backup/0hs8kgk0_1_1.db

List of Datafiles in backup set 12

File LV Type Ckp SCN  Ckp Time  Name

---- -- ---- ---------- ---------

----

1  Incr 299789  05-JUL-17

/u01/app2/ora11/oradata/standby/system01.dbf

2  Incr 299789  05-JUL-17

/u01/app2/ora11/oradata/standby/sysaux01.dbf

3  Incr 299789  05-JUL-17

/u01/app2/ora11/oradata/standby/undo.dbf

BS Key  Type LV Size  Device Type Elapsed Time

Completion Time

------- ---- -- ---------- ----------- ------------

---------------

13  Incr  19.83M

DISK  00:00:00

05-JUL-17

BP Key: 13  Status: AVAILABLE

Compressed: NO  Tag:

INCR_UPDATE

Piece Name:

/u01/app2/backup/0is8kgk8_1_1.db

Control File Included: Ckp SCN: 299795

Ckp time:

05-JUL-17

RMAN>  recover database noredo;

Starting recover at 05-JUL-17

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=28 device type=DISK

channel ORA_DISK_1: starting incremental datafile backup set

restore

channel ORA_DISK_1: specifying datafile(s) to restore from

backup set

destination for restore of datafile 00001:

/u01/app2/ora11/oradata/standby/system01.dbf

destination for restore of datafile 00002:

/u01/app2/ora11/oradata/standby/sysaux01.dbf

destination for restore of datafile 00003:

/u01/app2/ora11/oradata/standby/undo.dbf

channel ORA_DISK_1: reading from backup piece

/u01/app2/backup/0hs8kgk0_1_1.db

channel ORA_DISK_1: piece

handle=/u01/app2/backup/0hs8kgk0_1_1.db tag=INCR_UPDATE

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time:

00:00:07

Finished recover at 05-JUL-17

SQL> archive log list

Database log mode  Archive Mode

Automatic archival  Enabled

Archive destination  /u01/app2/arch

Oldest online log sequence  73

Next log sequence to archive  0

Current log sequence  75

SQL> alter database recover managed standby database

disconnect from session;

Database altered.

同步验证，主端删除数据，备端查看

SQL> delete from sun.tab1 WHERE id< 1000;

1001 rows deleted.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

23:59:14 SQL> SELECT count(*) FROM sun.tab1;

COUNT(*)

----------

10003

Elapsed: 00:00:00.00

23:59:41 SQL> SELECT count(*) FROM sun.tab1;

COUNT(*)

----------

900

至此恢复完成，那我们怎么验证备端是否有新建表空间呢？

[ora11@stand trace]$ cat alert_standby.log |grep -i

"tmpdata"

[ora11@stand trace]$

通过查看备端ALERT是否有，表空间tmpdata的信息来严重，可以看到没有查到，我们就认为备端没有创建；

从原理上来看，也应该不会创建新表空间，

增量备份只是当前数据文件检查块级别的SCN是否大于指定SCN，而进行相应的备份操作，而不会去追溯日志中内容，况且这段时间也没有归档;

本文标签： oracle adg备库可以写吗 Oracle