How to Identify the Static and Dynamic Parameter in Oracle

Sometimes, we may not very sure whether an oracle parameter is static(restarting database is required to come under the action) parameter or dynamic(can be changed without restarting) parameter . We can check this by using the v$parameter2 view which is very similar to v$parameter having few extra rows for long parameters . The another difference between the v$parameter and v$parameter2 is that the format of the output .. For example, if a parameter value say  "x,y"  in V$PARAMETER view does not tell us if the parameter has two values ("x" and "y") or one value ("x, y") whereas V$PARAMETER2 makes the distinction between the list parameter values clear.

SQL> select value from v$parameter WHERE name LIKE 'control_files' ; 


SQL> select value from v$parameter2 WHERE name LIKE 'control_files' ; 

 
Here, If  ISSES_MODIFIABLE  parameter is true, the parameter can be changed on session level , and if  ISSES_MODIFIABLE or ISINSTANCE_MODIFIABLE is true, then parameter can be changed on system level. Here is an example

SQL> SELECT name,Value ,ISSES_MODIFIABLE , ISINSTANCE_MODIFIABLE FROM v$parameter2  WHERE name LIKE '%target%'  ; 

Enjoy     :-)

Oracle(OUI) Silent Mode Installation on Linux

OUI (Oracle Universal Installer) is a program used to install Oracle software and database options.We generally use the OUI in GUI mode to install the Oracle software . Sometimes, it may required to install the oracle software in silent mode that is without invoking the OUI . This can be done by using the reponse file . 

An Oracle database response file specifies parameters for configuring an Oracle event database.The response file can be created by running the Oracle universal installer (OUI) and records the steps and stages in the response file or we can found the demo response file in oracle software in "response" directory .We can create the response file by invoking the below command 

[oracle@Ramtek ~]$ ./runInstaller -record -destinationFile /tmp/db_silent.rsp 

The "-record" parameter tells the installer to write to the response file and the "-destinationFile" parameter defines the name and location of the response file.The process is similar to using Kickstart for Linux installations .We can prepare the oracle environment from here . I have editted by response file and highlighted(bold with blue shade) the modified value. Below is response file.

