General Topics

How to start vncserver on Linux

Open a terminal and issue the following command to start the vncserver service or process:

vncserver :1

This command can be issued at root user or at any other user.

After this we can start vnc viewer and put the following to connect:

VNC Server = 147.43.0.13:1
Encryption can be anything

While connecting system will ask a password which we allocate for vnc server when we configure this for the first time.


To use "cls" like windows DOS in linux in place of "clear" command, we can use link command:
$which clear
/usr/bin/clear

$ln /usr/bin/clear /usr/bin/cls


To check the global name of a database:
select * from global_name;


Oracle Version Details

Identifying Your Oracle Database Software Release

Release Number Format

11.2.0.3.0

11 = Major database release number
2 = database maintenance release number
0 = application server release number
3 = component specific release number
0 = platform specific release number

col product format a35
col version format a15
col status format a15
select * from product_component_version;

select banner from v$version;

SELECT * FROM V$VERSION;

SELECT version FROM V$INSTANCE;

SET SERVEROUT ON
BEGIN 
DBMS_OUTPUT.PUT_LINE(DBMS_DB_VERSION.VERSION || '.' || DBMS_DB_VERSION.RELEASE); 
END;


To know the oracle binaries are 32 bit or 64 bit:

On Unix/Solaris/Linux:
cd $ORACLE_HOME/bin

file oracl*

This will display the file type of your oracle binaries. If you are running 64-bit binaries, the output should look like this:

oracle: ELF 64-bit MSB executable SPARCV9 Version 1, dynamically linked, not stripped 
oracleO: ELF 64-bit MSB executable SPARCV9 Version 1, dynamically linked, not stripped

If your binaries are 32-bit, the output will look like this:

oracle: ELF 32-bit MSB executable SPARC Version 1, dynamically linked, not stripped


How could I know if my OS is 32-bit or 64-bit version?

$uname -a
$uname -m

To get database server OS version from client side:

select platform_id,platform_name from v$database;

How to determine database compatibility level ?

COMPATIBILITY allows you to use a new release of Oracle, while at the same time guaranteeing backward compatibility with an earlier release.  In order to determine your current database compatibility level you can query from view database_compatible_level.

col value for a11
col description for a50
select * from database_compatible_level;

We can also check the following:

show parameter compatible

We can also query from v$parameter:

select value from v$parameter where name='compatible';

Listener Version

$ lsnrctl version

Version of OPATCH

perl $ORACLE_HOME/OPatch/opatch.pl version

Sun Solaris Version

cat /etc/release

RedHat Linux Version

cat /etc/redhat-release

Java Version

java -version

Version of Installed packages on Linux

rpm -qagrep

Bit of your Oracle Software

file $ORACLE_HOME/bin/oracle

Finding Oracle data block size

SHOW PARAMETER DB_BLOCK_SIZE




SYS_CONTEXT

SYS_CONTEXT returns the value of parameter associated with the context namespace. You can use this function in both SQL and PL/SQL statements.

SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER') FROM DUAL;

SELECT SYS_CONTEXT ('USERENV', 'HOST') FROM DUAL;

Monitoring with alert_sid.log file live

SQL> show parameter back;
background_dump_dest = /u01/app/oracle/diag/rdbms/prod/prod/trace

cd /u01/app/oracle/diag/rdbms/prod/prod/trace

$ tail -f alert_prod.log

$ tail -10f alert_prod.log

To find alert log location or trace file location

SQL> select value from v$parameter where name='background_dump_dest';

alert log file is : /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_prod.log

To find the location of the diagnostic_dest

SQL> select value from v$parameter where name='diagnostic_dest';

What is oracle inventory

While installing Oracle software a new folder Directory created in ORACLE_BASE called oinventory.  what is it ? and what are the benefits of using this folder ? What kind of information will be stored ? 

The inventory is created once you install Database or used for upgrades and patches. Two kind of oracle inventory,  one called central inventory per server and another one called local inventory for each ORACLE_HOME, the difference between each of them central inventory basically contains a high-level list of components installed on the server. It is updated each time components are installed or uninstalled but it does not have detailed information such as the patch level of each ORACLE_HOME.  On another hand  local inventory contains some component information included with patch-level information.

Another difference is the central inventory contains the information related to all Oracle products installed on a host, and consists of a file called inventory.xml, which lists all the Oracle Homes installed on the node.

For local inventory ( $ORACLE_HOME/inventory ) that contains the detail of the patch level for that ORACLE_HOME. The Local Inventory gets updated whenever a patch is applied to the ORACLE_HOME, using OUI. If the Local Inventory becomes corrupt or is lost, this is very difficult to recover, and may result in having to reinstall the ORACLE_HOME and re-apply all patchsets and patches.


