02 September 2014
12c New Features - Notes
Latest patch right now: 12.1.0.2
HEAT
GEN 0 process flushes
audit records from the memory, or you can flush them manully from the SGA ,
to the unifiied trail. SYS.UNIFIED_AUDIT_TRAIL view;
Paralel In-memory on
ALL
FIELDS OVERRIDE – skip the columns with default values
OEM CC
Chargeback basaed on capacity
App Mgmt: based on APIs: PS, VMware,...
Plugg-ins
are used instead of db2 (life for SAP )
agent
has to be installed
Default ports:
4889/4900
(http/https) – agents
7788/7801 –
OMS
1521 – JDBC
– Repository
Agents can monitor PS suite
Homepage based on the role
Express EM is free: Web Servier is running within XMLDB of
the instance. It it’s down, the no monitoring, It monitor only through SQLs. https://host:5500/em (browser requires a Flash
Plugin)
In 12c, XMLDB is a mandatory option.
It uses dispatcher, so you need shared servers setup
SQL Developer
It allows DBA operations
Multi Tenant
PD$SEED (like a r/o Model db in SQL) + PD$ROOT (root
container) + pluggable dbs
They all share memory, redo logs, flashbadck, control files,
undo, temp
CDB_ views; SHOW
CON_NAME; SHOW CON_ID
Common vs. Local (Common users use C## prefix)
Don’t share: SYSTEM, SYSAUX
Creating
CREATE DB... ENABLE PLUGGABLE DATABASE ...
SEED FILE _NAME_CONVERT
(‘/dir1’,’/dir2’)
or use DB_CREATE_FILE _DEST
or PDB_FILE _NAME_CONVERT
Two containers created by default: CBD $ROOT
and CBD $SEED
Default service name is DBNAME.DOMAIN
New PDB from the SSID will have PDBA user with PDB_DBA role
CREATE PLUGGABLE DATABASE dbname
ADMIN USER admin1 IDENTIFIED BY pwd ROLES=(CONNECT)
FILES_NAME...
There is local and common DBA role
Plugging in
Violations are listed in PDB_PLUG _IN_VIOLATIONS
Non CDB --> PDB gets SYSTEM, SYSAUX, catalog, service
EXEC
DBMS_PDB.DESCRIBE(‘/tmp/ORCL .xml’);
CREATE
PLUGGABLE DB (NAME) USING ‘/tmp/ORCL .xml’;
...
@/noncdb_to_pdb
You can unplug PDB from one CDB and plug it into another
Dropping
ALTER DB... CLOSE; DROP PLUGGABLE DB ... [INCLUDING
DATAFILES];
Migration 11G--> 12C
1) Upg and plug OR 2) Create empty PDB and exp/impdp
Managing
SQL> SHOW CON_NAME
#Show db you are connected to
STARTUP MOUNT; ALTER PLUGGABLE DB ALL
OPEN/CLOSE;
ALTER SESSION SET CONTAINER=name; #to switch to a container
2 new EVENT triggers: Before unplug and after;
You cannot create local user in ROOT PDB.
Backup/Recovery
S/w request for introduction flashback for each PDB, as you
can only flashback instance
New RC_PDBS
HEAT MAP or ILM – Advanced Compression
option
Enable it to use ADO
– Automatic Data Optimization. HEAT_MAP =ON
As data gets less used or tblsp fuller, it compresses more
and more
6 levels: BASIC, ADVANCED, QUERY (LOW
or HIGH), ARHIVE (l or h)
Can be set on tblspc level, segment, partition, row
Lowest level policy will first take effect
ALTER TABLE tab1 ILM ENABLE_ALL ;
- enable all policies
Disable on instance level: EXEC DBMS_ILM_ADMIN.DISABLE_ILA:
Move DF online
ALTER DATABASE MOVE
DATAFILE ‘...’ TO ‘...’ [REUSE/KEEP];
Monitor progress in v$SESSION_LONGOPS
IN-DB ARCHIVING
ALTER TABLE... ROW
ARCHIVAL;
Column ORA_ARCHIVE_STATE added to each table (0 for new
rows, 1 for archived)
ALTER SESSION SET ROW
ARCHIVE VISIBILITY=ALL ;
This is not automatic. It has to be done manually
Temporal Validity
CREATE TABLE... ( ..., PERIOD FOR user_time);
It creates 2 new columns: user_time_start/end
DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME(‘ASOF’,date);
SELECT ... AS OF ....
Unified Auditing – an option
To flush:
EXEC DBMS_AUDIT+AUDIT_MGMT.FLUSH_UNIFIED...
You can configure them Immediate-Write mode of Queued_Write
(from memory)
CREATE POLICY for the “component” and enable it.
Components:
DP, RMAN, FGA, OLS, DVault, RA Security
To switch it on: relink the libraries
Roles: AUDIT_ADMIN and AUDIT_VIEWER
Policy can be on: actions, priv, roles with WHEN stmt; and
can be “BY user1, user2”
Privileges
New: SYSBACKUP, SYSDG, SYSKM (they are in PW file)
Privilege Analysis work with db Vault option
DBMS_PRIVILEGE_CAPTURE.ENABLE/DISABLE/CREATE_CAPTURE
DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT
View DBA_[UN]USED_PRIVS
Grant INHERIT PRIVILEGE user tanja to simon; -- this means
Tanja trusts that Simon’s code is honest enough to run stuff over Tanja’s
objects
Recreate pw file after the 12c upgrade to get the new PW
format
Revoke INHERIT PRIV fromPUBLIC, as every new user gets them
Redaction – option (hiding data) !
DBMS_REDACT pkg ADD/DROP/ALTER/ENABLE/DISABLE_POLICY
Full redaction, partial (xxxx-xxxx-xxxx-3424), randmon
RMAN
We can run SQL statms from RMAN prompt
In 11g, use MULTI SECTON BACKUP with SECTION SIZE option.
DUPLICATE uses backup sets in 12c by default. You can leave
dest db mounted.
Transport dbs across platform by using backup sets. In 11g,
we had to copy files and convert. DB source neds to be r/o
Table Recovery – uses AUX instance (1Gb SGA ),
called Bpsw.
Space used:
SYSTEM + recovered tablepsaced +
undo + SYSAUX
Monitoring
11.1v+ Real-Time SQL Monitoring – see where inexec plan SQL
stmt is
DB Ops – Tuning Pack ! – group statemts by users or app
Schema Comparison DBLM Pack !
It propages schema changes to diff envs
Adaptive SQL Plan Mgmt – Tuning pack !
optimizer_user_sql_plan_baseline = TRUE
It takes first plan P1 as baseline B1. When P2 comes along,
it will compare. If it is at least 3 times better create B2, otherwise, wait
for DBA to approve.
In 11g, P2 was compared to P1 just once. In 12c, it
reconsiders them every time “comparison job” runs.
11g:
DBMS_SPM.EVOLVE_SQL_PLAN _BASELINE
12C:
sys_audot_smp_evolve_task
Adaptive Executions Plan
V$SQL.IS_RESOLVED_ADAPATIVE_PLAN
Set OPTIMIZER_ADAPTIVE_REPORTING_ONLY=FALSE +
compat>=12.1
Two techniques: Dynamic plans or re-optimization
It doesn’t
touch SQLs with profile or baseline
1) Just 2 sub-plans are
considered, with the lowest costs
2) Checks whether stats have
changed by some threshold
Cardinality feedback : _optimizer_user_feedback=TRUE
It saves STATS on predicate
conditions, like ‘p>100 and t=’femail)=200 rows in real life, not 400,000
optimizer thinks it is
Stats
CREATE TABLE.. AS SELECT and INSET INTO empty table will do
stats by auto
Gathering stats can be concurrent (parallelised)
Each session has its own stats for shared GLOBAL TEMP table
Extended stats on column group or expressions
EXEC DBMS_STATS.SEED_COL_USAGE –
O does the rest automatically
Dynamic sampling=11 will consider any of the previous
numbers
Histograms
Height balance are not used any more
2 new: Top-frequency and Hybrid
1) When most data is in few buckets
2) It picks only top n popular values and puts them into n
buckets
abbbbbcccdd
Hybird: b5, c3,d2 Freq:
a1,b6,c8,d10 Top-freq:b6,c8,d10
Emergency Monitoring – Diag pack
You don’t have to wait for ADDM .
Just run it in real-time:
select dbms_addm.real_time_addm_report()
from dual;
The report does cause and effect analysis.
ASH
MMON writes to a memory buffer. When full or periodically,
MMNL empties it to AWR snapshots.
Multi-threaded architecture
It uses 1 main process that spawns threads instead of lots
of processes.
ALTER SYSTEM SET threaded_execution=true
SCMN – spawns client processes instead of listener
Flash Cache
Add flash drives to SGA
memory. In 11g=> only 1 possible
Paralel In-memory on RAC
parallel_degree_policy=AUTO
It divides segments into chunks and sends them to separate
nodes
Temporary UNDO
In 11g, undo was logged to redo
In 12c, no redo at all. ALTER SYSTEM/SESS SET
TEMPO_UNDO_ENABLED
Multiple Indexes
You can create multiple indexes on the same columns, but
only B Tree or bitmap. Either one or the other must be visible at a time.
Invisible Columns
CREATE TABLE... (... col2 NUMBER INVISIBLE);
It can be accessed only by explicitly naming the column in
DML commands or SET COLINVISIBLE ON in SQL session.
Online Redefinition
You can add dml_lock_timeout for REDEF procedure to waits on
other DMLs
It works better with VPD
Compression
Renamed: Compress in 11g is Row Store Compress in 12c
Compress for OLTP is called row store compress advanced.
Compression Adviser is slightly more clever. – option!
ONLINE DMLs
DROP INDEX/CONSTRAINT; ALTER INDEX UNUSABLE; SET COLUMN
UNUSED
ADR
New directories: Under SID /log/ddl
and debug
ALTER SYSTEM SET enable_ddl_logging=TRUE -
for DDL logging FALSE for debug
adrci: show log
Network performance: Compression – option!
SDU is 8K by default for dedicated sessions, 64K for shared,
maximum allowed is 2Mb
SQLNET.COMPRESSION=ON
Data Pump, Loader & External Tables
11.2.0.3+ whole db can be transported into another. It works
like an enhanced version of transportable tblscp. Expdp FULL =y
TRANSPORTABLE=always ...
Take tblespaces into r/o mode;
expdp; copy (use rman convert or dbms_file_transfer for endians); then impdp
TRANSPORT_DATAFILES=....
Or you can use db link from the
destitation: impdp FULL =Y NETWORK_LINK =sourcedb
TRANSPORTABLE=always....
Redo logging during import can be disabled
impdp ...
TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y|N[:TABLE|IND ]
Views can be exported as tables; Constraints preserved.
expdp VIEWS_AS_TABLES=...
Encryption password ENCRYPTION_PWD_PROMPT=Y – provided
silently at run time
Compression can be defined at import time:
TRANSFORM=TABLE_COMPRESSION_CLAUSE:’ROW
STORE COMPRESS|...”
LOBs can be converted to secure files during import:
LOB+STORAGE:SECUREFILE
SQL*Loader supports insert into IDENTITY columns, even via
direct path roads.
CREATE TABLE (col1 NUMBER
GENERATED
[ALWAYS/BY DEFAULT on NULL] AS
IDENTITY
SQL*Loader allows wildcards INFILE (‘emp?.sh*’)
SQL*Loader supports CSV
files with newlines: FIELDS CSV
WITH EMBEDDED
SQL*Loader- FIELDS ... , NULLIF = “value”
FIELD NAMES
FIRST FILE – skip the first field
Express
mode: $sqlldr user TABLE=tablename - must have tablename.dat file
Partitioning
ALTER TABEL ... MOVE
PARTITION p1 [COMPRESS .../TABLESPACE t1]
UPDATE INDEXES ONLINE; - Basic compression is free.
Reference partitioning introduced in11g. Child is
partitioned in the same way as the parent (inherited through FK key), even if
the child doesn’t have the partition column.
In 12c: INTERVALs
on reference ;
In 12c: TRUNCATE/EXCHANGE
PARTITION CASCADE
11g –only 2 partitions at a time 12c-> all at a time; for
Add/truncate/drop/merge/split
Indexes are
also affected in the same way
Partitioned Indexes: each partition is a completely diff index.
Local index can be created on a group of selected
partitions. ...
...PARTITION p1 ....) INDEXING
ON/OFF,
CREATE INDEX ... INDEXING PARTIAL.. – creates global index
only on partitions which have “INDEXING ON’.
DROP/TRUNCATE partition with UPDATE INDEXES will mark the
change in meta-data. Later, maint. job SYS.PMO_DEFERRED_GIDX_MAINT_JOB
kicks in to clean up those ORPHANED_ENTRIES (column in DBA views). You can
manually kick this with DBMS_PART.CLEANUP_GID ;
Various
TRUNCATE TABLE name CASCADE; as long as there is 1 constr
with ON DELETE CASCADE clause
select * from
tale(dbms_xplan.display_curosr(format=>’basice +note’);
[N]VARCHAR2 and RAW is now 32767 bytes – alter system set
max_string_size=extended;, run utl32k.sql script, bounce the instance, rebuild
indexes.
DB Migration Assistant – migrates dbs from standard to
Unicode set.
SecureFiles are now default storage mechanism for LOBs
SELECT ... FETCH FIRST 5 ROWS ONLY; or FETCH FIRST 10
PERCENT ONLY;
SELECT ... OFFSET 100 FETCH NEXT
10 rows only: --fetches 101-110 rows