RESPONSEFILE_VERSION=2.2.1.0.0
UNIX_GROUP_NAME="dba"
FROM_LOCATION="/home/oracle/database/stage/products.xml"
FROM_LOCATION_CD_LABEL=<Value Unspecified>
ORACLE_HOME="/home/oracle/product/10.2.0/db_1"
ORACLE_HOME_NAME="OraDb10g_home1"
SHOW_WELCOME_PAGE=true
SHOW_CUSTOM_TREE_PAGE=true
SHOW_COMPONENT_LOCATIONS_PAGE=true
SHOW_SUMMARY_PAGE=true
SHOW_INSTALL_PROGRESS_PAGE=true
SHOW_REQUIRED_CONFIG_TOOL_PAGE=true
SHOW_CONFIG_TOOL_PAGE=true
SHOW_RELEASE_NOTES=true
SHOW_ROOTSH_CONFIRMATION=true
SHOW_END_SESSION_PAGE=true
SHOW_EXIT_CONFIRMATION=true
NEXT_SESSION=false
NEXT_SESSION_ON_FAIL=true
NEXT_SESSION_RESPONSE=<Value Unspecified>
DEINSTALL_LIST={"oracle.server","10.2.0.1.0"}
SHOW_DEINSTALL_CONFIRMATION=true
SHOW_DEINSTALL_PROGRESS=true
CLUSTER_NODES={}
ACCEPT_LICENSE_AGREEMENT=true
TOPLEVEL_COMPONENT={"oracle.server","10.2.0.1.0"}
SHOW_SPLASH_SCREEN=false
SELECTED_LANGUAGES={"en"}
COMPONENT_LANGUAGES={"en"}
INSTALL_TYPE="Custom"
oracle.server:DEPENDENCY_LIST={"oracle.rdbms:10.2.0.1.0","oracle.options:10.2.0.1.0","oracle.network:10.2.0.1.0","oracle.sysman.console.db:10.2.0.1.0","oracle.rdbms.oci:10.2.0.1.0"}
oracle.network:DEPENDENCY_LIST={"oracle.network.listener:10.2.0.1.0"}
oracle.options:DEPENDENCY_LIST={"oracle.rdbms.partitioning:10.2.0.1.0"}
sl_superAdminPasswds=<Value Unspecified>
sl_dlgASMCfgSelectableDisks={}
s_superAdminSamePasswd=<Value Unspecified>
s_globalDBName="orcl"
s_dlgASMCfgRedundancyValue="2 (Norm)"
s_dlgASMCfgNewDisksSize="0"
s_dlgASMCfgExistingFreeSpace="0"
s_dlgASMCfgDiskGroupName="DATA"
s_dlgASMCfgDiskDiscoveryString=""
s_dlgASMCfgAdditionalSpaceNeeded=" MB"
s_dbSelectedUsesASM=""
s_dbSIDSelectedForUpgrade=""
s_dbRetChar=""
s_dbOHSelectedForUpgrade=""
s_ASMSYSPassword=<Value Unspecified>
n_performUpgrade=0
n_dlgASMCfgRedundancySelected=2
n_dbType=1
n_dbSelection=0
b_useSamePassword=false
b_useFileSystemForRecovery=true
b_receiveEmailNotification=false
b_loadExampleSchemas=false
b_enableAutoBackup=false
b_dlgASMShowCandidateDisks=true
b_centrallyManageASMInstance=true
sl_dlgASMDskGrpSelectedGroup={" "," "," "," "}
s_dlgRBOUsername=""
s_dlgEMCentralAgentSelected="No Agents Found"
b_useDBControl=true
s_superAdminSamePasswdAgain=<Value Unspecified>
s_dlgEMSMTPServer=""
s_dlgEMEmailAddress=""
s_dlgRBORecoveryLocation="/home/oracle/product/10.2.0/flash_recovery_area"
n_upgradeDB=1
n_configurationOption=1
sl_upgradableSIDBInstances={}
n_upgradeASM=0
sl_dlgASMCfgDiskSelections={}
s_ASMSYSPasswordAgain=<Value Unspecified>
n_dbStorageType=0
s_rawDeviceMapFileLocation=""
sl_upgradableRACDBInstances={}
s_dlgRBOPassword=<Value Unspecified>
b_stateOfUpgradeDBCheckbox=false
s_dbSid="orcl"
b_dbSelectedUsesASM=false
sl_superAdminPasswdsAgain=<Value Unspecified>
s_mountPoint="/home/oracle/product/10.2.0/oradata"
b_stateOfUpgradeASMCheckbox=false
oracle.assistants.server:OPTIONAL_CONFIG_TOOLS="{}"
oracle.has.common:OPTIONAL_CONFIG_TOOLS="{}"
oracle.network.client:OPTIONAL_CONFIG_TOOLS="{}"
oracle.sqlplus.isqlplus:OPTIONAL_CONFIG_TOOLS="{}"
oracle.sysman.console.db:OPTIONAL_CONFIG_TOOLS="{}"
varSelect=3
s_nameForOPERGrp="dba"
s_nameForDBAGrp="dba"

A silent installation is initiated using the following command.

[root@Ramtek ~]# su - oracle
[oracle@Ramtek ~]$ cd /home/oracle/database
[oracle@Ramtek database]$ ./runInstaller -silent -force -ignoreSysPrereqs -responseFile /tmp/db.rsp
Starting Oracle Universal Installer...
Checking installer requirements...
Checking operating system version: must be redhat-3, SuSE-9, redhat-4, UnitedLinux-1.0, asianux-1 or asianux-2
                                      Passed
All installer requirements met.
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2012-04-12_11-25-29AM. Please wait ...[oracle@Ramtek database]$ Oracle Universal Installer, Version 10.2.0.1.0 Production
Copyright (C) 1999, 2005, Oracle. All rights reserved.
You can find a log of this install session at:
 /home/oracle/oraInventory/logs/installActions2012-04-12_11-25-29AM.log
.................................................................................................... 100% Done.
Loading Product Information
................................................................................................................... 100% Done.
Analyzing dependencies
........................................................................
Starting execution of Prerequisites...
Total No of checks: 9
Performing check for CertifiedVersions
Checking operating system requirements ...
Expected result: One of redhat-3,redhat-4,SuSE-9,asianux-1,asianux-2
Actual Result: redhat-4
Check complete. The overall result of this check is: Passed
Check complete: Passed
=======================================================================
Performing check for Packages
Checking operating system package requirements ...
Checking for make-3.79; found make-1:3.81-3.el5.        Passed
Checking for binutils-2.14; found binutils-2.17.50.0.6-9.el5.   Passed
Checking for gcc-3.2; found Not found.  Failed <<<<
Checking for libaio-0.3.96; found libaio-0.3.106-3.2.   Passed
Check complete. The overall result of this check is: Failed <<<<

