Oracle Dba's Area for DBA to share thier views

Interview Questions IV

noreply@blogger.com (Jafar Ali) — Sun, 24 Aug 2008 13:05:00 +0000

Differentiate between TRUNCATE and DELETE

RUNCATE	DELETE
It is a DDL statement	It is a DML statement
It is a one way trip, cannot ROLLBACK	We can Rollback
Doesn't have selective features (where clause)	Has
Doesn't fire database triggers	Does
It requires disabling of referential constraints.	Does not require

Display the number value in Words?

SQL> select sal, (to_char(to_date(sal,'j'), 'jsp'))

from emp;

the output like,

SAL (TO_CHAR(TO_DATE(SAL,'J'),'JSP'))

--------- -----------------------------------------------------

800 eight hundred

1600 one thousand six hundred

1250 one thousand two hundred fifty

If you want to add some text like, Rs. Three Thousand only.

SQL> select sal "Salary ",

(' Rs. '|| (to_char(to_date(sal,'j'), 'Jsp'))|| ' only.'))

"Sal in Words" from emp

Salary Sal in Words

------- ------------------------------------------------------

800 Rs. Eight Hundred only.

1600 Rs. One Thousand Six Hundred only.

1250 Rs. One Thousand Two Hundred Fifty only.

How to get the 25th row of a table

select * from emp where rowid=(select max(rowid) from emp where rownum<26)

elect * from Emp where rownum < 26

minus

select * from Emp where rownum<25

Why equality condition does'nt work with rownum, i'm giving my explanation here?

the query
select * from emp where rownum=25;

it fetches first record makes it rownum 1 as it does'nt match the where criteria, the record discarded

it fetches 2nd record makes it rownum 1 again
that is why rownum=x does'nt work

How do we display the column values of a table?

DECLARE

CURSOR cr_data

SELECT ROWID, a.*

FROM fnd_user a where rownum < 10;

l_table_name VARCHAR2(2000) := 'FND_USER';

-- IMP--This table name should be same as your from table in the above cursor

l_value VARCHAR2 (2000);

l_str VARCHAR2 (2000);

CURSOR column_names (p_table_name VARCHAR2)

SELECT *

FROM all_tab_columns

WHERE table_name = p_table_name;

-- You can use order by clause here if you want.

BEGIN

FOR cr_rec IN cr_data

LOOP

-- We should pass the same Table Name

FOR cr_columc_rec IN column_names (l_table_name)

LOOP

l_str :=

'Select '

|| cr_columc_rec.column_name

|| ' from '||l_table_name||' where rowid = '||chr(39)

|| cr_rec.ROWID||chr(39);

DBMS_OUTPUT.put_line ('Query is ' || l_str);

EXECUTE IMMEDIATE l_str

INTO l_value;

DBMS_OUTPUT.put_line ( 'Column is '

|| cr_columc_rec.column_name

|| ' and Value is '

|| l_value

);

END LOOP;

END;

Tell me the difference between instead of trigger, database trigger, and schema trigger?

Instead of trigger : A view cannot be updated , so if the user tries to update a view, then this trigger can be used , where we can write the code so that the data will be updated in the table, from which the view was created. Database trigger : this trigger will be fired when a database event ( dml operation ) occurs in the database table, like insert , update or delete. System triggers : this trigger will fire for database events like dtartup / shutdown of the server, logon / logoff of the user, and server errors ... and also for the ddl events, like alter, drop, truncate etc.

What is the diff between %Rowtype and %type?

%Rowtype means associating a single variable to a entire row.(It is one way of Declaring a composite plsql datatype "RECORD")

%type means associating a single variable to a particular column in table.

both %Rowtype and %type declarations are known as Anchored Declarations in plsql .

Wat is difference between Cursor and Ref Cursor ?

· Cursor is static one and ref cursor is dynamic with return type

Example for cursor:

declare

cursor c1 is select * from emp;

begin

for r1 in c1 loop

dbms_output.put_line(r1.empno||' '||r1.ename);

end loop;

end;

Example for ref cursor

CREATE OR REPLACE package emp_data is

-- Author : RPSINGH

-- Created : 8/17/2006 12:54:03 AM

-- Purpose : displaying the data from different tables

-- Public type declarations

type my_cur is ref cursor;

-- Public constant declarations

-- Public variable declarations

-- Public function and procedure declarations

procedure tabledata(tname in varchar2, v_cur out my_cur);

end emp_data;

CREATE OR REPLACE PACKAGE BODY EMP_DATA IS

procedure tabledata(tname in varchar2,v_cur out my_cur) is

begin

OPEN V_CUR FOR

'SELECT * FROM '||TNAME;

end tabledata;

end emp_data;

Now to use this code:

declare

v_cur employees%rowtype;

C_CUR EMP_DATA.MY_CUR;

begin

emp_data.tabledata('EMPLOYEES', C_CUR);

LOOP

FETCH C_CUR INTO V_CUR;

EXIT WHEN C_CUR%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(V_CUR.FIRST_NAME||' '||V_CUR.LAST_NAME);

END LOOP;

END;

Cursor is a structure which points to a memory locations

While Ref-cursor is a data structure which point to a object which intern points to Memory locations.

Advantage of having Ref-cursor is that we can pass dynamically the Ref-cursor as a parameter to a procedure.

Can we create a table using with Procedure or Function?

DECLARE

BEGIN

EXECUTE IMMEDIATE 'create table employee(empno number(3),ename varchar2(10))';
END;

What is the Mutating trigger error?

Mutating error:- occurs when row level trigger accesses same table on which it is based while executing or the table currently being modified by the DML statements.

Interview Questions III

noreply@blogger.com (Jafar Ali) — Sun, 24 Aug 2008 12:57:00 +0000

Difference between decode and case.
In which case we are using case and in which case we are using decode?
With an example?

Ans:- decode is a function where case is expression .
decode gives result different when using null see below

SQL> select decode( null,null,1,0) from dual;

DECODE(NULL,NULL,1,0)
---------------------
1

SQL>select case null when null then 1 else 0 end from dual
SQL> /

CASENULLWHENNULLTHEN1ELSE0END
-----------------------------
0
but see here

SQL> select case when null is null then 1 else 0 end from dual;

CASEWHENNULLISNULLTHEN1ELSE0END
-------------------------------
1

The Main Difference is that

1) Decode cannot be used in Where clause but Case can.

2) In Decode Else can be specifed in the statement it self but in Case a seperate statement has to be written.

What are the advantages and disadvantages of View?...

advantages

1. hiding the data.

2. you can use two tables data in view.

3. security will be there.

disadvantages

1.when table is not there view will not work.

2. dml is not possible if that is more than one table.

3. it is also database object so it will occupy the space.

Explain what is mutation and what is mutating table and how this mutation problem is solved in a table?

Mutation happens in case of triggers. A 'Mutating table’ is a table which is being updated by Insert, update or delete triggers. It can also be a table which is being updated when delete cascade is run.

Mutation occurs when a trigger is trying to update a row which it is using currently. To solve this either we have to use intermediate table or a view so that it can choose from one while updating the other.

