Resumé Oracle

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Physical Disk Operations

-list
CellCLI> list physicaldisk
CellCLI> list physicaldisk detail
CellCLI> list physicaldisk 34:0
CellCLI> list physicaldisk 34:0 detail
CellCLI> describe physicaldisk
CellCLI> list physicaldisk attributes name, disktype,status
CellCLI> list physicaldisk attributes all
CellCLI> list physicaldisk attributes name, disktype,status where disktype = 'HardDisk'

-modification (  turn on or off the service LED)
CellCLI> alter physicaldisk  34:0,34:1 serviceled on
CellCLI> alter physicaldisk  34:0,34:1 serviceled off
CellCLI> alter physicaldisk harddisk serviceled on
CellCLI> alter physicaldisk all serviceled on  (hard disks and flash disks)

- Creation (no action)
- Deletion (no action)

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
lun Disk Operations

- list
CellCLI> list lun
CellCLI> list lun detail
CellCLI> list lun 0_0 detail
CellCLI> list lun attributes all
CellCLI> describe LUN

- modification
The properties of a LUN are not modifiable, except one. The LUNs are re-enabled after a failure
CEllCLI> alter lun 0_0 reenable
CEllCLI> alter lun 0_0 reenable force
- Creation (no action)
- Deletion (no action)

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Cells Operations
-list
CellCLI> list cell
CellCLI> list cell detail
CellCLI> list cell attributes all
CellCLI> describe cell

-modification
CellCLI> alter cell shutdown services rs
CellCLI> alter cell restart services rs
CellCLI> alter cell led on
CellCLI> alter cell led off
CellCLI> alter cell validate configuration  -- validate configuration

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
BMC Management
BMC, or Baseboard Management Controller, controls the compoments of the cell
CellCLI> alter cell restart bmc
CellCLI> alter cell configurebmc

Automatic Service Requests (ASRs)
CellCLI> alter cell validate snmp type=ASR

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
 Cell Disks Operations
 -list
 CellCLI> list celldisk
 CellCLI> list celldisk detail
 CellCLI> list celldisk attributes all
 CellCLI> describe celldisk

 - modification (Only two attributes of the cell disks are changeable: the comment and the name.)
 CellCLI> alter celldisk FD_00_cell01 comment='Flash Disk'
 CellCLI> alter celldisk all harddisk comment=’Hard Disk’
 CellCLI> alter celldisk all harddisk comment=’Hard Disk’

- Deletion
CellCLI> drop celldisk CD_00_cell01 force

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Grid Disks Operations

-list
CellCLI> list griddisk
CellCLI> list griddisk DBFS_DG_CD_02_cell01 detail
CellCLI> describe griddisk
CellCLI> list griddisk attributes name,cellDisk,diskType
CellCLI> list griddisk attributes name,cellDisk,status where size=476.546875G
CellCLI> list griddisk attributes name, ASMDeactivationOutcome, ASMModeStatus -- ASM STATUS

- modification ( name, comment, Available To, status and size)
CellCLI> alter griddisk PRORECO_CD_11_cell01 inactive -- offline associated asm disk
CellCLI> alter griddisk PRORECO_CD_11_cell01 inactive force
CellCLI> alter griddisk PRORECO_CD_11_cell01 inactive nowait
CellCLI> alter griddisk PRORECO_CD_11_cell01 active
CellCLI> ALTER GRIDDISK gd0 SIZE=40M

-Deletion
CellCLI> drop griddisk DBFS_DG_CD_02_prolcel14
CellCLI> drop griddisk prefix=DBFS
CellCLI> drop griddisk PRORECO_CD_11_cell01 force
CellCLI> drop griddisk flashdisk
CellCLI> drop griddisk harddisk

-Creation
CellCLI> create griddisk PRORECO_CD_11_cell01 celldisk=CD_11_cell01
CellCLI> create griddisk all prefix PRORECO
CellCLI> create griddisk all flashdisk prefix FLASH
CellCLI> create griddisk PRORECO_CD_11_cell01 celldisk=CD_11_cell01 size=100M

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Flashcache Operations
 purpose is to provide a secondary buffer
