DATABASE

USER MANAGEMENT-2

what is profile ?

- Profile is a set of limits on database resources

- Once we assign the user with in the profile then that user cannot exceeds the limits

- Before creating profile we must enable resource_limit parameter

Resource Limit

- Resource limits are enforced in database profiles

- Profiles only take effect when resource limits are "turned on" for the database as a whole

Check resource_limit

=============================

SQL> show parameter resource_limit

NAME TYPE VALUE

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

resource_limit boolean FALSE

enable these parameter

============================

SQL>Alter system set resource_limit=TRUE;

System altered.

check the parameter again

SQL> show parameter resource_limit

NAME TYPE VALUE

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

resource_limit boolean TRUE

Resource Parameters

*SESSION_PER_USER - specify the no of concurrent session allow to limit the user.

*CPU_PER_SESSION - specify the CPU time limit for a session, expressed in hundredth of seconds.

*CPU_PER_CALL - Specify the CPU time limit for a call (a parse, execute, or fetch), expressed in hundredths of seconds.

*CONNECT_TIME - Specify the total elapsed time limit for a session, expressed in minutes.

*IDLE_TIME - Specify the permitted periods of continuous inactive time during a session, expressed in minutes. Long-running queries and other operations are not subject to this limit.

*LOGICAL_READS_PER_SESSION - Specify the permitted number of data blocks read in a session, including blocks read from memory and disk

*LOGICAL_READS_PER_CALL - Specify the permitted number of data blocks read for a call to process a SQL statement (a parse, execute, or fetch).

*PRIVATE_SGA - Specify the amount of private space a session can allocate in the shared pool of the system global area (SGA), expressed in bytes.

Creating Profile Resource Parameters

===================================

SQL> Create profile my_profile LIMIT

SESSIONS_PER_USER 2

IDLE_TIME 5

CONNECT_TIME 10;

in above i'm created one profile along with limits then i created a user for profile

SQL> create user tom5 identified by tom5;

Assign the user to profile

SQL>alter user tom5 profile my_profile;

User altered.

right now i ll login as SAM user and over limitation as we assigned means already opened two sessions and i tired connect third session it ll throw an error

sqlplus tom5

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Nov 26 15:57:23 2007

Enter password:

ERROR:

ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit

Lets go to 2nd step IDLE_TIME.Here we go again

SQL>select * from tab;

select * from tab

ERROR at line 1:

ORA-02396: exceeded maximum idle time, please connect again

here my session idle time is more than 5 mins that why oracle server kill mine session.

To view profile limitations

=============================

SQL>select * from dba_profiles where profile='MY_PROFILE';

PROFILE RESOURCE_NAME RESOURCE LIMIT

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

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

MY_TEST COMPOSITE_LIMIT KERNEL DEFAUL

MY_TEST SESSIONS_PER_USER KERNEL 2

MY_TEST IDLE_TIME KERNEL 5

MY_TEST CONNECT_TIME KERNEL 10

PASSWORD MANAGEMENT

*FAILED_LOGIN_ATTEMPTS - Maximum times the user is allowed in fail login before locking the user account * 10

*PASSWORD_LIFE_TIME :Number of days the password is valid before expiry * 108 days

*PASSWORD_REUSE_TIME :Number of day after the user can use the already used password * UNLIMITED

*PASSWORD_REUSE_MAX :Number of times the user can use the already used password

* UNLIMITED *PASSWORD_LOCK_TIME :Number of days the user account remains locked after failed login * 1 day

*PASSWORD_GRACE_TIME :Number of grace days for user to change password * 7 days

*PASSWORD_VERIFY_FUNCTION :PL/SQL that can be used for password verification * NO DEFAULT SETTING

*SEC_CASE_SENSITIVE_LOGON :To control the case sensitivity in passwords * TRUE

Check the profile

=============================================

only DBA role person to view this

SQL> describe DBA_PROFILES

Name Null? Type

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

PROFILE NOT NULL VARCHAR2(30)

RESOURCE_NAME NOT NULL VARCHAR2(32)

RESOURCE_TYPE VARCHAR2(8)

LIMIT VARCHAR2(40)

SQL> select * from dba_profiles ;

RESOURCE_NAME RESOURCE_TYPE LIMIT

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

