Performance Tuning Optimizer mode. It will collect the statistics of the object. Optimizer will 1.transform the query – parsed 2.estimate 3. Generate a plan. Rule based optimizer (RBO) – deprecated from 10g onwards. Cost based optimizer (CBO) Keyword RBO – Rule. 15 objects can be gathered. CBO – Choose (or) All_rows, First_rows & first_rows_n. More than 256 objects can be gathered. The cost based optimizer will always select the low cost. It may be an index scan or table scan, which is lower. Parameters Choose / All_rows – It will gather the stats for all the rows of the table. First_rows – Only first row of the table First_rows_n –…
-
-
Oracle RMAN Configuration and Monitoring Scripts-14
RMAN Configuration and Monitoring Scripts RMAN – SHOW ALL is the code to know the RMAN configurations. RMAN backup configuration and schedule Find out the backup configuration info All rman> show all; List used block bakup rman> list backup; rman> list backup of datafile 4; rman> list backup of tablespace tools; List image copy backup rman> list copy of database; rman> list copy of datafile 4; rman> list copy of tablespace tools; Based on the application requirements this needs to be configured. By default backup will be stored in disk. We can also change this to tape. Keyword: Disk –Disk Tape — SBT/ SBT_TAPE rman> configure default…
-
Oracle RMAN Recovery manager-13
RMAN- Recovery manager It’s a utility and an oracle internal tool. Keyword — rman Features of RMAN RMAN only backup the used block, incremental backup with block change tracking and optimization on archivelog. Whereas hot & cold backup. Less space used. From 10g onwards, You can backup used +free data by rman. It’s called image copy. Used data backup is called backupset backup. Default one. All data backup — Image copy Used backup–Backupset By default, We can compress the currently not used & unused block compression to skip datafile blocks RMAN also supports binary compression of backup sets. The supported algorithms are BZIP2 (default) – max compression and ZLIB –…
-
Oracle Export and Import Backup and Restore-12
Export and Import Backup and Restore Logical backup: Normal export/import and Data pump Data pump has many features. Like (PARALLEL, Job,NETWORK_LINK,REMAP DATAFILE & tablespace, exclude, include, version, extimate_only) It mostly done in the DB server itself. It access data by direct path and external tables a master process, and worker processes. Whereas normal export/import uses Conventional path. DataPump: Faster, Filtering option (remaps, compression & parallel), job, & self-tuning etc Data Pump Export/Import Normal Export/Import Filtering of data and metadata and remaps etc Not much Change PARTITION_OPTIONS while import No Two process at DB server level Master & Worker It runs on DB & client level Data Pump jobs runs server,…
-
Oracle Backup and Recovery-11
Backup and Recovery Types: Physical backup – i. Cold backup ii. Hot backup & RMAN Logical backup – i. Export ii. Import Cold Backup Hot Backup RMAN Offline / consistent backup Online / partial / inconsistent backup, only a read consistent and no active transactions backup consistent DB down Can Backup online. Can Backup online. Both modes Database should be in archive mode Any Mode All physical files Tablespace, control file All physical + logical Cold Backup: It’s an offline backup/ consistent backup. Database needs to be down by – Shutdown immediate /normal Oracle will run checkpoint and write a SCN number. If you use, shutdown abort for…