-list
CellCLI> list flashcache -- one flashcache per cell
CellCLI> list flashcache detail
CellCLI> list flashcache attributes all
CellCLI> describe flashcache

-modification
no action

- Creation
CellCLI> create flashcache celldisk='FD_13_cell01,FD_00_cell01...'
CellCLI> create flashcache all -- use all the flash-based cell disks
CellCLI> create flashcache all size=365.25G

- Deletion
CellCLI> drop flashcache

-writeback
By defaults, Exadata Database Machine operates in WriteThrough mode, in which it only caches the read IOs to the PCI flash. Write-Back Flash Cache (WBFC) provides the ability to cache write IOs directly to the PCI flash in addition to the read IOs
CellCLI> alter cell flashCacheMode=writeback # WriteThrough



&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Flashcachecontent  Operations
- list
CellCLI> list flashcachecontent -- may be long
            . dbID and dbUniqueName
            . objectNumber the DATA_OBJECT_ID (not OBJECT_ID) value from DBA_OBJECTS
            . tableSpaceNumber
            ( partitioned tables, materialized views and indexes DATA_OBJECT_ID <> OBJECT_ID)
           
CellCLI> describe flashcachecontent
CellCLI> list flashcachecontent where objectnumber=211409 detail
SQL> select name
    from ts$
    where ts# = 846;

-modification
no action

-deletion
no action

-creation
no action

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
flashlog Operation
- When the storage cells receive a redo log write request, Exadata will perform a // write to both the on-disk redo logs as well a small amount
of flash storage carved out from PCI flash storage
- 512k


&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Group Commands : DCLI
The DCLI interface is not a command but rather a Python script that executes the command on the other cells

-c option
[celladmin@prolcel01 ~]# dcli -c prolcel01,prolcel02,prolcel03,prolcel04,prolcel05,prolcel06,prolcel07,prolcel08, prolcel09,prolcel10,prolcel11,prolcel12,prolcel13,prolcel14 -l root "cellcli -e list cell"
    . The last part of the command cellcli –e list cell is what we want to run on all the other cells.
    . The -c option specifies the cells where this command cellcli –e list cell should be run
    . The -l option specifies the user the command should be run as. In this case it’s root. The default is celadmin.

-g option
[celladmin@prolcel01 ~]# dcli -g all_cells -l root "cellcli -e list cell"
[celladmin@prolcel01 ~]# dcli -l root -g all_cells ps -aef|grep OSWatcher

- Command Scripts
[celladmin@prolcel01 ~]#cat err.dcl
cellcli -e list physicaldisk attributes errCmdTimeoutCount,errHardReadCount,errHardWriteCount,errMediaCount,errOtherCount,errSeekCount,errorCount where disktype='HardDisk'

[celladmin@prolcel01 ~]# dcli -l root -g all_cells -x err.dcl

-f option
Copies the files to the other cells but does not execute them. It's useful for copying files and executing them later.

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Metrics Operations
-list
CellCLI> list metriccurrent -- huge outout
CellCLI> list metriccurrent gd_io_rq_w_sm
CellCLI> list metriccurrent gd_io_rq_w_sm detail
CellCLI> describe metriccurrent
CellCLI> list metriccurrent attributes name,objectType where alertState != 'normal'
CellCLI> list metriccurrent attributes all where objectType = 'CELL'
CellCLI> list metricdefinition -- list of all metrics
CellCLI> list metricdefinition attributes all where objecttype='CELL'
CellCLI> list metrichistory
CellCLI> list metrichistory where objectType = 'CELL'

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Alert Operations
When an alert triggers, the cell takes the action on the notification schedule – it either sends an email or an SNMP trap, or both
CellCLI> list alertdefinition detail


