Analysis of Oracle 12c PDB

Analysis of Oracle 12c PDB

In any case, Oracle 12c has been out for so long and has never started learning for various reasons. It seems a little late now. I have always heard that PDB is a brand new architecture model in 12c. In various technical chats, we probably know that PDB is a new and pluggable architecture model, but it seems that SQLServer also has a similar architecture, in any case, the Oracle circle is still very popular, and I heard that 12c r2 can support 4096 PDBs. This is too big. Try installing docker :)

I tried it locally. In fact, I spent some time in the middle, and I was always interrupted by various things in the middle. So I left some fragments of knowledge, I deleted the environment again using my own index and then did it several times.

In this attempt, I tried two learning methods. The first one is that I did not read the official document at all, but I did it with my feelings. I encountered problems and checked google, Baidu, and metalink, in the end, I barely achieved some results, but I felt that some problems were not solved by the conventional approach. The learning deviation between the monthly solution and the actual solution seems to be larger. In the end, some implicit parameters have been changed. I think it is not desirable for beginners to learn, so despite the fact that something is barely possible, however, I feel that this learning method is not systematic and comprehensive. It is easily influenced by various strategies and is not recommended.

After a day, I plan to take an hour or so to learn more. I found that the official documents are much more detailed and more comprehensive, the images in many blogs are basically on the official website. Therefore, it is easier to learn by yourself. The probability of making mistakes based on the images is small and will not be difficult.

However, I also selected the document. I sorted out my information and made a simple summary.

This figure is official. I don't think we can figure out a better image than this one. In this example, CDB is the container, PDB is the plug-in database, hrpdb, and salespdb are all managed by the corresponding PDBA, and the overall management is the responsibility of the CDB administrator. Root is the basic template that stores containers, while seed provides a template mechanism. You can create the corresponding PDB Based on the seed template. This method is also demonstrated.

First, we should create a 12c database with the plug-in Gable database, or use the dbca silent method to do it. A command can be done directly.

The main difference between this method and 10g and 11g is that there is an option to create CDB.

Dbca-silent-createDatabase-templateName $ ORACLE_HOME/assistants/dbca/templates-gdbname newtest-sid newtest-characterSet UTF8-createAsContainerDatabase true-sysPassword oracle-systemPassword oracle

Copying database files

1% complete

3% complete

11% complete

18% complete

37% complete

Creating and starting Oracle instance

40% complete

45% complete

46% complete

47% complete

52% complete

57% complete

58% complete

59% complete

62% complete

Completing Database Creation

66% complete

70% complete

74% complete

85% complete

96% complete

100% complete

After the creation is complete, let's take a look at some simple operations on CDB and PDB.

First, let's take a look at the container id and container name.

Sqlplus/as sysdba

SQL> show con_id con_name

CON_ID

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

1

CON_NAME

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

CDB $ ROOT

SQL> select file_name from dba_data_files;

FILE_NAME

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

/U01/app/oracle/oradata/newtest/system01.dbf

/U01/app/oracle/oradata/newtest/sysaux01.dbf

/U01/app/oracle/oradata/newtest/undotbs01.dbf

/U01/app/oracle/oradata/newtest/users01.dbf

Switch to seed to view the container id and name.

SQL> alter session set container = pdb $ seed;

Session altered.

SQL> show con_id con_name

CON_ID

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

2

CON_NAME

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

PDB $ SEED

Check the data file. In fact, both root and seed have independent system tablespaces.

SQL> select file_name from dba_data_files;

FILE_NAME

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

/U01/app/oracle/oradata/newtest/pdbseed/system01.dbf

/U01/app/oracle/oradata/newtest/pdbseed/sysaux01.dbf

For more clear pdb overview information, use show pdbs.

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB $ SEED READ ONLY NO

Log On With sysdba. If multiple pdbs exist, more results will be displayed in show pdbs. No PDB has been created, only one seed

SQL> conn/as sysdba

Connected.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB $ SEED READ ONLY NO

Create a new PDB and use seed-based creation.

By default, if the file ing relationship is not specified during the creation of PDB, the following problems may occur. We can avoid using OMF first.

SQL> CREATE PLUGGABLE DATABASE pdb1 ADMIN USER pdb1admin IDENTIFIED BY oracle;

Create pluggable database pdb1 admin user pdb1admin identified by oracle

*

ERROR at line 1:

ORA-65016: FILE_NAME_CONVERT must be specified

SQL> alter system set db_create_file_dest = '/U01/app/oracle/oradata/newtest ';

System altered.

