Physical Backup
1. Can Full Backup be performed when the database is open?
No.
No.
2. What are the advantages of operating a database in ARCHIVELOG mode over operating it in NO ARCH IVELOG mode?
Complete database recovery from disk failure is possible only in ARCHIVELOG mode. Online database backup is possible only in ARCHIVELOG mode.
3. How do you restore and recover a datafile while the database is up and running?
Make the datafile offline, restore and recover datafile, make the datafile online;
4. Can you take COLD backup while database is running?
No
5. HOT backup is consistent backup or inconsistent backup?
Inconsistent
6. Can you take logical backups in mount stage?
No
7. How do you know whether the specific tablespace is in begin backup mode?
Select status from v$backup. if it is active it means it is in begin backup mode
8. When will you take Cold back up especially?
During up gradation and migration
9. What are the modes/options in incomplete recovery?
Cancel based, change based, time based
10. How do you applying archive logs to cold backup of previous day?
Steps involved in recovery are:
- restore cold backup
- Startup mount
-recover database using backup control file until cancel
-alter database open resetlogs
-shutdown
-startup
11. What is hot backup and how it can be taken?
The database which is 24/7 those databases are never shutdown. Such databases backup should be taken when database is running on.This type of physical backup is called as Hot backup.
Steps to take Hot backup.
1 Begin backup
2 Cp *.dbf <Backup location>
3 End backup
4 Log switch
5 Take controlfile backup with sql statement.
12. What is cold backup? Cold backup is taking backup of all physical files after normal shutdown of database. We need to take.
1. All Data files.
2. All Control files.
3. All on-line redo log files.
4. The init.ora file (Optional)
13. What is Partial Backup?
A Partial Backup is any operating system backup short of a full backup, taken while the database is open or shut down.
14. Explain the difference between a hot backup and a cold backup and the benefits associated with each.
A hot backup is basically taking a backup of the database while it is still up and running and it must be in archive log mode. A cold backup is taking a backup of the database while it is shut down and does not require being in archive log mode. The benefit of taking a hot backup is that the database is still available for use while the backup is occurring and you can recover the database to any point in time. The benefit of taking a cold backup is that it is typically easier to administer the backup and recovery process. In addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a slight performance gain as the database is not cutting archive logs to disk.
15. You have just had to restore from backup and do not have any control files. How would you go about bringing up this database?
I would create a text based backup control file, stipulating where on disk all the data files where and then issue the recover command with the using backup control file clause.
RMAN
1. Which role you grant to rman user while configuring rman user
Recovery_catalog_owner role.
2. Temporarily recovery catalog database (for RMAN) is down. Can you still run a backup? How?
Yes, using no catalog mode
3. You have run a backup of database using RMAN nocatalog. How do you sync the recovery catalog with the metadata about the backup that was taken?
Resync catalog
4. What is compress parameter?
It reduces the size of backup
5. With our catalog can we connect 2 target databases at a time?
Yes …we can…
6. We have rman backup. can we restore it without using rman and run the Db?
No
7. Is it needs to take bkp of catalog db?
Not mandatory...
8. Why RMAN scripts not work in no catalog mode?
Rman scripts stored in a catalog. but in no catalog mode we not creating catalog. So no scripts.
9. What use of command line parameter cmd file? It is a command line argument that allows you to specify a file that contains a set of arguments for run.
10. What is the use of nohup?The execution of a specific task is performed in the server side with out any interrupting Usage : nohup cp -r * /tmp/. &
11. How do u set crontab to delete 5 days old trace files at daily 10'0 clock?
crontab –e 0 10 * * * /usr/bin/find /disk2/oradata/prod/diag/rdbms/prod/prod/trace -name "*.trc" -mtime +5 -exec rm -rf {} \; save and exit (wq!)
12. From RMAN, how do you check if an archive log file is backedup to tape or not? Report need backup;
13. How do you send the data to tape? Using 1. tar -cvf or 2. cpio
14. How do you enable/disable debugging mode in unix? Set -x and set +x
15. How to configure RMAN?
Configuring RMAN can be done two types. Using catalog and with out using catalog. If you are using catalog it you will have to specify a separate tablespace for the catalog. This will remove the overhead on control file maintaining all the info in it.
16. How to configure RMAN on tape drives (VTL)?
There is a parameter called device_type just give it as sbt and specify the tape location.
17. RMAN improvements
Oracle10g comes with a just a ton of improvements (I like that word, plethora!) for RMAN. These include the following:
1. The Flash Recovery Area
2. Fast Recovery
3. Using the catalog and uncatalog commands
4. Dropping a database in RMAN
5. Unregistering a database in RMAN
6. Making and Using RMAN backup copies
7. Configuring default disk backup types.
8. Changes to incremental backups.
9. Recovering datafiles not backed up.
10. Changes in error reporting.
11. Compressing RMAN Backups
12. RMAN Related TSPITR changes.
18. What is difference between OBSOLETE and EXPIRED?
Obsolete backups mean inconsistency backups for recovery purpose. Expired backups means those backups physical files are not available on disk.
19. How speed up backup jobs in Rman?
By increase number of channels.
Performance Tuning
1. What is performance Tuning?
Performance tuning is the term used to improve the performance of a C.P.U to increase the speed of response time with minimum resource.
2. Why and when should one tune?One of the biggest responsibilities of a DBA is to ensure that the Oracle database is tuned properly. The Oracle RDBMS is highly tunable and allows the database to be monitored and adjusted to increase its performance.
One should do performance tuning for the following reasons:
1.The speed of computing might be wasting valuable human time (users waiting for response);
2.Enable your system to keep-up with the speed business is conducted; and
3.Optimize hardware usage to save money (companies are spending millions on hardware).
One should do performance tuning for the following reasons:
1.The speed of computing might be wasting valuable human time (users waiting for response);
2.Enable your system to keep-up with the speed business is conducted; and
3.Optimize hardware usage to save money (companies are spending millions on hardware).
3. What database aspects should be monitored?
One should implement a monitoring system to constantly monitor the following aspects of a database. Writing custom scripts, implementing Oracle’s Enterprise Manager, or buying a third-party monitoring product can achieve this. If an alarm is triggered, the system should automatically notify the DBA (e-mail, page, etc.) to take appropriate action.
Infrastructure availability: • Is the database up and responding to requests
• Are the listeners up and responding to requests
• Are theOracle Names and LDAP Servers up and responding to requests
• Are the Web Listeners up and responding to requests
Things that can cause service outages:• Is the archive log destination filling up?
• Objects getting close to their max extents
• Tablespaces running low on free space/ Objects what would not be able to extend
• User and process limits reached
Infrastructure availability: • Is the database up and responding to requests
• Are the listeners up and responding to requests
• Are the
• Are the Web Listeners up and responding to requests
Things that can cause service outages:• Is the archive log destination filling up?
• Objects getting close to their max extents
• Tablespaces running low on free space/ Objects what would not be able to extend
• User and process limits reached
4. What tuning indicators can a DBA use?The following high-level tuning indicators can be used to establish if a database is performing optimally or not:
1 Buffer Cache Hit Ratio
Formula: Hit Ratio = (Logical Reads - Physical Reads) / Logical Reads
Action: Increase DB_CACH E_SIZE (DB_BLOCK _BUFFERS prior to 9i) to increase hit ratio
2Library Cache Hit Ratio
Action: Increase the SHARED_POOL_SIZE to increase hit ratio
1 Buffer Cache Hit Ratio
Formula: Hit Ratio = (Logical Reads - Physical Reads) / Logical Reads
Action: Increase DB_
2Library Cache Hit Ratio
Action: Increase the SHARED_POOL_SIZE to increase hit ratio
5. What are the values that can be specified for OPTIMIZER MODE Parameter?All_rows,rule,first_rows_1000,first_rows_100,first_rows_10,first_rows_1,choose,first_rows.
6. When you enable tracing for a SQL statement, where do you look for the trace files?
In 11g /disk1/oradata/prod/diag/rdbms/db_name/instance_name/trace In 10g /disk1/oradata/prod/udump
7. What is the function of Optimizer?
The goal of the optimizer is to choose the most efficient way to execute a SQL statement.
8. What are the different approaches used by Optimizer in choosing an execution plan?
Rule-based and Cost-based.
9. What are the values that can be specified for OPTIMIZER MODE Parameter?
All_rows and rule.
The goal of the optimizer is to choose the most efficient way to execute a SQL statement.
8. What are the different approaches used by Optimizer in choosing an execution plan?
Rule-based and Cost-based.
9. What are the values that can be specified for OPTIMIZER MODE Parameter?
All_rows and rule.
10. What is COST -based approach to optimization?
Considering available access paths and determining the most efficient execution plan based on statistics in the data dictionary for the tables accessed by the statement and their associated clusters and indexes.
Considering available access paths and determining the most efficient execution plan based on statistics in the data dictionary for the tables accessed by the statement and their associated clusters and indexes.
11. What is RULE-based approach to optimization?
Choosing an executing plan based on the access paths available and the ranks of these access paths.
Choosing an executing plan based on the access paths available and the ranks of these access paths.
12. Diff between Production, Development& QA database?
PRODUCTION database is currently using by end users.
Development database using by developers.
13. Diff b/w patching and upgrading?
Patching for solving any bugs in the database.
Up gradation for changing versions and release no's
Patching for solving any bugs in the database.
Up gradation for changing versions and release no's
14. How do you check the locks in the database and determine if there is any deadlock issue?
Transaction deadlocks occur when two or more transactions are attempting to access an object with incompatible lock modes. The following script can be used to identify deadlocks in the database. The query depends upon objects that are created by the script ORACLE _HOME/rdbms/admin/dbmslock.sql. Log on as SYS or with SYSDBA authority and run this script in all databases
15. Which view is used to see dead locks?
v$lock
v$session
v$parameter
16. How does u know when system was last booted?
3 ways
1.uptime cmd
2.top
3.who -b
20. How do u know load on system?
1. W
2. Top
21. How do you know when the process is started?
Using ps -ef| grep process name
22. What type of default optimizer does 10g use?
Application Tuning
1. What is Parallel Server?Oracle Parallel Server is a robust computing environment that harnesses the processing power of multiple, interconnected computers. Oracle Parallel Server software and a collection of hardware known as a "cluster", unites the processing power of each component to become a single, robust computing environment. A cluster generally comprises two or more computers, or "nodes".(i.e) Multiple instances accessing the same database (Only In Multi-CPU environments)
2. What is mean by Program Global Area (PGA ) ?
The PGA (Program or Process Global Area) is a memory area (RAM) that stores data and control information for a single process. it typically contains a sort area, hash area, session cursor cache, etc.
3. How would you generating an EXPLAIN plan?
It is pre execution plan .If you do an EXPLAIN PLAN , Oracle will analyze the statment and fill a special table with the Execution plan for that statement. If you omit the INTO TABLE_NAME clause, Oracle fills a table named PLAN _TABLE by default.
Usage:explain plan into table_name for your-precious-sql-statement;
The plan table is the table that Oracle fills when you have it explain an execution plan for an SQL statement. You must make sure such a plan table exists. Oracle ships with the script UTLXPLAN.SQL which creates this table, named PLAN _TABLE (which is the default name used by EXPLAIN PLAN ). If you like, however, you can choose any other name for the plan table, as long as you have been granted insert on it and it has all the fields as here.
4. Can you enable trace for a session?
Yes. We can enable SQL trace for a session using “ALTER SESSION SET sql_trace=TRUE ”. But it is not advisable as it is a performance issue. It has to be used only when you want to trace a session to monitor performance related issues and then stop it.
5. What is the parameter to set the user trace enabling?
Sql_trace = true
6. How to create a trace file?
Set sql_trace=true
7. When 100 users connect to database,Hw u see which statement is taking long time and which statement is doing physical reading in Performance Tuning?
By using explain plan or TKPROF
8. What is Explain Plan? When do we take it?
Explain plan tells us how the query is being executed, whether it is using index or not if so what kind of index, how many loops are being used, what is the cost of each line in the SQL query, total cost involved, estimated rows returned, estimated KB returned, types of joins used and stuff like that.
9. What is the cache hit ratio, what impact does it have on performance of anOracle database?For the buffer cache hit ratio, it calculates how often a requested block has been found in the buffer cache without requiring disk access. This ratio is computed using data selected from the dynamic performance view V$SYSSTAT. The buffer cache hit ratio can be used to verify the physical I/O as predicted by V$DB_CACHE_ADVICE.
10. How does u improve the performance of Report program?There are having in so many ways.
1) You can use the sort after Declare the buffering in Read statement.
2) You don't using inner joines You can use for all entries.
3) Maintain the Work area.
4) Maintain the Variables
1) You can use the sort after Declare the buffering in Read statement.
2) You don't using inner joines You can use for all entries.
3) Maintain the Work area.
4) Maintain the Variables
11. What is the use of tkprof and how to generate it?Tkprof is one of the most useful utilities available to DBAs for diagnosing performance issues. It essentially formats a trace file into a more readable format for performance analysis. The DBA can then identify and resolve performance issues such as poor SQL, indexing, and execution plan.
12. What would you do to increasing the buffer cache hit ratio?
If the hit ratio is below 90%, and the dictionary cache has been tuned, increase the init.ora parameter DB_CACHE_SIZE to increase the buffer.
If the hit ratio is below 90%, and the dictionary cache has been tuned, increase the init.ora parameter DB_CACHE_SIZE to increase the buffer.
13. What is hit ratio?
It is a measure of well the data cache buffer is handling requests for data. It is a percentage of available and Non-available of data blocks in any memory component to increase performance.
It is a measure of well the data cache buffer is handling requests for data. It is a percentage of available and Non-available of data blocks in any memory component to increase performance.
14. What are hints in Oracle ? HINTS are nothing but the comments used in a SQL statement to pass instructions to the Oracle optimizer.The optimizer uses these hints to an execution plan for the statement.
Database Tuning
1. How do you disable monitoring of a table?
Alter table tablename no monitoring
2. How do you enable monitoring of a table?
Alter table tablename monitoring
3. How do you find the files whose are more than 500k?
fnd . -name "*" -size +500k
fnd . -name "*" -size +500k
4. What is a Parallel Server option in ORACLE ?
5. What does
Oracle10g offers more automatic mechanisms for rudimentary SQL tuning. The AWR tables allow Oracle10g to collect and maintain detailed SQL execution statistics, and this stored data is then used by the Advanced Database Diagnostic Monitor (ADDM, pronounced ‘adam’). ADDM attempts to supply a root cause analysis along with recommendations on what to do to fix the problem. An ADDM output might contain information that there is read/write contention, a free list problem, or the need to use locally managed tablespaces.
1. Excessive I/O
2. CPU Bottlenecks
3.Contention Issues
4. High Parsing
5.Lock Contention
6.Buffer Sizing Issues
7. RAC Tuning Issues
Creating a new snapshot with information populated in dba_hist_snapshot:
exec dbms_workload_repository.create_snapshot(); The addm_rpt.sql script can be used to view the output of the snapshot.
6. What is PGA ?
APGA is a memory region that contains data and control information for a server process. It is nonshared memory created by Oracle Database when a server process is started. Access to the PGA is exclusive to the server process. There is one PGA for each server process. Background processes also allocate their own PGA s. The total memory used by all individual PGA s is known as the total instance PGA memory, and the collection of individual PGA s is referred to as the total instance PGA, or just instance PGA.
A
7. Daily routine of dba
(1). Day running of theORACLE database, log files, backup situation, the database space usage, the use of system resources to inspect, identify and solve problems.
(2). Every space on the expansion of the database objects, data growth monitoring, health checks done on the database, the state of the database objects for check-ups.
(3). Monthly tables and indexes, etc. Analyze, check the list of space debris, looking for opportunities forperformance tuning the database, the database performance tuning, space management plan proposed by the next step.ORACLE database on the state to conduct a comprehensive inspection.
Daily work
(1). Day running of the
(2). Every space on the expansion of the database objects, data growth monitoring, health checks done on the database, the state of the database objects for check-ups.
(3). Monthly tables and indexes, etc. Analyze, check the list of space debris, looking for opportunities forperformance tuning the database, the database performance tuning, space management plan proposed by the next step.
Daily work
(1). Make sure all the INSTANCE state normal landing to all databases or routine testing ORACLE background process:
$ Ps-ef | grep ora
(2). Check the file system using the (free space). If the file system free space is less than 20%, need to delete unused files to free space. $ Df-k
(3). Check the log files and trace files record alert and trace files for errors.
$ Ps-ef | grep ora
(2). Check the file system using the (free space). If the file system free space is less than 20%, need to delete unused files to free space. $ Df-k
(3). Check the log files and trace files record alert and trace files for errors.
(4). Check the validity of the database using rman utility .
(5). Check the state of the record data file is not "online" data file and do recovery.
Select file_name from dba_data_files where status = 'OFFLINE'
(6). Monitor database performance running bstat / estat report generation system or use statspack to collect statistical data
Select file_name from dba_data_files where status = 'OFFLINE'
(6). Monitor database performance running bstat / estat report generation system or use statspack to collect statistical data
(7). Inspection database performance, records database, cpu use, IO, buffer hit ratio, etc. to use vmstat, iostat, glance, top, etc. command
20. Memory Tuning
1. Who is using which UNDO segment?
Execute the following query to determine who is using a particular UNDO or Rollback Segment:
Execute the following query to determine who is using a particular UNDO or Rollback Segment:
SQL> SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial,
NVL(s.username, 'None') orauser,s.program,r.name undoseg,t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo" FROM sys.v_$rollname r,sys.v_$session s,sys.v_$transaction t, sys.v_$parameter x
WHERE s.taddr = t.addr
AND r.usn = t.xidusn(+)
AND x.name = 'db_block_size'
SID_SERIAL ORAUSER PROGRAM UNDOSEG Undo
---------- ---------- ------------------------------ --------------- -------
260,7 SCOTT sqlplus@localhost.localdomain _SYSSMU4$ 8K
(TNS V1-V3)
2. Where can one find the high water mark for a table?
There is no single system table which contains the high water mark (HWM) for a table. A table's HWM can be calculated using the results from the following SQL statements:
SELECT BLOCKS FROM DBA_SEGMENTS
WHERE OWNER=UPPER(owner) AND SEGMENT_NAME = UPPER(table);
ANALYZE TABLE owner.table ESTIMATE STATISTICS;
SELECT EMPTY_BLOCKS
FROM DBA_TABLES
WHERE OWNER=UPPER(owner) AND TABLE_NAME = UPPER(table);
Thus, the tables' HWM = (query result 1) - (query result 2) - 1
3. Define the SGA?
System Global Area.It consists of Shared pool, Large pool, Java pool, Buffer cache, Log buffer, Nonstandard block size buffer caches, Keep and recycle buffer caches, and Streams pool.
System Global Area.It consists of Shared pool, Large pool, Java pool, Buffer cache, Log buffer, Nonstandard block size buffer caches, Keep and recycle buffer caches, and Streams pool.
4. You have 4 instances running on the same UNIX box. How can you determine which shared memory and semaphores are associated with which instance?Ipcs
SQL> oradebug setmypid
SQL> oradebug ipc
SQL>oradebug tracfile_name
5. When looking at v$sysstat you see that sorts (disk) is high. Is this bad or good? If bad -How do you correct it? If you get excessive disk sorts this is bad. This indicates you need to tune the sort area parameters in the initialization files. The major sort are parameter is the SQL> oradebug setmypid
SQL> oradebug ipc
SQL>oradebug tracfile_name
6. What are SGA_TARGE T and SGA_MAX?SGA_Target is the amount SGA that is used by an instance. If this parameter is set in initialization parameter file then ASMM (Automatic shared memory management) is done where the buffer cache, Stream pool, Java pool size, Shared pool size and large pool are managed by Oracle .
SGA_MAX is the Maximum possible size of SGA allowed when you enable ASMM. SGA_MAX cannot be changed dynamically. If you raise the SGA_target to more that SGA_MAX you will get error.
7. What is different initialization parameters related to tuning?
Some of the parameters that effect performance are DB_CACH E_SIZE, SGA_MAX,PGA _AGGREGATE _TARG ET, SHARED_POOL _SIZE, and SGA_TARGET when you use ASMM.
8. Name the parts of the database buffer cache.The database buffer cache consists of the keep buffer cache, recycle buffer cache, and the default buffer cache.The keep buffer cache retains the data block in memory.The recycle buffer cache removes the buffers from memory when it’s not needed.The default buffer cache contains the blocks that are not assigned to the other pools
9. Which memory structures are shared? Name two.The library cache contains the shared SQL areas, private SQL areas, PL/SQL procedures, and packages, and control structures. The large pool is an optional area in the SGA.
10. What is the maximum number of database writer processes allowed in anOracle instance?
The maximum is 20. Every Oracle instance begins with only one database writer process, DBW0. Additional writer processes may be started by setting the initialization parameter DB_WRITER_PROCESSES.
Network Tuning
1. What is Parallel Server?
Multiple instances accessing the same database (Only In Multi-CPU environments)
2. Describe a parallel server configuration.
2. Describe a parallel server configuration.
In a parallel server configuration multiple instances known as nodes can mount one database. In other words, the parallel server option lets you mount the same database for multiple instances. In a multithreaded configuration, one shared server process takes requests from multiple user processes.
3. If you want to configure shared servers which three parameters you need to specify in init.ora file?
LOCAL_LISTENER, SHARED_SERVERS ,DISPATCHERS.
4. What is the function of Dispatcher (Dnnn) ?
Dispatcher (Dnnn) process is responsible for routing requests from connected user processes to available shared server processes and returning the responses back to the appropriate user processes
5. How many Dispatcher Processes are created ?At least one Dispatcher process is created for every communication protocol in use.
6. View to see how many dispatchers are created by dba in database? V$dispatchers
7. What are the Disadvantages of dedicated servers?
While using a dedicated server is ideal in many situations, there are also some disadvantages for those who choose this option.One of the biggest disadvantages to using a dedicated server is the cost. While most hosting packages that make use of shared servers are relatively inexpensive, purchasing a hosting package that is not shared is very costly.For users that are not the most technically savvy webmasters, sometimes a dedicated server can be more than they can handle. Another disadvantage to dedicated hosting is the lack of free scripts and other additional features that those on a shared server have access to. Most web hosts offer these preinstalled on their shared hosting packages, but leave them off the dedicated servers.
While using a dedicated server is ideal in many situations, there are also some disadvantages for those who choose this option.One of the biggest disadvantages to using a dedicated server is the cost. While most hosting packages that make use of shared servers are relatively inexpensive, purchasing a hosting package that is not shared is very costly.For users that are not the most technically savvy webmasters, sometimes a dedicated server can be more than they can handle. Another disadvantage to dedicated hosting is the lack of free scripts and other additional features that those on a shared server have access to. Most web hosts offer these preinstalled on their shared hosting packages, but leave them off the dedicated servers.
8. Disadvantages of shared servers?
1. Security issues:
2. Limited Resources:
3. Dynamic IP:
4. Not good For Large Data Base E-Commerce Sites:
9. Advantages of shared servers?
1. Cheap Cost or Affordable Price:
2. No Maintenance Cost:
3. Fast Setup:
4. Good For Small Sites:
10. What is a dispatcher?
D
ISPATCHER configures dispatcher processes in the shared server architecture. The parsing software supports a name-value syntax to enable the specification of attributes in a position-independent, case-insensitive manner.
Installation
1. What is SHMMAX in /etc/system file of Solaris? Set shmsys:seminfo_shmmax=4294967295
2. What happens when root.sh is run? Permissions of oracle home dir is changed.
3. Can one user install different oracle software versions like 8i,9i,10g?Yes
4. It possible to install different oracle software on one operating system?Yes
5. Can root user install Oracle S/W?No
6. Can you create multiple databases using one operating system login id?
Yes
Yes
7. What is difference between 32-bit and 64-bit O/S?
The terms 32-bit and 64-bit refer to the way a computer's processor (also called a CPU), handles information. The 64-bit version of Windows handles large amounts of random access memory (RAM) more effectively than a 32-bit system.
8. Compatible parameter using for oracle version .it is giving in parameter file?
This parameter allows you to use a new release, while at the same time guaranteeing backward compatibility with an earlier release (in case it becomes necessary to revert to the earlier release).it is given in pfile.
9. How do you kill a session from the database?
Alter system kill 'sid,serial#'
Usage : alter system kill '9,8' (Get the info from v$session)
10. In which file oracle inventory information is available?
/etc/oraInst.loc
11. Where do you configure your hostname in linux?
vi /etc/hosts
12. How do you check the Solaris Server configuration like amount of physical memory, number of CPUs?
Prtdiag
13. If our $O_H filled up with core files,what actions we will take?
Remove all old core files and create space in the filesystem.
14. Where do you get to know the version of your oracle software and what is your version?
From v$version(field is banner) and the version is 9.2.0.1.0
15. Tell me the location of Unix/Solaris log messages stored?
/var/log/messages(Unix)
/var/adm/messages (solaris)
16. Where all oracle homes and oracle sid information available?
/etc/oratab
17. What are Sémaphores?
A semaphore is a value in a designated place in operating system (or kernel) storage that each process can check and then change. Depending on the value that is found, the process can use the resource or will find that it is already in use and must wait for some period before trying again. Semaphones can be binary (0 or 1) or counting.
Oracle 11g New features
1. What is Assm?
Automatic segment space management (ASSM) is a simpler and more efficient way of managing space within a segment. It completely eliminates any need to specify and tune the pctused, freelists, and freelist groups’ storage parameters for schema objects created in the tablespace. If any of these attributes are specified, they are ignored.
2. What is Asmm?
ASMM (Automatic Shared Memory Management) is the collective name for the dynamic memory allocation technologies added in Oracle 9i and improved with each subsequent release. This reduces the amount of manual configuration required and allows the database to adapt to workload changes.
3. What is the use in memory_target parameter?
MEMORY_TARGET provides the following:
1. A single parameter for total SGA and PGA sizes
2. Automatically sizes SGA components and PGA
3. Memory is transferred to where most needed
4. Uses workload information
5. Uses internal advisory predictions
6. Can be enable by DBCA at the time of Database creation.
4. What is the use of Mman?
Mman stands for Memory Manager; it is a background process that manages the dynamic resizing of SGA memory areas as the workload increases or decreases. This process was introduced in Oracle 10g.
5. Oracle 11g new features?
1. Improved data compression ratios (up to 20x).
2. Ability to upgrade database applications while users remain online.
3. New ease-of-use features that make Grid computing more accessible.
4. Automation of key systems management activities
.
6. What is AWR and ADDM?
AWR (Automatic Workload Repository) is a built-in repository (in the sysaux tablespace) that exists in everyOracle Database. At regular intervals, the Oracle Database makes a snapshot of all of its vital statistics and workload information and stores them in the AWR.
7. What is proactive tablespace management system?
The Proactive Tablespace Management (PTM) capability in the Oracle Database 10g brings efficient and powerful space monitoring, notification and space trending to the Oracle Database. Prior to Oracle Database 10g, the tools available for monitoring and setting up notifications regularly polled the database to monitor its space usage. Querying space usage information requires collecting data about the state of the database — state that is constantly changing in a production system. Because such queries are inherently expensive, the space monitoring tools typically run them infrequently, once a day or once every couple of hours. When they are run, the queries steal CPU, IO and memory (especially the buffer cache) resources away from critical business activity in the production system. It’s a health check that is either late or hurts the health of the system or worse, both!
8. What are the advantages of AWR?
Advantages of the new workload repository include:
1. AWR is a record of all database in-memory statistics historically stored. In the past, historical data could be obtained manually using the ‘statspack’ utility. AWR automatically collects more precise and granular information than past methods.
2. With a larger data sample, more informed decisions could be made. The self-tuning mechanism uses this information for trend analysis.
3. Another benefit is that AWR statistics are accessible to external users, who can build their own performance monitoring tools, routines, and scripts.
4. Awr collects database performance identical values from different layers like
1. C.P.U resources utilization
2. Memory utilization
3. Timing statistics
4. Typical executed Queries Latches statistics etc.
Data Guard
1. How do you findout the most recent log applied to physical standby?
SELECT SEQUENCE#, APPLIED FROM V$ARCH IVED_LOG;
2. Where do you look for Oracle alert log?
/disk2/oradata/prod/diag/rdbms/db_name/instance_name/trace ls alert_instance_name.log
3. What happens if you add a datafile in primary and there is no space on contingency server and how do you resolve it?
Increase disk space
4. How do you add a datafile to a tablespace in a database with physical standby setup? By setting standby_file_management=auto, if a tablespace is created on primary it will be automatically created on standby.
5. How do you troubleshoot if you suspect any problems with archive log shipping to standby database? Check for network configuration and standby archive location specified in primary init.ora
6. How do you open a physical standby database in managed recovery mode in 'READ-ONLY' mode?
Cancel the MRM , and then open the database using sql statement ‘alter database open;’
7. What is the difference between Physical and logical stand by databases?
1. Physical standby can be opened in read only mode, logical standby can be opened in read write mode
2. Redo apply will take place in physical standby and sql apply will be in logical standby
3. MRP process will work for redo apply,lsp process for sql apply
8. What is the state of stand by database; is it open?
Physical standby in read only
Logical standby in read write
9. Can we create logical standby from physical standby?
Yes.
10. What is an oracle stream?
Streams are a 10g feature. It is using for replicating data between two databases
11. How to create a logical standby?
Standby>>alter database recover managed standby database cancel;
Execute dbms_logstdby.build;
Alter database recover to logical standby new_db_name;
Shut immediate
Startup mount
Alter database open resetlogs;
Alter database start logical standby apply immediate;
12. What is Database maintain plan and Disaster Recovery plan?
Disaster recovery plans start with risk assessment. You need to identify all the risks that your data center faces and then determine the business impact should that risk become an event. For instance, typical risks that are considered are: loss of a disk drive, loss of a server, complete loss of your data center, etc.
There are many ways to handle your disasters. You'll have to look at the risks you face. Each risk should have a solution. In some cases, multiple risks have the same solution.
In order to be able to transport tablespaces between platforms, you must meet two criteria. One, you must be running Oracle 10g on both platforms. Two, both platforms must have the same "endianess" or you'll need to perform a conversion step. The endianness refers to the byte ordering on that platform. Solaris is big endian. Windows is little endian. If both platforms were big endian or both were little endian, then you would have no difficulties transporting the tablespaces.
13. What is the state of standby database, is it open?
Physical Standby database is in "Recovery Mode" in order to apply received archive logs from primary database. We can make it to "Read Only" mode to make it available for application users for reporting purposes. Once the database is applied in "Read Only" mode we cannot apply redo logs received from primary database(oracle 10g).
From 11g we can put the database in MRM mode even in open mode ---active standby
Logical standby database is logically identical to the primary database. Logical standby can be in open state and at the same time its tables are updated from primary database.
14. How do you configure a stand by database?
Steps to create standby database:
- Enable forced logging
- Create password file if it does not exist.
- Set primary database initialization parameters
- Enable archiving if it is not.
- Create a backup copy of primary database datafiles.
- Create standby control file.
- Prepare the initialization parameters for Standby database.
- Copy all the files to standby database – datafiles, control files, init.ora files
- Configure Listener on standby.
- Enable broken connection detection.
- Create
Oracle net service names. - Create Spfile for standby database.
- Start standby database in read only mode.
- Start redo apply when necessary.
- Verify standby is working properly.
15. Give me the syntax to create a standby controlfile using RMAN?
Alter database create standby controlfile as ‘/disk2/oradata/prod/dg/stand.ctl’;
16. How do you perform the switch over from primary to standby and standby to primary?
Prod>>select switchover_status from v$database; To_standby
Prod>>alter database commit to switchover to standby;
Standby>>alter database commit to switchover to primary;
Standby>>alter database open;
17. Give me the Top 5 init parameters which need to be setup in the target db in the physical standby config?
Fal_server=to_stand #tns service of prod
Fal_client=to_prod #tns service of standby
Log_archive_dest_1=’location=/disk2/oradata/prod/arch’ #archive log dest of production
Log_archive_dest_1=’service=to_stand lgwr sync affirm reopen=10’
Standby_file_management=auto
Standby_archive_dest=/disk2/oradata/prod/arch
18. What is Failover and Switchover?
Switchover: The switchover feature provides you with the ability to switch the role of the primary database to one of the available standby databases. The chosen standby database becomes the primary database, and the original primary database then becomes a standby database.
Failover: You invoke a failover operation when a catastrophic failure occurs on the primary database and there is no possibility of recovering the primary database in a timely manner. During a failover operation, the failed primary database is removed from the Data Guard environment, and a standby database assumes the primary database role. You invoke the failover operation on the standby database that you want to fail over to the primary role.
19. Can you create clone database using HOT backup?
Yes
20. Without password file can we configure standby database?
No
No comments:
Post a Comment