Daily maintenance of Oracle 12c
1. Start the CDB and PDB databases
SQL > select con_id,dbid,name,open_mode from v$pdbs
CON_ID DBID NAME OPEN_MODE
-
2 1226396669 PDB$SEED READ ONLY
3 411454583 DB12CCD MOUNTED
SQL > alter pluggable database all open
Pluggable database altered.
SQL > select con_id,dbid,name,open_mode from v$pdbs
CON_ID DBID NAME OPEN_MODE
-
2 1226396669 PDB$SEED READ ONLY
3 411454583 DB12CCD READ WRITE
2. Create a PDB database
SQL > select name,cdb from v$database
NAME CDB
DB12C YES
SQL > select pdb_id,pdb_name,dbid,status,creation_scn from dba_pdbs
PDB_ID PDB_NAME DBID STATUS CREATION_SCN
--
3 DB12CCD 411454583 NORMAL 1744330
2 PDB$SEED 1226396669 NORMAL 1594399
SQL > create pluggable database test admin user test identified by test file_name_convert= ('/ u01ActionActionoradataloradataCdbAccordOnOnOnOnOnOnOnOnActionoradataUniplesoradataUniteSigma
Pluggable database created.
Create pluggable database test1 admin user test1 identified by test1 file_name_convert= ('/ u01qapax oraclecosoradata'/ u01qqapqqoracle.oradata /'/ u01qqapqqoracle.oradata /'/ u01qqapacle.oradata
3. Establish a connection
-- enter cdb
Sqlplus / as sysdba
Sqlplus sys/sys@192.168.10.32:1521/cdb as sysdba
-- enter pdb
3.1) Log in with the created admin user via tns
[oracle@ora12cA ~] $sqlplus sys/oracle@192.168.10.32:1521/test as sysdba
SQL > show con_name
CON_NAME
-
TEST
-- View the permissions that admin user has
SQL > select * from session_privs
PRIVILEGE
-
SET CONTAINER
CREATE PLUGGABLE DATABASE
CREATE SESSION
3. 2) connect pdb via set container
SQL > show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
-
2 PDB$SEED READ ONLY NO
3 PDB MOUNTED
4 TEST READ WRITE NO
SQL > alter session set container=test
Session altered.
SQL > show con_name
CON_NAME
-
TEST
4. Turn pdb on and off
SQL > alter pluggable database all close immediate
SQL > show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
-
2 PDB$SEED READ ONLY NO
3 PDB MOUNTED
4 TEST MOUNTED
-- Open a single pdb
SQL > alter pluggable database pdb open
Pluggable database altered.
SQL > show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
-
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
4 TEST MOUNTED
-- Open all pdb
SQL > alter pluggable database all open
Pluggable database altered.
SQL > show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
-
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
4 TEST READ WRITE NO
-- close all pdb
SQL > alter pluggable database all close immediate
Pluggable database altered.
SQL > show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
-
2 PDB$SEED READ ONLY NO
3 PDB MOUNTED
4 TEST
5. Create a user.
1)。 Overview
Container=all is the default in cdb, and only global users can be created in cdb.
In pdb, the default is container=current, and only local users can be created in pdb.
Http://dovelauren.blog.51cto.com/9876026/1719310
Creating a user in cdb creates the user in cdb and all pdb, but the global user in pdb requires additional authorization to access it in pdb.
2)。 Create a global user
SQL > show con_name
CON_NAME
-
CDB$ROOT
SQL > create user caterpillar identified by t
User created.
SQL > show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
-
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
4 PDB1 READ WRITE NO
3)。 Create a local user
SQL > alter session set container=pdb1
Session altered.
SQL > create user t identified by t
User created.
SQL > show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
-
4 PDB1 READ WRITE NO
6. User authorization
By default, user authorization is only given to the current container. You can also specify container=all in cdb to authorize all pdb of open and the user that exists.
Connected.
SQL > grant connect,resource to caterpillar
Grant succeeded.
SQL > grant connect,resource to caterpillar container=all
Grant succeeded.
7. Modify parameters
If you modify it in cdb, pdb will inherit it; if you modify it in pdb, it will override the meaning of parameters inherited by pdb from cdb
SQL > show con_name
CON_NAME
-
CDB$ROOT
SQL > show parameter open_cursors
NAME TYPE VALUE
-
Open_cursors integer 300
SQL > alter system set open_cursors=500
System altered.
SQL > show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
-
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
4 PDB1 READ WRITE NO
SQL > alter session set container=pdb1
Session altered.
SQL > show parameter open_cursors
NAME TYPE VALUE
-
Open_cursors integer 500
SQL > alter system set open_cursors=100
System altered.
SQL > show parameter open_cursors
NAME TYPE VALUE
-
Open_cursors integer 100
SQL > show con_name
CON_NAME
-
PDB1
SQL > alter session set container=CDB$ROOT
Session altered.
SQL > show parameter open_cursors
NAME TYPE VALUE
-
Open_cursors integer 500
