What is the difference between Automatic memory management & Automatic shared memory management?

AMM: Automatic memory management  is introduced from 11g, we can specify the target size for the instance memory. The instance automatically tunes the target memory size, redistributing the memory as per the demand of SGA and the instance PGA.

ASMM:  Automatic Shared memory management is introduced from 10g, it is partially automated, in this mode we can set the target size for the SGA and then setting the aggregate target size for the PGA or managing PGA area individually.

Read More

What is the PGA in Oracle? What is the use of it?

In Oracle PGA is specific to operation system process or thread that is not shared by the other process or threads and it is process specific, it could not establish in SGA.

PGA is a heap that contains session dependent variables required by a dedicated or shard server process, server process allocates memory structures that requires in PGA.

Private Sql Area:

A private sql area contains the information about parsed sql statements and other session specific information for processing. When a server process executes a sql or pl/sql statements/codes the process uses the private sql area to store the bind variable values, query execution state information and query execution work areas.

Private sql area can be placed Program global are (PGA) in dedicated server architecture, In shared server architecture private sql area creates at SGA.

Read More

What are the bind variable in oracle? What is the use of bind variables?

How to create the bind variable in oracle, types of bind variables.

A bind variable is a placeholder of a sql statement that must be replaced with the valid value or value address for the statement to execute successfully. Using the bind variables can write the sql statements that accepts inputs or parameters at run time.

Below example will explain in detail about bind variable:

Select * from emp where emp_id= :v_empid; In this statement v_empid is bind varaiable.

Referencing bind variables:

We can reference bind variable in pl/sql by typing : colon and immediate followed by the variable.

Ex: :retrive_val :=1;

To change this bind variable in sql*plus you must enter pl/sql block

Begin

  :retrive_val := 4;

End;

/

Displaying the bind variable

Sql> print retrive_val;

Read More

Standby database creation steps for oracle standard edition in windows

Manual standby creation in oracle standard edition, manual log shipping method for creation standby database

In general, we use Data guard for Oracle standby database creation in enterprise edition, comes to standard edition we are unable to create the Disaster Recovery site using Data guard, because this feature disabled in oracle standard edition.

In Oracle standard edition standby database can creates using log shipping method, here we go with the complete steps.

For standby database creation proceed with rman recovery and user managed recovery process.

Steps to create standby DB in windows: 

1.       Make sure primary database is in archive log mode.

2.       Install oracle binaries at standby side and create same folder structure at standby server as like primary.

3.       Create pfile from spfile and send it to standby server ‘C:\app\oracle\product\11.2.0\database\’.

4.       Create standby control file send standby controlfile to DR server.

Sql> alter database create standby controlfile as ‘Ç:\app\standbycontrol.ctl’;

5.       Update the controlfile information in pfile.

6.       Create oracle service using oradim command and start the service.

oradim -NEW -SID ORCL -STARTMODE manual -PFILE C:\app\oracle\product\11.2.0\database\INITORCL.ORA"

7.       Take the backup of database from primary side.

Sql> alter database begin backup;

Copy all datafiles to standby server

Sql> alter database end backup;

8.       Start the standby database in mount state.

Sql> shut immediate

Sql> startup nomount

Sql> alter database mount standby database;

9.       Create standby archive log folder as a network drive at primary database side.

10.   Schedule the archives copy script at primary server and recover database script at standby server side.

11.   Archives_copy.bat

copy C:\app\oracle\oradata\ORCL\archive\*.0001 E:\dba\backup\arc_backup

12.   Recover_database.bat

@echo off

C:

cd \oracle\product\11202\BIN

set ORACLE_HOME=C:\oracle\product\11202

set ORACLE_SID=orcl

sqlplus / as sysdba @D:\oradata\orcl\archive\recover.sql

3. recover.sql

spool D:\oradata\orcl\archive\log.recovery replace

select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;

SELECT MAX(RECID) "Log id now" FROM V$LOG_HISTORY;

recover standby database;

auto

spool off

exit;

Read More

Oracle database migration from server to server using RMAN

Datafiles move from one disk to another disk, Database move from one disk to another disk, switch database to rman incrementally updated backup copy images.

Complete database migration from one server to another using rman backup in scheduled window / downtime:

Source server:

1.       Check the all application services are in down or not. Have make sure all the application services down before starting.

Sql>select process, program, username from v$session where username not like '%oracle%';

2.       Make sure the switch log file / check point at database level.

Sql> alter system switch logfile;

Or

Sql> alter system checkpoint;

3.       Take the rman full backup.

Rman > backup database plus archivelog;

Above command takes the full database backup. Also takes backup of spfile and controlfile but have to configure controlfile auto backup on then it takes the backup as $ORACLE_HOME/dbs/c-dbid-date.

4.       Using scp send the all backup files to target server.

5.       Copy all password file, listener, tnsnames, sqlnet.ora file to target server.

 Target server:

1.       Install oracle binaries, place the password file in $ORACLE_HOME/dbs location, listener, tnsnames and sqlnet.ora file in $ORACLE_HOME/network/admin location.

