Skip to content
Advertisements

Posts from the ‘12c’ Category

Oracle 12c forms is released

Oracle 12c forms is released now. Please find the download link here

Advertisements

creating a pluggable database from an existing non-CDB Using the DBMS_PDB package

There are three ways of creating a pluggable database from an existing non-CDB:
• Using the DBMS_PDB package to generate metadata and then creating the pluggable database
with CREATE PLUGGABLE DATABASE SQL statement
• Data Pump (using the transportable tablespace feature)
• GoldenGate replication

Note :

When using the DBMS_PDB package to convert a non-CDB to a pluggable database, the non-CDB must be Oracle12c or higher.

Take care of your Non CDB and CDB chraceter set before pluggable database

In Oracle Database 12c, all pluggable databases (PDBs) in a container database (CDB) must have
* the same Database character set (NLS_CHARACTERSET) or the NLS_CHARACTERSET need to be a (Plug-in compatible) binary subset of the CDB NLS_CHARACTERSET
* the same National character set (NLS_NCHAR_CHARACTERSET)
  as the CDB’s root container
in order to be able to plug in.

If PDB NLS_CHARACTERSET is a (Plug-in compatible) binary subset of the CDB’s NLS_CHARACTERSET the NLS_CHARACTERSET of the PDB is automatically changed to the NLS_CHARACTERSET of the CDB at the first open.

 

more detail support id : 12c Multitenant Container Databases (CDB) and Pluggable Databases (PDB) Character set restrictions / ORA-65116/65119: incompatible database/national character set ( Character set mismatch: PDB character set CDB character set ) (Doc ID 1968706.1)

Step 1:  Non CDB in read only mode

SQL> select name from v$database;

NAME
———
ORA12C

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 6710886400 bytes
Fixed Size 5298856 bytes
Variable Size 2835350872 bytes
Database Buffers 3858759680 bytes
Redo Buffers 11476992 bytes
Database mounted.
SQL> alter database open read only;

Database altered.

Step 2: run the DBMS_PDB package to create an XML file that describes the structure of the non-CDB database

SQL> BEGIN
DBMS_PDB.DESCRIBE(pdb_descr_file => ‘/u01/app/12c/ncdb.xml’);
END;
/ 2 3 4

PL/SQL procedure successfully completed.

Step 3: shutdown the non cdb database
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

 

Step 4: connect to cdb container

SQL> show user con_id con_name
USER is “SYS”

CON_ID
——————————
1

CON_NAME
——————————
CDB$ROOT

Step 5: Now, you can optionally check to see if the non-CDB is compatible with the CDB in which it will be plugged.
When you run this code provide the directory and name of the XML file that was created previously

SQL> SET SERVEROUTPUT ON
DECLARE
hold_var boolean;
begin
hold_var := DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file=>’/u01/app/12c/ncdb.xml’);
if hold_var then
dbms_output.put_line(‘YES’);
else
dbms_output.put_line(‘NO’);
end if;
end;SQL> 2 3 4 5 6 7 8 9 10
11 /
YES

PL/SQL procedure successfully completed.

If there are no compatibility issues, a YES is displayed by the prior code; a NO is displayed if the pluggable database
is not compatible. You can query the contents of the PDB_PLUG_IN_VIOLATIONS view for details on why a pluggable
database is not compatible with a CDB.

Step 6: create pluggable database

SQL> CREATE PLUGGABLE DATABASE ora12c
USING ‘/u01/app/12c/ncdb.xml’
2 3 COPY
4 FILE_NAME_CONVERT = (‘/premia/app/12c/oracle/oradata/ora12c/’,
5 ‘/premia/app/12c/oracle/oradata/CDB/ora12c/’,’/premia/app/12c/oracle/product/dbhome_1/dbs/’,’/premia/app/12c/oracle/oradata/CDB/ora12c/’);

Pluggable database created.

Step 7:

SQL> select con_id, name from v$containers;

