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