image

Location of Oracle Net Listener Parameters (listener.ora)

Solution 1 : Just type $lsnrctl status this command will show the location of your listener file.                                     OR By default, the listener.ora file is located in the $ORACLE_HOME/network/admin directory on UNIX operating systems and the %ORACLE_HOME%\network\admin directory … continue reading

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

ORA-01033 : file 1 needs media recovery

Possbile Solutions: Basically your system.dbf is corrupted / incomplete.. needs recovery1. kill all connects from OS level2. ps -ef |grep pmonans kill the database service that is running…3. Do you have any RMAN backup or archivelogs?If YES – Recover database with the archivelogsif NO – try to recover using all the redo logs..Commnad: recover database … continue reading

image

exact fetch returns more than requested number of rows

Problem : DECLARE empname VARCHAR2(200); BEGIN select ENAME into empname from emp where dept_id=20; END; / Reason : The above code returns the multiple rows , here the select into statements accepts only one row. i.e A SELECT INTO statement will throw an error if it returns anything other than 1 row. If it returns 0 rows, … 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

why we use crosscheck, obsolete , restore validate in rman?

1. why we use crosscheck command in rman ? 2. why we use obsolete command in rman ? 3. why we go for restore database validate ? Solution : .1. To check whether backups are physically available2. To check whether backups are valid (redundant)3. To check the existing backup pieces are valid to restore  

image

What is meant by incremental,cumulative and complete backups?

Full BackupsA full backup is different from a whole database backup. A full data file backup is a backup that includes every used data block in the file. RMAN copies all blocks into the backup set or image copy, skipping only data file blocks that have never been used. For a full image copy, the … continue reading

image

how to find current size, available size, used size and total size in a Tablespace .

For used size: select sum(bytes) from dba_segments where tablespace_name=”; For available size: select sum(bytes) from dba_datafiles where tablespace_name=”; select df.tablespace_name “Tablespace”, totalusedspace “Used MB”, (df.totalspace – tu.totalusedspace) “Free MB”, df.totalspace “Total MB”, round(100 * ( (df.totalspace – tu.totalusedspace)/ df.totalspace)) “Pct. Free” from (select tablespace_name, round(sum(bytes) / 1048576) TotalSpace from dba_data_files where tablespace_name like ‘%xxxxxxxxxxxxxx%’ group … continue reading

image

Why expdp is faster than regular exp?

Solutions: 1. expdp is faster due to parallelism. 2. expdp is more advanced. 3. expdp is direct path export which is Oracle Data block. Exp uses SQL level extraction. 4. Exp,imp runs on client side, but expdp and impdp runs on server side.so we have much control compared to traditional exp,imp !  

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  

Visit Us On FacebookVisit Us On Google PlusVisit Us On Youtube