COMPOSITE_LIMIT KERNEL UNLIMITED

CONNECT_TIME KERNEL UNLIMITED

CPU_PER_CALL KERNEL UNLIMITED

CPU_PER_SESSION KERNEL UNLIMITED

IDLE_TIME KERNEL UNLIMITED

LOGICAL_READS_PER_CALL KERNEL UNLIMITED

LOGICAL_READS_PER_SESSION KERNEL UNLIMITED

PRIVATE_SGA KERNEL UNLIMITED

SESSIONS_PER_USER KERNEL UNLIMITED

FAILED_LOGIN_ATTEMPTS PASSWORD 10

PASSWORD_GRACE_TIME PASSWORD 7

PASSWORD_LIFE_TIME PASSWORD UNLIMITED

PASSWORD_LOCK_TIME PASSWORD 1

PASSWORD_REUSE_MAX PASSWORD UNLIMITED

PASSWORD_REUSE_TIME PASSWORD UNLIMITED

PASSWORD_VERIFY_FUNCTION PASSWORD NULL

Alter Profile

-We can alter the profile once we created

===========================================

SQL> alter profile MY_TEST LIMIT SESSIONS_PER_USER 1;

system altered.

View the profile

=================

SQL>select * from dba_profiles where profile='MY_TEST';

PROFILE RESOURCE_NAME RESOURCE LIMIT

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

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

MY_TEST COMPOSITE_LIMIT KERNEL DEFAUL

MY_TEST SESSIONS_PER_USER KERNEL 1

MY_TEST IDLE_TIME KERNEL 5

MY_TEST CONNECT_TIME KERNEL 10

Drop Profile

- Drop profile using "Drop Profile" command

- We can drop "Default Profile" - If the user has been assigned to profile we can't drop the profile so we use the CASCADE it drop the profile and it ll change user into default profile

SQL> DROP PROFILE MY_TEST;

ERROR at line 1:

ORA-02382: profile ACCOUNTANT has users assigned, cannot drop without CASCADE

SQL> DROP PROFILE MY_TEST CASCADE;

USER MANAGEMENT-1

1)Managing Users

2)Managing Privileges

3)Managing Roles

4)Managing Profiles

1 )Create User - We can create new user by using "Create User" statement -Before executing this statement we must have "Create user" system privilege -It's a powerful privilege ,a DBA or security administrator is normally have this privilege

sql>create user tom identified by tom;

i)Creating user with default tablespace:

create user tom1 identified by tom1 default tablespace users;

ii)Creating user with default and temp tablespaces:

create user tom2 identified by tom2 default tablespace users temporary tablespace temp;

ii)Allocating Space:

create user tom3 identified by tom3 default tablespace users quota 1m on users;

here i'm created a user sam and allocated space for "SAM" on zen tablespace (Note: if we not mentioned quota size on tablespace it automatically allow up to 125mb but its not showing dba_ts_quotas so we must allocate space and

one more thing bytes column is '0' that user ve unlimited space on that particular tablepsace)

2) Alter User -To change user password and account limitations -Before executing this statement we must have "Alter user" system privilege

alter user tom4 identified by tom4;-->reset password

i)Alter space with limit:

alter user tom3 quota 2m on users;

here i 'm change the user quota of tablepsce

ii)Alter space with unlimited

alter user tom3 quota unlimited on users;

To View Quota Allocation

i)DBA Level

select * from dba_ts_quotas;

ii)User Level

select * from user_ts_quotas;

To view default tablespace

SQL> select property_name,property_value from database_properties;

SQL> col property_name for a25

SQL> col property_value for a28

SQL> /

PROPERTY_NAME PROPERTY_VALUE

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

DICT.BASE 2

DEFAULT_TEMP_TABLESPACE TEMP

DEFAULT_PERMANENT_TABLESP USERS

ACE

DEFAULT_EDITION ORA$BASE

Flashback Timestamp TimeZ GMT

one

TDE_MASTER_KEY_ID

DST_UPGRADE_STATE NONE

DST_PRIMARY_TT_VERSION 11

DST_SECONDARY_TT_VERSION 0

DEFAULT_TBS_TYPE SMALLFILE

NLS_LANGUAGE AMERICAN

NLS_TERRITORY AMERICA

NLS_CURRENCY $