What is the difference between single quote (') and double quote(") in relates to using in SQL. When do you use 'xxx' and "xxx"? what is the difference both of them?

Single quote is used to write a character string or character in sql query.

but,double quotes are used to print the name in sql screen.

for eg:-

select sysdate "current date" from dual;

current date

-----------------

24-mar-06.

How to get first 5 Records then next 5 records till end of row count in SQL –Oracle ?

elect * from emp where rownum<6

Then we can get the next 5 records by the following sql:

select * from emp where rownum<6>

and so on..

Find the two minimum salaries among table

Ans: select sal from (select * from order by sal asc) where rownum <>

How to get the prime number rows from table ie like1,3,5,7,11

Ans :

I tried in sql but sql is not sufficient. U may create a function like this

create or replace function fn_chk_pm(v_num in number) return number
is
v_flag number:=0;
v_j number:=round(v_num/2);
begin
for v_cnt in 2..v_j
loop
if mod(v_num,v_cnt)=0 then
v_flag:=1;
exit when v_flag=1;
end if;
end loop;
if v_flag=0 then
return 1;
end if;
if v_flag=1 then
return 0;
end if;
end;

Assumuming the table as follows

SQL> select * from dummyag;

COL1 COL2
---------- -
2 b
3 c
4 d
1 a

I can call it from sql statement as follows:

SQL>

1 select a.rn,a.col1,a.col2 from (select rownum rn,col1,col2 from dummyag) a
2 where fn_chk_pm(a.rn)=1
3* and a.rn!=1

With the result,

RN COL1 C
---------- ---------- -
2 3 c
3 4 d

Ans 2 : ((select * from emp where (rowid,1) in (select rowid,mod(rownum,2) from emp)) Minus
(select * from emp where (rowid,0) in (select rowid,mod(rownum,3) from emp))) union
(select * from emp where (rowid,3) in (select rowid,rownum from emp));

Oracle's Interview question's II

noreply@blogger.com (Jafar Ali) — Sun, 17 Aug 2008 14:40:00 +0000

Bitmap index

An index that maintains a binary string of ones and zeros for each distinct value of a column within the index.

Materialized View

A materialized view can help speed queries by storing data in a previously joined
or summarized format. Unlike a traditional view, this stores only the query and runs that query every time the view is accessed; a materialized view stores the results of the query in addition to the SQL statements of the view itself. Because the materialized view already contains the results of the view’s underlying query,
Using a materialized view can be as fast as accessing a single table.

OR

What is materialized view?

A materialized view is a database object that contains the results of a query. They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table's data. Materialized views, which store data based on remote tables are also, know as snapshots.A materialized view can query tables, views, and other materialized views. Collectively these are called master tables (a replication term) or detail tables (a data warehouse term)

Alias

An alternate name for a column, specified right after the column name in a SELECT statement, seen in the results
Of the query.

DML (Data Manipulation Language)

Includes INSERT, UPDATE, DELETE, and MERGE statements that operate specifically on database tables. Occasionally, SELECT statements are included in the SQL DML category.

DUAL
A special table, owned by the Oracle SYS user, that has one row and one column.
It is useful for ad hoc queries that don’t require rows from a specific table.

NULL

A NULL is usually used to represent a value that is unknown,
not applicable, or not available.

Why order by clause maintains column number values instead of column names?

Every Column have unique number in the table, when we write ORDER BY Clause with number then it refers to that unique number and display the result.

You can see the column Id as per below query

SELECT COLUMN_NAME, COLUMN_ID FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'EMP';

What is difference between SQL and SQL*PLUS?

SQL*PLUS is a command line tool where as SQL and PL/SQL language interface and reporting tool. Its a command line tool that allows user to type SQL commands to be executed directly against an Oracle database. SQL is a language used to query the relational database(DML,DCL,DDL). SQL*PLUS commands are used to format query result, Set options, Edit SQL commands and PL/SQL.

There are 10 rows in Table A and 0 Rows in table B.
Select * from A,B
How many rows will be retrieved.

Ans : it will not select any row.coz according to this query it has to give cartesian product.that means it will select m into n rowsso 10*0=0

No Rows Selected.

What is the difference between Rename and Alias?

Rename is a permanent name given to a Table or Column Whereas Alias is a temporary name given to a table or a column, which do not exist once the SQL statement is executed.

What is the Difference between stored procedures and anonymous procedure?

An external procedure, also sometimes referred to as an external routine, is a procedure stored in a dynamic link library (DLL), or libunit in the case of a Java class method. You register the procedure with the base language, and then call it to perform special-purpose processing. A stored procedure is a group of SQL statements that form a logical unit and perform a particular task. This will be stored in oracle database.

We have EMP and dept table .how do you get the entire department and corresponding employee details and the department which are not allotted it?

We use joins to get details from both the tables that’s kind of joins r called outer joins to get the missed statements.

select e.eno, e.ename, e.deptno
from emp e, dept d
where (+)e.deptno=d.deptno;

What is Complex View?Where we can use?

Views containing any group functions or joining tables are known as complex views.

Write query for the following questions
________________________________________
select * from emp where (rowid,0) in (select rowid,mod(rownum,2) from emp)

select * from emp where (rowid,0) not in (select rowid,mod(rownum,2) from emp)

How to retrieving the data from 11th column to n th column in a table.

select * from emp where rowid in ( select rowid from emp where rownum <=&upto minus select rowid from emp where rownum <&startfrom)

Oracle Interview Questions

noreply@blogger.com (Jafar Ali) — Sun, 17 Aug 2008 14:34:00 +0000

Oracle FAQ's

1)FOREIGN KEY constraint

A constraint that establishes a parent child relationship between two tables via one or more common columns.

The foreign key in the child table refers to a primary or unique key in the parent table.

2)Transaction

A logical unit of work consisting of one or more SQL statements that must all succeed or all fails to keep the database in a logically consistent state. A transfer of funds from one bank account is a logical

Transaction, in that both the withdrawal from one account and the deposit to another account must succeed for the transaction to succeed.

3)Schema

A named group of objects associated with a particular user account, such as tables, indexes, functions, and so forth.

4)Relational table

The most common form of a table in the Oracle database; the default type created with the

CREATE TABLE statement. A relational table is permanent and can be partitioned.

5)External table

A table whose definition is stored in the database but whose data is stored externally to the database.

6)Index

A database object designed to reduce the amount of time it takes to retrieve rows from a table. An index is created based on one or more columns in the table.

7)Composite index

If an index that is created on two or more columns in a table. This is called as Composite index.

8)Sequence

It is a database structure that automatically generates a series of numbers typically used to assign primary key values to database tables.

9)Synonyms

A synonym is an alias for another database object, such as a table, sequence, or view.

Synonyms provide easier access to database objects outside the user’s schema.

There are two kinds of synonyms: public and private. Public synonyms are Available to all database users. A private synonym is available only in the session of the schema owner who created it.

10)Username

An Oracle database account identifier that, along with a password, allows a user to connect to the database.

11)Privileges

The right to perform a specific action in the database granted by the DBA or other database users.

12)System privileges

Privileges that allow users to perform a specific action on one or more database objects or users in the database.

13)Object privileges

Privileges that allow users to manipulate the contents of database objects in other schemas.

14)ROWID

A unique identifier for a row in a table, maintained automatically in the table by the Oracle server.

ROWIDs are unique throughout the database.

15)Unique Index

In a Unique index, there are no duplicate values. An error is returned if you try to insert two rows into a table with the same index column values. By default, an index is nonunique.

Changing SYSMAN password

noreply@blogger.com (Jafar Ali) — Sat, 16 Aug 2008 06:52:00 +0000

Stop the DB console by using below command on the command prompt.

$>emctl stop dbconsole

This should stop dbconsole and the agent.

Have a check first.

$>emctl status dbconsole

$>emctl status agent

$>sqlplus / as sysdba

SQL>alter user sysman identified by [New Password];

Then check the new password by connecting with sysman user. This is to make sure sysman is not locked. Because when the password is changed and you try to reach the OEM, it will lock the sysman user.

SQL>conn sysman/newpassword
connected.

if you get
ora-28000: account is locked

