oracle 12c 取消omf,Oracle12c管理

--EMDE

show parameter dispatchers

exec DBMS_XDB_CONFIG.SETHTTPPORT(0);

exec DBMS_XDB_CONFIG.SETHTTPSPORT(0);

select dbms_xdb_config.gethttpport from dual;

select dbms_xdb_config.gethttpsport from dual;

exec DBMS_XDB_CONFIG.SETHTTPPORT(0);

exec DBMS_XDB_CONFIG.SETHTTPSPORT(5500);

pdb创建服务

EXEC DBMS_SERVICE.CREATE_SERVICE('QINTEST','QINTEST');

EXEC DBMS_SERVICE.START_SERVICE('QINTEST');

EXEC DBMS_SERVICE.STOP_SERVICE('QINTEST');

EXEC DBMS_SERVICE.DELETE_SERVICE('QINTEST');

要为每个pdb创建及修改服务

srvctl add service -d orcl -s qintest -pdb pdb1

srvctl start serivce -d orcl -s qintest -pdb pdb1

select * From cdb_services

从PDB#SEED 创建PDB

create pluggable database pdb19d admin user qin identified by

oracle roles=(dba)

file_name_convert=('/data/oradata/ORA19C/pdbseed','/data/oradata/ORA19C/pdb19d');

克隆本地PDB

alter pluggable database pdb1 close;

alter pluggable database pdb1 open read only;

方式1：--OMF创建格式

create pluggable database pdb19d from pdb19c

create_file_dest='/data/oradata/ORA19D';

方式2：--建议这种模式

create pluggable database pdb19d from pdb19c

file_name_convert=('/data/oradata/ORA19C/pdb19c','/data/oradata/ORA19C/pdb19d');

方式3：先设置参数再复制数据库

alter session set

pdb_file_name_convert='/data/oradata/ORA19C/pdb19c','/data/oradata/ORA19C/pdb19d';

create pluggable database pdb19d from pdb19c

alter pluggable database pdb19d open;

alter pluggable database pdb19d close immediate;

drop pluggable database pdb19d including datafiles;

克隆远程非CDB qintest

ORACLE_SID=qintest

shutdown immediate

startup open read only;

create public database link qinlink connect to systen

identified by qintest using 'qinlink';

mkdir -P 数据库路径

登录cdb

alter system set

db_create_file_dest='/data/oradata/ORA19C/pdb19d';

create plusgable database pdb19d from non$cdb@qinlink;

alter session set container=pdb19d;

@?/rdbms/admin/noncdb_to_pdb.sql;

alter pluggable database pdb19d open;

--设置自动打开PDB

alter pluggable database all open;

alter pluggable database all save state;

alter pluggable database all discard state;

shutdown immediate

startup

select con_name,instance_name, state from

cdb_pdb_saved_states;

pdb参数文件

alter session set container=pdb19d;

alter system set

db_create_file_dest='/data/oradata/ORA19C/pdb19d';

select * From cdb_tables t where

t.table_name='PDB_SPFILES';

select a.pdb_uid,b.name,a.name,a.value$ from pdb_spfile$ a

,v$pdbs b

where a.pdb_uid=b.con_uid

order by b.name;

select * from v$system_parameter d where

d.name='db_create_file_dest';

查看PDB的历史记录

select

a.PDB_NAME,OP_TIMESTAMP,a.OPERATION,a.CLONED_FROM_PDB_NAME from

cdb_pdb_history a;

重命名PDB

alter pluggable database pdb2 close immediate;

alter pluggable database pdb2 open restricted;

或者

shutdown immediate

startup open restrict

alter session set container=pdb2;

select * From global_name;

alter pluggable database pdb2 rename global_name to

pdb19d;

shutdown immediate

startup;

PDB数据文件联机

alter pluggable database datafile

'/data/oradata/ORA19C/pdb19d/qintest01.dbf'online;

PDB默认表空间

alter pluggable database default tablespace qintest;

更改PDB默认表空间

alter pluggable database default tempporary tablespace

temp_tbs;

用户创建

alter system set common_user_prefix='C##' scope=spfile;

startup force

show pdbs

drop user C##qin cascade;

create user C##qin identified by qin;

grant connect,resource,unlimited tablespace to C##qin;

select * From cdb_users d where d.USERNAME='C##QIN';

alter system set common_user_prefix='' scope=spfile;

--pdb中创建用户

container=current

create user qin identified by qin container=current;

grant connect,resource,unlimited tablespace to qin;

临时undo

alter session set temp_UNDO_ENABLED=true;

alter system set temp_UNDO_ENABLED=true;