NLS_ISO_CURRENCY AMERICA

NLS_NUMERIC_CHARACTERS .,

NLS_CHARACTERSET WE8MSWIN1252

NLS_CALENDAR GREGORIAN

NLS_DATE_FORMAT DD-MON-RR

NLS_DATE_LANGUAGE AMERICAN

NLS_SORT BINARY

NLS_TIME_FORMAT HH.MI.SSXFF AM

NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM

NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR

NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR

NLS_DUAL_CURRENCY $

NLS_COMP BINARY

NLS_LENGTH_SEMANTICS BYTE

NLS_NCHAR_CONV_EXCP FALSE

NLS_NCHAR_CHARACTERSET AL16UTF16

NLS_RDBMS_VERSION 11.2.0.1.0

GLOBAL_DB_NAME TEST

EXPORT_VIEWS_VERSION 8

WORKLOAD_CAPTURE_MODE

WORKLOAD_REPLAY_MODE

NO_USERID_VERIFIER_SALT 820EA1118701F6539A50393BB68B

7AD0

DBTIMEZONE 00:00

36 rows selected.

-By using this command we can find which tablespace is set on default

To change default tablespace

====================================

alter database default tablespace users1;

-The above command replace the default tablespace

To view username and passwords

==============================

select username,password from dba_users;

To view the user password

select spare4 from users$ where username='SAM';

To view user password version

==============================

SQL> select username,password,PASSWORD_VERSIONS from dba_users where username='USA';

USERNAME PASSWORD PASSWORD

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

USA 10G 11G

Here Password_versions - Database version in which the password was created or changed To view account status

select username,account_status from dba_users ;

To unlock the user account

==============================

i)Unlock

alter user ram identified by ram account unlock;

ii)Unlock separation

alter user ram identified by ram;

alter user ram account unlock;

To lock the user account

i)Lock

alter user ram password expire account lock;

ii)Lock separation

alter user ram password expire;

alter user ram account lock;

To Check the user account by connection

===============================

SQL> grant connect ,resource to SAM;

Grant succeeded.

SQL> conn SAM/SAM

Connected.

SQL> select * from tab;

no rows selected

CONFIGURARION OF ORACLE STREAM IN 10G DATABASE-DRAFT

Step 1 Create Users & Setup Privileges

SQL>CREATE USER SADM IDENTIFIED BY SADM;

SQL>GRANT CONNECT, RESOURCE, DBA TO SADM;

SQL>GRANT SELECT_CATALOG_ROLE TO SADM;

SQL>GRANT UNLIMITED TABLESPACE TO SADM;

SQL>EXECUTE DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(GRANTEE => 'SADM');

Step 2 Create database links at source and target databases

connect SADM/SADM@TST1

CREATE DATABASE LINK TST2 CONNECT TO SADM IDENTIFIED BY SADM USING 'TST2';

connect SADM/SADM@TST2

CREATE DATABASE LINK TST1 CONNECT TO SADM IDENTIFIED BY SADM USING 'TST1';

Step 3 Create the queue at TST1 - Source Database

connect SADM/SADM@TST1

BEGIN

DBMS_STREAMS_ADM.SET_UP_QUEUE (

QUEUE_TABLE => 'C1_STREAM_Q1_QT',

QUEUE_NAME => 'C1_STREAM_Q1',

QUEUE_USER => 'SADM');

END;

Create the queue at TST2 (Target) ##

connect SADM/SADM@TST2

BEGIN

DBMS_STREAMS_ADM.SET_UP_QUEUE (

QUEUE_TABLE => 'A1_STREAM_Q1_QT',

QUEUE_NAME => 'A1_STREAM_Q1',

QUEUE_USER => 'SADM');

END;

Step 4 Create capture at source

Connect to TST1...

connect SADM/SADM@TST1

BEGIN

DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(

schema_name =>'EDW_APP_OWNER',

streams_type =>'CAPTURE',

streams_name =>'C1_STREAM',

queue_name =>'SADM.C1_STREAM_Q1',

include_dml =>TRUE,

include_ddl =>TRUE,

source_database =>'TST1');

END;

Step 5 Create apply process at target

connect SADM/SADM@TST2

BEGIN

