RAM&KRISHNA BLOG

oracle@. oraenv     >   set env to the specific dB ORACLE_SID = [TEST] ? oracle@/home/oracle/db/scripts: rman target / Recovery Manager: Release 10.2.0.5.0 - Production on Tue Jan 27 19:35:06 2015 Copyright (c) 1982, 2007, Oracle.  All rights reserved. connected to target database: TEST (DBID=2909445435) RMAN> list archivelog all; allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=145 devtype=DISK 6692    1    4137    A 21-JAN-15 /backup/TEST/arch/1_4137_860869935.dbf 6693    1    4138    A 21-JAN-15 /backup/TEST/arch/1_4138_860869935.dbf 6694    1    4139    A 21-JAN-15 /backup/TEST/arch/1_4139_860869935.dbf 6695    1    4140    A 21-JAN-15 /backup/TEST/arch/1_4140_860869935.dbf 6696    1    4141    A 21-JAN-15 /backup/TEST/arch/1_4141_860869935.dbf 6697    1    4142    A 21-JAN-15 /backup/TEST/arch/1_4142_860869935.dbf 6698    1    4143    A 21-JAN-15 /backup/TEST/arch/1_4143_860869935.dbf 6699    1    4144    A 21-JAN-15 /backup/TEST/arch/1_4144_

------------------------------------------------------------------------------------------------------ col USERNAME format a10; col MACHINE format a40; col OSUSER format a10; col PROCESS format a10; set linesize 200; select SID,serial#,username,MACHINE,OSUSER,process from v$session where username='DBSNMP' and status='INACTIVE'; select SID,serial#,username,MACHINE,OSUSER,process from v$session where username='DBSNMP' and status='ACTIVE'; ------------------------------------------------------------------------------------------------------ col sid format 999999 col username format a20 col osuser format a15 select b.spid,a.sid, a.serial#,a.username, a.osuser from v$session a, v$process b where a.paddr= b.addr and b.spid='&spid' order by b.spid; ------------------------------------------------------------------------------------------------------ select p.spid,s.action,s.sid,s. serial#,s.username,s.s

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ * USE :  _DATE - Contains the current date or a user defined fixed string. Please see below Example: SQL> spool &schema._cc_update_&_Date.txt Enter value for schema: TEST SQL> select name from v$database; NAME --------- XE SQL> spool off; Log File Genarated > TEST_cc_update_23-JAN-15.txt ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ - ANOTHER WAY - EXAMPLE: SQL> col dcol new_value mydate noprint SQL> select to_char(sysdate,'YYYY-MM-DD') dcol from dual; SQL> spool &mydate._MY_SPOOL_FILENAME SQL> select name from v$database; NAME --------- XE SQL> spool off; Log File Genarated: 2015-01-23_MY_SPOOL_FILENAME ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Question 1. Explain the difference between a hot backup and a cold backup and the benefits associated with each.?? Answer: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. Question 2. You have just had to restore from backup and do not have any control files. How would you go about bringing up this database?

The Oracle Real Application Clusters (RAC) option was first released as part of Oracle 9.0.1 in the summer of 2001. It is considered by many within the Oracle community to be the most significant feature introduced in Oracle 9 i . Oracle Real Application clusters allows multiple instances to access a single data base, the instances will be running on multiple nodes. In an standard Oracle configuration a database can only be mounted by one instance but in a RAC environment many instances can access a single database. The table below describes the difference of a standard oracle database (single instance) an a RAC environment Component Single Instance Environment RAC Environment SGA Instance has its own SGA Each instance has its own SGA Background processes Instance has its own set of background processes Each instance has its own set of background processes Datafiles Accessed by  only  one instance

Have received the below error while doing schema level refresh. ======================================================================== Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT ORA-39083: Object type REF_CONSTRAINT failed to create with error: ORA-04020: deadlock detected while trying to lock object TEST.CLIENT Failing sql is: ALTER TABLE "TEST"."TRANSACTION" ADD CONSTRAINT "TRANSACTION_FK" FOREIGN KEY ("LAST_UPDATED_BY_CLIENT_ID") REFERENCES "TEST"."CLIENT" ("CLIENT_ID") ENABLE; ======================================================================== First check for locked objects in the database. using the below queries. SQL> sqlplus / as sysdba ------------------------------------------------------------------------- SQL> select * from v$locked_object; no rows selected -------------

Cause: A host language program attempted to open too many cursors. The initialization parameter OPEN_CURSORS determines the maximum number of cursors per user. Action: Modify the program to use fewer cursors. If this error occurs often, shut down Oracle, increase the value of OPEN_CURSORS, and then restart Oracle. Question: I keep encountering ORA-01000 and can't even create a table Answer: First, you should try increasing your OPEN_CURSORS and take a look at the application to see if/why cursors are staying open.  Here is an example from a forum: ALTER SYSTEM SET open_cursors = 400 SCOPE=BOTH; Furthermore, to resolve ORA-01000, try to close whatever cursors are no longer in use, raise the OPEN_CURSORS parameter within your initialization file, and restart Oracle. Resolving ORA-01000 try setting this OPEN_CURSORS to a higher number.  Keep in mind when resolving ORA-01000 in this way that changing this value in the proper way should not alter the system performance