CON_ID NAME
———- ——————————
1 CDB$ROOT
2 PDB$SEED
3 HRPDB
4 ORA12C

Step 8:  Run the below script

SQL> alter session set container=ora12c;

Session altered.

SQL> show user con_id con_name user
USER is “SYS”

CON_ID
——————————
4

CON_NAME
——————————
ORA12C
USER is “SYS”

SQL> @/premia/app/12c/oracle/product/dbhome_1/rdbms/admin/noncdb_to_pdb.sql

Step 9:

SQL> select con_id,name,open_mode from v$containers;

CON_ID NAME OPEN_MODE
———- —————————— ———-
4 ORA12C MOUNTED

SQL> alter pluggable database ORA12C open;

Pluggable database altered.

Step 10:

SQL> conn test/test@ora12c
Connected.
SQL> select * from emp;

EMPNO
———-
1
2
3
4

4 rows selected.

 

flashback database point in time recovery where is my relocated datafile available in 12c

Its new location

Documentaion says

“A flashback operation does not relocate a moved data file to its previous location. If you move a data file online from one location to another and later flash back the database to a point in time before the move, then the data file remains in the new location, but the contents of the data file are changed to the contents at the time specified in the flashback.”

Let see the example :

Step 1:

22:18:14 SQL> select current_scn from v$database;

CURRENT_SCN
———–
6270275

22:18:26 SQL> create table emp(empno number) tablespace users;

Table created.

22:18:55 SQL> insert into emp values(000001);

1 row created.

22:19:11 SQL> commit;

Commit complete.

22:19:14 SQL> select current_scn from v$database;

CURRENT_SCN
———–
6270313

Step 2:

22:19:51 SQL> select name from v$datafile;

NAME
——————————————————————————–
/premia/app/12c/oracle/oradata/ora12c/system01.dbf
/premia/app/12c/oracle/oradata/ora12c/sysaux01.dbf
/premia/app/12c/oracle/oradata/ora12c/undotbs01.dbf
/premia/app/12c/oracle/oradata/ora12c/users01.dbf
/premia/app/12c/oracle/product/dbhome_1/dbs/tools01.dbf

Step 3:

22:19:59 SQL> alter database move datafile ‘/premia/app/12c/oracle/oradata/ora12c/users01.dbf’ to ‘/premia/app/12c/oracle/product/dbhome_1/dbs/users01.dbf’;

Database altered.

Step 4:

22:20:29 SQL> select name from v$datafile;

NAME
——————————————————————————–
/premia/app/12c/oracle/oradata/ora12c/system01.dbf
/premia/app/12c/oracle/oradata/ora12c/sysaux01.dbf
/premia/app/12c/oracle/oradata/ora12c/undotbs01.dbf
/premia/app/12c/oracle/product/dbhome_1/dbs/users01.dbf
/premia/app/12c/oracle/product/dbhome_1/dbs/tools01.dbf

Step 5:

22:20:39 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
22:22:57 SQL> startup mount
ORACLE instance started.

Total System Global Area 5016387584 bytes
Fixed Size 5293992 bytes
Variable Size 1258293336 bytes
Database Buffers 3741319168 bytes
Redo Buffers 11481088 bytes
Database mounted.
22:23:09 SQL> !rman target /

Recovery Manager: Release 12.1.0.2.0 – Production on Sat Sep 26 22:23:23 2015

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORA12C (DBID=264834751, not open)

RMAN> FLASHBACK DATABASE TO SCN 6270275;

 

RMAN> alter database open resetlogs;

RMAN> select name from v$datafile;

NAME
——————————————————————————–

/premia/app/12c/oracle/oradata/ora12c/system01.dbf
/premia/app/12c/oracle/oradata/ora12c/sysaux01.dbf
/premia/app/12c/oracle/oradata/ora12c/undotbs01.dbf
/premia/app/12c/oracle/product/dbhome_1/dbs/users01.dbf
/premia/app/12c/oracle/product/dbhome_1/dbs/tools01.dbf