DBMS_STREAMS_ADM.ADD_SCHEMA_RULES (

SCHEMA_NAME => 'EDW_APP_OWNER',

STREAMS_TYPE => 'APPLY',

STREAMS_NAME => 'A1_STREAM',

QUEUE_NAME => 'SADM.A1_STREAM_Q1',

INCLUDE_DML => TRUE,

INCLUDE_DDL => TRUE,

SOURCE_DATABASE => 'TST1');

END;

BEGIN

DBMS_APPLY_ADM.SET_PARAMETER(

apply_name => 'A1_STREAM',

parameter => 'disable_on_error',

value => 'n');

END;

Step 6 Create propagation at source

connect SADM/SADM@TST1

BEGIN

DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES (

SCHEMA_NAME => 'EDW_APP_OWNER',

STREAMS_NAME => 'P1_STREAM',

SOURCE_QUEUE_NAME => 'SADM.C1_STREAM_Q1',

DESTINATION_QUEUE_NAME => 'SADM.A1_STREAM_Q1@TST2',

INCLUDE_DML => TRUE,

INCLUDE_DDL => TRUE);

END;

Step 7 Instantiation at TST2

At source TST1...

SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;

expdp SADM/SADM@TST1 SCHEMAS=EDW_APP_OWNER DIRECTORY=expadmin DUMPFILE=EDW_APP_OWNER.dmp logfile=EDW_APP_OWNER.log PARALLEL=4 FLASHBACK_SCN=<scn>

At Target TST2...

impdp SADM/SADM@TST2 SCHEMAS=EDW_APP_OWNER DIRECTORY=expadmin DUMPFILE=EDW_APP_OWNER.dmp logfile=EDW_APP_OWNER.log PARALLEL=4

Check if schema instantiation is working fine..

select * from DBA_APPLY_INSTANTIATED_SCHEMAS;

connect SADM/SADM@TST2

declare

v_scn number;

begin

v_scn := 943015;

dbms_output.put_line('Scn : ' || v_scn);

dbms_apply_adm.set_schema_instantiation_scn(

source_schema_name => 'EDW_APP_OWNER',

source_database_name => 'TST1',

instantiation_scn => v_scn,

recursive => true);

end;

Step 8 At target start apply

connect SADM/SADM@TST2

exec dbms_apply_adm.start_apply('A1_STREAM');

Step 9 At source start capture

connect SADM/SADM@TST1

exec DBMS_CAPTURE_ADM.START_CAPTURE('C1_STREAM');

INSTALLATION OF ORACLE 10G R 2 IN RHEL

TECHPLAN
================
COPY 10G SOFTWARE TO PENDRIVE AND MOUNT IT TO THE LINUX MACHINE

stop the vm and share the 10g software folder in vm,then start the vm ,you will see automatically the software ,copy that

software
#cd /media/vdc/10gr2
#cd database
#cd
#cd /media/vdc
#cp -rvf 10gr2 /u1
#groupadd dba
#useradd -g dba -d /u1 -m oracle
#chown -R oracle:dba /u1
#chmod -R 775 /u1
#cd /u1/11gr2/database/
#firefox welcome.html
go to 6 line and open with html
copy 10 kernel parameter

******************************
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586

#vi /etc/sysctl.conf

go to end

paste 10 parameter
:wq
#sysctl -a
#sysctl -p
#su - oracle
$cd /u1/10gr2/database
$./runInstaller

next........
at the end 2 script will come that is oraInst.sh & root.sh .
Run the 2 script in # prompt

Set bash profile
===============
$vi .bash_profile

go to last and write manually this path

export ORACLE_HOME=<PATHE OF ORACLE HOME SHOWING IN /ETC/ORATAB>
export PATH=$ORACLE_HOME/bin:$PATH:.

:wq
$. .bash_profile
$dbca(same process for database creation)

How to Audit the user activity

How to Audit the user activity

Table creation:-
================
create table

stats$user_log

(

user_id varchar2(30),

session_id number(8),

host varchar2(30),

last_program varchar2(48),

last_action varchar2(32),

last_module varchar2(32),

logon_day date,

logon_time varchar2(10),

logoff_day date,

logoff_time varchar2(10),

elapsed_minutes number(8)

)

;

=======================
Log on Trigger:-

create or replace trigger

logon_audit_trigger

AFTER LOGON ON DATABASE

BEGIN

