image

How to know the size of oracle database?

Oracle database consist of datafiles,redo log files, control files and temp files. And sum of all these file is the size of the oracle database. i.e   select ( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) + ( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) + ( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) + ( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 … continue reading

image

what is consistency =y in export ?

It makes all of the queries run by exp be “as of the same point in time– consistent with regards to eachother”Imagine if you started an export at 9am of the EMP and DEPT tables.EMP started exporting at 9am and DEPT at 9:15am.Now, the EMP data would be “as of 9am”, but the DEPT data … continue reading

image

How to know log sequence number/SCN of Archive log file for restoration purpose

Possible Solutions: select * from v$log; select sequence# from v$archived_log; RMAN>RESTORE DATABASE PREVIEW  

image

Recovery – Loss of all control files (no catalog)

SCENARIO – LOSS OF ALL CONTROLFILES (NO CATALOG) SQL> insert into myobjects select * from myobjects; 919664 rows created. SQL> commit; Commit complete. SQL> select count(*) from myobjects; COUNT(*) ———- 1839328 >>>> need to check this record count after recovery SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST … continue reading

image

Incomplete Recovery (until log sequence)

Point in time recovery using RMAN (until a log sequence number) Recovery Objective SQL> conn scott/tiger Connected. SQL> select count(*) from myobjects; COUNT(*) ———- 249410 Switch a logfile SQL> conn / as sysdba Connected. SQL> alter system switch logfile; System altered. Note the current log sequence number (13) SQL> archive log list Database log mode … continue reading

image

Incomplete Recovery (until point in time)

POINT IN TIME RECOVERY via RMAN Scenario: DBA dropped the tablespace TEST which is important for application. Requirement: We need to restore the database before the TEST tablespace was dropped. Solution: SQL> drop tablespace test including contents and datafiles; Tablespace dropped. DBA realized the mistake; He will refer alert log for the exact timing when … continue reading

image

Loss Of All Files – Disaster Recovery

RECOVERY SCENARIO: Complete loss of all database files including SPFILE using RMAN Database Details —————— Database Name=OPSDBA Machine Name=ITLINUXDEVBLADE07 DBID=1499754868 (select dbid from v$database) SIMULATING CRASH ———————— opsdba:/u01/ORACLE/opsdba>ls -l total 1948980 drwxr-x— 2 oracle dba 4096 Feb 12 13:35 arch -rw-r—– 1 oracle dba 7389184 Feb 12 13:57 control01.ctl -rw-r—– 1 oracle dba 7389184 Feb … continue reading

image

Oracle Real time Interview Questions with Answer

1) How can you see the Current SCN number of the database? > Select current_scn from v$database; 2) How can you see the Current log sequence number the logwriter is writing in to? > Select * from v$log; 3) If you are given a database, how will you know how many datafiles each tablespace contain? … continue reading

image

The flashback recovery area has run out of space …under different scenarios

The flashback recovery area has run out of space what different solution to overcome that issue. as we see some expert solution to this question lets add some scenarios on that,now i know how i increase-up the FRA say db_recovery_file_dest_size = 10G what happened when we reduced it to db_recovery_file_dest_size = 5G……? The flash recovery … continue reading

image

What is the difference between ‘archived’ and ‘applied’ on v$archived_log view ?

Archived means whether the archive is generated or not on primary and  applied means whether the archive applied on standby or not. Enjoy.  

Visit Us On FacebookVisit Us On Google PlusVisit Us On Youtube