&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Using Grid Control
- setup
    . A plugin for storage server management for GC (Exadata Storage Server Plugin)
    . An agent installed in each of db node
    . To configure access to the cells from the Oracle Management Server
   
    Credential
        Cell Root Password
        InfiniBand Switch Nm2user Password
        ILOM username and password
        InfiniBand Switch root credentials: Username and Password

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Storage Index operation
-The information consists of the minimum value, the maximum value and
a flag to denote whether any Nulls exist for up to 8 different columns within each 1M storage region of disk
- SI tells to Oracle that a requested extent does not exists in specific locations

-views
v$mystat
v$statname

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
RAC INSTALLATION OEPRATION
- Network Configuration
Component Physical network interface IP address
Public IP Yes, 1 per node (minimum) Yes, 1 per node
Private IP Yes, 1 per node (minimum) Yes, 1 per node
Virtual IP No                        Yes, 1 per node
SCAN VIP No                        Yes, 3 total for cluster (minimum)

- prerequiquties
oracle> ./runcluvfy.sh

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
SCAN listener operation
- resource
    - SCAN VIP
    - SCAN Listener ( depend on privious)
- Use ( avoid using vip adress on client tns)

- fonctionality
    1) SCAN LISTENER handle clitent request
    2) redirect to lowest local listener
- command
srvctl start scan_listener
srvctl stop scan_listener
 

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Clusterware Operations
-check cluster
crsctl check crs <<-- for the local node

- stop clusterware
crsctl stop cluster –all # stop cluster on all nodes
crsctl stop cluster –n <HostName>

-starting clusterware : 2 components
    - Oracle High Availability Service Daemon (OHASD)
    - clusterware stack
 a) start 2 components on one command on node1
 crsctl start crs
 b) start in 2 steps on node1
 crsctl start has
 crsctl start cluster -n node1

- stopping clusterware
a) one command
crsctl stop crs
b) 2 commands
crsctl stop cluster -n node1
crsctl stop has

-automatic restart
crsctl enable crs
crsctl disable crs


&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Voting disks Operations
-check
crsctl query css votedisk
-replace
crsctl replace votedisk +OCRVD

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
nodeapps Operations
- components
    1) VIP.
    2) Oracle Net listener.
    3) Global Service Daemon. GSD
    4) Oracle Notification Service.: ONS
-stop/start automatically

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
OCR Operations
-backup
Oracle Clusterware automatically creates OCR backups every four hours.
ocrconfig -showbackup
ocrconfig -manualbackup
ocrconfig -backuploc directory_name

-check status
ocrcheck

- verify integrety
cluvfy comp ocr –n all -verbose

- replace
ocrconfig -replace +OCR1 -replacement +OCR2

- repair local node
ocrconfig –repair -add +OCR2
ocrconfig –repair -delete +OCR1
ocrconfig –repair -replace +OCR1 -replacement +OCR2
crsctl stop crs
crsctl start crs
ocrcheck

- restore
ocrconfig -showbackup
crsctl stop crs -f -- on all nodes
crsctl start crs -excl -- start in exclusive mode on one node
ocrconfig –restore {path_to_backup/backup_file_to_restore}
ocrcheck
crsctl stop crs -f

- contents
    The list of nodes
    The mapping of database instances to nodes
    Oracle Clusterware application resource profiles
    Resource profiles that define the properties of resources under Oracle Clusterware/Grid Infrastructure control
    Rules that define dependencies between the Oracle Clusterware/Grid Infrastructure resources
    The current state of the cluster

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
CLUSTER LOG Operations
-daemond
    CRS Home/log/hostname/<daemond>
    CRS home/log/hostname/crsd
    CRS Home/log/hostname/cssd
    CRS Home/log/hostname/evmd
- OCR
    CRS Home/log/hostname/client
- Alertlog
$GRID_HOME/log/<host>/alert<host>.log
   
12C -centralisation
 $ADR_BASE/diag/crs/hostname/crs

 



&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
OLR Operations
-backup
not backed up automatically
ocrconfig –local –manualbackup
ocrdump -local -backupfile olr_backup_file_name -- view contents
ocrconfig -local -export <export file name >
ocrconfig -local -import <file name>
crsctl start crs
cluvfy comp ocr –n all -verbose