Check complete: Failed <<<<
Problem: Some packages required for the Oracle Database 10g to function properly are missing (see above).
Recommendation: Install the required packages before continuing with the installation.
=======================================================================
Performing check for Security
Checking security kernel parameters
Checking for semmsl=250; found semmsl=250.      Passed
Checking for semmns=32000; found semmns=32000.  Passed
Checking for semopm=100; found semopm=100.      Passed
Checking for semmni=128; found semmni=128.      Passed
Checking for shmmax=536870912; found shmmax=4294967295. Passed
Checking for shmmni=4096; found shmmni=4096.    Passed
Checking for shmall=2097152; found shmall=268435456.    Passed
Checking for file-max=65536; found file-max=65536.      Passed
Checking for VERSION=2.6.9; found VERSION=2.6.18-128.el5.       Passed
Checking for ip_local_port_range=1024 - 65000; found ip_local_port_range=1024 - 65000.  Passed
Checking for rmem_default=262144; found rmem_default=262144.    Passed
Checking for rmem_max=262144; found rmem_max=262144.    Passed
Checking for wmem_default=262144; found wmem_default=262144.    Passed
Checking for wmem_max=262144; found wmem_max=262144.    Passed
Check complete. The overall result of this check is: Passed
Check complete: Passed
=======================================================================
Performing check for GLIBC
Checking Recommended glibc version
Expected result: ATLEAST=2.3.2-95.27
Actual Result: 2.5-34
Check complete. The overall result of this check is: Passed
Check complete: Passed
=======================================================================
Performing check for TotalMemory
Checking physical memory requirements ...
Expected result: 922MB
Actual Result: 1008MB
Check complete. The overall result of this check is: Passed
Check complete: Passed
=======================================================================
Performing check for SwapSpace
Checking available swap space requirements ...
Expected result: 1512MB
Actual Result: 2047MB
Check complete. The overall result of this check is: Passed
Check complete: Passed
=======================================================================
Performing check for OracleBase
Validating ORACLE_BASE location (if set) ...
Check complete. The overall result of this check is: Passed
Check complete: Passed
=======================================================================
Performing check for DetectAnyInvalidASMHome
Checking for proper system clean-up....
Check complete. The overall result of this check is: Passed
Check complete: Passed
=======================================================================
Performing check for CompatibilityChecks_Custom
Checking for Oracle Home incompatibilities ....
Actual Result: NEW_HOME
Check complete. The overall result of this check is: Passed
Check complete: Passed
=======================================================================
PrereqChecks complete
........................................... 100% Done.
-----------------------------------------------------------------------------
Summary
Global Settings
    Source: /home/oracle/database/stage/products.xml
    Oracle Home: /home/oracle/product/10.2.0/db_1 (OraDb10g_home1)
    Installation Type: Custom
Product Languages
   English
Space Requirements
   /home/ Required 1.21GB : Available 8.40GB
   / Required 108MB (only as temporary space) : Available 1004MB
