12c

12c New features

12c Upgrade 1Z0-060 preparation
Architecture :

The CDB have controlfile and the instance, but no datafiles directly attached to the CDB.
Once you startup CDB the PDBs remain in mount mode unless manually opened.
There’s a single active UNDO tablespace per CDB.

CDB$ROOT is a container you cannot unplug or clone it.
It have SYSTEM, SYSAUX, UNDO, TEMP tablepsaces, redo logss,…Archieved Logs, Control Files, Flashback Logs.It has a container ID of 1.
store the system metadata, objects used by the databases internally and owners created at the time of its creation i.e. SYS and SYSTEM. 

It also contains the information about all pluggable databases associated with it.

PDB$SEED can be cloned but not unplugged (but it’s a PDB).This PDB has a container ID of 2.
It’s always in read-only mode,
Seed database also can’t be dropped

Seed container can be backed up.

PDB can be cloned and unplugged
An unplugged PDB is closed forever. You cannot open it again.
When you create a PDB a service with same name is created automatically
If you unplug a PDB and plug it into another CDB, Common users within previous PDB will be locked. To access objects in Common users within previous PDB , create that common user in the new CBD as well (after closing the new PDB)
Witin PDB, CDB_xxx or DBA_xxx views show the same information relevant to that PDB only.
cdb_tablespaces , cdb_data_files and cdb_tempfiles_files will show files for all with the con_id.
CDB_USERS view which will show users belonging to either the current container or all the pluggable databases connected to that container.
Pending active transactions in Parent container(CDB/PDB) are are not committed when Create/Alter is used in the current PDB.
When a PDB is created from seed, it is created with its local temporary tablespace TEMP which cannot be dropped.
PDB can have its own local temporary tablespace else  it can share the temporary tablespace of the CDB

A PDB is unplugged by connecting to the root CDB

DBA_XXX  views If you connect to the root, the views show only objects stored in the root; if you connect to a PDB, they show only objects contained in that PDB.

ALL_XXX   These views show all objects that a current user in a PDB can access.


USER_XXX These views show all objects owned by the current user in a PDB.


SPFILE for CDB  stores parameter values associated with the root. These server as default values for all other containers.Value can be modified within PDBs where the column ISPDB_MODIFIABLE in V$PARAMETER is TRUE. These are stored in table PDB_SPFILE$ and maintained across CDB restart.

Stats : Concurrently gathering  statistics  on multiple tables using  EXEC DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT', 'ALL');

Dynamic Sampling : With Optimizer_Dynamic_Sampling = 11 value allows the optimizer to automatically perform dynamic sampling if table statistics are stale or insufficient.

ADDM : Real-time ADDM analysis from OEM

DataPump : DISABLE_ARCHIVE_LOGGING in datapump

Partitions         : Adding/drop/spli/merge multiple new partitions in single command and
                           online tablespace migration of partitions
                           Indexing ON/OFF for individual partition. Partial/FULL  index creation possible.
                           Global Index Maintened  asynchronous at a later time

Index : Multiple indexes on the same column and Invisible columns
                           

Datafile           : Online relocation of DF

RMAN : SYSBACKUP / SQL / Table  Recovery / Recover data files over the network for standby

PGA   : PGA_AGGREGATE_LIMIT

