Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to build PDB refresh in oracle 12c". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn how to build PDB refresh in oracle 12c.

PDB Refresh is a new feature introduced by 12C, which can incrementally synchronize the source PDB and quickly copy a mirrored copy of the database, similar to the BCV technology of EMC and the replication function of the storage layer that operators like to use. However, the copy can only be opened with read only, the copy speed is slow, refresh the network traffic, and there are few practical application scenarios.

The source and destination of PDB refresh can be in the same cdb or in different cdb, and the refresh method must be through dblink.

PDB refresh building

Database version

SYS@cdbtest1 (CDB$ROOT) > select banner from v$version where rownum=1

BANNER

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0-64bit Production

Pdb refresh has two basic requirements: archiving and local undo

Check to see if archiving is on:

SYS@cdbtest1 (CDB$ROOT) > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination / arch

Oldest online log sequence 42

Next log sequence to archive 45

Current log sequence 45

Check whether undo is in local mode

SYS@cdbtest1 (CDB$ROOT) > col PROPERTY_NAME for A25

SYS@cdbtest1 (CDB$ROOT) > col PROPERTY_VALUE for A30

SYS@cdbtest1 (CDB$ROOT) > select PROPERTY_NAME,PROPERTY_VALUE from database_properties where property_name='LOCAL_UNDO_ENABLED'

PROPERTY_NAME PROPERTY_VALUE

LOCAL_UNDO_ENABLED TRUE

Current pdb information

SYS@cdbtest1 (CDB$ROOT) > show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

-

2 PDB$SEED READ ONLY NO

3 CLONEMING READ WRITE NO

4 MING READ WRITE NO

Quick refresh pdb data file storage location is a little different from ordinary pdb, dba_data_files is not found, you need to use v$dbfile view.

Select name from v$dbfile

NAME

-

/ oradata/cdb/CDBTEST1/79C426B26B8C175BE053023DA8C0C3CA/datafile/o1_mf_system_fxvbwmck_.dbf

/ oradata/cdb/CDBTEST1/79C426B26B8C175BE053023DA8C0C3CA/datafile/o1_mf_sysaux_fxvbwmcp_.dbf

/ oradata/cdb/CDBTEST1/79C426B26B8C175BE053023DA8C0C3CA/datafile/o1_mf_test_fxvbwmcq_.dbf

/ oradata/cdb/CDBTEST1/79C426B26B8C175BE053023DA8C0C3CA/datafile/o1_mf_test_fxvbwmcr_.dbf

/ oradata/cdb/CDBTEST1/79C426B26B8C175BE053023DA8C0C3CA/datafile/o1_mf_undo_1_fxvbwmcr_.dbf

Its data files are placed in the cdb directory

If you use MING as the source-end pdb, you need to create a dblink to the cdb

SYS@cdbtest1 (CDB$ROOT) > create database link dblk_pdbming connect to system identified by "oracle" using 'pdbming'

Database link created.

Create database link dblk_pdbming connect to system identified by "oracle" using 'pdbming'

SYS@cdbtest1 (CDB$ROOT) > select sysdate from dual@dblk_pdbming

SYSDATE

-

03-NOV-18

Create a manual refresh refresh pdb

Create pluggable database mingdev1 from ming@dblk_pdbming refresh mode manual

Alert log

Create pluggable database mingdev1 from ming@dblk_pdbming refresh mode manual

2018-11-03T22:07:14.174103+08:00

MING (4): Opatch XML is skipped for PDB MING (conid=4)

MING (4): AUDSYS.AUD$UNIFIED (SQL_TEXT)-CLOB populated

2018-11-03T22:07:15.368785+08:00

Opatch validation is skipped for PDB MINGDEV1 (con_id=6)

2018-11-03T22:08:37.761704+08:00

MINGDEV1 (6): Endian type of dictionary set to little

*

Pluggable Database MINGDEV1 with pdb id-6 is created as UNUSABLE.

If any errors are encountered before the pdb is marked as NEW

Then the pdb must be dropped

Localundo-1, localundoscn-0x0000000000a123e2

*

2018-11-03T22:08:44.807546+08:00

Applying media recovery for pdb-4 from SCN 10563994 to SCN 10564176

Remote log information: count-1

Thr-1, seq-45, logfile-/arch/parlog_1_45_839db4a4_967464795.arc, los-10555323, nxs-18446744073709551615

MINGDEV1 (6): Media Recovery Start

2018-11-03T22:08:44.933181+08:00

MINGDEV1 (6): Serial Media Recovery started

2018-11-03T22:08:45.086257+08:00

MINGDEV1 (6): Media Recovery Log / arch/parlog_1_45_839db4a4_967464795.arc

2018-11-03T22:08:45.637158+08:00

MINGDEV1 (6): Incomplete Recovery applied until change 10564176 time 11exactly 03 Universe 22:08:39, 2018