SQL> CREATE PLUGGABLE DATABASE pdb1 ADMIN USER pdb1admin IDENTIFIED BY oracle;

Pluggable database created.

Start a new pdb.

SQL> alter pluggable database pdb1 open;

Pluggable database altered.

At this time, we can view two records again.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB $ SEED READ ONLY NO

3 PDB1 READ WRITE NO

If you view more roles, you can find that many DBA-related roles are added, including the PDB_DBA role.

SQL> SELECT ROLE FROM DBA_ROLES WHERE ROLE LIKE '% DBA % ';

ROLE

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

DBA

CDB_DBA

PDB_DBA

XDBADMIN

OLAP_DBA

LBAC_DBA

6 rows selected.

1 * alter session set container = PDB $ SEED

SQL> SELECT ROLE FROM DBA_ROLES WHERE ROLE LIKE '% DBA ';

ROLE

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

DBA

CDB_DBA

PDB_DBA

OLAP_DBA

LBAC_DBA

After the creation, let's see how to delete the PDB.

Delete pdb

SQL> alter session set container = pdb1;

Session altered.

SQL> select file_name from dba_data_files;

FILE_NAME

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

/U01/app/oracle/oradata/newtest/NEWTEST/23CD9A96BE236CE4E0532F857F0A62CC/datafil

E/o1_mf_system_c3pwol80 _. dbf

/U01/app/oracle/oradata/newtest/NEWTEST/23CD9A96BE236CE4E0532F857F0A62CC/datafil

E/o1_mf_sysaux_c3pwol8c _. dbf

Start deletion.

SQL> drop pluggable database pdb1 including datafiles;

Drop pluggable database pdb1 including datafiles

*

ERROR at line 1:

ORA-65040: operation not allowed from within a pluggable database

It cannot be deleted from the current user. If you do not have a CDB administrator, you can use sysdba to delete it.

SQL> conn/as sysdba

Connected.

SQL> drop pluggable database pdb1 including datafiles;

Drop pluggable database pdb1 including datafiles

*

ERROR at line 1:

ORA-65025: Pluggable database PDB1 is not closed on all instances.

To delete a file, you must close it first.

SQL> alter pluggable database pdb1 close;

Pluggable database altered.

After close, you can delete it with confidence.

SQL> drop pluggable database pdb1 including datafiles;

Pluggable database dropped.

Now let's take a look at how the CDB administrator creates

SQL> create user c # cdbadmin identified by oracle default tablespace users temporary tablespace temp;

User created.

Grant DBA permissions.

SQL> grant dba to c # cdbadmin;

Grant succeeded.

In this case, try to connect. Of course, the Administrator name of CDB starts with c #, which is the rule.

SQL> conn c # cdbadmin/oracle

Connected.

SQL> show pdbs

The SP2-0382: The show pdbs command is not available

If you create a new PDB instance, you may want to know how to create a new PDB instance and sell it to another user. It is worth mentioning that there is a problem.

SQL> CREATE PLUGGABLE DATABASE pdb1 ADMIN USER pdb1admin IDENTIFIED BY oracle;

Pluggable database created.

SQL> conn/as sysba

SQL> alter session set container = pdb1;

Session altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

3 PDB1 MOUNTED

Let's start it by changing the writing method. There is a problem at this time. It's still okay. How can we fix it now? It doesn't matter with the syntax.

SQL> startup

Warning: PDB altered with errors.

Pluggable Database opened.

However, check the PDB status.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

3 PDB1 READ WRITE YES

SQL> conn/as sysdba

Connected.

You can analyze errors in the following ways.

SQL> select message, time from pdb_plug_in_violations;

MESSAGE

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

TIME

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

Sync PDB failed with ORA-959 during 'create user c # cdbadmin identified by * d

Efault tablespace users temporary tablespace temp container = all'

05-NOV-15 11.30.37.118745 PM

If you have questions about ora-959, use oerr to look at it.

SQL>! Oerr ora 00959.

00959,000 00, "tablespace '% s' does not exist"

// * Cause:

// * Action:

This makes the problem clear. Because there is no uers tablespace currently

Alter session set container = pdb1;

Create one.

SQL> create tablespace users datafile '/U01/app/oracle/oradata/newtest/NEWTEST/23CDBE4149E16F08E0532F857F0A58E3/users01.dbf 'size 20 M;

Tablespace created.

There is no problem with shutdown and startup again.

SQL> shutdown immediate

Pluggable Database closed.

SQL> startup

Pluggable Database opened.

These are the beginning of PDB learning, and we hope to continue to summarize them. We need to continue to practice these areas and make data migration a good job.