New Installations (100 products)
   Oracle Database 10g 10.2.0.1.0
   Enterprise Edition Options 10.2.0.1.0
   Oracle Partitioning 10.2.0.1.0
   Oracle Enterprise Manager Console DB 10.2.0.1.0
   Oracle Net Services 10.2.0.1.0
   Oracle Database 10g 10.2.0.1.0
   Oracle Net Listener 10.2.0.1.0
   HAS Files for DB 10.2.0.1.0
   Oracle Internet Directory Client 10.2.0.1.0
   Oracle Call Interface (OCI) 10.2.0.1.0
   Oracle interMedia 10.2.0.1.0
   Enterprise Manager Agent Core 10.2.0.1.0
   Oracle JVM 10.2.0.1.0
   Database Configuration and Upgrade Assistants 10.2.0.1.0
   Oracle interMedia Locator 10.2.0.1.0
   Oracle XML Development Kit 10.2.0.1.0
   Oracle Text 10.2.0.1.0
   Oracle Database Utilities 10.2.0.1.0
   Generic Connectivity Common Files 10.2.0.1.0
   Oracle Advanced Security 10.2.0.1.0
   Enterprise Manager Repository Core 10.2.0.1.0
   PL/SQL 10.2.0.1.0
   Oracle Net 10.2.0.1.0
   Assistant Common Files 10.2.0.1.0
   Enterprise Manager plugin Common Files 10.2.0.1.0 Beta
   Buildtools Common Files 10.2.0.1.0
   Installation Common Files 10.2.0.1.0
   Oracle LDAP administration 10.2.0.1.0
   Oracle Java Client 10.2.0.1.0
   Precompiler Common Files 10.2.0.1.0
   Oracle Recovery Manager 10.2.0.1.0
   SQL*Plus 10.2.0.1.0
   Enterprise Manager plugin Common Files 10.2.0.1.0
   HAS Common Files 10.2.0.1.0
   Oracle Clusterware RDBMS Files 10.2.0.1.0
   Oracle Wallet Manager 10.2.0.1.0
   Enterprise Manager Minimal Integration 10.2.0.1.0
   Oracle Database User Interface 2.2.13.0.0
   Secure Socket Layer 10.2.0.1.0
   Required Support Files 10.2.0.1.0
   Database SQL Scripts 10.2.0.1.0
   OLAP SQL Scripts 10.2.0.1.0
   PL/SQL Embedded Gateway 10.2.0.1.0
   Oracle Globalization Support 10.2.0.1.0
   Character Set Migration Utility 10.2.0.1.0
   LDAP Required Support Files 10.2.0.1.0
   Oracle Help for the  Web 1.1.10.0.0
   Oracle JDBC Thin Driver for JDK 1.4 10.2.0.1.0
   Oracle JDBC Thin Driver for JDK 1.2 10.2.0.1.0
   Oracle interMedia Client Option 10.2.0.1.0
   Oracle Notification Service 10.1.0.3.0
   Oracle Code Editor 1.2.1.0.0I
   Perl Interpreter 5.8.3.0.2
   JDBC Common Files 10.2.0.1.0
   Oracle Locale Builder 10.2.0.1.0
   Oracle Containers for Java 10.2.0.1.0
   Database Workspace Manager 10.2.0.1.0
   Oracle Core Required Support Files 10.2.0.1.0
   Platform Required Support Files 10.2.0.1.0
   Oracle interMedia Locator RDBMS Files 10.2.0.1.0
   Oracle JDBC/OCI Instant Client 10.2.0.1.0
   Oracle interMedia Annotator 10.2.0.1.0
   SQLJ Runtime 10.2.0.1.0
   Oracle interMedia Java Advanced Imaging 10.2.0.1.0
   Oracle Database 10g interMedia Files 10.2.0.1.0
   Oracle Data Mining RDBMS Files 10.2.0.1.0
   Enterprise Manager Baseline 10.2.0.1.0
   Oracle Help For Java 4.2.6.1.0
   Oracle UIX 2.1.22.0.0
   XML Parser for Java 10.2.0.1.0
   Precompiler Required Support Files 10.2.0.1.0
   XML Parser for Oracle JVM 10.2.0.1.0
   Oracle Message Gateway Common Files 10.2.0.1.0
   Oracle Starter Database 10.2.0.1.0
   Sample Schema Data 10.2.0.1.0
   Parser Generator Required Support Files 10.2.0.1.0
   Agent Required Support Files 10.2.0.1.0
   Oracle RAC Required Support Files-HAS 10.2.0.1.0
   RDBMS Required Support Files 10.2.0.1.0
   RDBMS Required Support Files for Instant Client 10.2.0.1.0
   XDK Required Support Files 10.2.0.1.0
   DBJAVA Required Support Files 10.2.0.1.0
   SQL*Plus Required Support Files 10.2.0.1.0
   Oracle JFC Extended Windowing Toolkit 4.2.33.0.0
   Oracle Ice Browser 5.2.3.6.0
   Oracle Display Fonts 9.0.2.0.0
   Oracle Extended Windowing Toolkit 3.4.38.0.0
   Enterprise Manager Common Files 10.2.0.1.0
   Enterprise Manager Agent DB 10.2.0.1.0
   Oracle Net Required Support Files 10.2.0.1.0
   Enterprise Manager Repository DB 10.2.0.1.0
   SSL Required Support Files for InstantClient 10.2.0.1.0
   regexp 2.1.9.0.0
   Bali Share 1.1.18.0.0
   Oracle Universal Installer 10.2.0.1.0
   Oracle One-Off Patch Installer 10.2.0.1.0
   Installer SDK Component 10.2.0.1.0
   Java Runtime Environment 1.4.2.8.0
   Sun JDK 1.4.2.0.8
   Sun JDK extensions 10.1.2.0.0