-repair
ocrconfig -local -repair olr <file name>

- restore
crsctl stop crs
ocrconfig -local -restore file_name
ocrcheck -local
crsctl start crs
cluvfy comp olr

- location
grid_home/cdata/<hostname>.olr
The location of OLR is stored in /etc/oracle/olr.loc

- contents
ORA_CRS_HOME
localhost version
active version
GPnP details
OCR latest backup time and location
information about OCR daily, weekly backup location
node name etc.


&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
ASM DISKGROUP Operations

create diskgroup MTXPRDDB10_DATA01
    external redundancy
    disk '/userdev/asm/mtxprddb10-local/c3t6006016017F02E001493A1CD34BEE211d0s0',
         '/userdev/asm/mtxprddb10-local/c3t6006016017F02E00448E0F3737BEE211d0s0'
    attribute 'AU_SIZE'='8M' ,
     'COMPATIBLE.ASM'='11.2' ,
     'COMPATIBLE.RDBMS'='11.2' ;
create diskgroup DATA normal redundancy
FAILGROUP FG12 disk
'/dev/oracleasm/disks/DATA1' name DATA_01,
'/dev/oracleasm/disks/DATA2' name DATA_02
FAILGROUP FG34 disk
'/dev/oracleasm/disks/DATA3' name DATA_03,
'/dev/oracleasm/disks/DATA4' name DATA_04
 attribute 'AU_SIZE'='4M' ,
     'COMPATIBLE.ASM'='12.1.0.2.0' ,
     'COMPATIBLE.RDBMS'='12.1.0.2.0' ;

ALTER DISKGROUP sp_dgroup2 dismount;
ALTER DISKGROUP sp_dgroup2 mount;

ALTER DISKGROUP cooked_dgroup1 drop disk '/dev/sb1/oracleasm/disk1';

ALTER DISKGROUP sp_dgroup2 undrop disks; -- repair accidental drop of a disk
ALTER DISKGROUP cooked_dgroup1 check all; -- check consistance

ALTER DISKGROUP cooked_dgroup1 add disk 'c:\oracle\asm_disk\_file_disk3' name new_disk;

RMAN> sql "alter tablespace test1 offline";
RMAN> backup as copy tablespace test1 format '+DG1';
RMAN> switch tablespace test1 to copy;
RMAN> sql "alter tablespace test1 online";

ASM Filter Driver (12C)

