Oracle DB Architecture.

ORACLE_SID .....U CAN have different version of oracle db in one server sqlplus 'as sysdba startup program starts by reading a file called spfile_oraclei-sid.ora that contrains the paramter identified by the oracle sid its a binary file .. init.ora file ..is a text file that historically predates the binary file db parameter will give the size of shared memory area .which is called sga system global area... a number of utility process will be started ...and in windows will have thread... when memory allocated and process started .sqlplus displays the total sga size ... as well as the size of forced part, fixed size,variable part ,db buffer and redo buffer At this point ,oracle is going to open and read a file that is so critical and it is always duplicated..which is called control file Found at location that is specified in the parameter file .. This isthe file where oracle find the name of the all the data files, temp file and log files, together that makes up the db. control file also will allow to check the global consistency ,whether something needs to be restored, whether the db is properly shutdown. whether an unfinised transaction has to be rolled back... With out control file your db is dead. After passing this stage .sqlplus will display the db is mounted. . which mostly means,that all files are identified.. The only step that remains is the opening of various file for read and write operation. when files are opened then the db is opened ..and the user who are not dba's can connect. That means ,they can connect ,if they are allowed to connect to the machine hosting the database. If you want to enables users or an application server to connect from other machine ,which is the most common case. you have to launch lsnrctl start command another program called tnslsnr which is the "listener" that waits for the incoming connection, after displaying alot of intimate details about its configuration found inthe lisntern.ora file , the program runs in the back end and everytihing is in place. Lets now contemplate a clinet program that runs on another machine,if it want to acccess the db ,it needs to provide three pieces of information. 1. the name of the host which oracle runs. 2. the port which is tnslsnr is listening 3. the service.the identifier of the db ,u want to connect. There are several ways to provide this connection. You can directly provide everything ,this is for instance what you do with jdbc and a pure java driver . If your client is using oracle client libraries ,you can provide alias ,and the client will automatically fetch the associated fetch the associated information from a file called tnsnames.ora These two methods are by far the most common ones ,but there are other possibilities such as using a nldap directory or the system utilities such as network information services as a subsitute to tnsnames.ora file Obviously oracle will not give uncontrolled access,you must sign on so that you are identified and db will cleanly know you are authorized to do . Usually you will provide username and password., authentication through an ldap directory is also sometimes. possible . In practice ,your client issues a connection request ,that is handled by listener .Listener will either spawn a sub- process ,run the oracle code and become the clients dedicated server, or it redirects the connection to an already existing shared server, which is much less common configuraiton. The server process is , so to speak the clients proxy on the Oracle side. It can access the SGA ,that is shared between all processes. It will also locate a primary called PGA,where it stores what is private to the process and to the session. But lets take a closer look to the various part in the SGA.There isnt much to say about the fixed system area. But the variable area contains the shared pool, which holds compiled version of queries ,as well as various bufferpools that will contain for instance data dictionary information. The data buffer contains data from the tables but also index structures and various work areas. Everything is split in pages or blocs; the block is the oracle basic storage unit and a typical size for a block is 8k. whenver you query or change the content of a table, you operate in memory on blocks,that are in this cache. Finaly a fourth area is used to store information before it is written to log files to ensure transnational consistency even in the occurrence of a crash . The parts that really matter to understand how Oracle works are the shared pool,, the data buffer and the log buffer. this is the where the dedicated server process will operate ,as well as the main utility processes started with the db. Lets see what happens when the client issues an SQL query. select empno, ename, job from emp where ename like 'S%' The query arrives as test to the server process that will start by computing a hash value that is used as a check sum . Hash Value = 3046656596 Then the server will check whether this query has already been recently executed ,and will inspect the shared pool . If the query is found, the analysis stops here ,this is called soft-parsing and the server process can execute the query. If a prior query with exactly the same text cannot be found,then we have hard-parsing that is CPU intensive and locks some shared resources. SQL syntax must be analyzed ,the objects that are referred to in the query must be identified ,some synonyms may have to be translated, and the best execution plan to in the query must be identified ,some synonyms may have to be translated, and the best execution plan must be determined among sometimes a very large number of possible execution plans. It may cause recursive queries on the data dictionary and input/output operations. This is why you shouldn't concatenate to the text of a query identifies that change with every call ,but you should pass them as a parameters, as if the query where a function. Once the query is analyzed and compiled ,it is going to be loaded in the shared pool and executed . It will then reference pieces of data that may or may not be in the data buffer. If data blocks are not in memory ,the server process fetches them from the data files and loads them . Block tat have been modified are written asynhronously to the datafile by one or several db wirter processes. When you update data, mechanism for analysing the statmenet and uploading data blocks in to memory are the same ones. But before modifying data ,initial values are copied to a work area called "undo segment" . Another process that want to read data being modified isnot blocked,but reads data from the undo segement instead of reading from the table block . Intial and new value are also copied to the redo buffer. If the redo buffer fills up ,the utility process LGWR ,or log write dumps its content to a log file. When you commit the transaction ,oracle records that the initial value is no longer required for rolling back the change , it records that the change is validated and the LGWR process writes the transaction to the log file . This thime the operation is synchronous ,and the server processwaits for the go ahead before the call retruns to the client That means the if you commit every single update in a loop, you will waste a lot of time just waiting for acknowledgment.

