活动第32/33次（2018-6-30星期六上下午）

botang · 发表于 2018-6-30 09:37:39

阶段三结束，准备多租户的环境：
1. nid把 11g的一直使用的orcl数据库改成orcl2，以符合《Oracle Database 12c:Managing Multitenant Architecture》之Activity Guide对环境的要求。

2. 把ASM磁盘都扩展大一些：

[oracle@station90 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Sat Jun 30 09:26:17 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn / as sysasm
Connected.
SQL> select name, total_mb, os_mb from v$asm_disk;
NAME
--------------------------------------------------------------------------------
TOTAL_MB OS_MB
---------- ----------
FRA_0002
964 3070
FRA_0001
964 3070
FRA_0000
964 3070
NAME
--------------------------------------------------------------------------------
TOTAL_MB OS_MB
---------- ----------
DATA_0005
964 3070
DATA_0004
964 3070
DATA_0003
964 3070
NAME
--------------------------------------------------------------------------------
TOTAL_MB OS_MB
---------- ----------
DATA_0002
964 3070
DATA_0001
964 3070
DATA_0000
964 3070
9 rows selected.
SQL> alter diskgroup data resize disk DATA_0000;
Diskgroup altered.
SQL> select name, total_mb, os_mb from v$asm_disk;
NAME
--------------------------------------------------------------------------------
TOTAL_MB OS_MB
---------- ----------
FRA_0002
964 3070
FRA_0001
964 3070
FRA_0000
964 3070
NAME
--------------------------------------------------------------------------------
TOTAL_MB OS_MB
---------- ----------
DATA_0005
964 3070
DATA_0004
964 3070
DATA_0003
964 3070
NAME
--------------------------------------------------------------------------------
TOTAL_MB OS_MB
---------- ----------
DATA_0002
964 3070
DATA_0001
964 3070
DATA_0000
3070 3070
9 rows selected.
SQL> alter diskgroup data resize disk DATA_0001;
Diskgroup altered.
SQL> alter diskgroup data resize disk DATA_0002;
Diskgroup altered.
SQL> alter diskgroup data resize disk DATA_0003;
Diskgroup altered.
SQL> alter diskgroup data resize disk DATA_0004;
Diskgroup altered.
SQL> alter diskgroup data resize disk DATA_0005;
Diskgroup altered.
SQL> alter diskgroup fra resize disk fra_0000;
Diskgroup altered.
SQL> alter diskgroup fra resize disk fra_0001;
Diskgroup altered.
SQL> alter diskgroup fra resize disk fra_0002;
Diskgroup altered.
SQL> select name, total_mb, os_mb from v$asm_disk;
NAME
--------------------------------------------------------------------------------
TOTAL_MB OS_MB
---------- ----------
FRA_0002
3070 3070
FRA_0001
3070 3070
FRA_0000
3070 3070
NAME
--------------------------------------------------------------------------------
TOTAL_MB OS_MB
---------- ----------
DATA_0005
3070 3070
DATA_0004
3070 3070
DATA_0003
3070 3070
NAME
--------------------------------------------------------------------------------
TOTAL_MB OS_MB
---------- ----------
DATA_0002
3070 3070
DATA_0001
3070 3070
DATA_0000
3070 3070
9 rows selected.
SQL>

复制代码

3. 操作时的实际输出：

[root@station90 桌面]# su - oracle
[oracle@station90 ~]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.DATA.dg ora....up.type ONLINE ONLINE station90
ora.FRA.dg ora....up.type ONLINE ONLINE station90
ora....ER.lsnr ora....er.type ONLINE ONLINE station90
ora.asm ora.asm.type ONLINE ONLINE station90
ora.cssd ora.cssd.type ONLINE ONLINE station90
ora.diskmon ora....on.type OFFLINE OFFLINE
ora.evmd ora.evm.type ONLINE ONLINE station90
ora.ons ora.ons.type OFFLINE OFFLINE
ora.orcl.db ora....se.type OFFLINE OFFLINE
[oracle@station90 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Sat Jun 30 09:01:06 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size 2230072 bytes
Variable Size 1325402312 bytes
Database Buffers 805306368 bytes
Redo Buffers 4947968 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@station90 ~]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.DATA.dg ora....up.type ONLINE ONLINE station90
ora.FRA.dg ora....up.type ONLINE ONLINE station90
ora....ER.lsnr ora....er.type ONLINE ONLINE station90
ora.asm ora.asm.type ONLINE ONLINE station90
ora.cssd ora.cssd.type ONLINE ONLINE station90
ora.diskmon ora....on.type OFFLINE OFFLINE
ora.evmd ora.evm.type ONLINE ONLINE station90
ora.ons ora.ons.type OFFLINE OFFLINE
ora.orcl.db ora....se.type ONLINE ONLINE station90
[oracle@station90 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Sat Jun 30 09:04:31 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> show parameter memory
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 2G
memory_target big integer 2G
shared_memory_address integer 0
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@station90 ~]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.DATA.dg ora....up.type ONLINE ONLINE station90
ora.FRA.dg ora....up.type ONLINE ONLINE station90
ora....ER.lsnr ora....er.type ONLINE ONLINE station90
ora.asm ora.asm.type ONLINE ONLINE station90
ora.cssd ora.cssd.type ONLINE ONLINE station90
ora.diskmon ora....on.type OFFLINE OFFLINE
ora.evmd ora.evm.type ONLINE ONLINE station90
ora.ons ora.ons.type OFFLINE OFFLINE
ora.orcl.db ora....se.type ONLINE ONLINE station90
[oracle@station90 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Sat Jun 30 09:10:13 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL> alter system switch logfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size 2230072 bytes
Variable Size 1325402312 bytes
Database Buffers 805306368 bytes
Redo Buffers 4947968 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@station90 ~]$ nid
DBNEWID: Release 11.2.0.3.0 - Production on Sat Jun 30 09:11:36 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Keyword Description (Default)
----------------------------------------------------
TARGET Username/Password (NONE)
DBNAME New database name (NONE)
LOGFILE Output Log (NONE)
REVERT Revert failed change NO
SETNAME Set a new database name only NO
APPEND Append to output log NO
HELP Displays these messages NO
[oracle@station90 ~]$ nid TARGET=sys/oracle_4U DBNAME=orcl2
DBNEWID: Release 11.2.0.3.0 - Production on Sat Jun 30 09:12:39 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to database ORCL (DBID=1343950367)
Connected to server version 11.2.0
Control Files in database:
+DATA/orcl/controlfile/current.260.816169631
+FRA/orcl/controlfile/current.256.816169633
Change database ID and database name ORCL to ORCL2? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 1343950367 to 960930567
Changing database name from ORCL to ORCL2
Control File +DATA/orcl/controlfile/current.260.816169631 - modified
Control File +FRA/orcl/controlfile/current.256.816169633 - modified
Datafile +DATA/orcl/datafile/system.256.81616955 - dbid changed, wrote new name
Datafile +DATA/orcl/datafile/sysaux.257.81616955 - dbid changed, wrote new name
Datafile +DATA/orcl/datafile/undotbs1.258.81616955 - dbid changed, wrote new name
Datafile +DATA/orcl/datafile/users.259.81616955 - dbid changed, wrote new name
Datafile +DATA/orcl/datafile/example.265.81616965 - dbid changed, wrote new name
Datafile +DATA/orcl/tempfile/temp.264.81616964 - dbid changed, wrote new name
Control File +DATA/orcl/controlfile/current.260.816169631 - dbid changed, wrote new name
Control File +FRA/orcl/controlfile/current.256.816169633 - dbid changed, wrote new name
Instance shut down
Database name changed to ORCL2.
Modify parameter file and generate a new password file before restarting.
Database ID for database ORCL2 changed to 960930567.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.
[oracle@station90 ~]$ ps aux | grep ora_smon
oracle 10212 0.0 0.0 103304 2028 pts/2 S+ 09:13 0:00 grep ora_smon
[oracle@station90 ~]$ cd /u01/app/oracle/product/
[oracle@station90 product]$ ls
11.2.0 11.2.0.3 12.1.0 agent gc_inst middleware swlib
[oracle@station90 product]$ cd 11.2.0
[oracle@station90 11.2.0]$ ls
dbhome_1 grid
[oracle@station90 11.2.0]$ cd dbhome_1/
[oracle@station90 dbhome_1]$ ls
apex config dc_ocm has javavm log odbc oui rdbms sqlplus usm
assistants crs deinstall hs jdbc md olap owb relnotes srvm utl
bin csmig demo ide jdev mesg OPatch owm root.sh station90.example.com_orcl wwg
ccr css diagnostics install jdk mgw opmn perl scheduler sysman xdk
cdata ctx dv instantclient jlib network oracore plsql slax timingframework
cfgtoollogs cv emcli inventory ldap nls oraInst.loc precomp sqldeveloper ucp
clone dbs EMStage j2ee lib oc4j ord racg sqlj uix
[oracle@station90 dbhome_1]$ cd dbs
[oracle@station90 dbs]$ ls
hc_DBUA0.dat hc_orcl.dat hc_rcat.dat init.ora initorcl.ora lkORCL lkRCAT orapworcl orapwrcat snapcf_orcl.f spfilercat.ora
[oracle@station90 dbs]$ mv orapworcl orapworcl2
[oracle@station90 dbs]$ ls
hc_DBUA0.dat hc_orcl.dat hc_rcat.dat init.ora initorcl.ora lkORCL lkRCAT orapworcl2 orapwrcat snapcf_orcl.f spfilercat.ora
[oracle@station90 dbs]$ mv initorcl.ora initorcl2.ora
[oracle@station90 dbs]$ exit
logout
[root@station90 桌面]# vim /etc/oratab
[root@station90 桌面]# su - oracle
[oracle@station90 ~]$ . oraenv
ORACLE_SID = [orcl] ? orcl2
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@station90 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Sat Jun 30 09:15:35 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size 2230072 bytes
Variable Size 1224739016 bytes
Database Buffers 905969664 bytes
Redo Buffers 4947968 bytes
SQL> show parameter memo
NAME TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
hi_shared_memory_address integer
0
memory_max_target big integer
2G
memory_target big integer
2G
shared_memory_address integer
0
SQL> show parameter instance_name
NAME TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
instance_name string
orcl2
SQL> show parameter db_name
NAME TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
db_name string
orcl
SQL> alter system set db_name=orcl2 ;
alter system set db_name=orcl2
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL> alter system set db_name=orcl2 scope=spfile;
System altered.
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size 2230072 bytes
Variable Size 1224739016 bytes
Database Buffers 905969664 bytes
Redo Buffers 4947968 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
SQL> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +FRA
db_recovery_file_dest_size big integer 3882M
SQL> alter system set db_recovery_file_dest=+data;
alter system set db_recovery_file_dest=+data
*
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM
SQL> alter system set db_recovery_file_dest='+data';
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size 2230072 bytes
Variable Size 1224739016 bytes
Database Buffers 905969664 bytes
Redo Buffers 4947968 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@station90 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Sat Jun 30 09:38:51 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL2 (DBID=960930567, not open)
RMAN> backup as copy database;
Starting backup at 30-JUN-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=69 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=130 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=194 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=6 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=70 device type=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: SID=131 device type=DISK
allocated channel: ORA_DISK_7
channel ORA_DISK_7: SID=195 device type=DISK
allocated channel: ORA_DISK_8
channel ORA_DISK_8: SID=7 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/orcl/datafile/system.256.816169553
channel ORA_DISK_2: starting datafile copy
input datafile file number=00003 name=+DATA/orcl/datafile/undotbs1.258.816169553
channel ORA_DISK_3: starting datafile copy
input datafile file number=00002 name=+DATA/orcl/datafile/sysaux.257.816169553
channel ORA_DISK_4: starting datafile copy
input datafile file number=00005 name=+DATA/orcl/datafile/example.265.816169651
channel ORA_DISK_5: starting datafile copy
input datafile file number=00004 name=+DATA/orcl/datafile/users.259.816169553
output file name=+DATA/orcl2/datafile/system.267.980156347 tag=TAG20180630T093907 RECID=1 STAMP=980156366
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
output file name=+DATA/orcl2/datafile/undotbs1.268.980156349 tag=TAG20180630T093907 RECID=2 STAMP=980156367
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:24
output file name=+DATA/orcl2/datafile/sysaux.269.980156355 tag=TAG20180630T093907 RECID=5 STAMP=980156368
channel ORA_DISK_3: datafile copy complete, elapsed time: 00:00:20
output file name=+DATA/orcl2/datafile/example.270.980156363 tag=TAG20180630T093907 RECID=3 STAMP=980156368
channel ORA_DISK_4: datafile copy complete, elapsed time: 00:00:14
output file name=+DATA/orcl2/datafile/users.271.980156367 tag=TAG20180630T093907 RECID=4 STAMP=980156368
channel ORA_DISK_5: datafile copy complete, elapsed time: 00:00:08
Finished backup at 30-JUN-18
Starting Control File and SPFILE Autobackup at 30-JUN-18
piece handle=+DATA/orcl2/autobackup/2018_06_30/s_980155553.272.980156375 comment=NONE
Finished Control File and SPFILE Autobackup at 30-JUN-18
RMAN> switch database to copy ;
datafile 1 switched to datafile copy "+DATA/orcl2/datafile/system.267.980156347"
datafile 2 switched to datafile copy "+DATA/orcl2/datafile/sysaux.269.980156355"
datafile 3 switched to datafile copy "+DATA/orcl2/datafile/undotbs1.268.980156349"
datafile 4 switched to datafile copy "+DATA/orcl2/datafile/users.271.980156367"
datafile 5 switched to datafile copy "+DATA/orcl2/datafile/example.270.980156363"
RMAN>

复制代码

4. 清理一下原数据文件（现在其实变成了copy）：

RMAN> list copy;
specification does not match any control file copy in the repository
specification does not match any archived log in the repository
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
6 1 A 30-JUN-18 2189775 30-JUN-18
Name: +DATA/orcl/datafile/system.256.816169553
7 2 A 30-JUN-18 2189775 30-JUN-18
Name: +DATA/orcl/datafile/sysaux.257.816169553
8 3 A 30-JUN-18 2189775 30-JUN-18
Name: +DATA/orcl/datafile/undotbs1.258.816169553
9 4 A 30-JUN-18 2189775 30-JUN-18
Name: +DATA/orcl/datafile/users.259.816169553
10 5 A 30-JUN-18 2189775 30-JUN-18
Name: +DATA/orcl/datafile/example.265.816169651
RMAN> delete copy ;
released channel: ORA_DISK_1
released channel: ORA_DISK_2
released channel: ORA_DISK_3
released channel: ORA_DISK_4
released channel: ORA_DISK_5
released channel: ORA_DISK_6
released channel: ORA_DISK_7
released channel: ORA_DISK_8
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=69 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=130 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=194 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=6 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=70 device type=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: SID=131 device type=DISK
allocated channel: ORA_DISK_7
channel ORA_DISK_7: SID=195 device type=DISK
allocated channel: ORA_DISK_8
channel ORA_DISK_8: SID=7 device type=DISK
specification does not match any control file copy in the repository
specification does not match any archived log in the repository
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
6 1 A 30-JUN-18 2189775 30-JUN-18
Name: +DATA/orcl/datafile/system.256.816169553
7 2 A 30-JUN-18 2189775 30-JUN-18
Name: +DATA/orcl/datafile/sysaux.257.816169553
8 3 A 30-JUN-18 2189775 30-JUN-18
Name: +DATA/orcl/datafile/undotbs1.258.816169553
9 4 A 30-JUN-18 2189775 30-JUN-18
Name: +DATA/orcl/datafile/users.259.816169553
10 5 A 30-JUN-18 2189775 30-JUN-18
Name: +DATA/orcl/datafile/example.265.816169651
Do you really want to delete the above objects (enter YES or NO)? YES
deleted datafile copy
datafile copy file name=+DATA/orcl/datafile/system.256.816169553 RECID=6 STAMP=980156387
deleted datafile copy
datafile copy file name=+DATA/orcl/datafile/sysaux.257.816169553 RECID=7 STAMP=980156387
deleted datafile copy
datafile copy file name=+DATA/orcl/datafile/undotbs1.258.816169553 RECID=8 STAMP=980156387
deleted datafile copy
datafile copy file name=+DATA/orcl/datafile/users.259.816169553 RECID=9 STAMP=980156388
deleted datafile copy
datafile copy file name=+DATA/orcl/datafile/example.265.816169651 RECID=10 STAMP=980156388
Deleted 5 objects
RMAN>

复制代码

5. 处理控制文件：

Recovery Manager complete.
[oracle@station90 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Sat Jun 30 09:45:02 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string +DATA/orcl/controlfile/current
.260.816169631, +FRA/orcl/cont
rolfile/current.256.816169633
control_management_pack_access string DIAGNOSTIC+TUNING
SQL> alter system set control_files='+data','+fra' scope=spfile;
System altered.
SQL> alter database backup controlfile to trace as '/home/oracle/orcl2.sql';
Database altered.

复制代码

6. 编辑orcl2.sql脚本，要使用set 1：

CREATE CONTROLFILE REUSE DATABASE "ORCL2" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'+DATA/orcl2/onlinelog/group_1.261.980155233',
'+FRA/orcl2/onlinelog/group_1.257.980155235'
) SIZE 50M BLOCKSIZE 512,
GROUP 2 (
'+DATA/orcl2/onlinelog/group_2.262.980155235',
'+FRA/orcl2/onlinelog/group_2.258.980155235'
) SIZE 50M BLOCKSIZE 512,
GROUP 3 (
'+DATA/orcl2/onlinelog/group_3.263.980155235',
'+FRA/orcl2/onlinelog/group_3.259.980155237'
) SIZE 50M BLOCKSIZE 512
DATAFILE
'+DATA/orcl2/datafile/system.267.980156347',
'+DATA/orcl2/datafile/sysaux.269.980156355',
'+DATA/orcl2/datafile/undotbs1.268.980156349',
'+DATA/orcl2/datafile/users.271.980156367',
'+DATA/orcl2/datafile/example.270.980156363'
CHARACTER SET AL32UTF8
;
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO RECOVERY WINDOW OF 1 DAYS');
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('BACKUP OPTIMIZATION','ON');
-- Configure RMAN configuration record 3
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEFAULT DEVICE TYPE TO','''SBT_TAPE''');
-- Configure RMAN configuration record 4
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
-- Configure RMAN configuration record 5
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK PARALLELISM 8 BACKUP TYPE TO BACKUPSET');
-- Configure RMAN configuration record 6
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','''SBT_TAPE'' PARALLELISM 2 BACKUP TYPE TO BACKUPSET');
-- Configure RMAN configuration record 7
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','1 DEVICE TYPE ''SBT_TAPE'' PARMS ''ENV=(OB_MEDIA_FAMILY=station90)''');
-- Configure RMAN configuration record 8
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','2 DEVICE TYPE ''SBT_TAPE'' PARMS ''ENV=(OB_MEDIA_FAMILY=station90)''');