USABLE_FILE_MB  =trunc((free_mb - required_mirror_free_mb) / 2 -- normal redundancy


&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
ASM INSTANCE  Operation
-process
ASMB (database) : Communicates with the ASM instance, managing storage and providing statistics
RBAL : it also coordinates rebalance activity for disk groups
ARBn : ARBn performs the actual rebalance data extent movements in an Automatic Storage Management instance.
DBWR :

-Views
v$asm_diskgroup
v$asm_disk
v$bgprocess



&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
ASM striping and mirring

- Striping
    Coarse-Grained Striping size equal AU
    Fine-Grained Striping size equal 128K ( redo logs, control files)
    - AU : smallest unit of storage
    - file extent :  made up of multiple AU
    - ASM file : set or collecion of extent
   
    FILE_EXTENT = AU nb_extents<2000
    FILE_EXTENT = 8*AU nb_extents 2000 - 4000
    FILE_EXTENT = 64*AU nb_extents> 4000
   
- mirroring
file extents in separate failure groups.

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
RMAN operations

- backup
backup incremental level 0  database;
backup archivelog all not backed up 1 times;
backup archivelog all not backed up delete input;
BACKUP AS BACKUPSET TAG 'WEEEKLY_PRD01_TBLS_BK_ONLY' TABLESPACE PRD01;
BACKUP AS BACKUPSET DATABASE PLUS ARCHIVELOG;


- restore controlfile
restore controlfile to '/oradata/ctvfile.bak' from autobackup;
restore controlfile to '/oradata/ctvfile.bak' from 'c-4119285822-20100302-0a'
RESTORE CONTROLFILE FROM TAG 'WEEKLY_FULL_BKUP';
restore spfile from autobackup
create spfile from MEMORY

- configure
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 4;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/10.2.0.4/dbs/snapcf_cis.f';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u02mars/ORADATA/cis/autobackup/%F';

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY ;
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK;

configure retention policy to recovery window of 7 days;
configure retention policy to redundancy 1;



-duplicate
DUPLICATE TARGET DATABASE TO MYDBDEV until time="to_date('30/06/2011 20:00:00', 'DD/MM/YYYY HH24:MI:SS')" NOFILENAMECHECK;
duplicate  database to MYDBDEV backup location ‘/home/oracle/stage/’ nofilenamecheck;

-deletion
delete noprompt obsolete;
delete noprompt archivelog until time 'sysdate - 8/24' backed up 1 times to device type sbt;
DELETE NOPROMPT OBSOLETE RECOVERY WINDOW OF 7 DAYS;
DELETE NOPROMPT EXPIRED archivelog all completed before 'sysdate -40' ;
CROSSCHECK BACKUP;

-- list
list incarnation of database;
log_archive_format =*%r
list backup of database;
list backupset by backup summary;

-- report
report need backup days 1;
report obsolete;
report need backup;



 -- catalog
  grant CONNECT , RESOURCE, RECOVERY_CATALOG_OWNER to catuser;
RMAN>CONNECT CATALOG catuser/catuser ;
RMAN>CREATE CATALOG TABLESPACE tbs_tab_repos
rman target / target catalog catuser/catuser@catmran;
register database;
unregister database;




-- recover
run {
set dbid=947549824 ;
run set until time  "to_date('2013/03/09:22:02:00','YYYY/MM/DD:HH24:MI:SS')" ;
restore controlfile;
restore database;
mount database;
switch datafile all;
recover database ;
sql 'alter database open resetlogs';
}

run {
 set until scn=4596400544762;
 recover  database;
}

RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE


-- restore preview
RESTORE DATABASE PREVIEW;
RESTORE DATABASE PREVIEW SUMMARY;

-- restore vavlidate
RESTORE DATABASE VALIDATE;
select * from V$DATABASE_BLOCK_CORRUPTION

-- views
v$backup_set
v$backup_piece
v$rman_configuration
v$rman_status
v$rman_output
v$rman_backup_job_details
v$backup_async_io





&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
BROKER and DG operations
DGMGRL> ADD DATABASE 'testdb' AS CONNECT IDENTIFIER IS testdb MAINTAINED AS PHYSICAL;
DGMGRL> CREATE CONFIGURATION 'dg' AS PRIMARY DATABASE IS 'prod' CONNECT IDENTIFIER IS prod.foo.com;

DGMGRL> ENABLE FAST_START FAILOVER
DGMGRL> DISABLE FAST_START FAILOVER;
DGMGRL> EDIT DATABASE devdb SET STATE='APPLY-OFF';
DGMGRL> EDIT DATABASE devdb SET STATE='APPLY-ON';
DGMGRL> EDIT DATABASE devdb SET STATE='TRANSPORT-OFF';
DGMGRL> EDIT DATABASE devdb SET STATE='TRANSPORT-ON';
DGMGRL> SHOW DATABASE VERBOSE 'test';

DGMGRL> EDIT DATABASE 'PTELST1_LXPT249A' SET PROPERTY 'ObserverConnectIdentifier'=PTELST1_LXPT249A_DG';

-- param
alter system set dg_broker_start=true;
alter system set dg_broker_config_file1='/u01/dg_broker_config_files/dr1TESTP.dat' sid='*';
alter system set dg_broker_config_file2='/u01/dg_broker_config_files/dr2TESTP.dat' sid='*';


--views
v$archived_log
v$dataguard_stats
v$recovery_progress
v$archive_dest_status
v$log_history
v$managed_standby



&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Tablespaces Operation


 - create
CREATE TABLESPACE thegeekstuff
 DATAFILE '/u02/oradata/tgs/thegeekstuff01.dbf'
 SIZE 100M REUSE
 AUTOEXTEND ON
 NEXT 1M MAXSIZE 2G;


 CREATE TABLESPACE thegeekstuff
 DATAFILE '/u02/oradata/tgs/thegeekstuff01.dbf'
 SIZE 100M
 EXTENT MANAGEMENT LOCAL
 SEGMENT SPACE MANAGEMENT AUTO;

 CREATE TABLESPACE thegeekstuff
 DATAFILE '/u02/oradata/tgs/thegeekstuff01.dbf'
 SIZE 100M
 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

 CREATE BIGFILE TABLESPACE thegeekstuffbig
 DATAFILE '/u02/oradata/tgs/thegeekstuffbig.dbf'
 SIZE 50G;

 CREATE UNDO TABLESPACE UNDOTBS1
  DATAFILE '/u01/oradata/devdb/undotbs1_01.dbf'
  SIZE 1024M AUTOEXTEND ON NEXT 100M MAXSIZE 2048M;
 


 -alter

 ALTER TABLESPACE thegeekstuff ADD DATAFILE '/u02/oradata/tgs/thegeekstuff02.dbf' SIZE 100M;

ALTER DATABASE
 DATAFILE '/u02/oradata/tgs/thegeekstuff01.dbf'
 RESIZE 200M;

 ALTER TABLESPACE thegeekstuff RENAME TO TGS;

 ALTER TABLESPACE thegeekstuff OFFLINE;
 ALTER TABLESPACE thegeekstuff ONLINE;

 ALTER DATABASE DATAFILE '/u02/oradata/tgs/thegeekstuff02.dbf' offline;
 ALTER DATABASE DATAFILE '/u02/oradata/tgs/thegeekstuff02.dbf' online;

 ALTER TABLESPACE THEGEEKSTUFF READ ONLY;
 ALTER TABLESPACE THEGEEKSTUFF READ WRITE;

 ALTER TABLESPACE thegeekstuff
 RENAME DATAFILE '/u02/oradata/tgs/thegeekstuff02.dbf'
 TO '/u02/oradata/tgs/tgs02.dbf';

 - drop
  DROP TABLESPACE thegeekstuff
 INCLUDING CONTENTS AND DATAFILES;

 ALTER TABLESPACE thegeekstuff
 DROP DATAFILE '/u02/oradata/tgs/thegeekstuff02.dbf';


 - view
    dba_free_space,dba_data_files,dba_tablespaces,dba_undo_extents
   
 - parameters
 undo_tablespace,undo_retention

 &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Gather stats Operation  

 &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
 Tables operation
 -ddl
 select dbms_metadata.get_ddl('TABLE','DEMO','RAJESH') FROM DUAL;


 &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
 Performances Operation
 -views


 dba_hist_active_sess_history
 dba_hist_sqltext
 dba_hist_sql_plan
 dba_hist_sqlbind
 dba_hist_pgastat
 dba_hist_sgastat
 dba_hist_snapshot
 dba_hist_sysstat
 dba_hist_seg_stat
 dba_hist_seg_stat_obj

 v$active_session_history

 v$sql
 v$sqlarea
 v$sqltext
 v$sql_bind_capture

 v$statname
 v$sesstat
 v$sysstat

 v$locked_object
 v$lock

 v$session_wait
 v$session_event
 v$session_longops


 v$license


 v$pgastat
 v$pga_target_advice
 v$sga_target_advice



 -display sql plan
 SELECT *  FROM   TABLE(DBMS_XPLAN.DISPLAY);
 SELECT *  FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ADVANCED'));
 DISPLAY_AWR
 DISPLAY_SQL_PLAN_BASELINE

 --
  Alter system kill session ‘SID,SERIAL#’;
 
 -- hint
 select /*+ index(c cust_primary_key_idx) */ * from customer c;

 -- monitor
 select dbms_sqltune.report_sql_monitor(type=>'active') from dual;

 -- Adaptative Cursor Sharing
 Oracle 11g uses Adaptive Cursor Sharing to solve this problem by allowing the server to compare the effectiveness of execution plans between executions with different bind variable values.

 -- Joins
  NL ==> join 2 tables with index
  Sort Merge Join : join 2 tables without index

 -- execution steps

1) PARSE:
2) BIND
3) EXECUTE
4) FETCH
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
 Statistics  Operation


 - Gather stats
 execute dbms_stats.gather_table_stats(ownname => 'MTX$OWNER', tabname => 'T_REPOMARGIN_CTRL_RESULT_MTX', estimate_percent =>100, cascade => TRUE);
 dbms_stats.gather_schema_stats

 - set preference
 
    SET_TABLE_PREFS
    SET_SCHEMA_PREFS
    SET_DATABASE_PREFS
    SET_GLOBAL_PREFS
   
    AUTOSTATS_TARGET ( Oracle, ALL, AUTO)
    CASCADE
    DEGREE
    ESTIMATE_PERCENT
    METHOD_OPT
    NO_INVALIDATE
    GRANULARITY
    PUBLISH
    INCREMENTAL
    STALE_PERCENT

 - Histogram
  dba_tab_col_statistics : col histogram
  sys.col_usage$ that stores information about column usage
  method_opt => 'FOR ALL COLUMNS SIZE 1' --no histogram
  method_opt=>'for all columns size skewonly'
  method_opt=>'for all columns size repeat'
  MENTOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE AUTO'
 
    - Frequency Histograms
        NDV <254
 
    - HEIGHT BALANCED
        NDV>254
 
    - Top-Frequency histogram(12c)
         3 conditions to have Top frequency histogram
            - nb buckets < NDV
            - The sampling percentage is the default one : AUTO_SAMPLE_SIZE
            - The top most frequent distinct values must exceed a certain threshold
           
    -  Hybrid histograms
        Conditions à prendre en compte
        ESTIMATE_PERCENT to AUTO_SAMPLE_SIZE
        distinct value should not be found in more than one bucket
        Replace HEIGHT BALANCED


 - extended statistics
 SELECT DBMS_STATS.report_col_usage('TEST', 'TAB1') FROM   dual;
 SELECT DBMS_STATS.create_extended_stats('TEST', 'TAB1') FROM   dual;

 -- Dictionary Statistics
 exec DBMS_STATS.GATHER_DICTIONARY_STATS;

 -- Fixed Object Statistics
 exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

 -- System Statistics
 execute dbms_stats.gather_system_stats('Start');
