oracle 12c维护命令,Oracle 12c CDB&PDB 基本维护

最新推荐文章于 2025-03-13 11:19:31 发布

赵大侠咯

最新推荐文章于 2025-03-13 11:19:31 发布

阅读量692

点赞数

文章标签： oracle 12c维护命令

1.查看数据库是否为多租户数据库(CDB)

SQL> SELECT CDB FROM V$DATABASE;

CDB

YES

2.查看当前容器名字

以下命令只能在12c客户端输入

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

2 PDB$SEED READ ONLY NO

3 ORCLPDB READ WRITE NO

SQL> show con_name

CON_NAME

CDB$ROOT

或

select sys_context('userenv', 'con_name') "Container DB" from dual;

3.pdb管理

3.1直接创建pdb数据库

SQL> CREATE PLUGGABLE DATABASE testdb

2 ADMIN USER testdbdb IDENTIFIED BY testdb

3 STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M)

4 DEFAULT TABLESPACE testdbdb DATAFILE 'C:\app\Administrator\virtual\oradata\orcl\testdbdb_001.dbf' SIZE 25M AUTOEXTEND ON;

DEFAULT TABLESPACE testdbdb DATAFILE 'C:\app\Administrator\virtual\oradata\orcl\testdbdb_001.dbf' SIZE 25M AUTOEXTEND ON

第 4 行出现错误:

ORA-65016: 必须指定 FILE_NAME_CONVERT

SQL> set line 1000

SQL> show parameter db_create_file_dest

NAME TYPE VALUE

db_create_file_dest string

SQL> alter system set db_create_file_dest='C:\app\Administrator\virtual\oradata';

系统已更改。

SQL> show parameter db_create_file_dest

NAME TYPE VALUE

db_create_file_dest string C:\app\Administrator\virtual\o

radata

切换到root容器创建

SQL> alter session set container=cdb$root;

会话已更改。

SQL> show con_name

CON_NAME

------------------------------ CDB$ROOT

SQL> CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb2 IDENTIFIED BY pdb2

2 STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M)

3 DEFAULT TABLESPACE pdb2

4 DATAFILE 'C:\app\Administrator\virtual\oradata\orcl\pdb2\pdb201.dbf' SIZE 250M

5 AUTOEXTEND ON 6 PATH_PREFIX = 'C:\app\Administrator\virtual\oradata\orcl\pdb2'

7 FILE_NAME_CONVERT = ('C:\app\Administrator\virtual\oradata\orcl\DATAFILE\O1_MF_S

8 YSTEMB9GXSYNO.DBF', 'C:\app\Administrator\virtual\oradata\orcl\pdb2\system01.dbf'

9 ,'C:\app\Administrator\virtual\oradata\orcl\DATAFILE\O1_MF_SYSAUXB9GXSYLX.DBF'

10 ,'C:\app\Administrator\virtual\oradata\orcl\pdb2\sysaux01.dbf'

11 ,'C:\app\Administrator\virtual\oradata\orcl\DATAFILE\PDBSEED_TEMP012017-12-13_15

12 -02-15-PM.dbf','C:\app\Administrator\virtual\oradata\orcl\pdb2\temp01.dbf');

CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb2 IDENTIFIED BY pdb2

第 1 行出现错误:

ORA-65005: 文件缺少文件名模式或其文件名模式无效 - C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\ORCL\PDBSEED\SYSTEM01.DBF

SQL> set line 200

SQL> col file_name for a80

SQL> select con_id,file_name from cdb_data_files order by 1;

CON_ID FILE_NAME

1 C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\ORCL\SYSTEM01.DBF

1 C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\ORCL\USERS01.DBF

1 C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\ORCL\UNDOTBS01.DBF

1 C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\ORCL\SYSAUX01.DBF

3 C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\ORCL\ORCLPDB\UNDOTBS01.DBF

3 C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\ORCL\ORCLPDB\USERS01.DBF

3 C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\ORCL\ORCLPDB\SYSTEM01.DBF

3 C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\ORCL\ORCLPDB\SYSAUX01.DBF

已选择 8 行。

SQL>create pluggable database pdb2 admin user pdb2 identified by oracle file_name_convert=('C:\app\Administrator\virtual\oradata\orcl\pdbseed','C:\app\Administrator\virtual\oradata\orcl\pdb2');

SQL> alter pluggable database pdb2 open read write;

SQL> create TABLESPACE pdb2

2 DATAFILE 'C:\app\Administrator\virtual\oradata\orcl\pdb2\pdb201.dbf' SIZE 250M

3 AUTOEXTEND ON

4 ;

表空间已创建。

SQL> alter user pdb2 DEFAULT TABLESPACE pdb2;

用户已更改。

SQL> select con_id,file_name from cdb_data_files order by 1;

CON_ID FILE_NAME

4 C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\ORCL\PDB2\SYSTEM01.DBF

4 C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\ORCL\PDB2\SYSAUX01.DBF

4 C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\ORCL\PDB2\UNDOTBS01.DBF

4 C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\ORCL\PDB2\PDB201.DBF

SQL> DROP PLUGGABLE DATABASE pdb2 INCLUDING DATAFILES;

DROP PLUGGABLE DATABASE pdb2 INCLUDING DATAFILES *

第 1 行出现错误:

ORA-65025: 未在所有实例上关闭可插入数据库 PDB2。

SQL> alter pluggable database pdb2 close 2 ;

插接式数据库已变更。

SQL> DROP PLUGGABLE DATABASE pdb2 INCLUDING DATAFILES;

插接式数据库已删除。

CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb2 IDENTIFIED BY pdb2

STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M)