Then unlock it first.

SQL> alter user sysman account unlock;

Modifying the emoms.properties file.

It’s located at ORACLE_HOME/[HOST]_[SID]/sysman/config. Replace HOST with your computer name and SID with your SID.

There are 2 parameters to modify:

oracle.sysman.eml.mntr.emdRepPwd= [Your encrypted password]
oracle.sysman.eml.mntr.emdRepPwdEncrypted=True

Change [Your encrypted password] with your new password in the first parameter (unencrypted current sysman password)
and change True to False in the second parameter.

As soon as you restart Dbconsole and Oracle agent, it will change false to true and will get encrypted.

Start the dbconsole

$>emctl start dbconsole
$>emctl start agent

Database recovery using BCV

noreply@blogger.com (Jafar Ali) — Thu, 17 Jul 2008 09:28:00 +0000

Database Recovery using BCV

Step 1. –Mounting BCV and User credentials

a) Mount BCV on to BCV mounting server (10.27.1.152)
b) Create appropriate oracle user (refer oracle user from Host server) with proper group spec
c) Copy oracle user profile
d) FTP the backup control file from /var/opt/oracle on host server to the BCV server say at $ORACLE_HOME/dbs
e) Cross check all the Filesystem mounted on to BCV mounting server with host server

All above steps to be done by Server support Team.

Step2. Database Recovery

a) Make changes in the Init ora file to read control file copied in step 1.d

Edit control file entry in the init{SID}.ora
#*.control_files='/u01/app/oracle/product/10g/oradata/ODSPROD/control01.ctl','/u04/oradata/ods1/control02.ctl','/
u05/oradata/ods2/control03.ctl'
*.control_files='/u01/app/oracle/product/10g/dbs/cntrl_bcv.dbf'
*.core_dump_dest='/u01/app/oracle/product/10g/admin/ODSPROD/cdump'
*.cursor_sharing='SIMILAR'
*.db_block_size=8192

b) Log in as Sysdba and startup database with pfile option as below

startup database in mount with pfile=init{SID}.ora

oracle@sun152>sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Dec 26 13:33:50 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to an idle instance.

SQL> startup mount pfile=initODSPROD.ora
ORACLE instance started.

Total System Global Area 4.0802E+10 bytes
Fixed Size 2250696 bytes
Variable Size 3294210104 bytes
Database Buffers 3.7497E+10 bytes
Redo Buffers 8650752 bytes
Database mounted.
SQL>

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

c) Check V$recover_file
Check above V$view for CHANGE# same for all datafiles and also check for any File# missing, to Ensure backup done on proper backup mode

d) View V$log to analyze Sequence# of redo logs and note down current redolog group and its filename from v$logfile.

SQL> Select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------
5 1 64087 536870912 2 NO CURRENT 1149658822 25-DEC-07
8 1 64086 536870912 2 YES INACTIVE 1149657091 25-DEC-07
7 1 64085 536870912 2 YES INACTIVE 1149466256 25-DEC-07
6 1 64084 536870912 2 YES INACTIVE 1149380057 25-DEC-07

d) Look for achivelog exist for above inactive Sequence#

ls –ltr archivelog destination

-rw-r----- 1 oracle oinstall 521260544 Dec 25 20:21 1_64084_605464450.arc
-rw-r----- 1 oracle oinstall 475254784 Dec 25 22:05 1_64085_605464450.arc
-rw-r----- 1 oracle oinstall 46111744 Dec 25 22:06 1_64086_605464450.arc

In our case Current is 64087 and we have archive of 64086.

e)Start Recovering database

SQL> recover database using BACKUP CONTROLFILE until cancel;
ORA-00279: change 1149657417 generated at 12/25/2007 22:05:09 needed for thread 1
ORA-00289: suggestion : /u03/oradata/archivelogs/1_64086_605464450.arc
ORA-00280: change 1149657417 for thread 1 is in sequence #64086

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1149658822 generated at 12/25/2007 22:06:37 needed for thread 1
ORA-00289: suggestion : /u03/oradata/archivelogs/1_64087_605464450.arc
ORA-00280: change 1149658822 for thread 1 is in sequence #64087
ORA-00278: log file '/u03/oradata/archivelogs/1_64086_605464450.arc' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}
/u14/oradata/origlogA-NEW/redo5.log =========== Supply the path of current redolog group
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL> archive log list.

F) Shutdown immediate and copy the controlfile from dbs to all 3 original locations, and then then startup.

G) Check for the temporary tablespace;

Creating new control files

noreply@blogger.com (Jafar Ali) — Tue, 15 Jul 2008 07:26:00 +0000

Please follow below steps for creating new controlfiles.

1.Make a list of all datafiles and redo log files of the database.

If you follow recommendations for control file backups as discussed in "Backing Up Control Files" , you will already have a list of datafiles and redo log files that reflect the current structure of the database. However, if you have no such list, executing the following statements will produce one.

SELECT MEMBER FROM V$LOGFILE;
SELECT NAME FROM V$DATAFILE;
SELECT VALUE FROM V$PARAMETER WHERE NAME = 'CONTROL_FILES';

If you have no such lists and your control file has been damaged so that the database cannot be opened, try to locate all of the datafiles and redo log files that constitute the database. Any files not specified in step 5 are not recoverable once a new control file has been created. Moreover, if you omit any of the files that make up the SYSTEM tablespace, you might not be able to recover the database.

2.Shut down the database.

If the database is open, shut down the database normally if possible. Use the IMMEDIATE or ABORT clauses only as a last resort.

3.Back up all datafiles and redo log files of the database or cold backup.

4.Start up a new instance in nomount stage.

STARTUP NOMOUNT

5.Create a new control file for the database using the CREATE CONTROLFILE statement.

When creating a new control file, specify the RESETLOGS clause if you have lost any redo log groups in addition to control files. In this case, you will need to recover from the loss of the redo logs (step 8). You must specify the RESETLOGS clause if you have renamed the database. Otherwise, select the NORESETLOGS clause.

6.Store a backup of the new control file on an offline storage device.

7.Edit the CONTROL_FILES initialization parameter for the database to indicate all of the control files now part of your database as created in step 5 (not including the backup control file). If you are renaming the database, edit the DB_NAME parameter in your pfile to specify the new name.

8.Recover the database if necessary. If you are not recovering the database, skip to step 9.

If you are creating the control file as part of recovery, recover the database. If the new control file was created using the NORESETLOGS clause (step 5), you can recover the database with complete, closed database recovery.

If the new control file was created using the RESETLOGS clause, you must specify USING BACKUP CONTROL FILE. If you have lost online or archived redo logs or datafiles, use the procedures for recovering those files.

9.Open the database using one of the following methods:

If you did not perform recovery, or you performed complete, closed database recovery in step 8, open the database normally.

ALTER DATABASE OPEN;

If you specified RESETLOGS when creating the control file, use the ALTER DATABASE statement, indicating RESETLOGS.

ALTER DATABASE OPEN RESETLOGS;

The database is now open and available for use.

How to rename a datafile

noreply@blogger.com (Jafar Ali) — Mon, 14 Jul 2008 06:18:00 +0000

How to rename/move datafile from one location to another

There is a need to relocate an Oracle datafile.

Cause and Prerequisites

You have created a datafile on the wrong filesystem or drive.
You add a new filesystem(s), drive(s) to the system and want to
redistribute the datafiles.
You restore some datafiles to a new location because the original is not
available any more ( media error )

Solution

There are several options to perform this task:

Option 1)