-----------------------------------------------------------------------------
Installation in progress (Thu Apr 12 11:26:13 PDT 2012)
.................................................... ..........  19% Done.
...............................................................  38% Done.
...............................................................  57% Done.
.............................................................    75% Done.
Install successful
Linking in progress (Thu Apr 12 11:43:57 PDT 2012)
...............................................         75% Done.
Link successful
Setup in progress (Thu Apr 12 11:44:36 PDT 2012)
..............                                                  100% Done.
Setup successful
End of install phases.(Thu Apr 12 11:45:01 PDT 2012)
WARNING:A new inventory has been created in this session. However, it has not yet been registered as the central inventory of this system.
To register the new inventory please run the script '/home/oracle/oraInventory/orainstRoot.sh' with root privileges.
If you do not register the inventory, you may not be able to update or patch the products you installed.
The following configuration scripts
/home/oracle/product/10.2.0/db_1/root.sh
need to be executed as root for configuring the system. If you skip the execution of the configuration tools, the configuration will not be complete and the product wont function properly. In order to get the product to function properly, you will be required to execute the scripts and the configuration tools after exiting the OUI.

The installation of Oracle Database 10g was successful.
Please check '/home/oracle/oraInventory/logs/silentInstall2012-04-12_11-25-29AM.log' for more details.

[oracle@Ramtek database]$ su - 
Password:
[root@Ramtek ~]# /home/oracle/oraInventory/orainstRoot.sh
Changing permissions of /home/oracle/oraInventory to 770.
Changing groupname of /home/oracle/oraInventory to dba.
The execution of the script is complete

For  preparing environment and database installation  click here


Enjoy         :-) 

What is redo log thread in oracle ?

On googling about the redo log thread, i have not found proper documentation  that clearly explains clearly what the redo log thread is . Here i am trying to cover the redo log threads in case of single instance and  RAC  taking reference from ASKTOM site .

Each instance has it's own personal set of redo and each redo thread is made up of at least two groups that have one or more members (files) .Two instances will never write to the same redo files - each instance has it's own set of redo logs to write to . Another instance may well READ some other instances redo logs - after that other instance fails for example - to perform recovery. Here is a scenario which helps us to understand the thread concepts .

Most V$ views work by selecting information from the corresponding GV$ view with a predicate "where instance_id = <that instance>". So V$SESSION in single Instance(i.e, 1) is actually 

SQL>select  *  from  gv$instance where inst_id= 1 ;

On a three node RAC database, if we select from v$session, we get sessions from that instance only. Selecting from GV$SESSION creates parallel query slaves on the other instances and gets the information back to our session. 

This works fine in almost all cases. There are few exceptions: in case of redo logs, the RAC instance must see all the redo logs of other instances as they become important for its recovery. Therefore, V$LOG actually shows all the redo logs, of all the instances, not just of its own. Contrast this with V$SESSION, which shows only sessions of that instance, not all. So, if there are 3 log file groups per instance (actually, per "thread") and there are 3 instances, V$LOG on any instance will show all 9 logfile groups, not 3. 

When we select form GV$LOG, remember, the session gets the information from other instances as well. Unfortunately, the PQ servers on those instances also get 9 records each, since they also see the same information seen by the first instance. On a three instance RAC, we will get 3X9 = 27 records in GV$LOG! 

To avoid this: 

1.) Always select from V$LOG, V$LOGFILE and V$THREAD in a RAC instance. GV$ views are misleading  or 

2.)  Add a predicate to match  THREAD#  with  INST_ID. (Beware: thread numbers are by default the same as the instance_id; but we may have defined a different thread number while creating the database) as 

SQL> select * from gv$log where inst_log=thread# ; 

Ref : http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:18183400346178753



Enjoy    :-)