-- one hour delay during high workload
execute dbms_stats.gather_system_stats('Stop');


 exec dbms_stats.SET_SYSTEM_STATS('MBRC',8);
 exec DBMS_STATS.GATHER_SYSTEM_STATS('EXADATA');
 select pname, PVAL1 from aux_stats$ where pname='MBRC';

 -views
 dba_tab_statistics,DBA_TAB_COL_STATISTICS

 - procedures
 dbms_stats.export_schema_stats
 dbms_stats.import_schema_stats
 exec dbms_auto_task_admin.disable
 dbms_stats.restore_table_stats
 dbms_stats.copy_table_stats
 dbms_stats.merge_col_usage(dblink IN VARCHAR2); import histogram


  &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
  Partitions Operation
 
  --views
  dba_tab_partitions
  dba_ind_partitions
  dba_ind_subpartitions
 
  --types
  Range
  List
  Hash
  Composite Partitioning
        range-hash
        range-list
        range-range
       
        list-range
        list-list
        list-hash
       
        hash-hash
  Interval Partitioning
  REF Partitioning
 
 &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
 Upgrade operation
 - registry
 select * from sys.registry$database;
 $ORACLE_HOME/perl/bin/perl catctl.pl -n  6 -l $ORACLE_HOME/diagnostics catupgrd.sql
 exec dbms_pdb.describe('/home/logiciel/oracle/tmp/CRSSP1/CRSSP1.xml');
 ./datapatch -verbose
 @noncdb_to_pdb.sql
 select message, name, time from PDB_PLUG_IN_VIOLATIONS order by time;