Connect to the open database.
Take the tablespace offline:
ALTER TABLESPACE OFFLINE;
Copy the datafiles to the new location by using OS commands.
Compare the size of the two files, they have to be the same.
Update the new datafile location with one of this commands:
- ALTER DATABASE RENAME FILE '' TO '';
- ALTER TABLESPACE RENAME DATAFILE '' TO '';
Bring the tablespace online with the command
ALTER TABLESPACE ONLINE;
Remove at OS level the original file.
You cannot use this method for the SYSTEM tablespace.

Option 2)

Stop the database.
Copy the datafiles to the new location by using OS commands.
Mount the database.
STARTUP MOUNT
Update the new datafile location:
ALTER DATABASE RENAME FILE '' TO '';
Open the database.
Remove at OS level the original file.

Option 3)

Make a trace file using the command:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
Modify datafile pointers in the tracefile generated above to
reflect the changes and rename the file so the extension is "sql".
Copy the datafiles to the new locations.
Run the script created before.
Remove at OS level the original file.

NOTE: BEFORE making any structural changes to a database, such as renaming
and relocating the datafiles of one or more tablespaces, always completely
backup the database. AFTER making any structural changes to a database,
always perform an immediate and complete backup.

How to rename a database?

noreply@blogger.com (Jafar Ali) — Mon, 14 Jul 2008 05:30:00 +0000

Following are the steps to rename a Database

1. Start by making a full database backup of your database (in case you need to restore if this procedure is not working).

2.Execute this command from sqlplus while connected to 'SYS AS SYSDBA':
ALTER DATABASE BACKUP CONTROLFILE TO TRACE RESETLOGS;

3.Locate the latest dump file in your USER_DUMP_DEST directory (show parameter USER_DUMP_DEST) - rename it to something like dbrename.sql.

4.Edit dbrename.sql, remove all headers and comments, and change the database's name. Also change "CREATE CONTROLFILE REUSE ..." to "CREATE CONTROLFILE SET ...".
5.Shutdown the database (use SHUTDOWN NORMAL or IMMEDIATE, don't ABORT!) and run dbrename.sql.

6.Rename the database's global name:
ALTER DATABASE RENAME GLOBAL_NAME TO new_db_name;

* To rename database you must recreate controlfiles.
* Through the GUI (DBAstudio) you can only BACKUP CONTROLFILE TO TRACE;
* Then find created trace file in your USER_DUMP_DESTINATION catalog.
* It will be something like this:
* The following commands will create a new control file and use it to open the database.
* Data used by the recovery manager will be lost. Additional logs may
be required for media recovery of offline data files. Use this
only if the current version of all online logs are available.

Eg:-vi odsprod_ora_22609_cntrl.trc

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ODSQA" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 2336
LOGFILE
GROUP 1 (
'/u02A/oradata/admin/origlogA/redo01.log',
'/u02C/oradata/admin/mirrlogA/redo01_2.log'
) SIZE 50M,
GROUP 2 (
'/u02A/oradata/admin/origlogA/redo02.log',
'/u02C/oradata/admin/mirrlogA/redo02_2.log'
) SIZE 50M,
GROUP 3 (
'/u02A/oradata/admin/origlogA/redo03.log',
'/u02C/oradata/admin/mirrlogA/redo03_2.log'
) SIZE 50M,
GROUP 4 (
'/u02A/oradata/admin/origlogA/redo04.log',
'/u02C/oradata/admin/mirrlogA/redo04_2.log'
) SIZE 50M,
GROUP 5 (
'/u02A/oradata/admin/origlogA/redo05.log',
'/u02C/oradata/admin/mirrlogA/redo05_2.log'
) SIZE 50M,
GROUP 6 (
'/u02A/oradata/admin/origlogA/redo06.log',
'/u02C/oradata/admin/mirrlogA/redo06_2.log'
) SIZE 50M
DATAFILE
'/u01/app/oracle/product/10g/oradata/ODSQA/system01.dbf',
'/u04/oradata/ods1/undotbs01.dbf',
'/u01/app/oracle/product/10g/oradata/ODSQA/sysaux01.dbf',
'/u01/app/oracle/product/10g/oradata/ODSQA/users01.dbf',
'/u07/oradata/ods4/DSSODS01.dbf',
'/u06/oradata/ods3/DSSODS02.dbf',
'/u06/oradata/ods3/EODODS01.DBF',
'/u06/oradata/ods3/EODODS02.DBF',
'/u06/oradata/ods3/Datemp01.dbf',
'/u06/oradata/ods3/EODODS03.DBF',
'/u06/oradata/ods3/DATEMP02.DBF',
'/u06/oradata/ods3/EODODS04.DBF',
'/u06/oradata/ods3/Datemp03.dbf',
'/u06/oradata/ods3/EODODS05.DBF',
'/u06/oradata/ods3/Datemp04.dbf',
'/u06/oradata/ods3/Datemp05.dbf',
'/u06/oradata/ods3/datemp06.dbf',
'/u06/oradata/ods3/EODODS06.dbf',
'/u06/oradata/ods3/Datemp07.dbf',
'/u06/oradata/ods3/EODODS07.DBF',
'/u06/oradata/ods3/EODODS08.DBF',
'/u05/oradata/ods2/datemp07.dbf',
'/u05/oradata/ods2/Datemp08.dbf',
'/u05/oradata/ods2/EODODS09.DBF',
'/u05/oradata/ods2/DSSODS03.dbf'
CHARACTER SET WE8ISO8859P1
;

7. Recovery is required if any of the datafiles are restored backups,if the last shutdown was not normal or immediate.
RECOVER DATABASE;

8. Database can now be opened normally.
ALTER DATABASE OPEN;

9. Commands to add tempfiles to temporary tablespaces.

ALTER TABLESPACE TEMP ADD TEMPFILE '/u05/oradata/ods2/temp01.dbf' SIZE 3077M REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

ALTER TABLESPACE ODSTEMP ADD TEMPFILE '/u05/oradata/ods2/ODSTEMP02.dbf' SIZE 1024M REUSE AUTOEXTEND ON NEXT 10485760 MAXSIZE 5120M;

ALTER TABLESPACE ODSTEMP ADD TEMPFILE '/u05/oradata/ods2/ODSTEMP01.DBF' SIZE 2048M REUSE AUTOEXTEND OFF;

ALTER TABLESPACE ODSTEMP ADD TEMPFILE '/u05/oradata/ods2/ODSTEMP.dbf' SIZE 2048M REUSE AUTOEXTEND OFF;

ALTER TABLESPACE EODODSTEMP ADD TEMPFILE '/u05/oradata/ods2/EODODSTEMP04' SIZE 2048M REUSE AUTOEXTEND OFF;

ALTER TABLESPACE EODODSTEMP ADD TEMPFILE '/u05/oradata/ods2/EODODSTEMP03.DBF' SIZE 2048M REUSE AUTOEXTEND OFF;

ALTER TABLESPACE EODODSTEMP ADD TEMPFILE '/u05/oradata/ods2/EODODSTEMP02.DBF' SIZE 1024M REUSE AUTOEXTEND OFF;

ALTER TABLESPACE EODODSTEMP ADD TEMPFILE '/u05/oradata/ods2/EODODSTEMP01.DBF' SIZE 1024M REUSE AUTOEXTEND ON NEXT 10485760 MAXSIZE 5120M;

Alter User MGMT_VIEW temporary tablespace TEMP;
Alter User DEVOPS temporary tablespace TEMP;
Alter User AKT007 temporary tablespace TEMP;
Alter User SYSMAN temporary tablespace TEMP;
Alter User SYS temporary tablespace TEMP;
Alter User SYSTEM temporary tablespace TEMP;
Alter User WMSYS temporary tablespace TEMP;
Alter User ORDSYS temporary tablespace TEMP;
Alter User EXFSYS temporary tablespace TEMP;
Alter User XDB temporary tablespace TEMP;
Alter User DMSYS temporary tablespace TEMP;
Alter User OLAPSYS temporary tablespace TEMP;
Alter User MDDATA temporary tablespace TEMP;
Alter User SI_INFORMTN_SCHEMA temporary tablespace TEMP;
Alter User ORDPLUGINS temporary tablespace TEMP;
Alter User OUTLN temporary tablespace TEMP;
Alter User MDSYS temporary tablespace TEMP;
Alter User TSMSYS temporary tablespace TEMP;
Alter User CTXSYS temporary tablespace TEMP;
Alter User DIP temporary tablespace TEMP;
Alter User ANONYMOUS temporary tablespace TEMP;
Alter User SCOTT temporary tablespace TEMP;

Create TEMPORARY TABLESPACE ODSTEMP TEMPFILE '/u05/oradata/ods2/ODSTEMP02.dbf' SIZE 1024M REUSE AUTOEXTEND ON NEXT 10485760 MAXSIZE 5120M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

CREATE TEMPORARY TABLESPACE temp2 TEMPFILE '/u02/oradata/TESTDB/temp2_01.dbf' SIZE 5M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE unlimited EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Create TEMPORARY TABLESPACE EODODSTEMP TEMPFILE '/u05/oradata/ods2/EODODSTEMP04' SIZE 2048M REUSE AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
alter database default temporary tablespace ODSTEMP;

DROP tablespace temp;

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/u05/oradata/ods2/temp01.dbf' SIZE 3077M REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M

10.we have to edit .bash profile

vi .profile
".profile" 20 lines, 721 characters
ORACLE_HOME=/u01/app/oracle/product/10g;
export ORACLE_HOME
ORACLE_SID=ODSQA;export ORACLE_SID
##ORACLE_SID=ODSPROD;export ORACLE_SID
ORACLE_PATH=/opt/bin;export ORACLE_PATH
PATH=$ORACLE_HOME/bin:$OPENWINHOME/bin:/usr/sbin/:/usr/ucb/:$PATH;export PATH
CURRENT_USER=`whoami`;export CURRENT_USER
LD_LIBRARY_PATH=$ORACLE_HOME/10.2.0/db_1/lib:$OPENWINHOME/lib:/cdrom/lib;
export LD_LIBRARY_PATH
EDITOR=vi;export EDITOR
ORACLE_DOC=$ORACLE_HOME/doc; export ORACLE_DOC
## Prompt Setup
PS1="`echo $CURRENT_USER`@`hostname | sed 's/\..*//'`>"
## end
#alias bdump='cd /u01/app/oracle/product/10g/admin/ODSPROD/bdump'
#alias archive='cd /u03/oradata/archivelogs'
#alias pfile='cd /u01/app/oracle/product/10g/dbs'
. ./.profile

11.edit initalisation parameter file with new database name

vi initODSQA.ora

ODSQA.__db_cache_size=10049552384
ODSQA.__java_pool_size=33554432
ODSQA.__large_pool_size=16777216
ODSQA.__shared_pool_size=1660944384
ODSQA.__streams_pool_size=33554432
*.audit_file_dest='/u01/app/oracle/product/10g/admin/ODSQA/adump'
*.background_dump_dest='/u01/app/oracle/product/10g/admin/ODSQA/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/app/oracle/product/10g/oradata/ODSQA/control01.ctl','/u04/oradata/ods1/control02.ctl','/u05/oradata/ods2/control03.ctl'
*.core_dump_dest='/u01/app/oracle/product/10g/admin/ODSQA/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='ODSQA'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ODSQAXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=/u03/oradata/archivelogs/'
*.log_archive_format='%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=3221225472
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=335
*.sga_target=11811160064
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/product/10g/admin/ODSQA/udump'

12.Create necessary folders in corresponding drives(/u01/app/oracle/product/10g/admin/ODSQA/)
mkdir bdump cdump udump adump

13.copy control file
/u01/app/oracle/product/10g/oradata/
mkdir ODSQA
oracle@DSS-ODSQA>cp ../ODSPROD//control01.ctl

CREATE CONTROLFILE REUSE DATABASE "ODSQA" NORESETLOGS NOARCHIVELOG

* DON’T FORGET BACKUP YOUR DATABASE BEFORE SCRIPT EXECUTION! *

Thanks and regards

Jafar Ali

Googlex Link Exchange

noreply@blogger.com (Jafar Ali) — Thu, 10 Jul 2008 14:40:00 +0000

Link Exchange

Oracle FAQS

noreply@blogger.com (Jafar Ali) — Thu, 10 Jul 2008 08:27:00 +0000

Q1. Which Oracle utility could you use to re-create Oracle tables and their
associated objects under a new schema name and move the associated data from
the previous schema to the new tables?

A) SQL*Loader
B) Direct-load insert
C) Schema Manager
D) Export/import utilities

