Quick Notes
- ADRCI to check the alert log ADRCI (Automatic Diagnostic Repository Command Interface) - show trace, show alert, show incident,set -.., IPS (Incident Packaging Service) add,
- asmcmd for ASM related activities.- ls, ;ls -l, lsdg, du, df, cp, iostat -G DATA, lsattr, md_restore, md_backup, remap (cp corrupts the file)
- csscan and csconv- used to scan characteristic and change characteristic of database resp.
- nid - DBNEWID use to change db ID and db name
- opmnctl - Start / stop application server
- emctl - start / stop Oracle enterprise manager
- srvctl -h - control RACservices
- crsctl -h -control cluster services.
- rman - backup and recovery
- Verify block corruptions from V$DATABASE_BLOCK_CORRUPTION
- FAN - Fast Application Notification
- Oracle Packages - DBMS_SQLTUNE (procedures - create sqlset / create sqlplan baseline etc) / DBMS_STATS (procedures - Gather Table stats / Gather schema Stats etc)
- DG Broker init parameters - dg_broker_start, dg_broker_config_file1, dg_broker_config_file2
Corruption -
- use DBVERIFY - or - DBMS_BLOCKREAP or RMAN - blockrecover corruption list; or DBMS_REPAIR
DBMS_REPAIR -
| Procedure Name | Description |
|---|---|
| CHECK_OBJECT | Detects and reports corruptions in a table or index |
| FIX_CORRUPT_BLOCKS | Marks blocks (that were previously identified by the CHECK_OBJECT procedure) as software corrupt |
| DUMP_ORPHAN_KEYS | Reports index entries (into an orphan key table) that point to rows in corrupt data blocks |
| REBUILD_FREELISTS | Rebuilds an object's free lists |
| SEGMENT_FIX_STATUS | Provides the capability to fix the corrupted state of a bitmap entry when segment space management is AUTO |
| SKIP_CORRUPT_BLOCKS | When used, ignores blocks marked corrupt during table and index scans. If not used, you get error ORA-1578 when encountering blocks marked corrupt. |
| ADMIN_TABLES | Provides administrative functions (create, drop, purge) for repair or orphan key tables. Note: These tables are always created in the SYS schema. |
12. scan - single client access name -
13. Client and server side load balancing.. Client side - set load balance =on and fail over =on and server side - set multiple listener, configure -local and remote listener
14.
Partition types
1. Hash, Range, List and new in Oracle 11g are - System, Reference partition, Virtual column partition and partition based on Interval - this is subdivided into hash, range and list.
Dataguard -
LNS - log-write network-server
RFS - remote file server
FAL - Fetch Archive Log
MRP - Managed Recovery Process
LSP - Logical Standby Process
1. Physical
2. Logical
3. Active standby
4. Snapshot standby
Snapshot Standby
Standby can be converted to snapshot standby
Can be opened in read-write mode (for testing)
Redo transport continues
Redo apply delayed
Standby can subsequently be converted back to physical standby
Active Data Guard
Separately licensed option
Updates applied to primary
Changes can be read immediately on standby databases
Standby database can be opened in read-only mode
Redo can continue to be applied
Three protection modes:
Maximum protection - zero data loss
Redo synchronously transported to standby database
Redo must be applied to at least one standby before transactions on primary can be committed
Processing on primary is suspended if no standby is available
Maximum availability - minimal data loss
Similar to maximum protection mode
If no standby database is available processing continues on primary
Maximum performance (default)
Redo asynchronously shipped to standby database
If no standby database is available processing continues on primary
Recovery from the lost archive log
1. Check current scn from the DG
2. Take incremetal backup from from the above scn from primary and copy it on the DG
3. Create controlfile for DG and
4. Recover DG
Recover the datafiles on primary db
1. from standby -Take the backup of the datafile as a copy with RMAN and scp it to primary
2. On primary - Catalog the backup copy file, take the datafile offline, restore the file from backup and recover it using RMAN command
Audit -
1. Setup fire wall
2. Protect port on which db is listening
3. Restrict user by giving just required provs
4. Setup Encryption -
Type of Encryption -
1- DES- Digital Encryption Standards - DES3ENCRYPT / DESC3DECRYPT
2. DES3 - Triple DES
3. Hashing - Hashing is making a strings of characters from an input string based on some calculations.
5. Create Profile - with profile can add - password reuse, function check, password grace time etc
6. Create Role - create role assign privs to it and assign to role to the users
7. Use of VPD - virtual private Directory
8. WRAP CODE
9 Setup listener with password
10. Audit - general , Trigger audit, Auditing grants security, Fined grained Auditing
views - aud$, dba_audit_trails, dba_audit_exists, dba_audit_session, , dba_audit_statement, dba_audit_object
Types of Audit - by session / by access,
set audit_sys_operations= true to trace user connection by sysdba
11. Triggers - db startup/shutdown , logon, logoff triggers, servererror, ddl trigger
RMAN -
New RMAN feature in 11g -
1. Configure for db_unique_name - used for standby db registration.
2. Improved Handling of Long-Term Backups - only archive logs required to restore last vackup are retained,
3. Archived Redo Log Failover - Searches for valid archive log backup
4. Archived Log Deletion Policy Enhancements -
5. Network-Enabled Database Duplication Without Backups- no need to copy backups and archive logs. Rman does it internally
6. Recovery Catalog Enhancements - Virtual Private Catalog.Oracle 11g has introduced the concept of the virtual private catalog, which is a subset of the base recovery catalog
7. IMPORT catalog - to allow recover catalog to be moved /merged
8. Multisection Backups - A file section is defined as a contiguous range of blocks from a single file. The SECTION SIZE parameter in the BACKUP command tells RMAN to create a backup set where each backup piece contains the blocks from one file section, allowing the backup of large files to be parallelized across multiple channels.
9. Undo Optimization - The BACKUP command no longer backs up undo that is not needed for recovery. As the majority of the undo tablespace is filled with undo generated for transactions that have subsequently been committed, this can represent a substantial saving.
This functionality is not configurable. It is not affected by the CONFIGURE BACKUP OPTIMIZATION {ON | OFF} command.
When you turn on backup optimization, all backup commands will skip backups of any file if it has not changed and if it has already been backed up to the allocated device type. A file can be any dbf file, an archived redo log or an RMAN "backup set". Here are some of the main features of the RMAN configure backup optimization on command:
10. Improved Block Media Recovery Performance - if flashback logs are present, RMAN will use these in preference to backups during block media recovery (BMR), which can significantly improve BMR speed.
11. Faster Backup Compression
12. Block Change Tracking Support for Standby Databases
13. Backup of Read-Only Transportable Tablespaces - In previous versions of Oracle, transportable tablespaces could only be backed up if they were in read/write mode. It is now possible to backup read-only transportable tablespaces.
RAC -
1. crsctl check crs.. srvctl stop database -d test
2. CSS is communicating with all nodes and Voting disk to check node availability
3. Volting disk - crctcl query check votedisk
4. ocrconfig - export /import /backuploc, show backup, manual backup/ replace- ocr/replace-ocrmirror/upgrade/downgrade/ repair ocrrmirror, repar ocr
and - Getting backed up every 4 hours and start of day ans week., Backup retailed are - Last 3 Daily backups and last 2 dackup taken - at start of the day and start of Week..
5. Only one CRS process is responsible for updating ocr. dia below for details
6. ASM - can have only instance on one node. Can manage any nos of dbs.
7. GRD - GCS + GES
8. Instance Recovery
Cluster Processes -
Here’s how I added disks.
1 /etc/init.d/oracleasm createdisk DATA11 /dev/xvdy1
/etc/init.d/oracleasm createdisk DATA12 /dev/xvdz1
/etc/init.d/oracleasm createdisk DATA13 /dev/xvdaa1
/etc/init.d/oracleasm createdisk DATA14 /dev/xvdab1
2 Running following on oraclebi2
/usr/sbin/oracleasm scandisks
3 Alter system set asm_power_limit=4 scope=both sid='*';
4 Add disks using asmca
5 Alter system set asm_power_limit=1 scope=both sid='*';
DBCC - database console commands used for Maintenance , Miscellaneous, Informational, Validation
- DBCC CHECKALLOC, DBCC CHECKDB, DBCC CHECKCATALOG, DBCC CHECKFILEGROUP, DBCC CHECKTABLE, DBCC TRACEON,
Recovery Model - Simpe, Full, Bulk Logged
Mirring - Principle, mirror and witness server
Mirror - Types are - High Performance(async), High Safety without auto failover(synchronus), High Safety with with auto failover(synchronus)
Log shipping -
SQL Server Log shipping allows you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databaseson separate secondary server instances. The transaction log backups are applied to each of the secondary databases individually. An optional third server instance, known as the monitor server, records the history and status of backup and restore operations and, optionally, raises alerts if these operations fail to occur as scheduled.
Database mirroring is functionality in the SQL Server engine that reads from the transaction log and copies transactions from the principal server instance to the mirror server instance. Database mirroring can operate synchronously or asynchronously. If configured to operate synchronously, the transaction on the principal will not be committed until it is hardened to disk on the mirror. Database mirroring supports only one miror for each principal database. Database mirroring also supports automatic failover if the principal database becomes unavailable. The mirror database is always offline in a recovering state, but you can create snapshots of the mirror database to provide read access for reporting, etc.
Log shipping is based on SQL Server Agent jobs that periodically take log backups of the primary database, copy the backup files to one or more secondary server instances, and restore the backups into the secondary database(s). Log shipping supports an unlimited number of secondaries for each primary database.
Database mirroring is preferable to log shipping in most cases, although log shipping does have the following advantages:
1. it provides backup files as part of the process
2. multiple secondaries are supported
3. it is possible to introduce a fixed delay when applying logs to allow the secondary to be used for recovering from user error
Other differences are listed below:
With Log Shipping:
Data Transfer: T-Logs are backed up and transferred to secondary server
Transactional Consistency: All committed and un-committed are transferred
Server Limitation: Can be applied to multiple stand-by servers
Failover: Manual
Failover Duration: Can take more than 30 mins
Role Change: Role change is manual
Client Re-direction: Manual changes required
With Database Mirroring:
Data Transfer: Individual T-Log records are transferred using TCP endpoints
Transactional Consistency: Only committed transactions are transferred
Server Limitation: Can be applied to only one mirror server
Failover: Automatic
Failover Duration: Failover is fast, sometimes < 3 seconds but not more than 10 seconds
Role Change: Role change is fully automatic
Client Re-direction: Fully automatic as it uses .NET 2.0
Linux commands
LDOM -Logical domain,
C-DOM - Control domain
ufsdump - to backup
svcadm - stop start services
FTP port - 21, SSH - 22, Telnet -23
Raid level - 0 - striping, 1-mirror, 3-Partly separate disk, 5 Partly access disk.
FSCK - Check file system
NEWFS - To create new file system
Editor - vi, pieco, emacs
v$sql_plan will give you the actual plan used for a query;
v$sql_plan_statistics will show you the actual execution statistics;
Use dbms_xplan package which provides an easy way to see the output of EXPLAIN PLAN command.
Learn what are Access Methods, Joins, Join Orders, Join Methods.
Learn how to trace: use dbms_monitor package, dbms_session/dbms_system to enable trace either at session or system level.
Tools: tkprof or trcsess. Make Tkprof your friend. Your best friend.
You can even use the Automate Tools like SQL Tuning Advisor which you can invoke from Enterprise Manager or API.
Do care about statistics, understand selectivity and cardinality. Defaults are good, but there is a whole world beyond defaults, things start to be more interesting and challenging; statistics are crucial for optimal query performance.
BUT, most importantly, read the DB Concepts Guide, followed by the Performance Tuning Guide. Is all in there.
No comments:
Post a Comment