Upgrade          : preupgrd.sql and catctl.pl    replace utlu[121]s.sql  and catupgrd.sql $ ./perl catctl.pl –n 3 -catupgrd.sql

 FLEX ASM : Starting ASM instance on differen node if ASM instance fail  and load balancing.
              (INSTANCE_TYPE = ASMPROX//$ asmcmd showclustermode // $ srvctl config asm)

                         OCR backup in ASM disk group ($ ./ocrconfig -backuploc +DG_OCR )

                         Estimate work on ASM (EXPLAIN WORK FOR ALTER DISKGROUP...) using  V$ASM_ESTIMATE;

SRVCTL        : Evaluate impact  eg : ./srvctl stop database –d MYDB –eval  and  ./crsctl eval modify resource <resource_name> -attr “value”

Flex Clusters  : Hub Node : Interconnected through private network and directly access storage. Leaf node : Access storage through Hub node

In database archiving : Instead of deleting old records, you can archive rows within a table by marking them as archived.In other words,individual rows are  “soft-deleted”  but still exist

ROW ARCHIVAL VISIBILITY  = ALL | Active

PDB1>alter table ilmtab no row archival;

New column ora_archive_state added for this

 Online operations :  Non-blocking online operations like  DROP CONSTRAINT / DROP INDEX / MOVE PARTITION / SET column UNUSED

Automatic Big Table Cache :  Caches full or partial big tables((= 2% of _db_block_buffers) thereby avoiding direct path reads by setting DB_BIG_TABLE_CACHE_PERCENT_TARGET

PDP Hot Cloning :   In Oracle 12.1.0.2c, PDBs can be hot cloned, i.e. you don’t need to put the source PDB n read only


Command evaluation without actually running :
srvctl relocate server -server rac02 -serverpool wt_sp -eval -verbose
crsctl eval stop resource EMCagent
crsctl eval stop resource -w "TYPE = ora.sacan_vip.type" -f

Predict result if a resource fails :

srvctl predict scan_listener -scannumber 1 - verbose
crsctl eval fail resource EMCagent


Check all Db running from home :

srvctl status database-DB -thishome/thisversion


Check orachk :
 >./orachk –profile <profilename>     (asm,clusterware,dba ,preinstall , switch, goldengate)


Collects diagnostics, traces, log files for DB, Clusterware, OS  and analyze individaul component :

$ $GI_HOME/tfa/bin/tfactl print [status|config]

$ $GI_HOME/tfa/bin/tfactl analyze –node racdb01,racdb02 -comp crs -since 1h


ADRCI :

adrci> show homes
adrci> set home crs

adrci> show tracefile

adrci> show incident


Check cluster Health :

$ crsctl status resource ora.crf -init


Rapid Home cloning provision : 
Store gold images in RHP and deploy to dev/test






oracle-database-12c-new-features


Orace 12c Activities :

Basic Oracle Multitenant tasks

Performing basic tasks in oracle

Clone PDB

Unplug and Plug PDB

Backup Recover PDB and CDB


alter pluggable database pdb3 close immediate;
alter pluggable database pdb3 open restricted;
select name, open_mode, restricted from v$pdbs;


connect sys/oracle@localhost:1521/pdb3 as sysdba
alter pluggable database pdb3 rename global_name to pdb3_bis;



alter pluggable database close immediate;
alter pluggable database open;
select name, open_mode from v$pdbs;

SQL Translation Framework :

exec dbms_sql_translator.create_profile('OOW');

select object_name, object_type from dba_objects where object_name like 'OOW';

exec dbms_sql_translator.register_sql_translation('OOW', 'SELECT max(price) most_expensive_order from sales', 'SELECT max(price) most_expensive_order from

julian.sales')
/

exec dbms_sql_translator.register_sql_translation('OOW','alter session set database_performance="SUPER_FAST"','alter session set inmemory_query="ENABLE"')
/

exec dbms_sql_translator.register_sql_translation('OOW','alter session set database_performance="RATHER_SLOW"','alter session set inmemory_query="DISABLE"')
/

exec dbms_sql_translator.register_sql_translation('OOW','alter session set database_performance="SUPER_SLOW"','begin uups; end;')
/

SELECT name, SUM(VALUE)
 FROM gv$sysstat
 WHERE name LIKE 'gc%lost'
 OR name LIKE 'gc%received'
 OR name LIKE 'gc%served'
GROUP BY name
 ORDER BY name;


http://edba.blogspot.in/2018/01/oracle-interview-questions.html