oracle 创建远程,Oracle 创建PDB-远程克隆

Oracle

创建PDB-

远程克隆

二：远程克隆Cloning a Remote PDB

远程克隆

将187服务器上chenpdb数据库远程克隆到222服务器上cjcpdb04库

---187

远程库

Oracle

Database

19

c

Enterprise

Edition Release

19.0

.0.0.0

-

Production

Version

19.3

.0.0.0

SQL

>

show

pdbs

CON_ID CON_NAME

OPEN

MODE

RESTRICTED

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

2

PDB$SEED

READ

ONLY

NO

3

CHENPDB

READ

WRITE

NO

---

创建测试数据

SQL

>

alter

session

set

container

=

chenpdb

;

Session

altered.

SQL

>

create

user

chen

identified

by

oracle

;

User

created.

SQL

>

grant

connect

,

resource

,

dba

,

create

pluggable

to

chen

;

SQL

>

grant

create

pluggable

to

chen

;

Grant

succeeded.

SQL

>

conn chen

/

oracle

@

chenpdb

SQL

>

create

table

employees_01

as

select

*

from

hr.employees

;

Table

created.

SQL

>

select

file_name

from

dba_data_files

;

FILE_NAME

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

/

u01

/

app

/

oracle19

/

oradata

/

CHENDB

/

chenpdb

/

system01.dbf

/

u01

/

app

/

oracle19

/

oradata

/

CHENDB

/

chenpdb

/

sysaux01.dbf

/

u01

/

app

/

oracle19

/

oradata

/

CHENDB

/

chenpdb

/

undotbs01.dbf

/

u01

/

app

/

oracle19

/

oradata

/

CHENDB

/

chenpdb

/

users01.dbf

---

为了保障数据一致性，也可以将待克隆的数据库设置为只读状态

---SQL> alter pluggable database chenpdb close;

---SQL> alter pluggable database chenpdb open read only;

---222

本地库

---tnsname.ora

添加

CHENPDB_LINK

=

(

DESCRIPTION

=

(

ADDRESS

=

(

PROTOCOL

=

TCP

)(

HOST

=

192.168

.2.187

)(

PORT

=

1521

))

(

CONNECT_DATA

=

(

SERVER

=

DEDICATED

)

(

SERVICE_NAME

=

chenpdb

)

)

)

CJCPDB04

=

(

DESCRIPTION

=

(

ADDRESS

=

(

PROTOCOL

=

TCP

)(

HOST

=

cjcos

)(

PORT

=

1521

))

(

CONNECT_DATA

=

(

SERVER

=

DEDICATED

)

(

SERVICE_NAME

=

cjcpdb04

)

)

)

[oracle

@

cjcos oradata]$ pwd

/

u01

/

app

/

oracle12

/

oradata

[oracle

@

cjcos oradata]$ mkdir cjcpdb04

---

创建连接远程库的dblink

SQL

>

CREATE

DATABASE

LINK

chenpdb_link

CONNECT

TO

chen

IDENTIFIED

BY

oracle

USING

'chenpdb_link'

;

---远程克隆

SQL

>

CREATE

PLUGGABLE

DATABASE

cjcpdb04

FROM

chenpdb

@

chenpdb_link

FILE_NAME_CONVERT

=

(

'/u01/app/oracle19/oradata/CHENDB/chenpdb/'

,

'/u01/app/oracle12/oradata/cjcpdb04/'

);

Pluggable

database

created.

---查看对应告警日志

2020

-

03

-

30

T13

:

55

:

14.265860

+

08

:

00

CREATE

PLUGGABLE

DATABASE

cjcpdb04

FROM

chenpdb

@

chenpdb_link

FILE_NAME_CONVERT

=

(

'/u01/app/oracle19/oradata/CHENDB/chenpdb/'

,

'/u01/app/oracle12/oradata/cjcpdb04/'

)

2020

-

03

-

30

T13

:

56

:

03.124780

+

08

:

00

CJCPDB04

(

6

):

Endian

type

of

dictionary

set

to

little

2020

-

03

-

30

T13

:

56

:

05.708201

+

08

:

00

****************************************************************

Pluggable

Database

CJCPDB04

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

local

undo

-

1

,

localundoscn

-

0

x0000000000000118

****************************************************************

2020

-

03

-

30

T13

:

56

:

08.228640

+

08

:

00

Applying media

recovery

for

pdb

-

4099

from

SCN

2162581

to

SCN

2162616

Remote

log

information

:

count

-

1

thr

-

1

,

seq

-

7

,

logfile

-/

u01

/

app

/

oracle19

/

product

/

19.0

.0

/

dbhome_1

/

dbs

/

archparlog_1_7_f2af50d_1036413577.arc

,

los

-

2146891

,

nxs

-

18446744073709551615

CJCPDB04