To set an editor to edit sqlplus command in buffer

SQL> define_editor=vi


Prompt Current Time and Username (glogin.sql)

cd $ORACLE_HOME/sqlplus/admin
vi glogin.sql
set sqlp "_user>"
set time on
set timing on

--We can also set below commands:

alter session set nls_date_format='DD-MM-YYYY';

set linesize 120;

set pagesize 20;

select 'Welcome, you are connected to '||user||' schema.' "Hello" from dual;

:wq


Creating and deleting an oracle service in windows
oradim -NEW -SID prod -STARTMODE manual

oradim -NEW -SID prod -STARTMODE manual -PFILE "C:\oracle\initprod.ora"

sc delete OracleServiceProd

To know the current ORACLE_SID

SQL> select instance_name from v$instance;
prod



Checking current memory structures (SGA Components)

column component format a30

select component, current_size, min_size, max_size from v$sga_dynamic_components;
    
select name, value from v$pgastat;

select name, value from v$pgastat
where name in('max PGA allocated','total PGA allocated');

select program from v$session order by program;

select program from v$process order by program;

To know how much free memory available in SGA:

select * from v$sgastat where name='free memory';

To see storage structures:

column tablespace_name format a15

select tablespace_name, extent_id, bytes, file_id, block_id, blocks
from dba_extents
where owner='SCOTT' and segment_name='EMP';

select file#, name from v$datafile where file#=4;

select file#, name from v$datafile;


Configuring FRA (Flash / Fast Recovery Area)

Create a folder called "FRA" (it can be any name and in any place in the system).  Once this is done set the below 2 parameters:

ALTER SYSTEM SET db_recovery_file_dest_size=10G SCOPE=BOTH;

ALTER SYSTEM SET db_recovery_file_dest = '/u01/prod/fra' SCOPE=BOTH;

Now all the RMAN backups will go automatically to this FRA.


ACID Concept in databases

ACID refers to the basic properties of a database transaction: 
Atomicity, Consistency, Isolation, and Durability.

Atomicity: The entire sequence of actions must be either completed or aborted. The transaction cannot be partially successful.

Either accept all DML statements or reject all DML statements in a batch. No partial acceptance.

Consistency: The transaction takes the resources from one consistent state to another.

Oracle guarantees for data which is committed and has SCN.

Isolation: A transaction's effect is not visible to other transactions until the transaction is committed.

Durability: Changes made by the committed transaction are permanent and must survive system failure.

Data must be saved once committed.


Setting Oracle Environment Variables

vi /etc/oratab

prod:/u01/app/oracle/product/11.2.0/db_1:N
std:/u01/app/oracle/product/11.2.0/db_1:N

We have 2 databases created with names "prod" and "std".  If we used dbca to create these databases then the above entries will be automatically created in oratab file. 

Now if we want to set the oracle env variables use the below command.

$. oraenv (It asks query and enter the db u want)


To Enable Archivelog

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

Before issuing these commands we must set a init parameter called
LOG_ARCHIVE_DEST_1=(archive logs folder).  This will be the location of the archive log files.

sql> alter system set log_archive_dest_1='location=/u01/prod/arch';

If we don't set this parameter then archive log files will go to $ORACLE_HOME/dbs folder.


Enable Force Logging

In 10g this must be done on mount stage but in 11g it can be done when the db is up and running.

alter database force logging;

To check this change in db:

select force_logging from v$database;


database_properties

select * from database_properties;

Important properties are:

DEFAULT_PERMANENT_TABLESPACE
DEFAULT_TEMP_TABLESPACE
GLOBAL_DB_NAME
NLS_CHARACTERSET
NLS_DATE_FORMAT

select * from database_properties where property_name='GLOBAL_DB_NAME';


Directory Object

How to view existing directory objects ?

SELECT OBJECT_NAME FROM ALL_OBJECTS 
WHERE OBJECT_TYPE='DIRECTORY';

To view directory object and its physical path

SELECT OWNER, DIRECTORY_NAME, DIRECTORY_PATH 
FROM ALL_DIRECTORIES;



Restricted Session 

sql> startup restrict;

sql> alter system disable restricted session;

When restricted session is on, only a selected few users have read only access.

Connecting to Linux from Windows command prompt using psftp:

PSFTP, the PuTTY SFTP client, is a tool for transferring files securely between computers using an SSH connection. 

psftp 192.168.1.125

(enter login credentials for unix)

Once connected we can issue linux commands at psftp prompt.

quit or exit to leave psftp sesson.

at psftp prompt use put command to copy local files to linux

psftp> put filename