复制代码

执行这个控制文件脚本：

[oracle@station90 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Sat Jun 30 09:50:55 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size 2230072 bytes
Variable Size 1224739016 bytes
Database Buffers 905969664 bytes
Redo Buffers 4947968 bytes
SQL> @/home/oracle/orcl2
Control file created.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string +DATA/orcl2/controlfile/curren
t.265.980157089, +FRA/orcl2/co
ntrolfile/current.329.98015708
9
control_management_pack_access string DIAGNOSTIC+TUNING
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+DATA/orcl2/onlinelog/group_1.261.980155233
+FRA/orcl2/onlinelog/group_1.257.980155235
+DATA/orcl2/onlinelog/group_3.263.980155235
+FRA/orcl2/onlinelog/group_3.259.980155237
+DATA/orcl2/onlinelog/group_2.262.980155235
+FRA/orcl2/onlinelog/group_2.258.980155235
6 rows selected.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/orcl2/datafile/system.267.980156347
+DATA/orcl2/datafile/sysaux.269.980156355
+DATA/orcl2/datafile/undotbs1.268.980156349
+DATA/orcl2/datafile/users.271.980156367
+DATA/orcl2/datafile/example.270.980156363
SQL>

复制代码

零碎的整理（临时文件和块跟踪文件：）

alter system set audit_file_dest='/u01/app/oracle/admin/orcl2/adump' scope=spfile;

复制代码

SQL> select name from v$tempfile;
no rows selected
SQL> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +data
db_recovery_file_dest_size big integer 3882M
SQL> alter system set db_recovery_file_dest='+fra';
System altered.
SQL> alter database open ;
Database altered.
SQL> alter tablespace temp add tempfile size 30M autoextend on ;
Tablespace altered.
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DATA/orcl2/tempfile/temp.260.980157325
SQL> alter database enable block change tracking using file '+fra';
Database altered.
SQL> select * from v$block_change_tracking;
STATUS
----------
FILENAME
--------------------------------------------------------------------------------
BYTES
----------
ENABLED
+FRA/orcl2/changetracking/ctf.256.980157387
11599872
SQL>

复制代码

最后处理参数文件（为升级到12c，暂时要把参数文件放在/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl2.ora）：

SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/orcl/spfileorcl.ora
SQL> create pfile from spfile;
File created.
SQL> create spfile from pfile;
File created.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size 2230072 bytes
Variable Size 1224739016 bytes
Database Buffers 905969664 bytes
Redo Buffers 4947968 bytes
Database mounted.
Database opened.
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfileorcl2.ora
SQL>

复制代码

----------------------------------------------------------------------------------------------------------------------------------------------------------------
开始升级12c的INFRA：

12c已经不需要手工执行root.sh/rootupgrade.sh：

如果遇到以下的情况，应该是11g时使用raw devices造成，盘组挂不上：

在ASM实例中：
alter system set asm_disk_string='/dev/raw/raw*';
srvctl start diskgroup -g data
srvctl start diskgroup -g fra

/u01/app/oracle/product/11.2.0.3/grid/bin/crs_unregister ora.orcl.db

复制代码

在执行数据库从11g升级到12c之前，务必要扩大闪回区的大小：

[oracle@station90 \u684c\u9762]$ sqlplus /nolog
SQL*Plus: Release 12.1.0.2.0 Production on Sat Jun 30 10:52:48 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> select name , usable_file_mb from v$asm_diskgroup;
NAME
--------------------------------------------------------------------------------
USABLE_FILE_MB
--------------
FRA
8936
DATA
5231

复制代码

alter system set db_recovery_file_dest_size=8936M;

复制代码

dbua:

《Oracle Database 12c:Managing Multitenant Architecture》之Activity Guide对环境以下面监听器的输出为标准的(另外OMS要启动,agent要启动 )：
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=yourserver)(PORT=1521)
))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=yourserver)(PORT=5500)
)(Presentation=HTTP)(Session=RAW))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=yourserver)(PORT=5501)
)(Presentation=HTTP)(Session=RAW))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=yourserver)(PORT=5502)
)(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "cdb1" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this
service...
Service "cdb1XDB" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this
service...
Service "em12rep" has 1 instance(s).
Instance "em12rep", status READY, has 1 handler(s) for this
service...
Service "em12repXDB" has 1 instance(s).
Instance "em12rep", status READY, has 1 handler(s) for this
service...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this
service...
Service "orcl2" has 1 instance(s).
Instance "orcl2", status READY, has 1 handler(s) for this
service...
Service "orcl2XDB" has 1 instance(s).
Instance "orcl2", status READY, has 1 handler(s) for this
service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this
service...
Service "pdb1_1" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this
service...
The command completed successfully

以下三个窗口的图例：
白色  cdb1（多租户的根容器）
黄色  cdb1的插件数据库pdb1_1
绿色  orcl2普通的12c的数据库（从11.2.0.3升级而来）

三个窗口探索v$database：

三个窗口探索v$services：

三个窗口探索容器名：

三个窗口探索容器ID：

三个窗口探索第一个数据字典视图（在多租户环境中，不查dba视图，只查cdb视图）：

在多租户体系结构中，如果查询视图，尽量查询v$视图；如果要进行两个v$视图的关联，一定要加上a.con_id=b.con_id，否则会产生迪卡尔集：

SQL> select d.CON_ID , d.NAME, d.STATUS , t.name
2 from v$datafile d , v$tablespace t
3 where d.ts#=t.ts# and d.con_id=t.con_id
4 order by 1,2;
CON_ID NAME STATUS NAME
---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------- ------------------------------
1 +DATA/CDB1/DATAFILE/sysaux.266.980163695 ONLINE SYSAUX
1 +DATA/CDB1/DATAFILE/system.264.980163759 SYSTEM SYSTEM
1 +DATA/CDB1/DATAFILE/undotbs1.258.980163825 ONLINE UNDOTBS1
1 +DATA/CDB1/DATAFILE/users.259.980163825 在多租户体系结构中，如果查询视图，尽量查询v$视图；如果要进行两个v$视图的关联，一定要加上a.con_id=b.con_id，否则会产生迪卡尔集： ONLINE USERS
2 +DATA/CDB1/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE/sysaux.276.980163931 ONLINE SYSAUX
2 +DATA/CDB1/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE/system.277.980163931 SYSTEM SYSTEM
3 +DATA/CDB1/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/example.280.980164609 ONLINE EXAMPLE
3 +DATA/CDB1/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/sysaux.281.980164609 ONLINE SYSAUX
3 +DATA/CDB1/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/system.282.980164609 SYSTEM SYSTEM
3 +DATA/CDB1/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/users.283.980164609 ONLINE USERS
10 rows selected.
SQL>

复制代码

在插件里头查询，更加证明：在多租户体系结构中，如果查询视图，尽量查询v$视图；如果要进行两个v$视图的关联，一定要加上a.con_id=b.con_id，否则会产生迪卡尔集，因为能看到undo表空间：

SQL> select d.CON_ID , d.NAME, d.STATUS , t.name
2 from v$datafile d , v$tablespace t
3 where d.ts#=t.ts# and d.con_id=t.con_id
4 order by 1,2;
CON_ID NAME STATUS NAME
---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------- ------------------------------
0 +DATA/CDB1/DATAFILE/undotbs1.258.980163825 ONLINE UNDOTBS1
3 +DATA/CDB1/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/example.280.980164609 ONLINE EXAMPLE
3 +DATA/CDB1/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/sysaux.281.980164609 ONLINE SYSAUX
3 +DATA/CDB1/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/system.282.980164609 SYSTEM SYSTEM
3 +DATA/CDB1/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/users.283.980164609 ONLINE USERS
SQL>

复制代码

看不到undo表空间的对比：

在多租户环境中，特别是standalone server的环境中，根容器/种子/插件数据库，临时文件的位置其实跟GUID有关系：

-----------------------------------------------------------------------
在根容器查system用户：

select c.COMMON,c.CON_ID,c.DEFAULT_TABLESPACE,
c.TEMPORARY_TABLESPACE,c.INITIAL_RSRC_CONSUMER_GROUP,c.USERNAME
from cdb_users c
4 where c.USERNAME='SYSTEM';
COM CON_ID DEFAULT_TABLESPACE TEMPORARY_TABLESPACE INITIAL_RSRC_CONSUMER_GROUP USERNAME
--- ---------- ------------------------------ ------------------------------ -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
YES 1 SYSTEM TEMP SYS_GROUP SYSTEM
YES 3 SYSTEM TEMP SYS_GROUP SYSTEM
SQL>

复制代码

在插件数据库查system用户：

select c.COMMON,c.CON_ID,c.DEFAULT_TABLESPACE,
c.TEMPORARY_TABLESPACE,c.INITIAL_RSRC_CONSUMER_GROUP,c.USERNAME
from cdb_users c
4 where c.USERNAME='SYSTEM';
COM CON_ID DEFAULT_TABLESPACE TEMPORARY_TABLESPACE INITIAL_RSRC_CONSUMER_GROUP USERNAME
--- ---------- ------------------------------ ------------------------------ -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
YES 3 SYSTEM TEMP SYS_GROUP SYSTEM

复制代码

在普通数据库查system用户（用户不存在“公共”或者“本地”的概念）：

select c.COMMON,c.CON_ID,c.DEFAULT_TABLESPACE,
c.TEMPORARY_TABLESPACE,c.INITIAL_RSRC_CONSUMER_GROUP,c.USERNAME
from cdb_users c
4 where c.USERNAME='SYSTEM';
COM CON_ID DEFAULT_TABLESPACE TEMPORARY_TABLESPACE INITIAL_RSRC_CONSUMER_GROUP USERNAME
--- ---------- ------------------------------ ------------------------------ -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
NO 0 SYSTEM TEMP SYS_GROUP SYSTEM

复制代码

根容器上的用户都是用来“传递”给插件数据库，也就时说在根容器上创建一个用户user1，那么在每一个插件数据库上都会有user1这个定义。也就是说根容器上是不可能有本地用户的。也就是说本地用户只可能在插件数据库上。

select c.COMMON,c.CON_ID,c.DEFAULT_TABLESPACE,
c.TEMPORARY_TABLESPACE,c.INITIAL_RSRC_CONSUMER_GROUP,c.USERNAME
from cdb_users c
4 where c.COMMON='NO';
COM CON_ID DEFAULT_TABLESPACE TEMPORARY_TABLESPACE INITIAL_RSRC_CONSUMER_GROUP USERNAME
--- ---------- ------------------------------ ------------------------------ -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
NO 3 EXAMPLE TEMP DEFAULT_CONSUMER_GROUP IX
NO 3 EXAMPLE TEMP DEFAULT_CONSUMER_GROUP SH
NO 3 USERS TEMP DEFAULT_CONSUMER_GROUP PDBADMIN
NO 3 EXAMPLE TEMP DEFAULT_CONSUMER_GROUP BI
NO 3 EXAMPLE TEMP DEFAULT_CONSUMER_GROUP OE
NO 3 USERS TEMP DEFAULT_CONSUMER_GROUP SCOTT
NO 3 EXAMPLE TEMP DEFAULT_CONSUMER_GROUP HR
NO 3 EXAMPLE TEMP DEFAULT_CONSUMER_GROUP PM
8 rows selected.

复制代码

角色（虚拟人）也是一样，只不过默认有装示例方案的pdb1_1没有本地角色而已：

SQL>
select r.ROLE,r.CON_ID
from cdb_roles r
3 where r.COMMON='NO';
no rows selected
SQL> select r.ROLE,r.CON_ID
2 from cdb_roles r
3 where r.COMMON='YES';
ROLE CON_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
CONNECT 1
RESOURCE 1
DBA 1
AUDIT_ADMIN 1
AUDIT_VIEWER 1
SELECT_CATALOG_ROLE 1
EXECUTE_CATALOG_ROLE 1
DELETE_CATALOG_ROLE 1
CAPTURE_ADMIN 1
EXP_FULL_DATABASE 1
IMP_FULL_DATABASE 1
ROLE CON_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
CDB_DBA 1
PDB_DBA 1
LOGSTDBY_ADMINISTRATOR 1
DBFS_ROLE 1
GSMUSER_ROLE 1
AQ_ADMINISTRATOR_ROLE 1
AQ_USER_ROLE 1
DATAPUMP_EXP_FULL_DATABASE 1
DATAPUMP_IMP_FULL_DATABASE 1
ADM_PARALLEL_EXECUTE_TASK 1
PROVISIONER 1
ROLE CON_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
XS_RESOURCE 1
XS_SESSION_ADMIN 1
XS_NAMESPACE_ADMIN 1
XS_CACHE_ADMIN 1
GATHER_SYSTEM_STATISTICS 1
OPTIMIZER_PROCESSING_RATE 1
RECOVERY_CATALOG_OWNER 1
RECOVERY_CATALOG_USER 1
EM_EXPRESS_BASIC 1
EM_EXPRESS_ALL 1
SCHEDULER_ADMIN 1
ROLE CON_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
HS_ADMIN_SELECT_ROLE 1
HS_ADMIN_EXECUTE_ROLE 1
HS_ADMIN_ROLE 1
GLOBAL_AQ_USER_ROLE 1
OEM_ADVISOR 1
OEM_MONITOR 1
XDBADMIN 1
XDB_SET_INVOKER 1
AUTHENTICATEDUSER 1
XDB_WEBSERVICES 1
XDB_WEBSERVICES_WITH_PUBLIC 1
ROLE CON_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
XDB_WEBSERVICES_OVER_HTTP 1
GSMADMIN_ROLE 1
GSM_POOLADMIN_ROLE 1
GDS_CATALOG_SELECT 1
WM_ADMIN_ROLE 1
JAVAUSERPRIV 1
JAVAIDPRIV 1
JAVASYSPRIV 1
JAVADEBUGPRIV 1
EJBCLIENT 1
JMXSERVER 1
ROLE CON_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
JAVA_ADMIN 1
JAVA_DEPLOY 1
CTXAPP 1
ORDADMIN 1
OLAP_XS_ADMIN 1
OLAP_DBA 1
OLAP_USER 1
SPATIAL_WFS_ADMIN 1
WFS_USR_ROLE 1
SPATIAL_CSW_ADMIN 1
CSW_USR_ROLE 1
ROLE CON_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
LBAC_DBA 1
APEX_ADMINISTRATOR_ROLE 1
APEX_GRANTS_FOR_NEW_USERS_ROLE 1
DV_REALM_RESOURCE 1
DV_REALM_OWNER 1
DV_ACCTMGR 1
DV_OWNER 1
DV_ADMIN 1
DV_SECANALYST 1
DV_PUBLIC 1
DV_PATCH_ADMIN 1
ROLE CON_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
DV_MONITOR 1
DV_STREAMS_ADMIN 1
DV_GOLDENGATE_ADMIN 1
DV_XSTREAM_ADMIN 1
DV_GOLDENGATE_REDO_ACCESS 1
DV_AUDIT_CLEANUP 1
DV_DATAPUMP_NETWORK_LINK 1
CONNECT 3
RESOURCE 3
DBA 3
AUDIT_ADMIN 3
ROLE CON_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
AUDIT_VIEWER 3
SELECT_CATALOG_ROLE 3
EXECUTE_CATALOG_ROLE 3
DELETE_CATALOG_ROLE 3
CAPTURE_ADMIN 3
EXP_FULL_DATABASE 3
IMP_FULL_DATABASE 3
CDB_DBA 3
PDB_DBA 3
LOGSTDBY_ADMINISTRATOR 3
DBFS_ROLE 3
ROLE CON_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
GSMUSER_ROLE 3
AQ_ADMINISTRATOR_ROLE 3
AQ_USER_ROLE 3
DATAPUMP_EXP_FULL_DATABASE 3
DATAPUMP_IMP_FULL_DATABASE 3
ADM_PARALLEL_EXECUTE_TASK 3
PROVISIONER 3
XS_RESOURCE 3
XS_SESSION_ADMIN 3
XS_NAMESPACE_ADMIN 3
XS_CACHE_ADMIN 3
ROLE CON_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
GATHER_SYSTEM_STATISTICS 3
OPTIMIZER_PROCESSING_RATE 3
RECOVERY_CATALOG_OWNER 3
RECOVERY_CATALOG_USER 3
EM_EXPRESS_BASIC 3
EM_EXPRESS_ALL 3
SCHEDULER_ADMIN 3
HS_ADMIN_SELECT_ROLE 3
HS_ADMIN_EXECUTE_ROLE 3
HS_ADMIN_ROLE 3
GLOBAL_AQ_USER_ROLE 3
ROLE CON_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
OEM_ADVISOR 3
OEM_MONITOR 3
XDBADMIN 3
XDB_SET_INVOKER 3
AUTHENTICATEDUSER 3
XDB_WEBSERVICES 3
XDB_WEBSERVICES_WITH_PUBLIC 3
XDB_WEBSERVICES_OVER_HTTP 3
GSMADMIN_ROLE 3
GSM_POOLADMIN_ROLE 3
GDS_CATALOG_SELECT 3
ROLE CON_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
WM_ADMIN_ROLE 3
JAVAUSERPRIV 3
JAVAIDPRIV 3
JAVASYSPRIV 3
JAVADEBUGPRIV 3
EJBCLIENT 3
JMXSERVER 3
JAVA_ADMIN 3
JAVA_DEPLOY 3
CTXAPP 3
ORDADMIN 3
ROLE CON_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
OLAP_XS_ADMIN 3
OLAP_DBA 3
OLAP_USER 3
SPATIAL_WFS_ADMIN 3
WFS_USR_ROLE 3
SPATIAL_CSW_ADMIN 3
CSW_USR_ROLE 3
LBAC_DBA 3
APEX_ADMINISTRATOR_ROLE 3
APEX_GRANTS_FOR_NEW_USERS_ROLE 3
DV_REALM_RESOURCE 3
ROLE CON_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
DV_REALM_OWNER 3
DV_ACCTMGR 3
DV_OWNER 3
DV_ADMIN 3
DV_SECANALYST 3
DV_PUBLIC 3
DV_PATCH_ADMIN 3
DV_MONITOR 3
DV_STREAMS_ADMIN 3
DV_GOLDENGATE_ADMIN 3
DV_XSTREAM_ADMIN 3
ROLE CON_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
DV_GOLDENGATE_REDO_ACCESS 3
DV_AUDIT_CLEANUP 3
DV_DATAPUMP_NETWORK_LINK 3
168 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select r.ROLE,r.CON_ID
2 from cdb_roles r
3* where r.COMMON='YES' and r.con_id=1
SQL> /
ROLE CON_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
CONNECT 1
RESOURCE 1
DBA 1
AUDIT_ADMIN 1
AUDIT_VIEWER 1
SELECT_CATALOG_ROLE 1
EXECUTE_CATALOG_ROLE 1
DELETE_CATALOG_ROLE 1
CAPTURE_ADMIN 1
EXP_FULL_DATABASE 1
IMP_FULL_DATABASE 1
ROLE CON_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
CDB_DBA 1
PDB_DBA 1
LOGSTDBY_ADMINISTRATOR 1
DBFS_ROLE 1
GSMUSER_ROLE 1
AQ_ADMINISTRATOR_ROLE 1
AQ_USER_ROLE 1
DATAPUMP_EXP_FULL_DATABASE 1
DATAPUMP_IMP_FULL_DATABASE 1
ADM_PARALLEL_EXECUTE_TASK 1
PROVISIONER 1
ROLE CON_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
XS_RESOURCE 1
XS_SESSION_ADMIN 1
XS_NAMESPACE_ADMIN 1
XS_CACHE_ADMIN 1
GATHER_SYSTEM_STATISTICS 1
OPTIMIZER_PROCESSING_RATE 1
RECOVERY_CATALOG_OWNER 1
RECOVERY_CATALOG_USER 1
EM_EXPRESS_BASIC 1
EM_EXPRESS_ALL 1
SCHEDULER_ADMIN 1
ROLE CON_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
HS_ADMIN_SELECT_ROLE 1
HS_ADMIN_EXECUTE_ROLE 1
HS_ADMIN_ROLE 1
GLOBAL_AQ_USER_ROLE 1
OEM_ADVISOR 1
OEM_MONITOR 1
XDBADMIN 1
XDB_SET_INVOKER 1
AUTHENTICATEDUSER 1
XDB_WEBSERVICES 1
XDB_WEBSERVICES_WITH_PUBLIC 1
ROLE CON_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
XDB_WEBSERVICES_OVER_HTTP 1
GSMADMIN_ROLE 1
GSM_POOLADMIN_ROLE 1
GDS_CATALOG_SELECT 1
WM_ADMIN_ROLE 1
JAVAUSERPRIV 1
JAVAIDPRIV 1
JAVASYSPRIV 1
JAVADEBUGPRIV 1
EJBCLIENT 1
JMXSERVER 1
ROLE CON_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
JAVA_ADMIN 1
JAVA_DEPLOY 1
CTXAPP 1
ORDADMIN 1
OLAP_XS_ADMIN 1
OLAP_DBA 1
OLAP_USER 1
SPATIAL_WFS_ADMIN 1
WFS_USR_ROLE 1
SPATIAL_CSW_ADMIN 1
CSW_USR_ROLE 1
ROLE CON_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
LBAC_DBA 1
APEX_ADMINISTRATOR_ROLE 1
APEX_GRANTS_FOR_NEW_USERS_ROLE 1
DV_REALM_RESOURCE 1
DV_REALM_OWNER 1
DV_ACCTMGR 1
DV_OWNER 1
DV_ADMIN 1
DV_SECANALYST 1
DV_PUBLIC 1
DV_PATCH_ADMIN 1
ROLE CON_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
DV_MONITOR 1
DV_STREAMS_ADMIN 1
DV_GOLDENGATE_ADMIN 1
DV_XSTREAM_ADMIN 1
DV_GOLDENGATE_REDO_ACCESS 1
DV_AUDIT_CLEANUP 1
DV_DATAPUMP_NETWORK_LINK 1
84 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select r.ROLE,r.CON_ID
2 from cdb_roles r
3* where r.COMMON='YES' and r.con_id=3
SQL> /
ROLE CON_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
CONNECT 3
RESOURCE 3
DBA 3
AUDIT_ADMIN 3
AUDIT_VIEWER 3
SELECT_CATALOG_ROLE 3
EXECUTE_CATALOG_ROLE 3
DELETE_CATALOG_ROLE 3
CAPTURE_ADMIN 3
EXP_FULL_DATABASE 3
IMP_FULL_DATABASE 3
ROLE CON_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
CDB_DBA 3
PDB_DBA 3
LOGSTDBY_ADMINISTRATOR 3
DBFS_ROLE 3
GSMUSER_ROLE 3
AQ_ADMINISTRATOR_ROLE 3
AQ_USER_ROLE 3
DATAPUMP_EXP_FULL_DATABASE 3
DATAPUMP_IMP_FULL_DATABASE 3
ADM_PARALLEL_EXECUTE_TASK 3
PROVISIONER 3
ROLE CON_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
XS_RESOURCE 3
XS_SESSION_ADMIN 3
XS_NAMESPACE_ADMIN 3
XS_CACHE_ADMIN 3
GATHER_SYSTEM_STATISTICS 3
OPTIMIZER_PROCESSING_RATE 3
RECOVERY_CATALOG_OWNER 3
RECOVERY_CATALOG_USER 3
EM_EXPRESS_BASIC 3
EM_EXPRESS_ALL 3
SCHEDULER_ADMIN 3
ROLE CON_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
HS_ADMIN_SELECT_ROLE 3
HS_ADMIN_EXECUTE_ROLE 3
HS_ADMIN_ROLE 3
GLOBAL_AQ_USER_ROLE 3
OEM_ADVISOR 3
OEM_MONITOR 3
XDBADMIN 3
XDB_SET_INVOKER 3
AUTHENTICATEDUSER 3
XDB_WEBSERVICES 3
XDB_WEBSERVICES_WITH_PUBLIC 3
ROLE CON_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
XDB_WEBSERVICES_OVER_HTTP 3
GSMADMIN_ROLE 3
GSM_POOLADMIN_ROLE 3
GDS_CATALOG_SELECT 3
WM_ADMIN_ROLE 3
JAVAUSERPRIV 3
JAVAIDPRIV 3
JAVASYSPRIV 3
JAVADEBUGPRIV 3
EJBCLIENT 3
JMXSERVER 3
ROLE CON_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
JAVA_ADMIN 3
JAVA_DEPLOY 3
CTXAPP 3
ORDADMIN 3
OLAP_XS_ADMIN 3
OLAP_DBA 3
OLAP_USER 3
SPATIAL_WFS_ADMIN 3
WFS_USR_ROLE 3
SPATIAL_CSW_ADMIN 3
CSW_USR_ROLE 3
ROLE CON_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
LBAC_DBA 3
APEX_ADMINISTRATOR_ROLE 3
APEX_GRANTS_FOR_NEW_USERS_ROLE 3
DV_REALM_RESOURCE 3
DV_REALM_OWNER 3
DV_ACCTMGR 3
DV_OWNER 3
DV_ADMIN 3
DV_SECANALYST 3
DV_PUBLIC 3
DV_PATCH_ADMIN 3
ROLE CON_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
DV_MONITOR 3
DV_STREAMS_ADMIN 3
DV_GOLDENGATE_ADMIN 3
DV_XSTREAM_ADMIN 3
DV_GOLDENGATE_REDO_ACCESS 3
DV_AUDIT_CLEANUP 3
DV_DATAPUMP_NETWORK_LINK 3
84 rows selected.
SQL>

复制代码

不管是系统权限还是本地权限，本身都比用户/角色简单，因为权限不分公共或本地，但是授权权限这个动作却分本地授权/公共授权。公共授权就是在根容器上授权公共用户完后，直接“传递”给插件数据库的公共用户（不要在插件数据库里再做授权）。

从种子创建插件数据库：

[oracle@station90 ~]$ sqlplus /nolog
SQL*Plus: Release 12.1.0.2.0 Production on Sat Jun 30 17:26:16 2018
SQL> conn / as sysdba
Connected.
SQL> create pluggable database pdb2_1 admin user pdb2_1_admin
2 identified by oracle_4U roles=(connect)
3 file_name_convert=('/u01/app/oracle/oradata/cdb2/pdbseed',
4 '/u01/app/oracle/oradata/cdb2/pdb2_1') ;
Pluggable database created.
SQL>

复制代码

在12.1.0.2下创建插件数据库，临时文件是会写日期的：

[oracle@station90 pdb2_1]$ ls
pdbseed_temp012018-06-30_04-54-31-PM.dbf sysaux01.dbf system01.dbf
[oracle@station90 pdb2_1]$ pwd
/u01/app/oracle/oradata/cdb2/pdb2_1

复制代码

首先必须关闭插件数据库，才能删除插件数据库：

alter pluggable database pdb2_1 close immediate;
select con_id, name , open_mode from v$pdbs;
drop pluggable database pdb2_1 including datafiles;

复制代码

多租户书的这一页中的3.是完全没有必要的：

多租户书的这一页中的2.是完全没有必要的（12.1.0.2之后做克隆，pdb2_1可以不关库到read only，可以在read write状态下做）：

不用OMF的克隆的语法：

alter system set db_create_file_dest='';
create pluggable database pdb2_3 from pdb2_1
file_name_convert=('/u01/app/oracle/oradata/cdb2/pdb2_1',
'/u01/app/oracle/oradata/cdb2/pdb2_3');
drop pluggable database pdb2_3 including datafiles;

复制代码