insert into stats$user_log values(

user,

sys_context('USERENV','SESSIONID'),

sys_context('USERENV','HOST'),

null,

null,

null,

sysdate,

to_char(sysdate, 'hh24:mi:ss'),

null,

null,

null

);

END;

/

==========================

Log off Trigger:-

create or replace trigger

logoff_audit_trigger

BEFORE LOGOFF ON DATABASE

BEGIN

-- ***************************************************

-- Update the last action accessed

-- ***************************************************

update

stats$user_log

set

last_action = (select action from v$session where

sys_context('USERENV','SESSIONID') = audsid)

where

sys_context('USERENV','SESSIONID') = session_id;

--***************************************************

-- Update the last program accessed

-- ***************************************************

update

stats$user_log

set

last_program = (select program from v$session where

sys_context('USERENV','SESSIONID') = audsid)

where

sys_context('USERENV','SESSIONID') = session_id;

-- ***************************************************

-- Update the last module accessed

-- ***************************************************

update

stats$user_log

set

last_module = (select module from v$session where

sys_context('USERENV','SESSIONID') = audsid)

where

sys_context('USERENV','SESSIONID') = session_id;

-- ***************************************************

-- Update the logoff day

-- ***************************************************

update

stats$user_log

set

logoff_day = sysdate

where

sys_context('USERENV','SESSIONID') = session_id;

-- ***************************************************

-- Update the logoff time

-- ***************************************************

update

stats$user_log

set

logoff_time = to_char(sysdate, 'hh24:mi:ss')

where

sys_context('USERENV','SESSIONID') = session_id;

-- ***************************************************

-- Compute the elapsed minutes

-- ***************************************************

update

stats$user_log

set

elapsed_minutes =

round((logoff_day - logon_day)*1440)

where

sys_context('USERENV','SESSIONID') = session_id;

END;

/

RAC FAQ

RAC FAQ

1.How to see db is running or not in nodes
--> srvctl status database -d <db_name>
output=>instance <instance_name> is running on node <node_name>
2.How to stop rac environment
i)stop dbconsole
$export ORACLE_SID=<sid_name>
$emctl stop dbcosole

ii)stop services
srvctl stop service -d <db_name>
iii)stop database
srvctl stop database -d <db_name>
iv)stop asm in rac1
srvctl stop asm -n <rac1>

v)stop asm in rac2
srvctl stop asm -n <rac2>
vi)stop (gsd,ons,listener,vip) nodeapps
srvctl stop nodeapps -n <rac1>
srvctl stop nodeapps -n <rac2>
vii)stop cluster
a)#cd /etc/init.d
#./init.crs stop
or
b)cd $ORACLE_HOME/bin
#./crsctl stop crs
3.How to register a database
$srvctl add database -d <db_name> -o $ORACLE_HOME
4.How to register an instance
$srvctl add instance -i <instance_name> -n <node_name> -d <db_name>
5.How to see the patches in cluster
$opatch lsinventory -detail $ORA_CRS_HOME
6.How to see the patches in db
$opatch lsinventory -detail $ORACLE_HOME
7.How to check OLR
bin#ocrconfig -local
8.How to know which nodes are participating in cluster
$./olsnodes
output=>mycluster A active
mycluster B active
9.What are the resources are registered in the cluster
$crs_stat -t
10.How to check the status of the cluster
$crsctl check crs
11.How to know the version of the cluster
$crsctl query crs softwareversion
or
$crsctl query crs activeversion
output=>clusterware version on node rac1 is 11.2.0.2.0

12.How to check/start/stop the cluster in 11gr2
bin#crsctl check/start/stop cluster -all
(in 11gr2 we can fire the command at once for all the nodes)
13.How to know the location of voting disk
$crsctl query css votedisk
14.How to know the location of ocr file and to check the integrity of the ocr
$ocrcheck
version:11.2.0.3
Total space:262120 (kbytes)
used space:- 2844
Avaialble space:- 264668210
15.How to know the location of OLR(11gr2)
bin#ocrcheck -local (need root privs)
16.How to dump the content of the OCR into a textfile
$ocrdump
17.How to know the default location of ocr file
$ocrconfig -showbackup
18.How to disable the cluster
$crsctl disable/enable crs
19.How to know the disk timeout latency (delay)
$crsctl get css disktimeout
20.How to know the network timeout latency
$crsctl get cssmiscount

