Some new features in ORACLE Database release 12.2
Feature No 1 :- Per Pluggable Falshback
Flashback work on pluggable level instead of database level . we can
flashback a single pluggable database to a restore point affected to a
particular pluggable database only.
For example:
To test this example ensure these prerequisites.
1.1- Flashback feature of database is on.
Values of the below
mentioned parameter are set properly.
DB_RECOVERY_FILE_DEST
DB_RECOVERY_FILE_DEST_SIZE
1.2- SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
1.3- Database must contain one container database
and at least one pluggable database.
Now check availability of
pluggable database and container database.
SQL> select
con_id,name from v$pdbs;
CON_ID NAME
---------- --------------------
2 PDB$SEED
3 ORCLPDB
4 PDB1
5 PDB2
SQL> alter session set container=pdb1;
Session altered.
We have connected with pluggable database pdb1.
SQL> create restore point restore_pdb_check;
Restore point created.
SQL> create table restore_pdb_check (check_no
number(3),check_name varchar2(20));
Table created.
SQL> alter pluggable database pdb1 close;
Pluggable database altered.
SQL> flashback pluggable database to restore point
restore_pdb_check;
Flashback complete.
SQL> alter pluggable database pdb1 open resetlogs;
Pluggable database altered.
SQL> desc restore_pdb_check;
ERROR:
ORA-04043: object restore_pdb_check does not exist
The database has been restored at restore point where we did not have
restore_pdb_check table.
All this flashback procedure is completed with in pluggable database
pdb1.
Feature No 2:- Hot Cloning of PDB
In Oracle database 12.2 hot cloning of PDB is very easy , no need to put
source PDB in read only mode cloned PDB can refresh with the source PDB .its
really suitable for production
environment.
For example:
To test this example ensure these prerequisites. This example is tested
on windows 64bit environment.
2.1 - The cloned PDB should be in
separate CDB(Container Database)
2.2 – The cloned PDB should be created
with refreshable option.
2.3 - The source CBD should be local undo mode.
2.4 – Database link should be created
for source and cloned PDBs.
2.5 – TNSNAMES.ora file should contain
all service name entries to connect each database.
I have tow CDBS to test this hot
cloning.
i-
ORCL
ii-
ORCLCON2
Connect
the first CDB.
SQL> conn system/maindb@orcl as sysdba;
Connected.
SQL>
select name,open_mode from v$pdbs;
NAME OPEN_MODE
---------------
----------
PDB$SEED READ ONLY
ORCLPDB MOUNTED
PDB1 READ WRITE
PDB2 MOUNTED
I will
use PDB1 as source PDB.
SQL> conn pdb1/maindb@pdb1 as sysdba;
Connected.
SQL> create user pdb1admin identified by maindb;
User created.
SQL> grant dba to pdb1admin;
Grant succeeded.
SQL> conn pdb1admin/maindb@pdb1;
Connected.
Create some table for testing under this
user.
SQL>
create table clone_check(check_no number(2),check_name varchar2(20));
Table created.
Now connect to the
destination CDB.
SQL> conn system/maindb@orclcon2 as sysdba;
Connected.
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
--------------- ----------
PDB$SEED READ ONLY
CON2PDB READ ONLY
we have one seed database
and one PDB in this CBD.
Creat one database link to
connect to source database for a pdb1admin user which we already created in
PDB1 pluggable.
SQL> create database link db_link connect to pdb1admin
identified by maindb using 'pdb1';
Database link created.
Now we are ready to create
a pluggable clone database in ORCLCON2 CDB.
SQL> create pluggable database pdb1_clone from
pdb1@db_link
file_name_convert=('c:\app\administrator\oradata\orcl\pdb1','c:\app\administrator\oradata\orcl\pdb1_clone')
refresh mode manual;
Pluggable
database created.
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
--------------- ----------
PDB$SEED READ ONLY
CON2PDB READ ONLY
PDB1_CLONE MOUNTED
To
connect with cloned database we need to update TNSNAME.ora file for pdb1_clone
service name.
SQL> select name from v$services;
NAME
---------------
SYS$BACKGROUND
SYS$USERS
orclcon2
pdb1_clone
con2pdb
orclcon2XDB
Now connect the PDB pdb1_clone to check all data.
SQL>
conn pdb1_clone/maindb@pdb1_clone as sysdba;
Connected.
Connect
with pdb1admin user to check all tables and data cloned from source PDB.
SQL> conn pdb1admin/maindb@pdb1_clone;
Connected.
SQL> select tname from tab;
TNAME
--------------------------------------------------------------------------------
CLONE_CHECK
Now connect to source PDB
to do some changes and try to refresh the cloned PDB.
SQL> conn system/maindb@orcl as sysdba;
Connected.
Inside
the CDB we need to connect to PDB PDB1
SQL> conn pdb1/maindb@pdb1 as sysdba;
Connected.
Inside the PDB we need to connect to user
pdbadmin1
SQL> conn pdb1admin/maindb@pdb1;
Connected.
Here we can
make some changes in database an we will refresh the cloned database. And these
changes will automatically reflect in cloned database.
SQL> create table
clone_check2(check2_no number(3),check2_name varchar2(20));
Table created.
SQL> select tname from tab;
TNAME
--------------------------------------------------------------------------------
CLONE_CHECK
CLONE_CHECK2
Now
connect to the cloned PDB and refresh PDB.
SQL> conn system/maindb@orclcon2 as sysdba;
Connected.
SQL> conn pdb1_clone/maindb@pdb1_clone as
sysdba;
Connected.
SQL> alter pluggable database pdb1_clone close;
Pluggable database altered.
SQL> alter pluggable database pdb1_clone refresh;
Pluggable database altered.
We have refreshed our cloned PDB and all the
changes are updated in cloned PDB. We can open it as read only and can check
the updates.
SQL> alter pluggable
database pdb1_clone open read only;
Pluggable database altered.
SQL> conn pdb1_clone/maindb@pdb1_clone as sysdba;
Connected.
SQL> conn pdb1admin/maindb@pdb1_clone;
Connected.
SQL> select tname from tab;
TNAME
--------------------------------------------------------------------------------
CLONE_CHECK
CLONE_CHECK2
We can see Hot cloning is
very easy in 12.2.
Feature
No 3 :- Increase INMEMORY area
with out restart the database;
In 12.2 we can increase INMEMORY-SIZE without restarting the database.
Before 12.2 when DBA increase the INMEMORY_SIZE they need to restart the
database but in 12.2 DBA can grow INMEMORY_SIZE according their requirements.
But the size cant not be shrink once we have grown.
For example:
To test this example ensure these prerequisites.
3.1 –
inmemory_area parameter should be
enabled.
SQL> startup
ORACLE instance started.
Total System Global Area 5117050880 bytes
Fixed Size
8929496 bytes
Variable Size
1207963432 bytes
Database Buffers
3892314112 bytes
Redo Buffers
7843840 bytes
Database mounted.
Database opened.
set
the INMEMORY_SIZE
SQL> alter system set inmemory_size=1G scope=spfile;
System altered.
SQL> startup force;
ORACLE instance started.
Total System Global Area 5117050880 bytes
Fixed Size 8929496 bytes
Variable Size
1056968488 bytes
Database Buffers
2969567232 bytes
Redo Buffers
7843840 bytes
In-Memory Area 1073741824 bytes
Database mounted.
Database opened.
Now we can grow INMEMORY_SIZE according our requirements.
SQL> alter system set
inmemory_size=2G scope=both;
System altered.
SQL> show parameter inmemory_size;
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
inmemory_size big integer 2G
SQL> show sga;
Total System Global Area 5117050880 bytes
Fixed Size
8929496 bytes
Variable Size
805310248 bytes
Database Buffers
2147483648 bytes
Redo Buffers
7843840 bytes
In-Memory Area 2147483648 bytes
No need to restart the
database to grow INMEMORY_SIZE.
Feature No 4 :- Longer Names
Oracle 12.2 support up to
128 character long object name. Now we
can keep table name , column name or other object name up to 128 character.
Feature No 5 :- Validate conversation to avoid Query
Crash
While using the conversion
function TO_DATE, TO_NUMBER we can validate
conversion whether the data is valid or not.
For example:
In this case con_check is one table in database that contains column
CON_DATE_STRING. The data type is varchar2 and it contains date data.
SQL> desc conv_check;
Name
Null? Type
-----------------------------------------
-------- ----------------------------
CHECK_NO
NUMBER(3)
CON_DATE_STRING
VARCHAR2(20)
SQL> select con_date_string from conv_check;
CON_DATE_STRING
--------------------
31-JAN-2018
28-FEB-2018
31-MAR-2018
31-APR-2018
SQL> select to_date(con_date_string,'DD/MM/YYYY') from
conv_check;
ERROR:
ORA-01858: a non-numeric character was found where a numeric was
expected
This query will result
an error.
Instead of this if we
use convert_validate function we can avoid the error.
SQL> select
to_date(con_date_string,'DD/MM/YYYY') from conv_check
where
validate_conversion(con_date_string as date,'DD/MM/YYYY')=1;
TO_DATE(C
---------
31-JAN-18
28-FEB-18
31-MAR-18
Feature No 6 :- history command
In oracle 12.2 we can
check history in SQL by running history command.
To enable this command
run set history on on SQL.
SQL> set history on;
SQL>
show hist;
history is ON and set to "100"
SQL>
history;
1 select name,open_mode from v$pdbs;
2 show hist;
3
select username from all_users;
4
select name,open_mode from v$pdbs;
5
select username from all_users;
6
select * from tab;
we can run any command
from history in SQL;
SQL> history 6 run;
These are only few
features that are new in oracle 12.2
from which we can take advantage. There may be a lot of other new
feature which are still unexplored.
I am sure this post is
informative about new features of oracle 12.2.
nicely explained
ReplyDelete