(

6

):

Media

Recovery

Start

2020

-

03

-

30

T13

:

56

:

08.253256

+

08

:

00

CJCPDB04

(

6

):

Serial Media

Recovery

started

CJCPDB04

(

6

):

max_pdb

is

8

2020

-

03

-

30

T13

:

56

:

08.725328

+

08

:

00

CJCPDB04

(

6

):

Media

Recovery

Log

/

u01

/

app

/

oracle19

/

product

/

19.0

.0

/

dbhome_1

/

dbs

/

archparlog_1_7_f2af50d_1036413577.arc

2020

-

03

-

30

T13

:

56

:

10.008175

+

08

:

00

CJCPDB04

(

6

):

Incomplete

Recovery

applied

until

change

2162616

time

03

/

30

/

2020

13

:

56

:

08

2020

-

03

-

30

T13

:

56

:

10.133184

+

08

:

00

CJCPDB04

(

6

):

Media

Recovery

Complete

(

cjcdb01

)

2020

-

03

-

30

T13

:

56

:

11.061364

+

08

:

00

CJCPDB04

(

6

):

Autotune

of

undo

retention

is

turned on.

2020

-

03

-

30

T13

:

56

:

51.439938

+

08

:

00

CJCPDB04

(

6

):

Undo

initialization

recovery

:

err

:

0

start

:

1180295010

end

:

1180295043

diff

:

33

ms

(

0.0

seconds

)

CJCPDB04

(

6

):

[

27424

] Successfully onlined

Undo

Tablespace

2

.

CJCPDB04

(

6

):

Undo

initialization

online

undo

segments

:

err

:

0

start

:

1180295043

end

:

1180295053

diff

:

10

ms

(

0.0

seconds

)

CJCPDB04

(

6

):

Undo

initialization finished serial

:

0

start

:

1180295010

end

:

1180295055

diff

:

45

ms

(

0.0

seconds

)

CJCPDB04

(

6

):

Database

Characterset

for

CJCPDB04

is

AL32UTF8

CJCPDB04

(

6

):

JIT

:

pid

27424

requesting

stop

CJCPDB04

(

6

):

Buffer

Cache

flush

started

:

6

CJCPDB04

(

6

):

Buffer

Cache

flush

finished

:

6

2020

-

03

-

30

T13

:

56

:

53.986700

+

08

:

00

Completed

:

CREATE

PLUGGABLE

DATABASE

cjcpdb04

FROM

chenpdb

@

chenpdb_link

FILE_NAME_CONVERT

=

(

'/u01/app/oracle19/oradata/CHENDB/chenpdb/'

,

'/u01/app/oracle12/oradata/cjcpdb04/'

)

---222

本地数据库

SQL

>

show

pdbs

CON_ID CON_NAME

OPEN

MODE

RESTRICTED

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

2

PDB$SEED

READ

ONLY

NO

3

CJCPDB01                       MOUNTED

4

CJCPDB02                       MOUNTED

5

CJCPDB03                       MOUNTED

6

CJCPDB04                       MOUNTED

SQL

>

alter

session

set

container

=

cjcpdb04

;

Session

altered.

SQL

>

startup

Pluggable

Database

opened.

---

查看数据

SQL

>

conn chen

/

oracle

@

cjcpdb04

Connected.

SQL

>

select

count

(*)

from

employees_01

;

COUNT

(*)

----------

107

---常见

错误：

---1

权限不足

CREATE

PLUGGABLE

DATABASE

cjcpdb04

FROM

chenpdb

@

chenpdb_link

*

ERROR

at

line

1

:

ORA

-

17628

:

Oracle

error

1031

returned

by

remote Oracle server

ORA

-

01031

:

insufficient

privileges

---187

远程数据库授予chen用户create pluggable database权限

SQL

>

conn

sys

/

oracle

@

chenpdb

as

sysdba

Connected.

SQL

>

grant

create

pluggable

database

to

chen

;

Grant

succeeded.

---2

本地数据库compatible低于远程数据库

ORA

-

65294

:

PDB

's compatible parameter value (19.0.0.0.0) is higher than CDB'

s

compatible parameter

value

(

12.2

.0.0.0

)

.

---222

本地数据库

SQL

>

show

parameter compatible

NAME

TYPE

VALUE

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

compatible

string

12.2

.0

noncdb_compatible

boolean

FALSE

---187

远程

SQL

>

show

parameter compati

NAME

TYPE

VALUE

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

compatible

string

19.0

.0

---222

本地数据库

SQL

>

alter

system

set

compatible

=

'19.0.0'

scope

=

spfile

;

System

altered.

SQL

>

shutdown

immediate

SQL

>

startup

SQL

>

show

parameter compatible

NAME

TYPE

VALUE

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

compatible

string

19.0

.0

欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！