How to create custome top?
Query to know which user to which instance?
select i.host_name, s.username from
gv$session s join
gv$instance i on (i.inst_id=s.inst_id)
where username is not null;
Own Session:
SELECT sid,serial#, inst_id,username,event,ownerid FROM GV$SESSION
WHERE sid=(select sid from v$mystat where rownum=1);
benefit of shared appl_top?
what you do to clone from mutiple node to single node?
how to relink?
How to create a custom manager?
1. Define Custom Workshifts (Optional)
system Administrator->Concurrent->Manager->Workshifts
Click + icon on the menu bar (Top left) to insert new record.
Enter details and save.
2. Define Concurrent Manager
System Administrator->Concurrent->Manager->Define
Enter details for following:
Manager - New Concurrent Manager Name
Short Name - Short Name
Application Name - Usually Custom Application Name or Existing Application (Ex : Application Object Library)
Type - Concurrent Manager
Cache Size - 1
Node - If you use multi node configuration, you can differentiate primary node for this manager from standard manager such as follows otherwise just keep them blank so that EBS automatically find the right node (Primary - XX2,Secondary - XX1)
Program Library Name - FNDLIBR
Save the Record
3.Define Workshifts for manager
Click 'Workshifts' button on Manager form
Enter following
Select Work shifts from drop down define in step 1 or select standard workshifts
Processes - 10
Sleep Seconds - 30
Save Details
4.Define Specializtion Rule for manager
Click 'Specialization Rules' button on Manager form
Enter what need to be run on new manager
Include/Exclude
Type - Program (Can include Program)
-Oracle ID (Can include Module)
-User (Can include Program execute by particular User )
Application - Application Name for Program
Name - Name of Program/User
Save the Record
5.Exclude from Standard Manager
System Administrator->Concurrent->Manager->Define
Query 'Standard Manager' and Click 'Specialization Rules' button
Exclude the Program/User defined in Step 4
Save the Details
Note : In Specialization Rules of 'Standard Manager', never use "Include" definition. Standard Manager is supposed to deal with all requests. If you define any Include record here, Standard Manager only deal with that "Include" object as if it is a special manager for that object and all other concurrent requests will be kept in "No" Manager" status
6.Activate Newly Created Concurrent Manager
System Administrator->Concurrent->Manager->Administer
Scroll down the record until find the newly created concurrent manager
Click the row and click 'Activate' Button
By Clicking 'Refresh' Button at regular interval, Monitor Actual and Target Processes are displayed as 10 which defined in step 3
7.Bounce the Standard Manager to reflect the latest definition
System Administrator->Concurrent->Manager->Administer
Scroll down the record until find the 'Standard Manager' concurrent manager
Click the row and click 'Deactivate' Button
By Clicking 'Refresh' Button at regular interval, Monitor Status Column, Once Status column become Deactivated then Click 'Activate' button to start the Standerd Manager.
8. Check If the concurrent request are running with newly created concurrent manager
select b.USER_CONCURRENT_QUEUE_NAME,
e.USER_CONCURRENT_PROGRAM_NAME
from fnd_concurrent_processes a,
fnd_concurrent_queues_vl b, fnd_concurrent_requests c,
FND_CONC_REQ_SUMMARY_V d,FND_CONCURRENT_PROGRAMS_TL e
where a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID
and a.CONCURRENT_PROCESS_ID = c.controlling_manager
and c.request_id=d.request_id
and d.concurrent_program_id=e.concurrent_program_id
and c.request_id =<Request_id>;
Query to know which user to which instance?
select i.host_name, s.username from
gv$session s join
gv$instance i on (i.inst_id=s.inst_id)
where username is not null;
Own Session:
SELECT sid,serial#, inst_id,username,event,ownerid FROM GV$SESSION
WHERE sid=(select sid from v$mystat where rownum=1);
what you do to clone from mutiple node to single node?
how to relink?
How to create a custom manager?
1. Define Custom Workshifts (Optional)
system Administrator->Concurrent->Manager->Workshifts
Click + icon on the menu bar (Top left) to insert new record.
Enter details and save.
2. Define Concurrent Manager
System Administrator->Concurrent->Manager->Define
Enter details for following:
Manager - New Concurrent Manager Name
Short Name - Short Name
Application Name - Usually Custom Application Name or Existing Application (Ex : Application Object Library)
Type - Concurrent Manager
Cache Size - 1
Node - If you use multi node configuration, you can differentiate primary node for this manager from standard manager such as follows otherwise just keep them blank so that EBS automatically find the right node (Primary - XX2,Secondary - XX1)
Program Library Name - FNDLIBR
Save the Record
3.Define Workshifts for manager
Click 'Workshifts' button on Manager form
Enter following
Select Work shifts from drop down define in step 1 or select standard workshifts
Processes - 10
Sleep Seconds - 30
Save Details
4.Define Specializtion Rule for manager
Click 'Specialization Rules' button on Manager form
Enter what need to be run on new manager
Include/Exclude
Type - Program (Can include Program)
-Oracle ID (Can include Module)
-User (Can include Program execute by particular User )
Application - Application Name for Program
Name - Name of Program/User
Save the Record
5.Exclude from Standard Manager
System Administrator->Concurrent->Manager->Define
Query 'Standard Manager' and Click 'Specialization Rules' button
Exclude the Program/User defined in Step 4
Save the Details
Note : In Specialization Rules of 'Standard Manager', never use "Include" definition. Standard Manager is supposed to deal with all requests. If you define any Include record here, Standard Manager only deal with that "Include" object as if it is a special manager for that object and all other concurrent requests will be kept in "No" Manager" status
6.Activate Newly Created Concurrent Manager
System Administrator->Concurrent->Manager->Administer
Scroll down the record until find the newly created concurrent manager
Click the row and click 'Activate' Button
By Clicking 'Refresh' Button at regular interval, Monitor Actual and Target Processes are displayed as 10 which defined in step 3
7.Bounce the Standard Manager to reflect the latest definition
System Administrator->Concurrent->Manager->Administer
Scroll down the record until find the 'Standard Manager' concurrent manager
Click the row and click 'Deactivate' Button
By Clicking 'Refresh' Button at regular interval, Monitor Status Column, Once Status column become Deactivated then Click 'Activate' button to start the Standerd Manager.
8. Check If the concurrent request are running with newly created concurrent manager
select b.USER_CONCURRENT_QUEUE_NAME,
e.USER_CONCURRENT_PROGRAM_NAME
from fnd_concurrent_processes a,
fnd_concurrent_queues_vl b, fnd_concurrent_requests c,
FND_CONC_REQ_SUMMARY_V d,FND_CONCURRENT_PROGRAMS_TL e
where a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID
and a.CONCURRENT_PROCESS_ID = c.controlling_manager
and c.request_id=d.request_id
and d.concurrent_program_id=e.concurrent_program_id
and c.request_id =<Request_id>;
Application user is complaining the database is slow?
What is statistic?
1.statistics are a collection of data that describe the database, and the objects in the database.
2.statistic are basically a mathematical representation of data.They show the distribution of data.
3.statistic are main tained automatically.
4. statistics are used by the Optimizer to choose the best execution plan for each SQL statement.
5.Statistics are stored in the data dictionary, and can be accessed using data dictionary views such as
USER_TAB_STATISTICS.
6.When statistics are updated for a database object, Oracle invalidates any currently parsed SQL statements,the statement is re-parsed and the optimizer automatically chooses a new execution plan based on the new statistics.
**Oracle statistics tell us the size of the tables, the distribution of values within columns, and other important information so that SQL statements will always generate the best execution plans.
Oracle provides more than one way of generating statistics.
DBMS_UTILITY
ANALYZE command
DBMS_DDL
DBMS_STATS
GATHER_INDEX_STATS > Index statistics
GATHER_TABLE_STATS > Table, column, and index statistics
GATHER_SCHEMA_STATS > Statistics for all objects in a schema
GATHER_DATABASE_STATS > Statistics for all objects in a database
GATHER_SYSTEM_STATS > CPU and I/O statistics for the system
NOTE= Oracle strongly recommends that you use the DBMS_STATS package rather than ANALYZE to collect optimizer statistics. That package lets you collect statistics in parallel, collect global statistics for partitioned objects, and fine tune your statistics collection in other ways. Further, the cost-based optimizer will eventually use only statistics that have been collected by DBMS_STATS.
However, you must use the ANALYZE statement rather than DBMS_STATS for statistics collection not related to the cost-based optimizer, such as:
* To use the VALIDATE or LIST CHAINED ROWS clauses
* To collect information on freelist blocks
Oracle recommends setting the ESTIMATE_PERCENT parameter of the DBMS_STATS gathering procedures to DBMS_STATS.AUTO_SAMPLE_SIZE to maximize performance gains while achieving necessary statistical accuracy. AUTO_SAMPLE_SIZE lets Oracle determine the best sample size for good statistics. For example, to collect table and column statistics for all tables in the OE schema with auto-sampling:
If you intend to ESTIMATE statistics because of time constraints, try to arrive at an optimal sample size that will yield excellent results for your database. In general, a sample size of 5% to 10% generates adequate statistics!
Gathering statistics on tables requires sorting to be done and this takes up resources. Gathering statistics on indexes does not require sorting. Considering this benefit, you may COMPUTE statistics on indexes for accurate data.
By default DBMS_STATS will generate statistics for the table and not it's indexes (By default CASCADE => FALSE). Gather statistics on the indexes for this table.
https://satya-dba.blogspot.com/2010/06/oracle-database-statistics-rbo-cbo.html
http://www.mbjconsulting.com/oracle_links/Gather_Statistics.html
Oracle Tables and Statistics:
The optimizer's job is to take SQL statements and decide how to get the data that is being asked for in the SQL statement and how to get it in the quickest way possible.When a SQL statement is executed, the database must convert the query into an execution plan and choose the best way to retrieve the data.For Oracle, each SQL query has many choices for execution plans, including which index to use to retrieve table row,what order in which to join multiple tables together, and which internal join methods to use (Oracle has nested loop joins,hash joins, star joins, and sort merge join methods).
The choice of executions plans made by the Oracle SQL optimizer is only as good as the Oracle statistics.To always choose the best execution plan for a SQL query, Oracle relies on information about the tables and indexes in the query.Once the optimizer has done its job, it provides an execution plan to Oracle.
The optimizer is influenced by the following factors:
OPTIMIZER_MODE in the initialization file
Statistics in the data dictionary
Hints
What is execution plan?
An execution plan is like a set of instructions that tells Oracle how to go and get the data.
What is difference between Explain Plan and Execution plan ?
Whenever we issue a sql statment oracle by default builds an EXECUTION PLAN to execute that statment. It takes some important decisions like whether to use indexes or not ,if more than one indexes are used which one is to be followed etc.
And if we want to explore that execution plan then it provides a sql syntax EXPLAIN PLAN to determine this.
EXPLAIN PLAN FOR
SELECT * FROM emp e, dept d WHERE e.deptno = d.deptno AND e.ename = 'SMITH';
SET LINESIZE 130
SET PAGESIZE 0
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
select * from table(dbms_xplan.display_awr('d6xu9cxq57ant'));
select plan_table_output from table(DBMS_XPLAN.display_cursor('7s78wpgr5wadp'));
Cost Based Optimization (CBO) vs. Rule Based Optimization (RBO):
Rule Based Optimization:
This is an old technique.
Basically, the RBO used a set of rules to determine how to execute a query. If an index was available on a table, the RBO rules said to always use the index.
There are some cases where the use of an index slowed down a query.
For example, assume someone put an index on the GENDER column, which holds one of two values, MALE and FEMALE. Then someone issues the following query:
SELECT * FROM emp WHERE gender='FEMALE';
If the above query returned approximately 50% of the rows, then using an index would actually slow things down.
It would be faster to read the entire table and throw away all rows that have MALE values.
Experts in Oracle query optimization have come to a rule of thumb that says if the number of rows returned is more than 5-10% of the total table volume, using an index would slow things down. The RBO would always use an index if present because its rules said to
RBO was supported in earlier versions of Oracle.
The Rule Based Optimizer (RBO) is now obsolete in Oracle 10g.
The biggest problem with the RBO was that it did not take the data distribution into account.
Cost Based Optimization:
The CBO uses statistics about the table, its indexes and the data distribution to make better informed decisions.
Motivation behind CBO is to come up with the cheapest execution plan available for each SQL statement. The cheapest plan is the one that will use the least amount of resources (CPU, Memory, I/O, etc.) to get the desired output (in relation to our travel analogy this can be Petrol, time, etc.).
Difference between ANALYZE TABLE and DBMS_STATS.GATHER_TABLE_STATS?
The ANALYZE command counts the leaf blocks, that are currently within the index structure.
The DBMS_STATS package counts the leaf blocks, that have currently data in them.
dbms_stats is a superset of analyze command.
DBMS_STATS can be done in parallel.
Monitoring can be done and stale statistics can be collected for changed rows using DBMS_STATS.
it is easier to automate with dbms_stats (it is procedural, analyze is just a command).
dbms_stats can analyze external tables, analyze cannot.
“DBMS_STATS gathers statistics only for cost-based optimization; it does not gather other
statistics. For example, the table statistics gathered by DBMS_STATS include the number
of rows, number of blocks currently containing data, and average row length but not the
number of chained rows, average free space, or number of unused data blocks.”
Analyze table hangs for hours using DBMS_STATS.GATHER_TABLE_STATS?
Did you look at v$session_wait to see what the session was waiting on?
Also have a look at v$session_longops.
How to check previoues plan?
Select SAMPLE_TIME,SESSION_ID,SQL_PLAN_HASH_VALUE from DBA_HIST_ACTIVE_SESS_HISTORY where SQL_ID='9hha8mvkmmjfd' order by 1;
Why table will be marked as stale?
Statistics on a table are considered stale when more than STALE_PERCENT (default 10%) of the rows are changed (total number of inserts, deletes, updates) in the table.
updating > stale_percent rows in a table will mark the stats as stale:
How do I get the total number of inserts/updates that have occurred in an Oracle database over a period of time?
https://stackoverflow.com/questions/6700010/how-do-i-get-the-number-of-inserts-updates-occuring-in-an-oracle-database
An event for which a process is waiting for.
Oracle process is BUSY when it utilises the cpu time and is NOT BUSY only when it’s waiting for some thing to happen.It waits for something…. WAITS FOR WHAT ? This could be various reasons and every reason has been given a specific name and is termed to be a ORACLE WAIT EVENT.
Difference between DB file sequential read and DB File Scattered Read?
Both "db file sequential read" and "db file scattered read" events signify time waited for I/O read requests to complete.A db file scattered read is the same type of event as "db file sequential read", except that Oracle will read multiple data blocks. Multi-block reads are typically used on full table scans.
These are two types of data block access in oracle:
db file sequential read is a single block IO, most typically these comes from index range scans and table access by index rowid.
db file sequential read (A single-block read (i.e., (for index scan)index fetch by ROWID)): It is the wait even for SINGLE BLOCK IO. A block at a time, block by block, from index to table. It reads block into contiguous memory.
If the query plan was "index range scan, table access by index rowid",it will use db file sequential reads, read index, read table block, read index, read table block - all single block IO.
There are many blocks being read - sequentially from index to table, index to table.
A single-block read with p3=1
db file sequential read => index scan, table access by index rowid
db file scattered read:A multiblock read (a full-table scan, OPQ, sorting): It is full-table scan read.
db file scattered read => full table scan, index fast full scan
read a bunch of blocks and SCATTER them in the buffer cache.
one block (p3 is always >= 2).
what is enqueue (enq:) waits:
This event indicates that the session is waiting for a lock that is held by another session.
what is Busy Buffer waits?
Busy Buffer waits means that the queries are waiting for the blocks to be read into the db cache.
there could be reason when the block may be busy in the cache and session is waiting for it.It could be undo/data block or segment header wait.
Select p1 "File #",p2 "Block #",p3 "Reason Code" from v$session_wait Where event = 'buffer busy waits';
Select owner, segment_name, segment_type from dba_extents where file_id = &P1 and &P2 between block_id and block_id + blocks -1;
Difference between Redo,Rollback and Undo?
Row Chaning and Row Migration?
How to find out background processes ?
select SID,PROGRAM from v$session where TYPE='BACKGROUND';
select name,description from V$bgprocess;
How to findout background processes from OS:
ps -ef|grep ora_|grep SID
Why drop table is not going into Recycle bin?
If using SYS user to drop any table then user’s object will not go to the recyclebin as there is no recyclebin for SYSTEM tablespace, even on setting already SET recycle bin parameter TRUE.
Select * from v$parameter where name = 'recyclebin';
Show parameter recyclebin;
Which factors are to be considered for creating index on Table? How to select column for index?
Creation of index on table depends on size of table, volume of data.If table size is large and only need few data for selecting or in report then need to create index.
There are some basic reason of selecting column for indexing like cardinality and frequent usage in where condition of select query. Business rule is also forcing to create index like primary key, because configuring primary key or unique key automatically create unique index. Too many indexes would affect the performance of DML on table because in single transaction should need to perform on various index segments and table simultaneously.
A hot (or on-line) backup is a backup performed while the database is open and available for use (read and write activity). Except for Oracle exports, one can only do on-line backups when the database is ARCHIVELOG mode.
A cold (or off-line) backup is a backup performed while the database is off-line and unavailable to its users. Cold backups can be taken regardless if the database is in ARCHIVELOG or NOARCHIVELOG mode.What is the difference between online and offline backups?
It is easier to restore from off-line backups as no recovery (from archived logs) would be required to make the database consistent. Nevertheless, on-line backups are less disruptive and don't require database downtime.
Point-in-time recovery (regardless if you do on-line or off-line backups) is only available when the database is in ARCHIVELOG mode.
What is the difference between restoring and recovering?
Restoring involves copying backup files from secondary storage (backup media) to disk. This can be done to replace damaged files or to copy/move a database to a new location.
Recovery is the process of applying redo logs to the database to roll it forward. One can roll-forward until a specific point-in-time (before the disaster occurred), or roll-forward until the last transaction recorded in the log files.
A cold (or off-line) backup is a backup performed while the database is off-line and unavailable to its users. Cold backups can be taken regardless if the database is in ARCHIVELOG or NOARCHIVELOG mode.What is the difference between online and offline backups?
It is easier to restore from off-line backups as no recovery (from archived logs) would be required to make the database consistent. Nevertheless, on-line backups are less disruptive and don't require database downtime.
Point-in-time recovery (regardless if you do on-line or off-line backups) is only available when the database is in ARCHIVELOG mode.
What is the difference between restoring and recovering?
Restoring involves copying backup files from secondary storage (backup media) to disk. This can be done to replace damaged files or to copy/move a database to a new location.
Recovery is the process of applying redo logs to the database to roll it forward. One can roll-forward until a specific point-in-time (before the disaster occurred), or roll-forward until the last transaction recorded in the log files.
How to enable/disable archivelog mode?
Enable:
SQL> CONNECT sys AS SYSDBA
SQL> shutdown immediate
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
Disable:
SQL> shutdown immediate
SQL> startup mount
SQL> alter database noarchivelog;
SQL> alter database open;
Other parameter:
SQL> show parameter recovery_file_dest
SQL> archive log list
SQL> alter system set log_archive_dest_1='LOCATION=/u02/app/oracle/oradata/orcl/arch' scope = both;
By default, archive logs will be written to the flash recovery area. If you do not want to write archive logs to the flash recovery area you can set the parameter LOG_ARCHIVE_DEST_n to the location in which you wish to write archive logs.
What are available way for backing-up an Oracle database?
Export/Import - Exports are "logical" database backups in that they extract logical definitions and data from the database to a file.
Cold or Off-line Backups - shut the database down and backup up ALL data, log, and control files.
Hot or On-line Backups - If the database is available and in ARCHIVELOG mode, set the tablespaces into backup mode and backup their files. Also remember to backup the control files and archived redo log files.
RMAN Backups - while the database is off-line or on-line, use the "rman" utility to backup the database.
Difference between OBSOLETE and EXPIRED?
OBSOLETE:If a backup item is no longer needed for recovery – because it is older than the retention policy – then it is obsolete.
EXPIRED:means that the backup piece or backup set is not found in the backup destination or missing .Since backup info is hold in our controlfile and catalog.
How do you identify what are the all the target databases that are being backed-up with RMAN database?
Connect to the catalog database and issue following query.
SQL> select name,dbid from rc_database;
When do you recommend hot backup? What are the pre-reqs?
When there is no down time for database (24/7) ,we should go for hot backup.
pre-req:database must be in archive log mode
How do you identify the expired, active, obsolete backups? Which RMAN command you use?
1) RMAN>crosscheck backup;This command will give you the output for the Active and Expired Backups.
2) RMAN>report obsolete;This command will shows you the obsolete backups.
Difference between recovery and restoring of the database.
A scenario to understand Restore & Recovery process:
Sunday 10pm : Database is backed up. and is running fine.
Monday 11am : Went down / crashed due to any reason.
To bring up the database, we have 2 options:
1. Simple Restore : copying files from backup taken Sunday night and open the database. Here, we loose all the changes that are done since Sunday night.
2. Restore and Recovery: Copying files from backup taken Sunday night and applying all the archive log and redo log files to bring up the database to the point of failure. Here you dont loose the changes done until Monday 11 am.
Restore : copying files from the backup overwriting the existing database files
Recovery: applying the changes to the database till point of failure. these changes are recorded in online redolog and archivelog ( which are the backups of redolog) files.
What happens when you run ALTER DATABASE OPEN RESETLOGS ?
The current online redo logs are archived, the log sequence number is reset to 1, new database incarnation is created, and the online redo logs are given a new time stamp and SCN.
The reason to do the open the database with the resetlogs is that after doing an incomplete recovery , the data files and control files still don't come to the same point of the redo log files. And as long as the database is not consistent within all the three file-data, redo and control, you can't open the database. The resetlogs clause would reset the log sequence numbers within the log files and would start them from 0 thus enabling you to open the database but on the cost of losing all what was there in the redo log files.
In what scenarios open resetlogs required ?
An ALTER DATABASE OPEN RESETLOGS statement is required,
1.after incomplete recovery (Point in Time Recovery) or
2.recovery with a backup control file.
3. recovery with a control file recreated with the reset logs option.
Difference between RESETLOGS and NORESETLOGS ?
The NORESETLOGS option does not clear the redo log files during startup and the online redo logs to be used for recovery. Only used in scenario where MANUAL RECOVERY is started, CANCEL is used, and then RECOVER DATABASE is started.
What is SCN (System Change Number) ?
The system change number (SCN) is an ever-increasing value that uniquely identifies a committed version of the database at a point in time. Every time a user commits a transaction Oracle records a new SCN in redo logs.
Oracle uses SCNs in control files datafile headers and redo records. Every redo log file has both a log sequence number and low and high SCN. The low SCN records the lowest SCN recorded in the log file while the high SCN records the highest SCN in the log file.
What is Database Incarnation ?
Database incarnation is effectively a new “version” of the database that happens when you reset the online redo logs using “alter database open resetlogs;”.
Database incarnation falls into following category Current, Parent, Ancestor and Sibling
i) Current Incarnation : The database incarnation in which the database is currently generating redo.
ii) Parent Incarnation : The database incarnation from which the current incarnation branched following an OPEN RESETLOGS operation.
iii) Ancestor Incarnation : The parent of the parent incarnation is an ancestor incarnation. Any parent of an ancestor incarnation is also an ancestor incarnation.
iv) Sibling Incarnation : Two incarnations that share a common ancestor are sibling incarnations if neither one is an ancestor of the other.
How to view Database Incarnation history of Database ?
Using SQL> select * from v$database_incarnation;
Using RMAN>LIST INCARNATION;
However, you can use the RESET DATABASE TO INCARNATION command to specify that SCNs are to be interpreted in the frame of reference of another incarnation.
•For example my current database INCARNATION is 3 and now I have used
FLASHBACK DATABASE TO SCN 3000;then SCN 3000 will be search in current incarnation which is 3. However if I want to get back to SCN 3000 of INCARNATION 2 then I have to use,
RMAN> RESET DATABASE TO INCARNATION 2;
The Checkpoint Process
The checkpoint process is responsible for updating file headers in the database datafiles. A checkpoint occurs when Oracle moves new or updated blocks (called dirty blocks) from the RAM buffer cache to the database datafiles. A checkpoint keeps the database buffer cache and the database datafiles synchronized. This synchronization is part of the mechanism that Oracle uses to ensure that your database can always be recovered.
https://blog.tuningsql.com/how-to-tell-if-statistics-are-stale-in-oracle/
What are available way for backing-up an Oracle database?
Export/Import - Exports are "logical" database backups in that they extract logical definitions and data from the database to a file.
Cold or Off-line Backups - shut the database down and backup up ALL data, log, and control files.
Hot or On-line Backups - If the database is available and in ARCHIVELOG mode, set the tablespaces into backup mode and backup their files. Also remember to backup the control files and archived redo log files.
RMAN Backups - while the database is off-line or on-line, use the "rman" utility to backup the database.
Difference between OBSOLETE and EXPIRED?
OBSOLETE:If a backup item is no longer needed for recovery – because it is older than the retention policy – then it is obsolete.
EXPIRED:means that the backup piece or backup set is not found in the backup destination or missing .Since backup info is hold in our controlfile and catalog.
How do you identify what are the all the target databases that are being backed-up with RMAN database?
Connect to the catalog database and issue following query.
SQL> select name,dbid from rc_database;
When do you recommend hot backup? What are the pre-reqs?
When there is no down time for database (24/7) ,we should go for hot backup.
pre-req:database must be in archive log mode
How do you identify the expired, active, obsolete backups? Which RMAN command you use?
1) RMAN>crosscheck backup;This command will give you the output for the Active and Expired Backups.
2) RMAN>report obsolete;This command will shows you the obsolete backups.
Difference between recovery and restoring of the database.
A scenario to understand Restore & Recovery process:
Sunday 10pm : Database is backed up. and is running fine.
Monday 11am : Went down / crashed due to any reason.
To bring up the database, we have 2 options:
1. Simple Restore : copying files from backup taken Sunday night and open the database. Here, we loose all the changes that are done since Sunday night.
2. Restore and Recovery: Copying files from backup taken Sunday night and applying all the archive log and redo log files to bring up the database to the point of failure. Here you dont loose the changes done until Monday 11 am.
Restore : copying files from the backup overwriting the existing database files
Recovery: applying the changes to the database till point of failure. these changes are recorded in online redolog and archivelog ( which are the backups of redolog) files.
What happens when you run ALTER DATABASE OPEN RESETLOGS ?
The current online redo logs are archived, the log sequence number is reset to 1, new database incarnation is created, and the online redo logs are given a new time stamp and SCN.
The reason to do the open the database with the resetlogs is that after doing an incomplete recovery , the data files and control files still don't come to the same point of the redo log files. And as long as the database is not consistent within all the three file-data, redo and control, you can't open the database. The resetlogs clause would reset the log sequence numbers within the log files and would start them from 0 thus enabling you to open the database but on the cost of losing all what was there in the redo log files.
In what scenarios open resetlogs required ?
An ALTER DATABASE OPEN RESETLOGS statement is required,
1.after incomplete recovery (Point in Time Recovery) or
2.recovery with a backup control file.
3. recovery with a control file recreated with the reset logs option.
Difference between RESETLOGS and NORESETLOGS ?
The NORESETLOGS option does not clear the redo log files during startup and the online redo logs to be used for recovery. Only used in scenario where MANUAL RECOVERY is started, CANCEL is used, and then RECOVER DATABASE is started.
What is SCN (System Change Number) ?
The system change number (SCN) is an ever-increasing value that uniquely identifies a committed version of the database at a point in time. Every time a user commits a transaction Oracle records a new SCN in redo logs.
Oracle uses SCNs in control files datafile headers and redo records. Every redo log file has both a log sequence number and low and high SCN. The low SCN records the lowest SCN recorded in the log file while the high SCN records the highest SCN in the log file.
What is Database Incarnation ?
Database incarnation is effectively a new “version” of the database that happens when you reset the online redo logs using “alter database open resetlogs;”.
Database incarnation falls into following category Current, Parent, Ancestor and Sibling
i) Current Incarnation : The database incarnation in which the database is currently generating redo.
ii) Parent Incarnation : The database incarnation from which the current incarnation branched following an OPEN RESETLOGS operation.
iii) Ancestor Incarnation : The parent of the parent incarnation is an ancestor incarnation. Any parent of an ancestor incarnation is also an ancestor incarnation.
iv) Sibling Incarnation : Two incarnations that share a common ancestor are sibling incarnations if neither one is an ancestor of the other.
How to view Database Incarnation history of Database ?
Using SQL> select * from v$database_incarnation;
Using RMAN>LIST INCARNATION;
However, you can use the RESET DATABASE TO INCARNATION command to specify that SCNs are to be interpreted in the frame of reference of another incarnation.
•For example my current database INCARNATION is 3 and now I have used
FLASHBACK DATABASE TO SCN 3000;then SCN 3000 will be search in current incarnation which is 3. However if I want to get back to SCN 3000 of INCARNATION 2 then I have to use,
RMAN> RESET DATABASE TO INCARNATION 2;
The Checkpoint Process
The checkpoint process is responsible for updating file headers in the database datafiles. A checkpoint occurs when Oracle moves new or updated blocks (called dirty blocks) from the RAM buffer cache to the database datafiles. A checkpoint keeps the database buffer cache and the database datafiles synchronized. This synchronization is part of the mechanism that Oracle uses to ensure that your database can always be recovered.
https://blog.tuningsql.com/how-to-tell-if-statistics-are-stale-in-oracle/