ADMINISTRING ORACLE RAC USING SRVCTL
====================================
1.How to checkup the status of all instances
$srvctl status database -d <db_name>
2.How to check the status of a specific instances
$srvctl status instance -i <instance_name> -d <db_name>
3.How to know the configuration of database
$srvctl config database -d <db_name>
4.How to enable/disable the database
$srvctl enable/disable database -d <db_name>
5.How to create a high availability service
$srvctl add service -s <service_name> -d <db_name> -r <preferred_instance> -a <available_instance> -P basic
6.How to check the status of a specific service
$srvctl status/start/stop service -s <service_name> -d <db_name>
7.How to stop the listener
$srvctl stop listener -n <node_name>
8.How to know the configuration of scan
$srvctl config scan
9.How to know the config/status of scan listener
srvctl status/config scan-listener
10.what is the default location of OLR
$GRID_HOME/cdata/<host_name>.olr
11.How to know the master node
select * from gv$ges_resource;
or
ocrconfig -showbackup or alert log file
12.How to take manual backup of ocr
$ocrconfig -export /opt/ocr.bkp
13.How to register OCR file from the default backup
$ocrconfig -restore $ORA_CRS_HOME/cdata/crs/ocr001.ocr
(note:- cluste should be down in the all the nodes)
14.How to restore ocr from manual backup
$ocrconfig -import /opt/ocr.bkp
15.By default Oracle doesnot take backup of voting disk
->if the vd is in the cluster file system use cp command to take the backup
->if the vd is in raw partition,use dd command to take the backup
#dd if=/dev/sda5 of=/opt/voting.bkp
16.How to restore the VD
dd if=/opt/voting.bkp of=/dev/sda5
==============================================
1.What is weak start dependency on vip property clusterware resources
->when db instance starts ,then the resource tries to start the vip for the node,if the vip doesnot start successfully,then the instance still starts but the services doesnot start.

2.What is the generic server pools
->
i)oracle defined server pool is called generic
ii)Oracle manages the generic server pool to support adminstrator managed dbs
iii)We can add or remove an adminstrator managed db using either srvctl or DBCA ,Oracle RAC creates or remove the server pools that are member of generic
iv)we can't use srvctl or crsctl to modify the generic server pool

3.what is policy managed dbs
->
i)we have to pmdbs in 11gr2
ii)pmdbs and amdbs can't coexist in same servers
iii)pmdbs runs in one or more db server pools that are created in cluster
iv)pmdbs runs in different server in different time
v)if you are using oasm with omf for your db storage,then when an instance starts and there is no redo thread available,oracle rac automatically enables one and creates the required redo log files
and undo tablespaces.

4.what is awr
A built in repository that exists in every oracle dbs.At regular intervals,oracle database makes a snapshot of all of its vital statics and workload and stores them in awr.

5.what is cache coherency.
->The synchronization of data in multiple caches sothat reading a memory location throug any cache will return the most recent data written to that location through any other cache.sometimes it is called
cache consistency.

6.what is cardinality
->The no of database instances you want running during normal operations
7.What is the cluster
->Multiple interconnected computers or servers that appear as if they are one server to end users and applications

7.What is the cluster file system
->A distributed filesystem that is a cluster of servers that collaborate to provide high performance services to their clients.Cluster file system s/w deals with distributing requests to storage
cluster component.

8.What is cluster ready services daemon (CRSD)
->The primary oracle clusterware process that performs high availability recovery and management operation such as maaintaining OCR

9.What is GV$ views
->
i)In addition to v$ information,each GV$ view contains an extra col.i,e inst_id .which displays the instance number from which the associated v$ view information was obtained.
ii)It is created automatically ,If we create database by DBCA
iii)IF we create db manually then we have to run catclustdb.sql script.

10.What is the advantages of policy managed database
i)Before 11gr2 databases are administored managed,where a dba managed each instance of db by defining specific instances to run on specific nodes in the cluster.
ii)11gr2 implemented dynamic grid configuration introduces policy managed databases where dba is required only to define the cardinality i,e no. of db instances required.
iii)Oracle clusterware manages the allocation of nodes to run the instances.
iv)Oracle RAC allocates the required redo threads and undo tablespaces .It is only happened if db uses only oracle managed files.