2018-11-03T22:08:45.639481+08:00

MINGDEV1 (6): Media Recovery Complete (cdbtest1)

2018-11-03T22:08:45.958911+08:00

Completed: create pluggable database mingdev1 from ming@dblk_pdbming refresh mode manual

SYS@cdbtest1 (CDB$ROOT) > sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

-

2 PDB$SEED READ ONLY NO

3 CLONEMING READ WRITE NO

4 MING READ WRITE NO

6 MINGDEV1 MOUNTED

SYS@cdbtest1 (CDB$ROOT) > alter pluggable database mingdev1 open

Alter pluggable database mingdev1 open

*

ERROR at line 1:

ORA-65341: cannot open pluggable database in read/write mode

Later, it was found that the report was wrong.

ORA-12012: thank you "SYS". "ORA$AT_OS_OPT_SY_101"

ORA-20001: Statistics Advisor: Invalid task name for the current user

ORA-06512: SYS.DBMS_STATS ", line 47207

ORA-06512: SYS.DBMS_STATS_ADVISOR ", line 882

ORA-06512: SYS.DBMS_STATS_INTERNAL ", line 20059

ORA-06512: SYS.DBMS_STATS_INTERNAL ", line 22201

ORA-06512: SYS.DBMS_STATS ", line 47197

Although you can't open read write, you can open it in read only mode.

SYS@cdbtest1 (CDB$ROOT) > alter pluggable database mingdev1 open read only

Pluggable database altered.

SYS@cdbtest1 (CDB$ROOT) > sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

-

2 PDB$SEED READ ONLY NO

3 CLONEMING READ WRITE NO

4 MING READ WRITE NO

6 MINGDEV1 READ ONLY NO

Sys user logs in to target pdb

SYS@mingdev1 (MINGDEV1) > alter pluggable database mingdev1 refresh

Alter pluggable database mingdev1 refresh

*

ERROR at line 1:

ORA-65025: Pluggable database MINGDEV1 is not closed on all instances.

To refresh pdb, the target pdb must be closed or mount first.

SYS@mingdev1 (MINGDEV1) > alter pluggable database mingdev1 close immediate

Pluggable database altered.

SYS@mingdev1 (MINGDEV1) > sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

-

6 MINGDEV1 MOUNTED

SYS@mingdev1 (MINGDEV1) > alter pluggable database mingdev1 refresh

Pluggable database altered.

SYS@mingdev1 (MINGDEV1) > sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

-

6 MINGDEV1 MOUNTED

SYS@mingdev1 (MINGDEV1) > alter pluggable database mingdev1 open

Alter pluggable database mingdev1 open

*

ERROR at line 1:

ORA-65341: cannot open pluggable database in read/write mode

SYS@mingdev1 (MINGDEV1) > alter pluggable database mingdev1 open read only

Pluggable database altered.

In fact, the process of refresh is a process of media recovery.

MINGDEV1 (6): alter pluggable database mingdev1 refresh

2018-11-04T09:05:44.122795+08:00

Applying media recovery for pdb-4 from SCN 10564176 to SCN 10654056

Remote log information: count-4

Thr-1, seq-47, logfile-/arch/1_47_967464795.dbf, los-10621887, nxs-10641265

Thr-1, seq-46, logfile-/arch/1_46_967464795.dbf, los-10598476, nxs-10621887

Thr-1, seq-45, logfile-/arch/1_45_967464795.dbf, los-10555323, nxs-10598476

Thr-1, seq-48, logfile-/arch/parlog_1_48_839db4a4_967464795.arc, los-10641265, nxs-18446744073709551615

MINGDEV1 (6): Media Recovery Start

2018-11-04T09:05:44.172534+08:00

MINGDEV1 (6): Serial Media Recovery started

2018-11-04T09:05:44.440066+08:00

MINGDEV1 (6): Media Recovery Log / arch/1_45_967464795.dbf

2018-11-04T09:05:49.927526+08:00

MINGDEV1 (6): Media Recovery Log / arch/1_46_967464795.dbf

2018-11-04T09:05:54.341779+08:00

MINGDEV1 (6): Media Recovery Log / arch/1_47_967464795.dbf

2018-11-04T09:05:58.832855+08:00

MINGDEV1 (6): Media Recovery Log / arch/parlog_1_48_839db4a4_967464795.arc

2018-11-04T09:06:02.365844+08:00

MINGDEV1 (6): Incomplete Recovery applied until change 10654056 time 11exactly 04 Universe 09:05:38

2018-11-04T09:06:02.367512+08:00

MINGDEV1 (6): Media Recovery Complete (cdbtest1)

MINGDEV1 (6): Completed: alter pluggable database mingdev1 refresh

Incremental synchronization

Desc ming.tx

Name Null? Type

-

A NUMBER (38)

Do some DDL and DML operations within the source pdb:

Alter table ming.tx add b int

Insert into ming.tx values (1 dint 1)