alter pluggable database D2P10R save state INSTANCES = ALL ;
select INSTANCE_NAME,STATE from dba_pdb_saved_states;
   
 
   
 &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
 Audit Operation
 - FGA
 fga_log$
 dba_fga_audit_trail
 dbms_fga

   
 &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
 SQL Profile / SQL Plan Managemen (Baseline) Operation
 -sql Profile
 dbms_sqltune.alter_sql_profile ( enable,disable)
 dbms_sqltune.drop_sql_profile

 -Sql plan baselines
 DBA_SQL_PLAN_BASELINES
  - ENABLE : the plan is available for the optimizer if it is also marked as accepted
  - FIXED : YES : If YES, the SQL plan baseline will not evolve over time. Fixed plans are used in preference to non-fixed plans
 
  dbms_spm.alter_sql_plan_baseline
   - source
   
 
 &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
 Undo Management Operations

 - parameters
 undo_tablespace,undo_retention,undo_management

 - views
 dba_undo_extents,v$undostat,v$transaction,dba_hist_undostat

 -extents status
    ACTIVE
    EXPIRED ==>the data in these extends is committed and the UNDO_RETENTION time has passed,
    UNEXPIRED extents==> are non-active extents that still honour UNDO_RETENTION

 -lobs
   LOBS : Automatic tuning of undo retention is not supported for LOBs.
 This is because undo information for LOBs is stored in the segment itself and not in the undo tablespace

 
 &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
 Sysaux Operations
 @?/rdbms/admin/awrinfo.sql
 v$sysaux_occupants


  &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
  Ressource Manager 12C Operations
 
  - level
    - CDB
    - Pdb
  - Limit the Parallel Degree for PX Queries with Resource Manager
 
 
  &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
