|
|
This was first published on https://blog.dbi-services.com/oracle-12cr2-multitenant-local-undo (2016-11-08)
Republishing here for new followers. The content is related to the the versions available at the publication date
Pluggable Databases are supposed to be isolated, containing the whole of user data and metadata. This is the definition of dictionary separation coming with multitenant architecture: only system data and metadata are at CDB level. User data and metadata are in separate tablespaces belonging to the PDB. And this is what makes the unplug/plug available: because PDB tablespaces contain everything, you can transport their datafiles from one CDB to another. However, if they are so isolated, can you explain why
There is something that is not contained in your PDB but is at CDB level, and which contains user data. The UNDO tablespace is shared:
You cannot flashback a PDB because doing so requires to rollback the ongoing transactions at the time you flashback. Information was in UNDO tablespace at that time, but is not there anymore.
It’s the same idea with Point-In-Time recovery of PDB. You need to restore the UNDO tablespace to get those UNDO records from the Point-In-Time. But you cannot restore it in place because it’s shared with other PDBs that need current information. This is why you need an auxiliary instance for PDBPITR in 12.1
To clone a PDB cannot be done with ongoing transactions because their UNDO is not in the PDB. This is why it can be done only when the PDB is read-only.
In 12.2 you can choose to have one UNDO tablespace per PDB, in local undo mode, which is the default in DBCA:
With local undo PDBs are truly isolated even when opened with ongoing transactions:
Look at the ‘RB segs’ column from RMAN report schema:
[oracle@OPC122 ~]$ rman target / Recovery Manager: Release 12.2.0.1.0 - Production on Tue Nov 8 18:53:46 2016 Copyright (c) 1982, 2016, Oracle and/or its affiliates. All rights reserved. connected to target database: CDB1 (DBID=901060295) RMAN> report schema; using target database control file instead of recovery catalog Report of database schema for database with db_unique_name CDB1 List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 880 SYSTEM YES /u02/app/oracle/oradata/CDB1/system01.dbf 3 710 SYSAUX NO /u02/app/oracle/oradata/CDB1/sysaux01.dbf 4 215 UNDOTBS1 YES /u02/app/oracle/oradata/CDB1/undotbs01.dbf 5 270 PDB$SEED:SYSTEM NO /u02/app/oracle/oradata/CDB1/pdbseed/system01.dbf 6 560 PDB$SEED:SYSAUX NO /u02/app/oracle/oradata/CDB1/pdbseed/sysaux01.dbf 7 5 USERS NO /u02/app/oracle/oradata/CDB1/users01.dbf 8 180 PDB$SEED:UNDOTBS1 NO /u02/app/oracle/oradata/CDB1/pdbseed/undotbs01.dbf 9 270 PDB1:SYSTEM YES /u02/app/oracle/oradata/CDB1/PDB1/system01.dbf 10 590 PDB1:SYSAUX NO /u02/app/oracle/oradata/CDB1/PDB1/sysaux01.dbf 11 180 PDB1:UNDOTBS1 YES /u02/app/oracle/oradata/CDB1/PDB1/undotbs01.dbf 12 5 PDB1:USERS NO /u02/app/oracle/oradata/CDB1/PDB1/users01.dbf List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 33 TEMP 32767 /u04/app/oracle/oradata/temp/temp01.dbf 2 64 PDB$SEED:TEMP 32767 /u04/app/oracle/oradata/temp/temp012016-10-04_11-34-07-330-AM.dbf 3 100 PDB1:TEMP 100 /u04/app/oracle/oradata/CDB1/PDB1/temp012016-10-04_11-34-07-330-AM.dbf
You have an UNDO tablespace in ROOT, in PDB$SEED and in each user PDB.
If you have a database in shared undo mode, you can move to local undo mode while in ‘startup migrate’. PDBs when opened will have an UNDO tablespace created. You can also create an UNDO tablespace in PDB$SEED.
Yes, in 12.2, you can open the PDB$SEED read/write for this purpose:
18:55:59 SQL> alter pluggable database PDB$SEED open read write force;
Pluggable database altered.
18:56:18 SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ WRITE NO
3 PDB1 READ WRITE NO
18:56:23 SQL> alter pluggable database PDB$SEED open read only force;
Pluggable database altered.
But remember this is only allowed for local undo migration.
The recommandation is to run in local undo mode, even in Single-Tenant.
More about it in the 12cR2 Multitenant book:
|
|
Hi Franck,
I have a doubt. I have created a lockdown profile in CDB. I have assigned the lockdown profile to PDB ORCLPDB1. When i query the output from cdb_lockdown_profiles i am getting the CON_ID column as 1.
SQL> conn / as sysdba Connected. SQL> select con_id,PROFILE_NAME,RULE_TYPE,RULE,CLAUSE,CLAUSE_OPTION,OPTION_VALUE,status from cdb_lockdown_profiles where profile_name='TEST_LOCK'; CON_ID PROFILE_NAME RULE_TYPE RULE CLAUSE CLAUSE_OPTION ---------- -------------------- -------------------- -------------------- -------------------- -------------------- OPTION_VALUE STATUS -------------------- ---------- 1 TEST_LOCK STATEMENT ALTER SESSION SET DISABLE 1 TEST_LOCK STATEMENT ALTER SESSION SET OPTIMIZER_MODE ENABLE 1 TEST_LOCK STATEMENT ALTER SESSION SET CURSOR_SHARING ENABLE 1 TEST_LOCK STATEMENT ALTER SYSTEM DISABLE SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 ORCLPDB1 READ WRITE NO 4 ORCLPDB2 READ WRITE NOAs per the con_id it should display as 3 right? why is it showing as 1? Have you tested this.Regards,
Harish
Hi Harish, This is expected. The lockdown profile rules are defined – and stored – in CDB$ROOT only. The parameter that you set for the PDB references a lockdown profile name which is stored in CDB$ROOT. So CON_ID is 1 and you can see the enabled and disabled option/feature/statements only when you are in CDB$ROOT. A PDB user will see ‘insufficient privileges’ but cannot see the reason.
Thanks a lot for your explanation.
Another scenario which i tested is below.
I created a lock down profile in CDB and set the pdb_lockdown parameter. I dropped a old lockdown profile from the CDB and set the value of pdb_lockdown to the new name. When i logged in back to the PDB it contains the old value? Why is it so?
SQL> conn /as sysdba Connected. SQL> drop lockdown profile LOCK_TEST; Lockdown Profile dropped. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 ORCLPDB1 READ WRITE NO 4 ORCLPDB2 READ WRITE NO SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> alter system set pdb_lockdown='TEST_LOCK'; System altered. SQL> alter session set container=ORCLPDB1; Session altered. SQL> alter system checkpoint; System altered. SQL> show parameter pdb_lockdown NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ pdb_lockdown string LOCK_TEST SQL> alter system checkpoint; System altered.Oracle docs say the below.If you set PDB_LOCKDOWN while connected to a CDB root, then the lockdown profile applies to all PDBs in the CDB. It does not apply to the CDB root.
If you set PDB_LOCKDOWN while connected to an application root, then the lockdown profile applies to the application root and all PDBs in the application container.
If you set PDB_LOCKDOWN while connected to a particular PDB, then the lockdown profile applies to that PDB and overrides the lockdown profile for the CDB or application container, if one exists.
Just wanted to Know if you have faced this scenario
Thanks,
Harish
Hi, The parameter that you set in the CDB applies to all PDBs as a default. But if the parameter is set at PDB level, then this one is used. This is not only for lockdown profiles but for all parameters. You have the parameter set to LOCK_TEST in the PDB and set to TEST_LOCK at CDB level. The LOCK_TEST will be used in the PDB, unless you remove it (alter system reset) at PDB level. Regards, Franck.
Thanks Franck got it. I got little confused when reading these docs hence wanted to get clarified. As always you are there to help me out.
Hi Franck, I have tested it and can confirm : There is no need to have an auxiliary instance while performing PITR of a PDB in 12.2 – pretty cool! I’m installing new 12.2 RACs and up to know, I did not found any issues
Best regards and thank you
Peter
Hi Franck, In my setup, PDB PIT recovery works in “Shared UNDO Mode” but fails in “Local UNDO Mode”
Details are as follow: Oracle 12cR2 on Linux, PDB PIT recovery fails in “Local UNDO Mode” if I drop tablespace from pdb. Steps performed are as follow: – Create PDB [A]- Create tablespace [B] in PDB [A] – Create table “emp” in tablespace[B] – Added few rows in table – backup PDB [A] – Drop tablespace [B] from PDB [A] – Run PDB [A] PIT Restore recovery – Now i get below output, when i try to get table data in PDB [A] SQL> select * from emp; select * from emp * ERROR at line 1: ORA-00376: file 18 cannot be read at this time ORA-01111: name for data file 18 is unknown – rename to correct file ORA-01110: data file 18: ‘/u01/app/oracle/product/12.2.0/dbhome_1/dbs/MISSING00018′
However same recovery works if i just make changes in table inside tablespace( instead of dropping tablespace) Also, PDB PIT recovery works fine in “Shared UNDO Mode” with same above steps (dropping tablespace).
Any idea what could be wrong?