Correct Answer(s): D) Export/import utilities

Explanation:
Answer d is correct. Export/import utilities are used to move tables, their
associated objects, and their data from one user to another user. SQL*Loader
is used to load data from external files into Oracle tables. Direct-load
insert writes data directly into Oracle datafiles, without using the buffer
cache. Schema Manager is an OEM utility used to create, edit, and examine
schema objects. It cannot move data from one schema to another.

Q2. What is the default port for the host naming method?

A) 1520
B) 1521
C) 1526
D) 1601
E) Any port may be used.

Correct Answer(s): B) 1521

Explanation:

The correct answer is b. The default port number of 1521 is required when
you configure host naming.

Q3. What is the purpose of the library cache area of the shared pool?

A) To store data on data dictionary caches
B) To store data retrieved from data files
C) To store shared SQL and PL/SQL
D) To provide for sort areas

Correct Answer(s): (C) To store shared SQL and PL/SQL

Explanation:

The correct answer is c. The library cache area of the SGA is used to store
shared SQL and PL/SQL. Answer a is incorrect because this is the function of
the data dictionary cache, which is a separate section of the shared pool
from the library cache. Answer b is incorrect because this is the function
of the database buffer pool, which is a separate section of the SGA from the
shared pool, which contains the library cache. Answer d is incorrect because
this is the function of the sort areas provided as a separate section of the
shared pool (in multithreaded servers) or as a part of the user's memory
area in multithreaded server (MTS) mode databases.

Q4. After you placed the tablespace LOOKUP_DATA in read-only mode, you took a
backup of LOOKUP_DATA. How often do you need to perform subsequent backups?

A) Each time a data file is added to the database.
B) Each time the control file is changed.
B) Each time the control file is changed.
C) Each time a database backup is performed.
D) Subsequent backups are not required.

Correct Answer(s):

D) Subsequent backups are not required.

Explanation:

The correct answer is d. The data in a read-only tablespace is static. You
only need to back up the LOOKUP_DATA tablespace immediately after it becomes
read-only. Answers a, b, and c are incorrect, because read-only tablespaces
don't need to be backed up under these circumstances.

Your production database has 15 data files spread across three disk drives.
How many backup sets are created by the following command sequence?

RMAN> RUN {
2> ALLOCATE CHANNEL c1 TYPE 'SBT_TAPE';
3> BACKUP
4> (DATABASE FILESPERSET = 3);
5> RELEASE CHANNEL c1; }

A) None
B) One
B) One
C) Two
D) Three
E) Four
F) Five

Correct Answer(s):

F) Five

Explanation:

The correct answer is f. The FILESPERSET option of the BACKUP command
specifies the number of files included in each backup set. For the sample
command, five backup sets with three files in each backup set will be
created. Answers a, b, c, d, and e are incorrect, because they don't match
the five backup sets the sample command will create.

What is the minimum number of redo log groups you need to configure for your
Oracle database?

A) One
B) Two
B) Two
C) Three
D) Four

Which of the following would be suitable circumstances in which to operate a
database in NOARCHIVELOG mode?

A) Control files are multiplexed.
B) Redo log files are multiplexed.
B) Redo log files are multiplexed.
C) The database can be shut down regularly to perform OS backups.
D) You can perform online backups while the database is in use.

Correct Answer(s):

C) The database can be shut down regularly to perform OS backups.

Explanation:

The correct answer is c. If you can regularly shut down a database for
backups, then you could consider operating the database in NOARCHIVELOG mode
if any lost data can be tolerated. Answers a and b are incorrect, because
these files should be multiplexed independent of the database log mode.
Answer d is incorrect, because ARCHIVELOG mode enables you to perform online
backups while the database is open and in use.

What is the minimum number of redo log groups you need to configure for your
Oracle database?

A) One
B) Two
B) Two
C) Three
D) Four

Which of the following would be suitable circumstances in which to operate a
database in NOARCHIVELOG mode?

A) Control files are multiplexed.
B) Redo log files are multiplexed.
B) Redo log files are multiplexed.
C) The database can be shut down regularly to perform OS backups.
D) You can perform online backups while the database is in use.

Correct Answer(s):

C) The database can be shut down regularly to perform OS backups.

Explanation:

The correct answer is c. If you can regularly shut down a database for
backups, then you could consider operating the database in NOARCHIVELOG mode
if any lost data can be tolerated. Answers a and b are incorrect, because
these files should be multiplexed independent of the database log mode.
Answer d is incorrect, because ARCHIVELOG mode enables you to perform online
backups while the database is open and in use.

Which of the following statements is NOT true about the recovery catalog?

A) The recovery catalog should be created in a database that is separate
from the target database.
B) The recovery catalog should be used when stored scripts are required.
B) The recovery catalog should be used when stored scripts are required.
C) The recovery catalog should be used when incremental block level backups
are required.
D) The recovery catalog should be used when historical information about
backup, restore, and recovery operations needs to be retained.
E) The recovery catalog should reside in the same database as the target
database.

What is the minimum number of redo log groups you need to configure for your
Oracle database?

A) One
B) Two
B) Two
C) Three
D) Four

Correct Answer(s):

B) Two
B) Two

Explanation:

The correct answer is b. The LGWR process writes redo log files in a
circular fashion, so two redo log groups are required to support this
operation. By default, Oracle creates two redo log groups. Answers a, b, and
d are incorrect, because they don't match the default number of two.

Which of the following statements is NOT true about the recovery catalog?

A) The recovery catalog should be created in a database that is separate
from the target database.
B) The recovery catalog should be used when stored scripts are required.
B) The recovery catalog should be used when stored scripts are required.
C) The recovery catalog should be used when incremental block level backups
are required.
D) The recovery catalog should be used when historical information about
backup, restore, and recovery operations needs to be retained.
E) The recovery catalog should reside in the same database as the target
database.

What is the minimum number of redo log groups you need to configure for your
Oracle database?

A) One
B) Two
B) Two
C) Three
D) Four

Correct Answer(s):

B) Two
B) Two

Explanation:

The correct answer is b. The LGWR process writes redo log files in a
circular fashion, so two redo log groups are required to support this
operation. By default, Oracle creates two redo log groups. Answers a, b, and
d are incorrect, because they don't match the default number of two.

Which of the following statements is NOT true about the recovery catalog?

A) The recovery catalog should be created in a database that is separate
from the target database.
B) The recovery catalog should be used when stored scripts are required.
B) The recovery catalog should be used when stored scripts are required.
C) The recovery catalog should be used when incremental block level backups
are required.
D) The recovery catalog should be used when historical information about
backup, restore, and recovery operations needs to be retained.
E) The recovery catalog should reside in the same database as the target
database.

What is the minimum number of redo log groups you need to configure for your
Oracle database?

A) One
B) Two
B) Two
C) Three
D) Four

Correct Answer(s):

B) Two
B) Two

Explanation:

The correct answer is b. The LGWR process writes redo log files in a
circular fashion, so two redo log groups are required to support this
operation. By default, Oracle creates two redo log groups. Answers a, b, and
d are incorrect, because they don't match the default number of two.

Which EXPORT mode can be used to export all objects owned by user Steve?

A) Full database
B) Tablespace
B) Tablespace
C) Table
D) User

Which of the following statements is NOT true about the recovery catalog?

A) The recovery catalog should be created in a database that is separate
from the target database.
B) The recovery catalog should be used when stored scripts are required.
B) The recovery catalog should be used when stored scripts are required.
C) The recovery catalog should be used when incremental block level backups
are required.
D) The recovery catalog should be used when historical information about
backup, restore, and recovery operations needs to be retained.
E) The recovery catalog should reside in the same database as the target
database.

Correct Answer(s):

E) The recovery catalog should reside in the same database as the target
database.

Explanation:

The correct answer is e. The recovery catalog should reside in a database
that is separate from the target database. Answers a, b, c, and d are
incorrect, because they are true statements about the recovery catalog.

Which EXPORT mode can be used to export all objects owned by user Steve?

A) Full database
B) Tablespace
B) Tablespace
C) Table
D) User

The answer to today's Question of the Day will appear in tomorrow's mailing.
If you would like to find out the answer immediately, please go to
http://www.examcram.com/studyresource/qod/login.asp The answer will be
accessible until Apr 3 2002.

----------------------------------------------------------------------------
Yesterday's Question of the Day and Answer for Oracle8 DBA: Backup and
Recovery
Mar 28 2002
----------------------------------------------------------------------------
Which of the following statements is NOT true about the recovery catalog?

A) The recovery catalog should be created in a database that is separate
from the target database.
B) The recovery catalog should be used when stored scripts are required.
B) The recovery catalog should be used when stored scripts are required.
C) The recovery catalog should be used when incremental block level backups
are required.
D) The recovery catalog should be used when historical information about
backup, restore, and recovery operations needs to be retained.
E) The recovery catalog should reside in the same database as the target
database.

Correct Answer(s):

E) The recovery catalog should reside in the same database as the target
database.

Explanation:

The correct answer is e. The recovery catalog should reside in a database
that is separate from the target database. Answers a, b, c, and d are
incorrect, because they are true statements about the recovery catalog.

Which of the following initialization parameter settings will enable check
summing for the online redo log files?

A) DB_BLOCK_CHECKSUM=Y
B) DB_BLOCK_CHECKSUM=TRUE
B) DB_BLOCK_CHECKSUM=TRUE
C) LOG_BLOCK_CHECKSUM=Y
D) LOG_BLOCK_CHECKSUM=TRUE

The answer to today's Question of the Day will appear in tomorrow's mailing.
If you would like to find out the answer immediately, please go to
http://www.examcram.com/studyresource/qod/login.asp The answer will be
accessible until Apr 4 2002.

----------------------------------------------------------------------------
Yesterday's Question of the Day and Answer for Oracle8 DBA: Backup and
Recovery
Mar 29 2002
----------------------------------------------------------------------------
Which EXPORT mode can be used to export all objects owned by user Steve?

A) Full database
B) Tablespace
B) Tablespace
C) Table
D) User

Correct Answer(s):

D) User

Explanation:

The correct answer is d. The EXPORT user mode will export all the objects in
a user's schema. Users can back up their own schema. A privileged user such
as the DBA can export all objects owned by one or more schemas. Answer a is
incorrect, because it will export all database objects except those owned by
the SYS schema. Answer b is incorrect, because it is an invalid export mode.
Answer c is incorrect, because it will only export specified tables owned by
the user schema.

When one of the Oracle background processes fails and the database shuts
down, where are the errors recorded?

A) CORE_DUMP_DEST
B) LOG_ARCHIVE_DEST
B) LOG_ARCHIVE_DEST
C) USER_DUMP_DEST
D) BACKGROUND_DUMP_DEST

Which of the following initialization parameter settings will enable check
summing for the online redo log files?

A) DB_BLOCK_CHECKSUM=Y
B) DB_BLOCK_CHECKSUM=TRUE
B) DB_BLOCK_CHECKSUM=TRUE
C) LOG_BLOCK_CHECKSUM=Y
D) LOG_BLOCK_CHECKSUM=TRUE

Correct Answer(s):

D) LOG_BLOCK_CHECKSUM=TRUE

Explanation:

The correct answer is d. When the LOG_BLOCK_CHECKSUM parameter is set to
TRUE, check summing for the online redo log files will be enabled. Answers a
and c are incorrect, because they are invalid parameter specifications.
Answer b is incorrect, because DB_BLOCK_CHECKSUM parameter applies to check
summing of data files.

Which of the following commands is used to perform an online backup of the
control file?

A) ALTER DATABASE BACKUP CONTROLFILE TO TRACE
B) ALTER SYSTEM BACKUP CONTROLFILE TO TRACE
B) ALTER SYSTEM BACKUP CONTROLFILE TO TRACE
C) ALTER SYSTEM BACKUP CONTROLFILE TO
D) ALTER DATABASE BACKUP CONTROLFILE TO
E) ALTER SESSION BACKUP CONTROLFILE TO

When one of the Oracle background processes fails and the database shuts
down, where are the errors recorded?

A) CORE_DUMP_DEST
B) LOG_ARCHIVE_DEST
B) LOG_ARCHIVE_DEST
C) USER_DUMP_DEST
D) BACKGROUND_DUMP_DEST

Correct Answer(s):

D) BACKGROUND_DUMP_DEST

Explanation:

The correct answer is d. When Oracle background processes encounter errors,
they will write the error trace files to the location specified by the
initialization parameter BACKGROUND_DUMP_DEST. Answers a, b, and c are
incorrect, because the background processes don't write error trace files to
these locations.

How will your backup strategy affect recoverability?

A) It will help you determine additional storage device needs.
B) It determines whether complete or incomplete recovery can be performed.
B) It determines whether complete or incomplete recovery can be performed.
C) It will help you get management support.
D) It will not affect recoverability.

Which of the following commands is used to perform an online backup of the
control file?

A) ALTER DATABASE BACKUP CONTROLFILE TO TRACE
B) ALTER SYSTEM BACKUP CONTROLFILE TO TRACE
B) ALTER SYSTEM BACKUP CONTROLFILE TO TRACE
C) ALTER SYSTEM BACKUP CONTROLFILE TO
D) ALTER DATABASE BACKUP CONTROLFILE TO
E) ALTER SESSION BACKUP CONTROLFILE TO

Correct Answer(s):

D) ALTER DATABASE BACKUP CONTROLFILE TO

Explanation:

The correct answer is d. The ALTER DATABASE BACKUP CONTROL-FILE TO
command is used to take an online backup of the control file.
Answer a is incorrect, because it only creates a text script trace file that
may subsequently be modified and executed to create the binary control file.
Answers b, c, and e are incorrect, because they are invalid command
specifications.

After you placed the tablespace LOOKUP_DATA in read-only mode, you took a
backup of LOOKUP_DATA. How often do you need to perform subsequent backups?

A) Each time a data file is added to the database.
B) Each time the control file is changed.
B) Each time the control file is changed.
C) Each time a database backup is performed.
D) Subsequent backups are not required.

How will your backup strategy affect recoverability?

A) It will help you determine additional storage device needs.
B) It determines whether complete or incomplete recovery can be performed.
B) It determines whether complete or incomplete recovery can be performed.
C) It will help you get management support.
D) It will not affect recoverability.

Correct Answer(s):

B) It determines whether complete or incomplete recovery can be performed.
B) It determines whether complete or incomplete recovery can be performed.

Explanation:

The correct answer is b. Your backup strategy determines the type of
recovery that can be performed. Answers a and c are incorrect, because they
don't affect recoverability. Answer d is incorrect, because your backup
strategy does affect recoverability.

The Hot Backup

noreply@blogger.com (Jafar Ali) — Thu, 10 Jul 2008 08:19:00 +0000

3. The Hot Backup.
The ARCHIVELOG function that came in OracleV6 opened up a whole new set of possible backup options, but also caused a lot of heartache until they got it working right.
Good parts about Archivelog:
It gives you 24-hour availability.
It gives you point-in-time recovery.
It allows you to restore without losing any data at all.
It lets you keep the database up, preserving the data in your SGA.
Bad parts about Archivelog:
Administration can be difficult.
Repetitive failed load attempts can create massive logs.
When it gets stuck, the database comes to a halt.
Implementing Archivelog:
To start using Archivelog, simply alter your startup command file:
startup mount exclusive
;connect internal;
alter database archivelog;
archive log start;alter database open;
and startup the database. From then on, the database will be in Archivelog mode until you turn it off (alter database noarchivelog). Note that a failed transaction is still a transaction; Archivelog keeps track of rollback segment extents, so rolled back inserts or deletes affect it just like completed transactions. Turn it off (shutdown, restart with noarchivelog mode) before doing big loads from SQL*Loader.
Another option for controlling the amount of archived redo log entries generated is to use the UNRECOVERABLE keyword, available as of 7.2. UNRECOVERABLE can be used with the CREATE INDEX and CREATE TABLE AS SELECT commands; if it is used, then the transaction will not generate redo entries.
When you start archiving, archived logs will be written every time the redo operation is about to overwrite a previously written logfile. It will write it to the directory indicated by the log_archive_dest parameter in your init.ora file. They will all be the same size (in V6; V7 can have variably sized archive logs) as your redo logs. They will increase in number until they run out of space on their destination device. At that point the database will freeze until you clear more space for them in the log_archive_dest location. SO, have a second location ready to receive them.

Backup strategy for Archivelogs:

1. Do each tablespace one at a time. That is, rather than setting them all offline, then backing them up, then setting them back online, do them each separately. You don't want to risk having a system crash while the entire database is in begin backup state; recovery is a mess. Minimize your window of vulnerability by having only one tablespace in backup state at any one time.

If you are using RAID devices or mirrored disks, in which the loss of a single disk does not cause the loss of a file, then you can simplify your backup procedures by taking all of the tablespaces offline at once, and backing them up as a set.

2. Before you backup the control file, force an archive log switch. This will update the header information in the control file.

3. Don't do it during user activity. When in backup state, a tablespace's activity is still written to the archive logs. However, it's written block-by-block rather than byte-by-byte. So changing one record in a tablespace that's being backed up will result in that record's entire block being written to the archive area. NOTE: This is correct only for those platforms where the physical sector size is less than the Oracle logical block size. On systems where the physical disk transfer size is equal to the Oracle block size, then we do not incur the penalty of having to log the entire block. This is true for MVS, VM, and perhaps other systems.
Sample Archive log command file for VMS:
NOTE: See Rama's book for a more complete script; the code is available at the Web site.

