Oracle 12c Daily Maintenance

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$seed1226396669 NORMAL 1594399

Sql> Create pluggable Database Test Admin user test identified by Test file_name_convert= ('/u01/app/oracle/oradata/cdb /pdbseed/', '/u01/app/oracle/oradata/cdb/test/');

Pluggable database created.

Create pluggable Database test1 Admin user test1 identified by test1 file_name_convert= ('/u01/app/oracle/oradata/cdb/ test/', '/u01/app/oracle/oradata/cdb/test1/');

3. Establish a connection

--Enter CDB

Sqlplus/as SYSDBA

Sqlplus Sys/[email protected]:1521/cdb as Sysdba

--Into the PDB

3.1) Login with Admin user created by TNS mode

[Email protected] ~]$ sqlplus sys/[email protected]:1521/test as Sysdba

Sql> Show Con_name

Con_name

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

TEST

--View Admin user's permissions

Sql> select * from Session_privs;

PRIVILEGE

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

SET CONTAINER

CREATE Pluggable DATABASE

CREATE SESSION

3.2) connect the 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 the 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

The default is Container=all in CDB, only global users can be created in CDB;

The PDB is container=current by default and only local users can be created in the PDB.

http://dovelauren.blog.51cto.com/9876026/1719310

If the user is created in CDB, the user is created in the CDB and all the PDB, but the global user in the PDB needs additional authorization to be accessible in the PDB.

2). Create a global user

Sql> Show Con_name

Con_name

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

Cdb$root

sql> Create User C # #t 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

User authorization By default is only given to the current container, in CDB can also be specified Container=all, for all open PDB and the existence of the user is authorized

Connected.

Sql> Grant Connect,resource to C # #t;

Grant succeeded.

Sql> Grant Connect,resource to C # #t Container=all;

Grant succeeded.

7. Modifying parameters

When modified in CDB, the PDB inherits, and if modified in the PDB overrides the meaning of the PDB inherited from the 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

This article is from the "unity of Knowledge" blog, please be sure to keep this source http://dovelauren.blog.51cto.com/9876026/1719416

Oracle 12c Daily Maintenance