alter system set temp_UNDO_ENABLED=false;

插入以前拔出的PDB

--创建数据库

create pluggable database pdb19d from pdb19c

file_name_convert=('/data/oradata/ORA19C/pdb19c','/data/oradata/ORA19C/pdb19d');

--关闭数据库

alter pluggable database pdb19d close immediate;

--拔出数据库

alter pluggable database pdb19d unplug into

'/data/oradata/ORA19C/pdb19d.xml';

删除数据库保留数据文件(也可删除文件，未测试)

drop pluggable database pdb19d keep datafiles;

插入之前拔出的数据库

create pluggable database pdb19d using

'/data/oradata/ORA19C/pdb19d.xml' nocopy tempfile reuse;

select name from v$pdbs;

非CDB数据库插入CDB

1、select name,cdb,open_mode from v$database;

CDB数据库实例ORCL

非CDB数据库实例TEST

startup mount

alter database open read only;

2、在TEST库中创建xml文件

exec dbms_pdb.describe(pdb_descr_file =>

'/tmp/test.xml');

3、创建test路径

mkdir /data/oradata/orcl/test

4、连接CDB并创建PDB，在test中我们就可以查询到ogg6的dbid

select con_id,dbid,name,open_mode from v$pdbs;

create pluggable database test using '/tmp/test.xml' copy

file_name_convert = ('/data/oradata/orcl',

'/data/oradata/orcl/test');

5、切换到PDB并执行脚本

alter session set container=test;

@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

show pdbs

show con_id

alter pluggable database test open;

表空间管理

alter session setcontainer=CDB$ROOT;

create tablespace dave datafile'/data/oradata/orcl/dave01.dbf'

size 1m autoextend on next 1m;

alter tablespace dave add

datafile'/data/oradata/orcl/dave02.dbf' size 1m autoextend on next

1m;

select tablespace_name,file_id,file_name from

dba_data_files;

drop tablespace dave including contents and datafiles;

alter session setcontainer=pdb19c;

create tablespace dave

datafile'/data/oradata/orcl/pdb1/dave01.dbf' size 1m autoextend on

next 1m;

alter tablespace dave add

datafile'/data/oradata/orcl/pdb1/dave02.dbf' size 1m autoextend on

next 1m;

select tablespace_name,file_id,file_name from

dba_data_files;

drop tablespace dave including contents and datafiles;

rman备份

直接连cdb内备份pdb

backup pluggable database pdb19c format

'/u01/rman/pdb19c_full_%U.bak';

list backup of pluggable database

pdb19c;

可以在cdb的mount\open状态恢复pdb数据库

restore pluggable database pdb19c;

recover pluggable database pdb19c;

backup database root FORMAT

'/backup/oraback/bk_%s_%p_%t';

restore database root;

recover database root;

eg:

connect target sys/oracle@orcl

备份全库

RMAN> backup database；

备份CDB库

RMAN> backup database root；

备份PDB库(可多个)

RMAN> backup pluggable database pdbeisoo,pdb3;

备份表空间

connect target sys/oracle@pdbeisoo

RMAN> backup tablespace tbs_pdbeisoo;

恢复全库

connect target sys/oracle@orcl

run

{

allocate channel c1 type disk;

startup mount;

restore database;

recover database;

alter database open;

release channel c1;

}

恢复CDB库

connect target sys/oracle@orcl

run

{

allocate channel c1 type disk;

startup mount;

restore database root;

recover database root;

alter database open;

release channel c1;

}

恢复PDB库(可多个同时恢复)

connect target sys/oracle@orcl

run

{

allocate channel c1 type disk;

alter pluggable database pdbeisoo,pdb3 close;

restore pluggable database pdbeisoo,pdb3;

recover pluggable database pdbeisoo,pdb3;

alter pluggable database pdbeisoo,pdb3 open;

release channel c1;

}

单独恢复CDB库数据文件

connect target sys/oracle@orcl

run{

allocate channel c1 type disk;

sql 'alter database datafile 13 offline';

restore datafile 13;

recover datafile 13;

sql 'alter database datafile 13 online';

release channel c1;

}

单独恢复PDB库数据文件

connect target sys/oracle@pdbeisoo

run{

allocate channel c1 type disk;

sql 'alter database datafile 16 offline';

restore datafile 16;

recover datafile 16;

sql 'alter database datafile 16 online';

release channel c1;

}

归档

alter system set

log_archive_dest_1='location=/u01/app/oracle/archivelog';

alter system set log_archive_format = "archive_%t_%s_%r.log"

scope=spfile;

alter system set log_archive_max_processes = 8;

shutdown immediate

startup mount;

alter database archivelog