12C Features Operations
 - GTT
    separate set of statistics on every session (GLOBAL_TEMP_TABLE_STATS to SESSION, deafault),SHARED
 [ DATA GUARD]
    Subset Standby ==> A subset standby enables users of Oracle Multitenant to designate a subset of the pluggable databases (PDBs) in a multitenant container database (CDB) for replication to a standby database
    Support for Transport Destinations of Different Endianess Than the Primary

[ FSFO ]
    Multiple targets
     Multiple Observers
     Maximum protection mode can  be configure
 [ DGMGRL]
- SQL commands and os command (host or !) can be used
[ RMAN]
     SET NEWNAME for entire tablespace or database
     MOVE command
    RESTORE + RECOVER for data file, tablespace and database : one step
[ Flasback database ]
    flasback table using flasbacklog
    v$flashback_database_logfile
   
   
 [ max_string_size= STANDARD | EXTENDED]
 MAX_STRING_SIZE controls the maximum size of VARCHAR2, NVARCHAR2, and RAW data types in SQL.
 32000 bytes extended

[  Unified Auditing ]
- parameter absent
     AUDIT_TRAIL, AUDIT_FILE_DEST, AUDIT_SYS_OPERATIONS
 uditing Pre-Oracle 12c
 Oracle 12c Unified Auditing – Mixed Mode
 Oracle 12c Unified Auditing – Pure Mode

- views
    UNIFIED_AUDIT_TRAIL
-  Two new roles
    - AUDIT_ADMIN
    - AUDIT_VIEWER
- Audit Policies
    Predefined Unified Audit Policies
     ORA_LOGON_FAILURES
     ORA_SECURECONFIG
     ORA_ACCOUNT_MGMT
   
   
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Optimizer 12C Operations

 OPTIMIZER_ADAPTIVE_PLANS=TRUE default
    » Adaptive joins
    » Bitmap pruning
    » Parallel distribution method
   
 OPTIMIZER_ADAPTIVE_STATISTICS = false (default))
    » The use of SQL Plan Directives (SPDs) for query optimization
    » Statistics feedback for joins
    » Adaptive dynamic sampling for parallel queries
    » Performance feedback

 OPTIMIZER_ADAPTIVE_REPORTING_ONLY ( test perpose)


 AUTO_STATS_ADVISOR_TASK job