11.What is RAC background process
i) ACMS:- Atomic controlfile to memory service. The acms per-instance process is an agent that contributes to ensuring a distributed sga memory update is either globally commited on success or globally
aborted if a failures occurs
ii)GTX0-J:-Global transaction process
It provides transparent support for XA global transaction in RAC env.The db auto tunes the no of these process based on workload of XA global transactions.
iii)LMON:- Global enque service monitor
It monitors global enque and resources across the cluster and performs global enque recovery operation
iv)LMD:- Global enque service daemon
it manages incoming remote resource requests within each instance
v)LMS:- Global cache service process

it maintains records of datafile statuses and each cached block by recording information in GRD
It controls the flow of messages to remote instances and manages global datablock acess and images between the buffer caches of different instances.
vi)LCK0:- Instance enque process
It manages non-cache fusion resource requests such as library and row cache requests
vii)RMSN:- oracle rac management process
it will create resources when new instance is added to the clusters
viii)RSMN:- Remote slave monitor
it manages background slave process creation and communication on remote instances.
This background slave process perform tasks on behalf of a coordinating process running in another instance

==============================================================
12.What is the process of adding a node in rac

step 1:- check is the new node is ready from a hardware and operating system perspective from rac1
rac1$su - grid
$export GRID_HOME=/u01/app/11.2.0/grid
$$GRID_HOME/bin/cluvfy stage -post hwos -n rac3

Step 2:- check the compartibility that is new node is compared to an existing node from rac1
rac1$$GRID_HOME/bin/cluvfy comp peer -refnode rac1 -n rac3 -orainv oinstall -osdba dba -verbose

Step 3:- verify the integrity of the cluster and wheather it is ready for a new node

$GRID_HOME/bin/cluvfy stage -pre nodeadd -n rac3 -fixup -verbose

Step 4:- From an existing node,extend to the new node using addNode.sh
rac1$export IGNORE_PREADDNODE_CHECKS=Y
rac1$$GRID_HOME/oui/bin/addNode.sh -silent "CLUSTER_NEW_NODES={rac3}""CLUSTER_NEW_VIRTUAL_HOSTNAMES={rac3-vip}"

Step 5:- Verify that the clusterware has been extended to the new node properly or not
rac1$$GRID_HOME/bin/cluvfy stage -post nodeadd -n rac3 -verbose

Step 6:- extend the oracle db s/w to new node
rac1$echo $ORACLE_HOME
rac1$$ORACLE_HOME/oui/bin/addNode.sh -silent "CLUSTER_NEW_NODES={rac3}"
run the root.sh commands on the new node as directed
rac3$/u01/app/oracle/product/11.2.0/db_1/root.sh

Step 7:- change the ownership of oracle executable in newly created $ORACLE_HOME on rac3
rac3$export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
rac3$chgrp asmadmin $ORACLE_HOME/bin/oracle
rac3$chmod 6751 $ORACLE_HOME/bin/oracle
rac3$ls -ltr $ORACLE_HOME/bin/oracle

Step 8:- Verify the adminstrative privilages across all nodes
rac3$$ORACLE_HOME/bin/cluvfy comp admprv -o db_config -d $ORACLE_HOME -n rac1,rac2,rac3 -verbose

Step 9:- satisfy node-instance dependancy from the new node rac3 .create password file,init.ora file and oratab entry for the new instance
rac3$echo $ORACLE_HOME
$cd $ORACLE_HOME/dbs
dbs$mv initracdb1.ora initracdb3.ora
dbs$mv orapwracdb1 orapwracdb3
$echo "racdb3:$ORACLE_HOME:N">>/etc/oratab

