xxxxxxxxxxxxxxxxxxx
Tell me some situations are when database get hang. How you find the problem and solve them.
1. When your online redo log files are missed
2. If all online redo log files are in ACTIVE state
3. If it is a resumable enabled
4. If DDL_WAIT_LOCK_TIME is set
5. If your standby database
is in MRM(Managed Recovery
Mode) in 9i
1. Temp tablespace is full and sorting requires temp tablespace.
2. Undo tablespace is full and some undo is getting generated to be stored in undo datafiles.
3. Database is in archivelog mode and redo is transferring the log member data to archive and yet not finished the transfer. At the same time redo buffers are generated too much and that requires to write in redo log which is still in the process of transferring the log to archive.
4. When long running scripts are running, which can be queried using v$session_longo
Xxxxxxxxxxxxxxxxxxxxxxxxxxxx
What is the crucial situation you got in last two years of carrier while handling production database?
We face the problem in Database in Production environment.
We have two database
1) Primary Database
2) Secondary Database
The Secondary Database which was Physical Standby Database.
We create a new tablespace in Primary Database, for that we create folder for Database file in Disk but we forget to create same folder in Secondary Database(Physical Standby Database). With Result our logfile move to the Secondary (Standby)Database but never apply to the database(Secondary).
It is mandatory for Standby Database is folder structure in terms of Database should be same in Primary & Secondary Database.
we can transfer physical files from producation database
to standby database through ftp.it is do manually.if any matheod to do this automatically?
Yes,
By using rsync we can transfer the files from Production to standby server.
"rsync -av filename stbyserver_location" and schedule this in cronjob
xxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxx
what are the 10g new features in oracle DBA?
Some of the new features in Oracle 10g for DBAs are:
1. Oracle 10g Utility : Data Pump
2. Automatic Shared Memory Management
3. Automatic Workload Repository
4. Tuning Advisors
5. Automatic Database Diagnostic Monitor
6. SQL Tuning Advisor
7. Server
generated alerts
8. Automatic Optimizer Statistics
9. OEM Enhancements
10. RBO desupported
SysAux Tablespace
Default tablespace for whole DB
Bigfile tablespace
xxxxxxxxxxxxxxxxxxxxxxxxxx
what is diff between mount and nomount stage of a databse?
startup belongs three mode,such as:
Nomount:
> In nomount stage instance has been started using the initialization parameter file plus and background process.
Mount:
> In mount stage oracle read controlfile and it's contents. follow up the controlfile oracle database goes to startup
ii
Three modes of startup of a database.
1. Nomount - Reads init parameter. Memory[SGA] and background processes are started. On unix prompt do ps -ef | grep pmon to see process monitor started, one of the background process in Oracle.
2. Mount - Reads control file. Location of datafile, redolog file is understood to Oracle.
3. Open - Opens the datafiles and Redolog files for read and write operations.
Xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
how to see alert log file for errors?
goto altert lig file address and open yext format alert log file(bdump file have alert log file).
Ii
goto altert lig file address and open yext format alert log file (bdump folder have alert log file).
Iii
Background dump destination [bdump] has alert_SID.log file.
To see background dump destination do this:
SQL> show parameter backg;
NAME TYPE VALUE
-------------------- ----------- ------------------------------
background_dump_dest string C:\ORACLE\PRODUCT\10.1.0\ADMIN
\ORCL\BDUMP
Go to BDUMP directory and do ls -ltr alert_SID.log on unix prompt.
do tail -f to see the latest information in the alertlog.
Xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
What are the real time issues faced by DBA
S?
The Real Time issue faced by DBA is
1. Performance
issue
2. Space Problem
3. Access of Object
4. Cloning of database
5. Export of Database
Ii
1. Resize Datafiles
2. backup
3. Upgrade
4. Clone / Duplicate etc
Xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
What are the New features of 11g?
1)flashback archivelog has been introduces in 11g
2)bdump,cdump,udump replaced with diagnostic_dest="location"
3)snapshot standby database introduced in 11g
4)we can make the indexes invisible from 11g.(optimizer will not use these invisible indesxes)
5)11g rman enhancements------data recovery adviser,proactive health check,zlib compression,parallel backup of a file,virtual private catalog
we have some more...............
Xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
if my database
size 100gb what is the size of sga?
it depends on the no users are connecting to database;
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
my database
is p and running...i lost my control file.... after that what will happen if i give the command like " alter system backup control file to trace" it will work or not..........?
ORA-00210: cannot open the specified control file
Ii
If the control file is not there, it will give the error as it is not identifying any control file. The command will not work. If you have executed the command alter database backup controlfile to trace; then you can recreate control file from this file.
Iii
alter database backup controlfile to trace; If you have been executed the command before the loss of control file, at that time you can recreate the control file from this file which will be stored in the trace file.
Xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
what is the meaning of roll forward and roll backward?
Rollforward is related to REDO and Roll backward is related to UNDO.
Roll forward means when instance is failed, Datas will be retrieved from Online redo log files, Roll backward means datas will be retrieved from undo TS
Xxxxxxxxxxxxxxxxxxxxxxxxxxx
what are the types of logs?
The physical architecture
comprises of two type of log file
1) Redo Log files
2) Archive Log file(if archive mode is enabled)
Besides there are many logs and trace files which located in the ORACLE_HOME through which a DBA
can asses the status of an database
(single instance or RAC node). The main log is the alert_SID.log located in the ORACLE_HOME/bdump folder which stores useful information regarding checkpoints,startup and shutdown etc.
Xxxxxxxxxxxxxxxxxxxxxxxxxxx
What are the different high availability solutions provided by Oracle?
• Oracle Real Application Clusters
• Oracle Clusterware
• Oracle Data Guard
• Oracle GoldenGate
• Oracle Streams
• Oracle Secure Backup
• Recovery Manager (RMAN)
• Flashback Technologies
• Oracle VM
• Oracle Cloud Computing
• Automatic Storage Management (ASM)
• Cross-Platform Transportable Tablespace
• Online Reorganization
Xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
i need cold backup script in linux?
Script about cold backup
1: Shutdown the database gracefully(normal, transactional, immediate).
2: Make one back destination directory.
3: Copy all the files to destination directory.
4: Startup
Ii
Go to rman prompt.
RMAN> connect target /
Then write a small piece of code as below.
RMAN> run
( allocate channel ch1 type disk format '%t_%s_%r';
backup database;
alter database open;
)
Xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
how identify that the perticular table has been exported or not in its lifetime.
Export Information gets updated in the following sys tables
SYS.INCEXP SYS.INCFIL SYS.INCVID
desc SYS.INCVID
EXPID
desc SYS.INCEXP
OWNER#
NAME
TYPE#
CTIME
ITIME
EXPID
desc SYS.INCFIL
EXPID
EXPTYPE
EXPFILE
EXPDATE
EXPUSER
by quering these system table you can find out
Xxxxxxxxxxxxxxxxxxxxx
I created a script in RMAN for incremental level 1 backup. But i forgot that script name. Now i wants to execute that script. How can i find that script name that is created by me? Which view i can use?
RMAN>LIST SCRIPT NAMES
Xxxxxxxxxxxxxxxxxxxxxxxx
What is the difference between whole database backup and full database backup?
Well whole database backup includes backing up the datafile,the archive files(if archiving is enabled), control file also including the parameter file and the password file.
Whereas in a full backup only the total backup of the datafiles is taken.
Xxxxxxxxxxxxxxxxxxxxxxx
How do you set up your tablespace on installation?
On Oracle Installation there is no need to setup/create the tablespace. At the time of Database Creation we need to setup/create the Tablespace.
Xxxxxxxxxxxxxxxx
In RMAN can we do complete & incomplete recovery ? how ?
Yes.. We can do the following incomplete recovery in RMAN.
1.Time based
2.SCN based
3.Log Sequence based
It can be done by using the command
"set until time/scn/logseq ...." before restoring the backup.
Xxxxxxxxxxxxxxxxxxxxxxxxx
where can you get a list of all initialization parameters for your instance? How about an indication if they are default settings or have been changed.?
You can look in the init.ora file for an indication or manually set parameters. For all parameters, their value and whether or not the current value is the default value, look in the v$parameter view.
Ii
select name,value,isdefault from v$parameter order by isdefault,name;
isdefault = false shows those values has been changed from default
iii
At SQL prompt type this
SQL> SHOW PARAMETER
Xxxxxxxxxxxxxxxxxxxxx
when should you increase copy latches. What parameters control copy latches.?
when you get excessive convention for the copy latches as shown by the "redo copy" latch hit ratio. You can increase copy latches via the initialization parameter log_simultaneous_copies to twice the number of cpu's on your system.
Xxxxxxxxxxxxxxxxxxxx
when looking at v$sysstat you see that sorts(disk) is high. Is this bad or good?
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 parameter is the sort_area_size parameter.
Ii
this is bad.....you need to increase the sort area by setting the parameter sort_area_size.
Xxxxxxxxxxxxxxxxxxxx
Explain the use of TKPROF? What initialization parameter should be turned on to get full TKPROF output?
the tkprof tool is a tuning tool used to determine cpu and execution times for sql statements. You use it by first setting timed_statistics to true in the initialization file and then turning on tracing for either the entire database via the sql-trace parameter or for the session using the alter session command. Once the trace file is generated you run the tkprof tool against the trace file and then look at the output from the tkprof tool. This can also be used to generate explain plan output.
sql_trce=true;
timed_statistics=true;
xxxxxxxxxxxxxxxx
what are some indications that you need to increase the shared_pool_size parameter?
Poor data dictionary or library cache hit ratios, getting error ora-04031. Another indication is steadily decreasing performance with all other tuning parameters the same.
Xxxxxxxxxxxxxxx
a tablespace has a table with 30 extents in it. Is this bad? Why or why not.
multiple extents in and of themselves are not bad. However if you also have chained rows this can hurt performance.
Xxxxxxxxxxxxxxxx
how do you generate file output from sql.
By use of the spool command.
Xxxxxxxxxxxxxxx
what is explain plan and how is it used.
the explain_plan command is a tool to tune sql statements. To use it you must have an explain_table generated in the user you are running the explain plan for. This is created using the utlxplan.sql script. Once the explain plan table exists you run the explain plan command giving as its argument the sql statement to be explained. The explain_plan table is then queried to see the execution plan of the statement. Explain plans can also be run using tkprof.
Xxxxxxxxxxxxxxxxx
What is tkprof and how is it used.?
The tkprof tool is a tunning tool used to determine cpu and execution times for sql statements. Use it by first setting timed_statistics to true in the initialization file and then turning on tracing for either the entire database via the sql_trace parameter or for the session using the alter session command. Once the trace file is generated you run the tkprof tool against the trace file and then look at the output from the tkprof tool. This can also be used to generate explain plan output.
Ii
tkproof can give the exact statistics about how long the sql-stmt took to run(in sec) and also what was the access path it has been used in the executin........
you need to set two parameters in parameter file
1)sql_trace=true;
2)timed_statistics=true;
after setting above parameters run sql-stmt
after that goto trace file location...and select brand new trace file.
you need to run the tkproof utility on the top of this trace file....
$tkproof abcd.trc abcd.log explain=scott/tiger;
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Explain the difference between $ORACLE_HOME and $ORACLE_BASE.
ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is where the oracle products reside.
Ii
ORACLE_BASE is the root directory for the oracle products.
ORACLE_HOME is the directory
where the oracle software is located.
Xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
How would you go about generating an explain plan?
Create a plan table with utlxplan.sql. Use the explain plan set statement_id='tst1' into plan_table for a SQL statement. Look at the explain plan with utlxplp.sql or utlxpls.sql.
Xxxxxxxxxxxxxxxxxxxxxxxxxx
What command would you use to create a backup control file?
Alter database backup
control file to trace.
Xxxxxxxxxxxxxxxxxxxx
what is the pre-requirement of installing oracle 10g on linux?
1. Checking Memory and Swap Space
2. Checking /tmp Space (OUI requires 400MB)
3. Checking Software Packages (RPMs)
4. Checking Kernel Parameters
5. Sizing Disk Space
for Oracle10g
6. Creating Oracle User Accounts
7. Creating Oracle Directories
8. Setting Oracle
Environments(bash_profile)
9. Installing Oracle10g
Xxxxxxxxxxxxx
ora 600 andora 155 how can you resolve that errors?
The ORA-00600 error is a generic internal error from Oracle, and you must look-up the numeric arguments to understand the nature of the internal error.
Xxxxxxxxxxxxxx
in imports we use ignore=y what is the use of it?
ignore=y used this keyword at the time of imp job,to ignore errors
ii
ignore=y keyword is ignore error for miss match etc.
xxxxxxxxxxxxxxx
No comments:
Post a Comment