2.       Set the bash_profile.sh for oracle user, export ORACLE_SID=orcl (instance name).

3.       Connect to the rman.

Rman target sys/pwd

Rman> set dbid 43123423;

Rman> restore spfile from ‘/u01/backup/c-43123423-20170823’ to ‘/u01/app/oracle/product/12.2.0/dbhome_1/dbs/spfileorcl.ora’;

Sql> startup nomount

Rman> restore controlfile from ‘/u01/backup/c-43123423-20170823’; (please make sure the path of the controlfile from pfile)

Sql> startup mount;

Rman>  run { 

catalog start with '/backup location/';

set newname for database to '/new database files location/%b';

restore database;

switch datafile all;

recover database;

}

Above run block

1.    Catalog start with ‘backup sets location’ : rman catalogs the backup copies.

2.    Set newname for database to ‘/destination/%b’ : it renames the all datafiles location to new location in target server.

%b specifies stripped datafiles to single location

3.    Restore database: restore the database

4.    Switch datafile all: it switches all the datafile to new location

5.    Recover database: it recovers the database

4.       Rename the all redo log files.

Sql> select member from v$logfile;

Sql> alter database rename file '/previous path/redo01.log' to '/new path/redo01.log';

5.       Open database using open reset logs.

Sql> alter database open reset logs;

6.       Create temporary tablespace make as default.

Read More

Oracle DBA 6 years experience interview questions

1. What is the pga?
2. What are the pga parameters?
3. What is the relation between pga and temporary tablespace?
4. If incase PGA memory is having 2gb, and the statement required more memory then what hapen?
5. What is the smon?
6. What are the states of the redo logfiles?
7. What happens if we deletes all the redo log members? how to restore and recover?
8. What happens once we place the tablespace in begin backup mode?
9. why archives generates heavily at begin backup mode?
10.what is the difference between avialable and expired in rman backup?
11. what is the crosscheck?
12. What is the checkpoint?
13. what is the difference between cpu and psu patches?
14. What are the steps to applying the psu patches?
15. What process can fetch data into buffer cahce from datafiles?
16. is uncommitted data can writes into datafiles?
17. What is the chache coherence? What maintaince this?
18. What is the GRD, where it placed and which instance it resides?
19. what is node eviction? what causes for the node eviction?
20. What parameters need to create standby?
21. Which background process applies redo at standby side?
22.In which view we can verify the psu patch applied or not?
23. What are the statistics? what is the use of it and what are the incremental statistics?
24. How to migrate database from one os to another os?
25. Why we can run the roo.sh and rootinst.sh in oracle installation?
26. Tell me the select statement execution process?
27. tel me about update statement in oracle?
28. what are the sequence of steps in rac 11gr2?
29. What are the rac specific background process?
30. How to move the datafile created at file system to RAC (ASM)?
31. What is your recently resolved performance issue?

1.how to know which databases are using asm instance
2.show parameter in exp
3.check the status of the  exp dump file
4.restore datafile to perticuler location using rman
5.how to check size of a directory in linux
6.status of  partitioned disk after it assigened to asm disk group
7.how to stop job in expdp
8.how many types of load balances available in RAC

9.when we issue startup upgrade command what will happen
10.what is db replay
11.compression
12.rman 11g features
13.total db size
14.rman backup size and time
15.logical backup size,time and schema size
16.where can we find node membership polling machanism in RAC
17.how much database size  grows daily

18.are the idexes are always benficial or not
19.how to stop crs without connecting to root user
20.how to see listener status if the listener is passwor protected
21.how to improve performance of a impdp command
22.what is default time and location for ocr backup
23.difference between cpu,psu,bug fixes patch

24.How to check backup type in list backup command.
25.Can we rename asm instance
26.Asm instance control file
27.Can we create more than one asm instance on one box
28.What is conflict check in opatch

29.Crs should be up or down to take voting disks backups using dd command
30.Why expdp is faster than normal exp
31.Difference between active dataguard and normal dataguard
32.Will rman create temporary tablespace after cloing or restore process.
33.Difference between 10g load balance machanism and 11gr2 load balance machanism

34.What is the use of pwd file
35.Why mrp process runs in single on standby 3 node rac
36.Name of view that stores plsql compilation errors
37.Diffrence between cloning and refresh
38.How to demonstrate to the developper taht if they created index on perticular column it would hep
39.Order of background process when we issue startup command
40.What is the shortest way to restore a table if we have only rman backup of db size 1.5tb
41.How will you recover spfile if we dont have backup and it got corrupted
42.How does scan identify that node is a least loaded node
43.How to change the rebalance value in asm
44.Where cluster checks the details of asm diskgroups before starting the css process and pass these names to kfed
45.Which process starts 1st after issuing  crs start command
46.What will be state of database if we kill dwr background process
47.How to find scan name of a cluster
48.How to find number of nodes in cluster from os level with out using rac or database commands
49.How to find location of inventory file
50.What information contains in inventory.xml file
51.What is use of napply in opatch
52.What is use of CONSISTENT parameter in exp
53.How to find sql query of  session if  i have only  os process id
54.How to create spfile for asm instance

Read More