From a node with an existing instance of racdb .create the public thread,undo tablespace and init.ora entries for new instance
rac1$export ORACLE_SID=racdb1
$.oraenv
$sqlplus "/as sysdba"
sql>alter database add logfile 2 group 7 ("+data,'+fra') size 100m ,group 8 ('+data','+fra') size 100m,group 9('+data','+fra') size 100m;
sql>alter database enable public thread 3;
sql>create undo tablespace undotbs 3 datafile '+data' size 200m;
sql>alter system set undo_tablespace=undotb3 scope=spfile sid='racdb3';
sql>alter system set cluster_database_instance=3 scope=spfile sid = '*';

Step 10:
Update in OCR for a new instance
rac3$srvctl add instance -d racdb -i racdb3 -n rac3
rac3$srvctl add status -d racdb -i racdb3 -n rac3
rac3$srvctl add config -d racdb -i racdb3 -n rac3

add racdb3 instance to the 'racsvc.colestock.test service and verify
rac3$srvctl add service -d racdb -s racsvc.colestock.test

Step 11:-
start the new instance and verify the status of instance and services
rac3$srvctl start instance -d racdb -i racdb3
rac3$srvctl status instance -d racdb -i racdb3 -v

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1.What is the purpose of private interconnect
-->
i)clusterware uses the private interconnect for cluster synchronization(network heartbeat) and daemon communication between the clustered nodes.This communication is based on TCP protocol.

ii)RAC uses the interconnect for cache fusion(UDP) and inter-process communication (TCP).Cache fusion is the remote memory mapping of oracle buffers shared between the caches of participating nodes
in the cluster.

2.Why do we have a virtual ip(vip) in oracle rac
-->
Without using vips or fan,clients connected to a node that died will often wait for a tcp timeout period(which can be upto 10mins) before getting an error.As a result,you don't really have a good
HA solution without using VIPS.

When a node fails,the vip associated with it is automatically failed over to some other node and new node re-apps the world indicating a new MAC address for the IP,subsequent packets sent to the vip
go to the new node,which will send error RST packets back to the clients .This results in the clients getting errors immediatly.

3.What is voting disk

-->
Oracle clusterware uses the voting disk to determine which instances are members of a cluster.The VD must reside on a shared disk.Basically all nodes in the RAC cluster register their heart beat
information on this VD.The number decides the number of active nodes in the RAC cluster.These are also used for checking the availability of instances in RAC and remove the unavailable nodes
out of the cluster.It helps in preventing split brain condition and keeps database information intact.
For high availability,oracle recommends that you have a min. of 3 VD.If you configure a single VD,Then you should use external mirroring to provide redundancy .You can have upto 32 VD in your
cluster.What I could understand about the odd value of no. of VD is that a node should see max. no of VD to continue to function,so with 2 ,if it can see only 1,Its not the maximum value but a half
value of VD.

4.What is split brain syndrome
-->
In a oracle rac environment all the instances/servers communicate with each other using high speed interconnect on private network .This pvt network interface or interconnect are redundant and
are only used for inter-instance oracle datablock transfers.
Now talking about split brain concept w.r.t oracle rac system,it occurs when the instance members in a RAC fail to ping/connect to each other via this pvt interconnect.But the servers are all
physically up and running and the database instance on each of these servers is also running.This individual nodes are running fine and can conceptually accept user connection and work independently

So basically due to lack of communication the instance thinks that the other instance that it is not able to connect is down and it needs to do something about the situation.The problem is if
we leave these instance running,the same block might read ,updated in these individual instances and there would be data integrity issue,as the blocks changed in one instance,will not be locked
and could be over-written by another instance.Oracle has efficiently implemented check for the split brain syndrome.

5.What does rac do incase node becomes inactive
-->
In rac if any node becomes inactive or if other nodes are unable to ping/connect to a node in the rac,then the node which first detects that one of the node is not accessible,it will evict that node
from the rac group.
Ex:- There are 4 nodes in a rac instance and node 3 becomes unavailble and node 1 tries to connect to node 3 and finds if not responding,then nodes will evict node 3 out of the rac groups and
will leave only node 1 ,node 2 and node 4 in the rac group to continue functioning.
Ex 2:- (complecated 10 nodes)
There are 10 rac nodes in a cluster .And say 4 nodes are not able to communicate with the other 6.So there are 2 groups formed in this 10 node rac cluster(one group of 4 nodes and other of 6 nodes)
Now the nodes will quickly try to affirm their membership by locking controlfile,then the node that lock the controlfile will try to check the votes of the other nodes.The group with the most
number of active nodes gets the preference and the others are evicted issue with only 1 node getting evicted and the rest function fine.
when we see that the node is evicted,usually oracle rac will reboot that node and try to do a cluster reconfiguration to include back the evicted node.The error is ORA-29740.