---- RMAN ----
backup strategy
---------------
We are using tape backup
every Sunday - Level 0 backup (full)
from Monday to sat - incremental backup
every 1 hour - archive log backup
Hot vs cold backup
-------------------
what is difference --> what is the benefit
cold backup does not required recovery - because it is a consistent backup
hot backup needs recovery -- because during backup time data will modify
restore vs recovery
--------------------
restore - restoring datafile from secondary backup location to primary location like from tape to disk
recovery- applying archivelogs - doing this it make database into consistent state
scenario
--------
1. If datafile SCN is higher than the other datafiles - what is the reason and how you do recovery
2. If i have 2 system datafiles, one of the datafile is lost (someone removed from OS level) can you bring the database in mount state?
1. How to check backups are failed? - V$RMAN_BACKUP_JOB_DETAILS
2. How to resolve backup failures issue?
It depends on the cause of backup failure. could be due to space issue, could be due to some maintenance activity (db restarted), could be
MML (media management layer error if it is a tape backup - will contact storage team)
--- database health ----
1. how to check database health
DBMS_HM -- Health monitor
2. what are the reports you receive emails?
tablespace utilization
user accounts details (lock/unlock)
RMAN backup status report
long running sessions
blocking sessions
GG process status report - lag reports
3. how to check the alert log file location?
select * from v$diag_info;
show parameter dump
----- ASM ---
1. how you know diskgroup is full
we have alerting configured - if any diskgroup reaches 80% of threshould we get an alert
where to check?
logging to asmcmd command line and run the LSDG command to see the diskgroup utlization
connect to ASM/RDBMS instance and query V$ASM_DISK and V$ASM_DISKGROUP views
2. how to add asm disk to diskgroup?
3. how to add datafile on non-rac and RAC env?
if we adding on production database - first you need to open a change and then add the datafile.
steps:
non-RAC: First check the space available on disk and add the datafile
ALTER TABLESPACE <TABLESPACE_NAME> ADD DTATAFILE <LOCATION> SIZE 100M ATUOEXTEND ON MAXSIZE UNLIMITED;
Rac: first check the space available on ASM diskgroup
ALTER TABLESPACE <TABLESPACE_NAME> ADD DATAFILE 'DISKGROUP_NAME' SIZE 100M ATUOEXTEND ON MAXSIZE UNLIMITED;
4. What is re-balancing and when re-balancing occur?
whenever we add or remove ASM disk oracle automatically do re-balancing
5. What is power in ASM?
power defines the no of parallel process that helps balancing and improve the performance of re-balancing.
6. ASM background processes?
7. what happens when one of the ASM disk is not discovered
----------- patching -----
1. how to check pre-req in patching?
I will if any conflicts -- opatch prereq CheckConflictAgainstOHWithDetail
I will /OPatch/opatchauto apply -analyze
i will make sure the filesytem should have a sufficient space -> why? because opatach takes the backup of home atumatically if it needs to restore.
2. how to verify patch is applied or not
opatch lsinventory or opatch lsinv
3. how you apply the patch on rac environment
opatchauto -rolling patches
4. rolling patch
lets say we have 2-node RAC : It first apply on node-1 and then on node-2
how it applies rolling patch:
step-1: it stop the cluster, services, instances on node-1
step-2: apply the patch on grid home and database home
step-3: once patch is applied it brings up the cluster, services and instances
step-4: it prompt --> node-1 patch is completed, are you ready to apply patch on another node - if we provide yes
step-5: followed from step-1 to step-4
5. How you apply patch on RAC? from which user you apply the patch?
root user or root sudo file
6. on RAC how to apply patch individually on grid and RDBMS home, how you apply?
i will specify ORACLE_HOME
grid home -> grid home /OPatch/opatchauto apply -oh <grid_home> ---------> using root user
RDBMS_home -> db_home /OPatch/opatchauto apply -oh <db_home> ---------> using oracle user
7. If patching failed, how do you troubleshoot or can we restart the patch
i will check what is error if it is related to some space issue i will reapply the backup- first rollback the patch then reapply the patch.
if it is something critical will open a SR with oracle.
8. if inventory corrupted how to restore it? --- not needed now
============= Performance tuning =============
-- how to generate execution plan
==> If you have sql_id:
-------------------------
dbms_xplan.display_awr -->
dbms_xplan.display_cursor -->
SYNTAX:
select * from table(dbms_xplan.display_awr('&sql_id'));
select * from table(dbms_xplan.display_cursor('&sql_id'));
==> if you don't have SQL_ID:
------------------------------
===> explain plan for <SQL Query>
@?/rdbms/admin/utlxpls.sql
Example:
---------
SQL> explain plan for select * from v$session;
Explained.
SQL> @?/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 3425234845
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 1480 | 0 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 4 | 1480 | 0 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 4 | 1224 | 0 (0)| 00:00:01 |
| 3 | FIXED TABLE FULL | X$KSLWT | 81 | 4698 | 0 (0)| 00:00:01 |
|* 4 | FIXED TABLE FIXED INDEX| X$KSUSE (ind:1) | 1 | 248 | 0 (0)| 00:00:01 |
|* 5 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 1 | 64 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("S"."INDX"="W"."KSLWTSID" AND BITAND("S"."KSUSEFLG",1)<>0 AND
BITAND("S"."KSSPAFLG",1)<>0 AND "S"."INST_ID"=USERENV('INSTANCE'))
5 - filter("W"."KSLWTEVT"="E"."INDX")
19 rows selected.
==> set autotrace traceonly explain
Example:
SQL> set autotrace traceonly explain
SQL> select * from v$session;
Execution Plan
----------------------------------------------------------
Plan hash value: 3425234845
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 1480 | 0 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 4 | 1480 | 0 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 4 | 1224 | 0 (0)| 00:00:01 |
| 3 | FIXED TABLE FULL | X$KSLWT | 81 | 4698 | 0 (0)| 00:00:01 |
|* 4 | FIXED TABLE FIXED INDEX| X$KSUSE (ind:1) | 1 | 248 | 0 (0)| 00:00:01 |
|* 5 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 1 | 64 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("S"."INDX"="W"."KSLWTSID" AND BITAND("S"."KSUSEFLG",1)<>0 AND
BITAND("S"."KSSPAFLG",1)<>0 AND "S"."INST_ID"=USERENV('INSTANCE'))
5 - filter("W"."KSLWTEVT"="E"."INDX")
1. My query was ruining good yesterday and today it is runing bad? how do you troubleshoot it?
There are multiple ways to analyze what has caused sql performance is slow
step-1: I will check the blocking sessions - v$lock and v$session (blocker user and session details)
step-2: I will check the current CPU load - running TOP and IOTOP command or SAR command
step-3: I will check the RMAN backup is running or not
step-4: will ask couple question to application team
any data loaded/modification since yesterday (insert + delete - 10 to 15%)
any table modification or newly column added to the SQL - not indexed - full table scan
Ex: previous- > SELECT ENO,ENAME FROM EMP WHERE ENO=100; ---- 3sec
NEW -> SELECT ENO,ENAME FROM EMP WHERE ENO=100 and ENAME='JOHN'; --- 10 min
step-5: will check is someone did recent stats gathered on those tables
from DBA_TABLES, DBA_INDEXES i will check last_analyzed column
step-6: To see what is happening - I will generate explain plan historically using ASH views - DBA_ACTIVE_SESSION_HISTORY
OR i have OEM access, i can generate historical plan - select AWR snap
step-7: I will trace the session if required.- i will ask user to run the SQL and trace the session.
step-8: I will generate ASH report (option)
step-9: could be table fragmented (Optional)
step-10: could be they are changing bind values.
2. how to force execution plan.
we use coexf profile and baseline to force the SQL by using SQL_ID and PLAN HASH VALUE
Scenarios:
same SQL ID but different or multiple hash value.
3. when to decide tables/indexes required stats gathered?
if data has modified more than 10 t0 to 15%
if estimated rows are higher than the actual rows (using TOAD or ADVANCED option when generating Explain plan DBMS_XPLAN)
4. wait events
db_sequentail
-------------
It is basically index scan or sing block read
this wait event basically occur when indexes are week or may be SQL is not picking up correct indexes.
index may need rebuild
indexes stats stale
may wrong indexes are being used -- i will test using hints in SQL
scattered read
---------------
It is a table scan or multi block read
we have analyze what tables are being used and tune the SQL or create the required indexes after proper testing
log file sync wait
------------------
It is triggered when a user session issues a commit (or a rollback).
when a user session commits the LGWR writes the data from log buffer to the redo log file once the LGWR writes the data
it will acknowlege to the user session.
The wait is entirely dependent on LGWR to write out the necessary redo blocks and send confirmation
of its completion back to the user session.
reasons:
high number of commits/rollbacks -- i will how many log switches
diks I/O slowness - IOSTATS
buffer busy or read by other session
------------------------------------
Buffer busy waits occur when an Oracle session needs to access a block in the buffer cache, but cannot because the
buffer copy of the data block is locked.
This buffer busy wait condition can happen for either of the following reasons:
-> The block is being read by another session, so the waiting session must wait for the block read to complete.
-> Another session has the buffer block locked in a mode that is incompatible with the waiting session's request.
reason:
hot blocks -- multipe users are accessing same block on same time - partitioning or pctfree and pctuse
tune the SQL - avoid accessing unnecessary data
5. does scattered read is for bad performance vs sequential read?
no scattered always not a bad performance wait event. it depends how much data the SQL is accessing. if a SQL is accessing
more than 15% of data they i thin Scattered would give more performance.
6. why query is not using index when column is already been indexed?
7. Hash join vs nested loop join
Hash join
---------
it creates a HASH table in memory using a driving table. and then it uses the hash table reference for matching on another table
rows. For some scenarios hash join will run faster than a nested loop join, but the hash join uses more RAM resources.
Nested loop
-----------
In a nested loops join, we have two tables a driving table and a secondary table.
The rows are usually accessed from a driving table "index range scan", and the driving table result
set is then nested within a probe of the second table, normally using an "index range scan" method.
8. while using hash join if hash memory is full or out of hash memory what happens?
it uses TEMP table and that is not good. - it impact performance.
10. what is index range scan?
it is ROWID access - basically it is a unique column access.
11. bitmap vs b tree index
Bitmap indexes are used on the columns which has lots of duplicate values(low cardinality) while B-tree indexes are
useful when there is less or no duplicate values (high cardinality).
B-tree index uses a tree-like structure with index nodes, where Bitmap index uses a two dimensional array with zeros and
ones (bits) values.
12. what is histogram?
Histograms are a feature of the cost-based optimizer (CBO) that allows the Oracle engine to determine how data
is distributed within a column. They are most useful for a column that is included in the WHERE clause of SQL
and the data distribution is skewed.
13. what is stale statistics?
stats is not up to date- 10% to 15% data modification -- dba_tab_statistics
14. If instance is running slow, how to troubleshoot?
step-1: I will check the blocking sessions - v$lock and v$session (blocker user and session details)
step-2: I will check the current CPU load - running TOP and IOTOP command or SAR command
step-3: I will check the RMAN backup is running or not
step-4: i will check memory and SWAP utilization - vmstats (OS level), free -g
step-5: Generate AWR, ASH, ADDM reports
AWR report - awrrpt.sql - $ORACLE_HOME/rdbms/admin - 30 min or 1 hours
ASH report - ashrpt.sql - dfault - 15 min
ADDM report - addmrpt.sql - AWR snap shot
step-6: same time i will monitor OEM
step-7: will check if any long running sessions
15. how to trace a user session?
I will get the session id and use the DBMS_SYSTEM set sql trace pacakge.
EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>TRUE);
16. how to force indexes to use SQL
using hints in SQL
/* index-name */
17. what is locking and different types of locking and wait events?
v$lock
dead lock
18. sync i/o and async io on OS level.
RAC
========
---- RAC -----
1. how to restore OLR and OCR files?
OCR restore and OLR:
-------------
we have every day backup running for OLR and OCR.
steps:
step-1: stop the CRS forcefully ==> crsctl stop crs -f
step-2: start the crs with nocrs option ==> crsctl start crs -excl -nocrs
step-3: restore the ocr file from backup ==> ocrconfig -restore
step-4: stop the crs
step-5: start the crs
2. How Database Connections are Created When Using SCANs.
--> when user connects to oracle rac databsae
1.
3. cluster startup sequence in oracle 11g.
10G vs 11G on RAC installation
10G ==> cluster home + ASM home + oracle database home => 3 homes
11G ==> (cluster home + ASHM home) = Grid home + oracle db home => 2 homes
10G vs 11G OCR and Vote disk storage
10G - no option to store cluster files on ASM => OCR + Votedisk are stored on shared location (ocfs)
11G - OCR + Votedisk can store on ASM - Mandatory
OLR file - this is a local registry file --> ASM files stores
when we run command CRSCTL START CRS
1. starts the HAS daemon process
2. once HAS started, it starts the GPNP and CSSD daemon processes
3. CSSD daemon process access the GPNP profile (.xml file)
4. once GPNP profile accessed the vote disk location it starts the cluster--> it access from ASM header
5. OHASD start the ASM instance and starts the CRS by accessing the OCR file
4. What are the files involved during cluster startup?
a. OLR
b. vote disk
c. OCR
5. GPNP prifle contains what information?
a. ASM Diskgroup Discovery String (ASM disk path)
b. ASM SPFILE location (Diskgroup name)
c. Name of the ASM Diskgroup containing the Voting Files
6. Differnece between OCR and OLR
7. how to check the health of cluster on all nodes
single node -> crsctl check crs
all node -> crsctl check cluster -all
8. How to check cluster resources status?
./crsctl status resource -t
9. how to check how many nodes are part of rac cluster?
olsnodes
10. how to relocate rac services?
1. check the service status and where it is running
SRVCTL STATUS SERVICE -D <DATBASE_NAME> -S <SERVICE_NAME>
2. relocate the service from one instance to another
SRVCTL RELOCATE SERVICE -D <DATBASE_NAME> -S <SERVICE_NAME> -oldinst <INSTANCE_NAME> -newinst <INSTANCE_NAME>
3. check the status of service
11. how to configured RAC :
-> linux instqll - linux 7
-> required rpm
Ip configured:
public IP
private IP ---> cluster interconnect
VIP --> virtual ip
SCAN IP - > 3 scan address
pre-req: cluvfy
grid software
database insall
db create
12. how to relocate rac services to another node
step-1: check the service status
SRVCTL STATUS SERVICE -S <SERVICE_NAME> -D <DATBASE_NAME>
step-2: Relocate the service to another node
SRVCTL RELOCATE SERVICE -S <SERVICE_NAME> -D <DATABASE_NAME> OLD_INSTANCE NEW_INSTANCE
13. how to make sure the services will relocate automatically to another node if one instance is down?
I will create RAC services using preferred and available services
SRVCTL ADD SERVICE -S <SERVICE_NAME> -D <DATABASE_NAME> -P NODE1 -A NODE2
14. Rac wait events.
gc cr request
--------------
It specifies the time it takes to retrieve the data from the remote cache.
High wait times for this wait event often are because of:
reason:
RAC Traffic Using Slow Connection ---> slow interconnect or slow network - using oradebug troubleshoot
Inefficient Queries --> find the bad sql and tune them
gc buffer busy acquire and gc buffer busy release
-------------------------------------------------
It specify the time the remote instance locally spends accessing the requested data block.
this wait event is very similar to the buffer busy wait events in a single-instance database.
Reason:
Hot Blocks - multiple sessions may be requesting a block that is either not in buffer cache or is in an incompatible mode.
Inefficient Queries
==== RAC ====
1. RAC wait events
2. What is cache fusion and who manges cache fusion
3. steps to perform RAC build
4. how to restore OLR file
5. What are RAC deamon process
6. Cluster logs on RAC
=== Dataguard ====
1. how to restore if datafile is lost (someone removed from OS Level) on primray database and on standby database?
==== PT ======
1. what is HASH Join and Nested loop join?
2. how to generate Execution plan?
3. What are wait events you aware and fix them?
startup and shutdown
cluster startup 11G vs 10G
scan
3 deamon
node
cache coherence vs cache fusion
service relocate
what happen when crs starts- what bp visible
node addition
cluvfy
patching
=============== RMAN ============
SYS@pltgo1 > show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string +TGO_DATA/pltgo/control01.ctl,
+TGO_FRA/pltgo/control02.ctl
all Controlfile lost
------------------------
1. startup nomount (SQLPLUS> STARTUP NOMOUNT)
2. restore controlfile from backup (RMAN> RESTORE CURRENT CONTROLFILE FROM BACKUP;)
3. mount database (SQLPLUS> ALTER DATBASE MOUNT;)
4. open database using resete logs ( SQLPLUS> ALTER DATBASE OPEN RESETLOGS;)
system datafile
----------------
1. shut abort the database if not down (SQLPLUS> SHUT ABORT)
2. startup nomount (SQLPLUS> STARTUP NOMUNT)
3. restore system datafile (RMAN> RESTORE TABLESPACE SYSTEM;)
4. mount database (SQLPLUS> ALTER DATABASE MOUNT)
5. restore database ( RMAN> RESTORE DATABASE;)
6. recover database (RMAN > RECOVER DATABASE;)
7. open database reset logs (SQLPLUS> ALTER DATABASE OPEN RESETLOGS;)
EVERYTHING IS LOST:
--------------------
1. startup nomount force - by default oracle startup with dummy spfile
2. create pfile from spfile; -- > shutdown the databse
3. startup nomount pfile='';
4. restore database;
5. recover database;
6. alter databse open resetlogs;
-- oracle architecture ---
How select, update, insert and delete works.
partitioning
reduce buffer busy wait event - hot blocks
1. Types of partition:
range partition
list partition
hash partition
range/list, and range/hash
Rang partition:
----------------
Range partition created based on DATE column on table.
Example:
Script for creating the TRANS table with range partitions
create table trans (
trans_id number,
trans_dt date,
product_code number,
store_id number,
trans_amount number(12,2)
)
partition by range (trans_dt)
(
partition y05q1 values less than (to_date('04/01/2005','mm/dd/yyyy')) tablespace y05q1, partition y05q2 values less than (to_date('07/01/2005','mm/dd/yyyy')) tablespace y05q2, partition y05q3 values less than (to_date('10/01/2005','mm/dd/yyyy')) tablespace y05q3,partition y05q4 values less than (to_date('01/01/2006','mm/dd/yyyy')) tablespace y05q4, partition pmax values less than (maxvalue) tablespace users
)
List partition
--------------
Based on city codes or city.
Example:
you could find a column that can be grouped into ranges of values such as dates, but it may not be possible to
group all columns that way. For example, a column holding names of U.S. states contains a finite and small number
of values. This type of column calls for list partitioning
2. how to check table is partition?
if you query the DBA_TABLES dictionary view—that the PARTITIONED column value is YES.
Tips:
You can get further details about the partitioning, such as the type of partitioning scheme and the number of partitions,
from the DBA_PART_TABLES view. You can get the details of each partition, such as the name and the upper boundary of
the partition, from the DBA_TAB_PARTITIONS view.
3. how to purge partition in oracle
4. how to gather stats on partition tables
====================================
Job Description-
Candidate should have
1. Extensive knowledge in Oracle RAC environment.
-> architecture-> BP, cluster files (OCR, VOTEDISK, OLR), cluster file backup mgmt,
-> check OCR health- > OCRCHECK,
-> which background process does the load balancing in oracle rac?
PMON
-> location of cluster log files
cssd, crsd, evmd, cluster log
2. Infrastructure Specialist in Oracle 10g/11g database servers --- will read
3. Installing and Managing Oracle 11g/12c RAC databases.
-> how to start, stop databases/instances/services
srvclt config, srvctl start, srvclt stop, srvclt status , srvclt add
-> how to relocate services
srvctl command
-> how to add instances
srvctl add instance
-> how to add node in oracle RAC --> will discuss this
location of Addnode.sh script -> $GRID_HOME/oui/bin
https://www.hhutzler.de/blog/addnode-setup/
-> how instance crash recovery happens in oracle RAC?
search in Google
-> how to troubleshoot Node evictions?
-> what is ASM redundancy you use to configure VOTEDISK?
Normal-> 3 way mirroring
external -> no mirroring
high -> 5 way mirroring
-> what is difference between 10G rac vs 11G rac?
10G -> we use to create CLUSTER_HOME and ASM home separately
but in 11G we use GRID_HOME as as cluster home + ASM home
no concept of SCAN in 10G
we can't store VOTEDISK and OCR on ASM
-> what are the 2 scripts you executer to compelete RAC information?
location of Addnode.sh script -> $GRID_HOME/oui/bin
https://www.hhutzler.de/blog/addnode-setup/
-> how instance crash recovery happens in oracle RAC?
-> how to troubleshoot Node evictions?
-> what is ASM redundancy you use to configure VOTEDISK?
Normal-> 3 way mirroring
external -> no mirroring
high -> 5 way mirroring
-> what is difference between 10G rac vs 11G rac?
-> what are the 2 scripts you executer to compelete RAC information?
two scripts:
orainstRoot.sh
root.sh -> starts HA services
it format and create ASM diskgroup for OCR and Votedisk and brings up the clusterware.
# /u01/app/oraInventory/orainstRoot.sh
Creating the Oracle inventory pointer file (/etc/oraInst.loc)
Changing permissions of /u01/app/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.
Changing groupname of /u01/app/oraInventory to oinstall.
The execution of the script is complete.
# /u01/app/11203/grid/root.sh
Performing root user operation for Oracle 11g
The following environment variables are set as:
ORACLE_OWNER= grid
ORACLE_HOME= /u01/app/11203/grid
Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.
Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /u01/app/11203/grid/crs/install/crsconfig_params
Creating trace directory
User ignored Prerequisites during installation
OLR initialization - successful
Adding Clusterware entries to upstart
CRS-4402: The CSS daemon was started in exclusive mode but found an active CSS daemon on node grac1, number 1, and is terminating
An active cluster was found during exclusive startup, restarting to join the cluster
clscfg: EXISTING configuration version 5 detected.
clscfg: version 5 is 11g Release 2.
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
Configure Oracle Grid Infrastructure for a Cluster ... succeeded
ASM
---
-> what are the background processes?
-> how to add disk?
-> how to create diskgroup?
-> how do you troubleshoot if ASM disk is missing?
1. first i will check when was the last successful start of ASM in ASM alert log file
2. query the V$ASM_DISK and V$ASM_DISKGROUP to find the list of ASM disks are currently mounted
3. i will compare the result from list of last successful ASM disk with current mounted ASM disks
4. then will create the missing ASM disks
-> what is power in ASM?
-> when re-balancing occur in oracle ASM?
-> what is use of ASMLIB?
-> What process does the rebalancing? what is meant by re-balancing?
-> what are the different types of redundancy?
there are 3 types of redundancy:
1. NORMAL: 2 way mirroring (handle loss of 1 ASM disk) --> 600G disk -> 300G free space
2. HIGH: 3 way mirroring (handle loss of 2 ASM disks) ---> 600G disk -> 200G free space
3. EXTERNAL: No mirroring RAID level of mirroring (OS level mirroring) - storage
-> Unable to open the ASM instance. What is the reason?
may disk is missing or permissions are changed
-> Can ASM instance and database (rdbms) be on different servers?
no, ASM and RDBMS instance should be on same host
-> Can we use ASM for storing Voting Disk/OCR in a RAC instance?
yes
-> Whats is Kfed?
to repair ASM corrupted disk
-> how to repair a corrupted disk in ASM?
-> Is it mandatory to use disks of same size and characteristics for Diskgroups?
no, but suggested to use same size.
-> how to backup ASM diskgroup/metdata?
connect to asmcmd command line and use md_backup tool
backup -> md_backup
restore -> md_restore
Example:
ASMCMD> md_backup /tmp/backup_ASM.bcp -G data,dgdup
4. experience in handling RAC production environments.
RAC basic commands.
5. Analyze SQL execution plans
it will show how data is accessed (access method), how many rows fetched and bytes and time and cost
-> ACCESS METHOD
-> NO OF ROWS
-> NO OF BYTES
-> COST (CPU OR RESOURCE)
-> TIME
-> what are the different ways to generate execution plans
-> what are the joins you have seen -> HASH JOIN and NESTED LOOP JOIN
-> what are the different types of index scans? --- call me
6. Configuring and Administering Oracle 11g Automatic Storage Management(ASM)
7. Experience in Database Migration/upgrade troubleshooting post upgrade issues.
list 3 to 4 post db migration or upgrade issues.
8. Experience in oracle snapshots, replication and streams. (Golden Gate) -- will discuss
9. Cloning development databases from PROD using RMAN and BCVs
-> active cloning --> it moves datafiles
-> duplicate database cloning
-> after cloning if i want to change the database name to different database name, how do you do that?
using NID utility
steps:
Changing DBNAME & DBID
SQL> select dbid, name from v$database;
DBID NAME
---------- ---------
1744662402 SFM
Backup the database.
Mount the database after a clean shutdown.
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT
Invoke the DBNEWID (NID) utility specifying the DBNAME from the command line using a user with SYSDBA privilege.
$ nid TARGET=sys/password DBNAME=new_name LOGFILE=change_dbname.log
The DBNEWID utility performs validations in the headers of the datafiles and control files before attempting I/O to the files. If validation is successful, then DBNEWID prompts you to confirm the operation (unless you specify a log file, in which case it does not prompt), changes the DBID in all datafiles and then exits. The database is left mounted but is not yet usable.
This utility won’t change the database name in pfile, so change the database name (DB_NAME) in pfile manually and create password file (if necessary).
Mount the database
SQL> STARTUP MOUNT
Open the database in RESETLOGS mode and resume normal use
SQL> ALTER DATABASE OPEN RESETLOGS;
SQL> select dbid, name from v$database;
DBID NAME
---------- ---------
1748860243 SFM_DEV
Make a new database backup. Because you had reset the online redo logs, the old backups and archived logs are no longer usable in the current incarnation of the database.
10. Configuring and Scheduling RMAN Backups
we are using cron job to configure RMAN backups
daily - level 1 database backup
weekly - level 0 database backup
archive - every 30 mins and 1 hours we have every 6 hours - SLAs
based on RTO (recovery time objective) and RPO (recovery point objective) every company designs backup policy.
11. Database Backup & recovery hands on experience using RMAN
- datfile lost (system and non-system datafile lost)
- controlfile lost (single and all controlfile lost)
- redo logfile lost (current log and non-current)
- tablespace lost
12. 21. Good communication and soft skills
SLA ---> Service level agreement
30 min SLA
1 hour SLA
ITIL mgmt tool -> HP service manger
changes (CR --> change request)
-> Standard changes - auto approved
-> Emergency changes - immediate fix
Incident (IM) -> something is wrong or client or business impacting - tablespace full ,archivelog full -- some issue
Request (RFA) -> creating of database or software installation
Problem (PM) -> permanent fix or root cause
3 types of incidents:
Sev -1: (business is impacting) critical - 30 min -> i have open a bridge call -> i will inform incident management team - onshore teams
sev-2: (little impact) 12 hours of SLA -> ack - 5 min
sev-3: (internal users impacting) 24 hours
Comments
Post a Comment