$ dup = "backup/ignore=(noback,interl,label)/log"
$ sqldbaCONNECT INTERNALalter tablespace system begin backup;exit
$ dup u01:[oracle]ora_system.dbs tape1:ora_system.bck/sav
$ sqldbaCONNECT INTERNALalter tablespace system end backup;alter tablespace appl1 begin backup;exit
$ dup u02:[oracle]appl1.dbs tape1:appl1.bck/sav
$ sqldbaCONNECT INTERNALalter tablespace appl1 end backup;exit
$!$! get archive logs
$ rename/log u03:[oracle.arcs]*.arc *.arclogs
$ rename/log u04:[oracle.arcs2]*.arc *.arclogs !secondary arcs dir
$ sqldbaCONNECT INTERNALalter system switch logfile;exit
$ dup u03:[oracle.arcs]*.arclogs,u04:[oracle.arcs2]*.arclogs tape1:logs.bck/sav
$ del/log u03:[oracle.arcs]*.arclogs;0
$ del/log u04:[oracle.arcs2]*.arclogs;0$!$! get control file
$ sqldbaCONNECT INTERNALalter database backup controlfile to 'u01:[oracle]control.bkp' reuse;exit
$ dup u01:[oracle]control.bkp tape1:control.bck/sav
Note: The "alter system switch logfile" command is all but undocumented, (see pg 3-15 of the DBA guide. It refers you to a nonexistent cross-reference). It will NOT show up in the alert log. Don't be alarmed by that; it does actually work.
NEW NOTE: Some people modify the script above to automatically pull the file names from DBA_DATA_FILES, so they don't have to hardcode the file names. I would only do that if the datafiles were all mirrored, so that a media failure wouldn't take down my database.
Integrating the three methods.
Use hot backups for all of your transaction-critical data. As a backup to the hot backups, perform cold backups periodically. As a backup to the physical file backups, use Exports.
If you have a large amount of static data, you may wish to export only certain small tables, while relying on loading programs to re-create the static data when needed. As your database grows larger, the time required to perform Imports will remove Export as a viable backup option for all but the smallest of your tables.
If at all possible in your O/S, it is also a good idea to shadow the disks on which your realtime and archived redo logs reside.

Cold Backup

noreply@blogger.com (Jafar Ali) — Thu, 10 Jul 2008 08:16:00 +0000

1. The Cold Backup.

Shut down the database and perform a cold backup by going disk by disk and backing up all files in the [oracle] directories. When complete, restart the database.
You may need to put an automated check into your backup program to use cold backups effectively, as several situations can cause the database shutdown to fail. For example, deadlocked users can prevent a SHUTDOWN IMMEDIATE from working. Also, "memory leaks" can keep the database is a suspended mode - in which the only solution is to SHUTDOWN ABORT. So add a script to your shutdown commands that does an independent check to see if the database actually went down.
Some DBAs prefer choose to use the following command sequence:
shutdown abort;
startup;
shutdown immediate;
That way, they can be sure the database goes down via the SHUTDOWN ABORT command, and then shut it down cleanly via the SHUTDOWN IMMEDIATE command. The alternative is to use just a SHUTDOWN IMMEDIATE, followed by a separate command that checks to make sure the shutdown worked

Oracle DBA's Daily Duties

noreply@blogger.com (Jafar Ali) — Wed, 09 Jul 2008 13:21:00 +0000

ORACLE – DATABASE ADMINISTRATOR

POSITION DESCRIPTION: This position requires the Data Base Administrator (DBA) to work on oracle databases from a unix/linux command line environment across the normal software life cycle spectrum of development, quality assurance, and production. The DBA is expected to have excellent knowledge of oracle internals and performance and tuning methods and processes. DUTIES AND RESPONSIBILITIES:
· Implementation of daily and weekly database policies and procedures
· Daily responsibility for database loads, database tuning, management of archive logs, journaling systems and preventive maintenance
· Development of tools to analyze the database SGA
· Development and maintenance of reports
· Support of application development
· Processing large datasets and implementation of data conversion routines
· Implementation and verification of tape backup activities
· Rotation of tape media to off site storage weekly
· Primary responsibility for development and test servers
· Client software installation and configuration for each host on the network
· Pre-release activities include functional and capacity review of new software and database changes and identification of performance issues as part of release
· Responsible for systems support as part of 24/7 support rotation.

Database Administrators
Each database requires at least one database administrator (DBA) to administer it. Because an Oracle database system can be large and can have many users, often this is not a one person job. In such cases, there is a group of DBAs who share responsibility.
A database administrator's responsibilities can include the following tasks:
Installing and upgrading the Oracle server and application tools.
Allocating system storage and planning future storage requirements for the database system.
Creating primary database storage structures (tablespaces) after application developers have designed an application.
Creating primary objects (tables, views, indexes) once application developers have designed an application.
Modifying the database structure, as necessary, from information given by application developers .
Enrolling users and maintaining system security.
Ensuring compliance with your Oracle license agreement.
Controlling and monitoring user access to the database .
Monitoring and optimizing the performance of the database.
Planning for backup and recovery of database information.
Maintaining archived data on tape.
Backing up and restoring the database.
Contacting Oracle Corporation for technical support.

Job Responsibilities include:-
Created new databases and users, setup RMAN backups, Export, and other monitoring scripts.
Responsible for Oracle 9i logical and physical databases design, implementation and maintenance on NT.
Sizes of the databases varied from 5 GB to 10 GB.
Creating tablespaces and planning the location of data, index and rollback tablespaces redo log files and control files in adherence to the Optimal Flex Architecture.
Defragmentation of tables and indexes for improved performance and effective space management. Managing database security.
Creating and assigning appropriate roles and privileges to users depending on the user activity.
Developed Oracle backup and recovery strategy and scripts. Implemented database refresh using full export.
Used EXPORT/IMPORT to do table level and full database defragmentation.
Performed SQL tuning by using Explain Plan and Tkprof.
Bitmap indexes were used on low cardinality columns as they result in reduced response time for queries and substantial reduction of storage space.
Checking for errors in alert logs and trace files.Designing and developing the screen of the entire master.
Designing and developing of all the printing reports.Designing, developing & implementing automated data backup and restoration procedure.
Providing technical support for database maintenance & disaster Recovery procedure.
Developing stored procedures in PL/SQL for the Oracle application.

Changing DB mode from NoArchivelog to Archive log

noreply@blogger.com (Jafar Ali) — Wed, 09 Jul 2008 12:59:00 +0000

Issue the following commands to put a database into ARCHVELOG mode:

1) Take Back up of Present SPfile by creating pfile F:\oracle\admin\RISLORA\pfile from present spfile.
2) Shutdown the database by SUTDOWN IMMEDIATE.
3) Take Cold backup of the database in a Disk.
4) Change the parameter in the pfile.
log_archive_start = TRUE
log_archive_dest_1 = 'LOCATION=Archive'
log_archive_dest_state_1 = ENABLE
log_archive_format = %d_%t_%s.arc
5) And make a spfile from the modified pfile in default location .
6) Start the database with spfile.

****************************************************************
SQL> CONNECT sys AS SYSDBA
SQL> STARTUP MOUNT ;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ARCHIVE LOG START;
SQL> ALTER DATABASE OPEN;

NOTE 1: Remember to take a baseline database backup right after enabling archivelog mode. Without it one would not be able to recover. Also, implement an archivelog backup to prevent the archive log directory from filling-up.
################################################################################
using target database controlfile instead of recovery catalogRMAN configuration parameters are:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'F:\Backup\rman\ora_cf%F';

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'F:\Backup\rman\ora_df%t_s%s_s%p';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'F:\Backup\rman\SNCFRILDB.ORA';

Thanks and regards
Jafar