sql command to check the complete details of oracle database

spool D:\central\configuration.txt select name,log_mode,open_mode from v$database; select name from v$controlfile; select value from NLS_DATABASE_PARAMETERS where parameter = 'NLS_CHARACTERSET'; select * from v$tablespace; select BLOCK_SIZE from dba_tablespaces where tablespace_name in ('SYSTEM','UNDOTBS1','SYSAUX','TEMP','USERS','USER_DATA',INDEX_DATA'); select BLOCK_SIZE from dba_tablespaces where tablespace_name = 'UNDOTBS1'; select BLOCK_SIZE from dba_tablespaces where tablespace_name = 'SYSAUX'; select BLOCK_SIZE from dba_tablespaces where tablespace_name = 'TEMP'; select BLOCK_SIZE from dba_tablespaces where tablespace_name = 'USERS'; select BLOCK_SIZE from dba_tablespaces where tablespace_name = 'USER_DATA'; select BLOCK_SIZE from dba_tablespaces where tablespace_name = 'INDEX_DATA'; select PROPERTY_VALUE from database_properties where property_name = 'DEFAULT_PERMANENT_TABLESPACE'; select value from v$parameter where name = 'db_block_size'; select value from v$parameter where name = 'undo_management'; select value from v$parameter where name = 'undo_tablespace'; select BLOCK_SIZE from dba_tablespaces where tablespace_name = 'UNDOTBS1'; select FILE_NAME, BYTES/1024/1024, AUTOEXTENSIBLE, MAXBYTES,INCREMENT_BY from dba_data_files where tablespace_name='UNDOTBS1'; select TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE, MIN_EXTLEN, STATUS, CONTENTS, LOGGING, EXTENT_MANAGEMENT, ALLOCATION_TYPE, BLOCK_SIZE, FORCE_LOGGING, SEGMENT_SPACE_MANAGEMENT from dba_tablespaces; select USERNAME, USER_ID, PASSWORD, ACCOUNT_STATUS, LOCK_DATE, EXPIRY_DATE, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE, CREATED, PROFILE, INITIAL_RSRC_CONSUMER_GROUP, EXTERNAL_NAME INITIAL_RSRC_CONSUMER_GROUP from dba_users; select PROFILE from dba_profiles group by profile; select RESOURCE_NAME, RESOURCE_TYPE, LIMIT from dba_profiles where profile=’DEFAULT';

sql command to check the tablespace used

SELECT /* + RULE */ df.tablespace_name "Tablespace", df.bytes / (1024 * 1024) "Size (MB)", SUM(fs.bytes) / (1024 * 1024) "Free (MB)", NVL(ROUND(SUM(fs.bytes) * 100 / df.bytes),1) "% Free", ROUND((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used" FROM dba_free_space fs, (SELECT tablespace_name, SUM(bytes) bytes FROM dba_data_files GROUP BY tablespace_name ) df WHERE fs.tablespace_name (+) = df.tablespace_name GROUP BY df.tablespace_name, df.bytes UNION ALL SELECT /* + RULE */ df.tablespace_name tspace, fs.bytes / (1024 * 1024), SUM(df.bytes_free) / (1024 * 1024), NVL(ROUND((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1), ROUND((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes) FROM dba_temp_files fs, (SELECT tablespace_name, bytes_free, bytes_used FROM v$temp_space_header GROUP BY tablespace_name, bytes_free, bytes_used ) df WHERE fs.tablespace_name (+) = df.tablespace_name GROUP BY df.tablespace_name, fs.bytes, df.bytes_free, df.bytes_used ORDER BY 4 ASC;

Sql command to check the size of database

select a.data_size+b.temp_size+c.redo_size "total_size" from ( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) a, ( select nvl(sum(bytes)/1024/1024/1024,0) temp_size from dba_temp_files ) b, ( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) c;

Oracle Database 10g full database export .

http://sabdarsyed.blogspot.qa/2006/12/to-take-full-database-export-oracle-10g.html Step 1 Create a Export Directory: On Windows: ----------- SQL> create or replace directory sys_dmp as 'D:\expdp'; Directory created. On Solaris: ----------- SQL> create or replace directory sys_dmp as '/u02/expdp'; Directory created. Step 2 Create a separate export user: ############################## SQL> Connect /as sysdba SQL> CREATE USER expdpadmin IDENTIFIED BY expdp default tablespace users; User created. Step 3 Grant Export and Import Privileges. ################################### SQL> GRANT CONNECT,RESOURCE TO expdpadmin; Grant succeeded. SQL> GRANT exp_full_database to expdpadmin; Grant succeeded. SQL> alter user expdpadmin quota unlimited on USERS; User altered. SQL> GRANT READ, WRITE ON DIRECTORY SYS_DMP to expdpadmin; Grant succeeded. To check on which directories you have privilege to read & write: ################################################################# SQL> SELECT privilege, directory_name 2 FROM user_tab_privs t, all_directories d 3 WHERE t.table_name(+)=d.directory_name 4 ORDER BY 2,1; Step 4 Exporting Full Database: ######################## expdp expdpadmin/XXXXXX full=y directory=sys_dmp dumpfile=full_db_expdp.dmp logfile=full_db_expdp.log

Accessing Oracle E-Business Suite from a Desktop Client

Administrator privileges are required for installing all JRE Plug-in versions on the desktop client. IE Browser Settings for User Download of the Java Plug-in (oaj2se.exe) Oracle recommends that customers running Oracle E-Business Suite through a Microsoft Internet Explorer (IE) browser should use a 'Medium' security setting through the 'Trusted Sites' zone in the browser at runtime. Further information on browser settings are available from Document 389422.1 titled, 'Recommended Browsers for Oracle E-Business Suite Release 12'. If the appropriate Plug-in or higher is not already installed on the desktop client, you should be prompted to download it when trying to connect to a 'forms link' through an Oracle E-Business Suite forms-based (professional user interface) responsibility. Note: The oaj2se.exe can usually be downloaded manually by adding '/OA_HTML/oaj2se.exe' to the base url of the environment. For example; if the base url of the environment was 'https://server1.example.com:4443', you could run 'https://server1.example.com:4443/OA_HTML/oaj2se.exe'. With a medium security setting, after clicking on a 'forms link', a message similar to the following should appear at the top of the browser window; JRE 6 Users "The website wants to install the following add-on: 'Java(TM) Runtime Environment 6.0 Update 38' from 'Sun Microsystems, Inc.'. If you trust this website and the add-on and want to install it, click here..." JRE 7 Users 'The website wants to install the following add-on: 'Java SE Runtime Environment 7.0 Update 10' from 'Oracle America, Inc.'. If you trust this website and the add-on and want to install it, click here...' If you see this message, follow the steps below to install the Plug-in: Click on the message above and select 'Install ActiveX Control...' Once the browser has stopped processing, click on the 'forms link' again, and the oaj2se.exe file should start to download Once the download has completed, a security warning pop-up window will ask, 'Do you want to install this software?' Click on the 'Install' button and follow the on screen instructions Alternatively, you can avoid this message by temporarily altering the security settings for the initial install, using one of the two methods below. Once the Plug-in has been installed on the desktop client, the browser security settings should be reset to 'medium'. Method A: Change Security Setting to Medium-low Select 'Tools' -> 'Internet Options -> 'Security' (Tab)' from the browser menu. Select 'Trusted Sites' -> 'Custom Level' (button) From the 'Reset custom settings' drop down select 'Medium-low' Click the 'Reset...' button and accept the changes. Press the 'OK' buttons on the 'Security Settings' and 'Internet Options' windows. Close the browser and start a new browser session for the settings to take effect. After launching Oracle E-Business Suite and downloading the oaj2se.exe file onto your desktop client, please reset the security setting back to 'Medium'. Method B: Change Individual Parameter Settings Select 'Tools' -> 'Internet Options -> 'Security' (Tab)' from the browser menu. Select 'Trusted Sites' -> 'Custom Level' (button) Under 'Settings' -> 'ActiveX controls and Plug-ins' Change 'Automatic prompting for ActiveX controls' to 'Enable' Change 'Download unsigned ActiveX controls' to 'Prompt' Click the 'OK' button and accept the changes and click the 'OK' buttons to close the window Close the browser and start a new browser session for the settings to take effect. After launching Oracle E-Business Suite and downloading the oaj2se.exe file onto your desktop client, please reset 'Automatic prompting for ActiveX controls' to 'Disable' and 'Download unsigned ActiveX controls' to 'Disable. Connecting to an Oracle E-Business Suite Instance for the First Time "The application's digital certificate cannot be verified. Do you want to run the application?" If accessing Oracle E-Business Suite using a Forms-based (Professional user interface) responsibility where the Java certificate for the environment you are accessing has not previously been installed into the Java certificate store, the following warning window will display: JRE 7 Users JRE 7u40 and Higher Users If your Jar files are still signed with a self-signed certificate, the option to 'remember the decision' for future logins has been removed in JRE 1.7.0_40 (7u40) and later. This warning message will therefore appear each time you start a new session requiring the user to agree to accept the risk before running. JRE 7u40 Security Warning Tick the checkbox and click the 'Run' button to open the form. To prevent this security window popping it is recommended that you sign all your jars using a trusted certificate authority (CA). Signing can be done using either an official CA (such as Verisign, Thawte etc.) or an in-house CA. For further information on Jar signing requirements see Document 1591073.1 titled, 'Enhanced JAR File Signing for Oracle E-Business Suite'. Running with a Self-Signed Certificate If your Jars are signed with a self-signed certificate you can still launch Java content and also prevent this window from popping by utilizing a Deployment Rule Set. However, the DeploymentRuleSet.jar file must itself be signed with a certificate from a Trusted CA, either an official CA (such as Verisign, Thawte etc.). If your Jars are signed with a self-signed certificate you can also still launch Java content using a self-signed certificate by utilizing the Exception Site List feature from the desktop. However, this will not suppress this warning message. Users will still be required to respond to this warning each time they launch a new session. JRE 7u21 and Lower Users If accessing Oracle E-Business Suite using a Forms-based (Professional user interface) responsibility where the Java certificate for the environment you are accessing has not previously been installed into the Java certificate store, the following, or similar, security warning window will display: JRE 7 Java certificate warning Check the 'I accept the risk and want to run this application' box to verify you trust the certificate. Click the 'Hide Options' button and check 'Do not show this again for apps from the publisher and location above'. Click the 'Run' button to open the form. Once the certificate has been successfully installed, this message will not appear again when accessing this environment from your desktop client. JRE 6 Users JRE 6u60 and Lower Users JRE 6 certificate warning Check 'Always trust content from this publisher' to verify you trust the certificate. Click the 'Run' button to open the form. Once the certificate has been successfully installed, this message will not appear again when accessing this environment from your desktop client. JRE 6u65 and Higher Users To further enhance Java security the option to install the certificate into the Java certificate store and remember the decision for future logins when using self-signed certificates has been removed in JRE 1.6.0_65 (6u65) and higher. This warning message will therefore appear each time you start a new session if using self-signed certificates. To prevent this message appearing sign your jar files using a trusted CA by following Document 1591073.1. JRE 6u65 certificate warning Click the 'Run' button to open the form. Note: If you are using a self-signed certificate for jar signing the Publisher will display as UNKNOWN in the Java 'Warning - Security' window as shown above. This is an intended change in behavior to avoid misrepresentation and spoofing attacks. Trusted signing authorities will continue to display the publisher name as in previous JRE Plug-in releases. Uninstalling a JRE Plug-in Version from your Desktop Client To uninstall a jre Plug-in version from your Windows desktop client, follow the steps appropriate to the version of Windows you are using. Windows XP To uninstall a JRE Plug-in version from your desktop client: 1. From the Windows 'Control Panel', click on 'Add or Remove Programs'. 2. Select the appropriate version of 'Java 2 Runtime Environment' and click 'Remove'. 3. Select the appropriate options in the dialogs which follow. Windows Vista and Windows 7 To uninstall a JRE Plug-in version from your desktop client: 1. From the Windows 'Control Panel' click on 'Programs and Features'. 2. Select the appropriate version of 'J2SE Runtime Environment' and click the 'Uninstall' button. 3. Select the appropriate options in the dialogs which follow. (An administrator's password is required for this). Reverting to a Previous Java Plug-in Version If you wish to revert back to previously installed version of the Java Plug-in to be used in the Oracle E-Business Suite, please re-install the required version by re-running the txkSetPlugin script with the appropriate parameters. Running this script is explained in Step 5.1. Run the JRE Upgrade Script (Unix Users) and Step 3.2. Run the JRE Upgrade Script (Windows Users). This will automatically update the JRE parameters in the configuration files of your Web Application Tier.

Deploying JRE (Native Plug-in) for Windows Clients in Oracle E-Business Suite Release 12 (Doc ID 393931.1)

Deploying JRE (Native Plug-in) for Windows Clients in Oracle E-Business Suite Release 12 (Doc ID 393931.1)