PATH_PREFIX = 'C:\app\Administrator\virtual\oradata\orcl\pdb2'

FILE_NAME_CONVERT = ('C:\app\Administrator\virtual\oradata\orcl\pdbseed\system01.dbf'

, 'C:\app\Administrator\virtual\oradata\orcl\pdb2\system01.dbf'

,'C:\app\Administrator\virtual\oradata\orcl\pdbseed\sysaux01.dbf'

,'C:\app\Administrator\virtual\oradata\orcl\pdb2\sysaux01.dbf'

,'C:\app\Administrator\virtual\oradata\orcl\pdbseed\TEMP012017-12-12_14-30-01-009-PM.dbf'

,'C:\app\Administrator\virtual\oradata\orcl\pdb2\temp01.dbf',

'C:\app\Administrator\virtual\oradata\orcl\pdbseed\UNDOTBS01.DBF'

,'C:\app\Administrator\virtual\oradata\orcl\pdb2\UNDOTBS01.DBF')；

SQL> alter pluggable database pdb2 open read write;

插接式数据库已变更。

SQL> alter session set container=pdb2;

会话已更改。

SQL> create TABLESPACE pdb2 DATAFILE 'C:\app\Administrator\virtual\oradata\orcl\pdb2\pdb201.dbf' SIZE 250M AUTOEXTEND ON;

表空间已创建。

SQL> alter user pdb2 DEFAULT TABLESPACE pdb2;

用户已更改。

SQL> set line 1000

SQL> col file_name format A100

SQL> select con_id,file_name from cdb_data_files order by 1;

CON_ID FILE_NAME

4 C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\ORCL\PDB2\SYSTEM01.DBF

4 C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\ORCL\PDB2\SYSAUX01.DBF

4 C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\ORCL\PDB2\UNDOTBS01.DBF

4 C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\ORCL\PDB2\PDB201.DBF

3.2 unplug

SQL> alter pluggable database pdb2 unplug into 'c:\pdb2.xml'; alter pluggable database pdb2 unplug into 'c:\pdb2.xml'

第 1 行出现错误:

ORA-65025: 未在所有实例上关闭可插入数据库 PDB2。

SQL> alter pluggable database pdb2 close;

插接式数据库已变更。

SQL> alter pluggable database pdb2 unplug into 'C:\app\Administrator\virtual\oradata\pdb2.xml';

插接式数据库已变更。

unplug后pdb只能mount不能open

SQL> alter pluggable database pdb2 open read only;

alter pluggable database pdb2 open read only

第 1 行出现错误:

ORA-65086: 无法打开/关闭可插入数据库

SQL> drop pluggable database pdb2;

插接式数据库已删除。

SQL> create pluggable database pdb2 using 'C:\app\Administrator\virtual\oradata\pdb2.xml' nocopy;

插接式数据库已创建。

静默建pdb和cdb

dbca -silent -createDatabase -templateName C:\app\Administrator\virtual\product\12.2.0\dbhome_2\assistants\dbca\templates\General_Purpose.dbc -gdbname newtest -sid newtest -characterSet UTF8 -createAsContainerDatabase true -sysPassword zncg3008_ZNCG -systemPassword zncg3008ZNCG