Commit

Select * from ming.tx

A B

--

1 1

You can't see it in refresh pdb at this time.

Sho con_name

CON_NAME

-

MINGDEV1

PDB refresh must be executed in the target pdb

Alter pluggable database mingdev1 refresh

SYS@cdbtest1 (CDB$ROOT) > alter pluggable database MINGDEV1 open read only

Pluggable database altered.

Desc ming.tx

Name Null? Type

-

A NUMBER (38)

B NUMBER (38)

Select * from ming.tx

A B

--

1 1

SYS@cdbtest1 (CDB$ROOT) > select index_name from dba_indexes where table_name='TX'

INDEX_NAME

TX_IDX_01

This method is implemented by timing job, as shown in the following job details:

Begin dbms_scheduler.create_job (

Job_name = > 'MINGDEV1_4131117117_refresh'

Job_type = > 'PLSQL_BLOCK'

Job_action = > 'begin

Execute immediate''alter session set container = MINGDEV1''

Execute immediate''alter pluggable database refresh''; end;'

Start_date = > systimestamp

Repeat_interval = > 'FREQ = MINUTELY; INTERVAL = 1'

Enabled = > TRUE

Comments = > 'MINGDEV1 refresh')

End

Manual refresh becomes automatic refresh

Let's take a look at the automatic refresh, which needs to be performed in the fast refresh pdb

SYS@cdbtest1 (CDB$ROOT) > @ entdb mingdev1

SYS@cdbtest1 (MINGDEV1) > ALTER PLUGGABLE DATABASE mingdev1 REFRESH MODE EVERY 1 MINUTES

Pluggable database altered.

SELECT pdb_id, pdb_name, refresh_mode, refresh_interval FROM dba_pdbs

View:

SYS@cdbtest1 (CDB$ROOT) > col pdb_name for A30

SYS@cdbtest1 (CDB$ROOT) > SELECT pdb_id, pdb_name, refresh_mode, refresh_interval FROM dba_pdbs

PDB_ID PDB_NAME REFRES REFRESH_INTERVAL

-

2 PDB$SEED NONE

3 CLONEMING NONE

4 MING NONE

6 MINGDEV1 AUTO 1

Insert a piece of data into the source pdb

Insert into ming.tx values (2Jing 2)

Commit

Select * from ming.tx

A B

--

1 1

2 2

You can find in the alert log:

MINGDEV1 (6): ALTER PLUGGABLE DATABASE mingdev1 REFRESH MODE EVERY 1 MINUTES

MINGDEV1 (6): Completed: ALTER PLUGGABLE DATABASE mingdev1 REFRESH MODE EVERY 1 MINUTES

2018-12-09T12:03:55.230825+08:00

MINGDEV1 (6): alter pluggable database refresh

MINGDEV1 (6): Completed: alter pluggable database refresh

2018-12-09T12:04:54.144791+08:00

MINGDEV1 (6): alter pluggable database refresh

MINGDEV1 (6): Completed: alter pluggable database refresh

2018-12-09T12:05:54.347415+08:00

MINGDEV1 (6): alter pluggable database refresh

MINGDEV1 (6): Completed: alter pluggable database refresh

But the data in refresh pdb has not changed.

SYS@cdbtest1 (MINGDEV1) > select * from ming.tx

A B

--

1 1

That's because refresh pdb is in read only state and does not refresh automatically in read only mode. You need to turn off pdb or mount.

SYS@cdbtest1 (MINGDEV1) > shutdown immediate

Pluggable Database closed.

SYS@cdbtest1 (CDB$ROOT) > alter pluggable database MINGDEV1 open read only

Pluggable database altered.

Check again

SYS@cdbtest1 (MINGDEV1) > select * from ming.tx

A B

--

1 1

2 2

Automatic refresh is converted to manual refresh

SYS@cdbtest1 (MINGDEV1) > alter pluggable database MINGDEV1 refresh mode manual

Pluggable database altered.

Transformation from refresh PDB to ordinary PDB

SYS@cdbtest1 (CDB$ROOT) > sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

-

2 PDB$SEED READ ONLY NO

3 CLONEMING READ WRITE NO

4 MING READ WRITE NO

6 MINGDEV1 READ ONLY NO

SYS@cdbtest1 (CDB$ROOT) > alter pluggable database MINGDEV1 close immediate

Pluggable database altered.

SYS@cdbtest1 (MINGDEV1) > alter pluggable database MINGDEV1 refresh mode none

Pluggable database altered.

SYS@cdbtest1 (CDB$ROOT) > alter pluggable database MINGDEV1 open

Pluggable database altered.

Note: this process is irreversible. Once the conversion is completed, refresh can no longer be implemented.

Thank you for your reading, the above is the content of "how to build PDB refresh in oracle 12c". After the study of this article, I believe you have a deeper understanding of how to build PDB refresh in oracle 12c, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.