Tuesday, August 19, 2014

Trick to trace or Dig export/Datapump slow job ...


Here is some trick to trace or Dig export/Dp slow job ...

For a detailed way to trace the DP processes and see what is going on behind the scenes follow the below steps :

-----------------------
1. Gather statistics for the dictionary and fixed objects. See MOS note:
Note 473423.1 Ext/Pub Poor Performance With DataPump Export on Large Databases

2. We will need to trace Data Pump export this way:

++ before restarting the expdp check if there are orphan Data Pump jobs left in database and remove them.
Use Note 336014.1 - "How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS ?"

++ Add/Replace the following parameters: METRICS=Y TRACE=480300 in expdp command line

3.
After expdp job started:

-- In SQL*Plus, obtain Data Pump process info:
CONNECT / as sysdba

ALTER SYSTEM SET MAX_DUMP_FILE_SIZE=UNLIMITED;

set lines 150 pages 100 numwidth 7
col program for a38
col username for a10
col spid for a7
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "DATE", s.program, s.sid, s.status, s.username, d.job_name, p.spid, s.serial#, p.pid
from v$session s, v$process p, dba_datapump_sessions d
where p.addr=s.paddr and s.saddr=d.saddr;

-- Get the sid and serial# for DM00 and DW00 and execute :
exec DBMS_SYSTEM.SET_EV([SID],[SERIAL#],10046,12 ,''); !!!!!!! Execute this for each process : DM00, DW00

If you reach the point where it is hanging let it for a couple of minutes and kill the process.

++ create standard tkprof output files for Data Pump Master and Worker SQL traces generated in bdump directory. For example:

% tkprof orcl_dm00_17292.trc tkprof_orcl_dm00_17292.out waits=y sort=exeela
% tkprof orcl_dw01_17294.trc tkprof_orcl_dw00_17294.out waits=y sort=exeela

------------------------
Check DM0, DW0 traces the TKPROFs obtained, this will give you the exact SQL that makes datapump to hang. The TKPROF outputs are ordered by the wait time.

Tuesday, August 5, 2014

High level step from upgrading database from 11.2.0.2 to 11.2.0,4 .


Below are simple high level steps  from upgrading database , Using below steps i upgraded 08 Prod database from 11.2.02 to 11.2.0.4 . here my source and target home is Some location .
 
1.- New Oracle home need to be install on server.
2.- run this command in new home  to pre check error
  NEW_ORACLE_HOME/db/rdbms/admin/utlu112i.sql
  
3-check spfile in common location
4-then run this command 
SQL> alter system set cluster_database=false scope=spfile sid='*';
5-Copy pfile in new home
6-export new oracle home :to make sure db home is new home
7- startup upgrade
8-@?/rdbms/admin/catupgrd.sql
9- shutdown immediate
10-startup
11-@?/rdbms/admin/catupgrd.sql
12@?/rdbms/admin/utlrp.sql
13-Change env file new home ~/bin/sid
14-Copy listener and tns new home  , please ignore if  listener is running from GI Home. 
15- copy password file into new HO .
16- now let upgrade new RDBMS Home(from where Oracle running )
srvctl upgrade database -d db_unique_name -o Oracle_home 
 (change above to db_unique_name and Oracle_home)
17- Now lets  make cluster_database=true in parameter file
alter system set cluster_database=true sid='*' scope=spfile; 
18- Change or update new HO in env file and pfile on all rac node.
19 -Using srvctl start your db.
20- check if all component are upgraded to new version.
SELECT comp_name || ' : '||version|| ' : '|| status FROM dba_registry;

Sunday, August 3, 2014

Creating Standby Database using RMAN -

Creating Physical Standby Database using RMAN -

db name is -sync
Primary Db instance/db_unique_name name is  sync
Standby Db instance/db_unique_name name is  syncxx

Some pre_check - please make sure your Primary db is archive log mode , and  forced Logging is enable before moving to next step.

Step_1 -TAKE A LEVEL O BACKUP @Primary database .

run
{
allocate channel d1 device type disk format '/shared/sync/8aug_backup/%U.bkp';
allocate channel d2 device type disk format '/shared/sync/8aug_backup/%U.bkp';
allocate channel d3 device type disk format '/shared/sync/8aug_backup/%U.bkp';
allocate channel d4 device type disk format '/shared/sync/8aug_backup/%U.bkp';
BACKUP INCREMENTAL LEVEL 0 DATABASE;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
}

Step_2 -TAKE CONTROL FILE BACKUP and parameter file for standby db.@Primary database .

 backup current controlfile for standby format '/shared/sync/8aug_backup/CTRL.bck';

Step_3- scp * standby_hostname:/backup/8aug_backup  @Primary database .
---- Come out from Primary db here --- all below command we will run into standby db server.-----

Step_4- Copy the primary database parameter file and make necessary changes :@Standby database .


sync.__db_cache_size=1610612736
sync.__java_pool_size=16777216
sync.__large_pool_size=16777216
sync.__shared_pool_size=452984832
sync.__streams_pool_size=33554432
*.background_dump_dest='/backup/syncxx/bdump/'
*.control_files='/backup/syncxx/data/control01.ctl'
*.core_dump_dest='/backup/syncxx/cdump/'
*.db_block_size=8192
*.db_name='sync'
instance_name = syncxx
service_names = syncxx
REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE
db_unique_name=syncxx
#*log_archive_dest_1='/backup/syncxx/arch'
*.processes=100
*.sessions=100
*.sga_target=2G
*.undo_management='AUTO'
standby_file_management = auto
*.user_dump_dest='/backup/syncxx/udump/'
-- create above folder -- like ( adump,bdump,data etc...)

Step_5 Startup nomount in standby server.@Standby database .

Step_6 Open one more standby db into putty** session and tail alerts logfile of standby db . .and keep eye on standby db .@Standby database
Step_7 restore control_file in standby.@Standby database .
      "restore standby controlfile from '/backup/8aug_backup/CTRL.bck'"

Step_8- Keep database into mount state now@Standby database .
      "Alter database Open"    
Step_9- Catalog the backup pieces@Standby database .
    "CATALOG START WITH '/backup/8aug_backup/';"

Step_10-Restore database in new location.@Standby database .

run
{
SET NEWNAME FOR DATAFILE 1 TO '/backup/syncxx/data/system01.dbf';
SET NEWNAME FOR DATAFILE 2 TO '/backup/syncxx/data/undo01.dbf';
SET NEWNAME FOR DATAFILE 3 TO '/backup/syncxx/data/sysaux01.dbf';
SET NEWNAME FOR DATAFILE 4 TO '/backup/syncxx/data/users.dbf';
SET NEWNAME FOR DATAFILE 5 TO '/backup/syncxx/data/users01.dbf';
SET NEWNAME FOR DATAFILE 6 TO '/backup/syncxx/data/users02.dbf';
SET NEWNAME FOR DATAFILE 7 TO '/backup/syncxx/data/system04.dbf';
SET NEWNAME FOR DATAFILE 8 TO '/backup/syncxx/data/tmp.dbf';
RESTORE DATABASE;
SWITCH DATAFILE ALL;
}

---- Since my db is smalll after taking backup only 25 arch files was created so i am not going to take another L1 backup .
if your db is big and more arch file was created aftre taking backup let's tale another L1 backup and appy that on standby db (via rman recover command)

Here your standby database is created , :) but archive logfile will not apply Automatically  on standby side.

Step_11-Create Listner on standby Db and  add new db detail into both Primary and standby db tns file @Standby database

Step_12-logfile into Primary db and copy passowrd file into standby Db , plz do not forget to change password file name and standb db.and update log_archive_dest_2/DG_CONFIG into Primary db server.@Primary database .

log_archive_dest_2 = 'SERVICE=STANDBY LGWR ASYNC NOAFFIRM'
DG_CONFIG=(syncxx,sync)';
Step_13- Put standby database in recovery mode@Standby database

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Step_14. swicth some arch file mannuly via ("Alter database swicth logilfe")

Step_15- Check if those file are moving are applying to standy db . @Standby database
 a -below step my help you to find standby side arch number -
 1- Archive log list
 2-SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;

after 15- 20 mints compare arch file primary and standby side make sure those are same _
 b- On the primary database issue the following to find out recent archive number .
SELECT THREAD#, MAX(SEQUENCE#)
FROM V$LOG_HISTORY
WHERE RESETLOGS_CHANGE# =
(SELECT RESETLOGS_CHANGE#
FROM V$DATABASE_INCARNATION
WHERE STATUS = 'CURRENT')
GROUP BY THREAD#;
 or -
 Archive log list




Saturday, August 2, 2014

Differential INCREMENTAL and CUMULATIVE INCREMENTAL backup Command

Below command to take Rman different type of backup.

Differential INCREMENTAL level 0 BackuP

run
{
allocate channel d1 device type disk format '/shared/sync/8aug_backup/%U.bkp';
allocate channel d2 device type disk format '/shared/sync/8aug_backup/%U.bkp';
allocate channel d3 device type disk format '/shared/sync/8aug_backup/%U.bkp';
allocate channel d4 device type disk format '/shared/sync/8aug_backup/%U.bkp';
BACKUP INCREMENTAL LEVEL 0 DATABASE;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
}


Differential INCREMENTAL level 1 Backup

run
{
allocate channel d1 device type disk format '/shared/sync/8aug_backup/%U.bkp';
allocate channel d2 device type disk format '/shared/sync/8aug_backup/%U.bkp';
allocate channel d3 device type disk format '/shared/sync/8aug_backup/%U.bkp';
allocate channel d4 device type disk format '/shared/sync/8aug_backup/%U.bkp';
BACKUP INCREMENTAL LEVEL 1 DATABASE;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
}

---CUMULATIVE LEVEL O BACKUP

run
{
allocate channel d1 device type disk format '/shared/sync/8aug_backup/%U.bkp';
allocate channel d2 device type disk format '/shared/sync/8aug_backup/%U.bkp';
allocate channel d3 device type disk format '/shared/sync/8aug_backup/%U.bkp';
allocate channel d4 device type disk format '/shared/sync/8aug_backup/%U.bkp';
INCREMENTAL LEVEL 0 CUMULATIVE DATABASE;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
}

CUMULATIVE LEVEL 1 BACKUP
run
{
allocate channel d1 device type disk format '/shared/sync/8aug_backup/%U.bkp';
allocate channel d2 device type disk format '/shared/sync/8aug_backup/%U.bkp';
allocate channel d3 device type disk format '/shared/sync/8aug_backup/%U.bkp';
allocate channel d4 device type disk format '/shared/sync/8aug_backup/%U.bkp';
INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
}



Monday, July 21, 2014

OCR backup and recovery

OCR calls Oracle Cluster Registry. It stores cluster configuration information.
It is also shared disk component. It must be accessed by all nodes in cluster environment.
It also keeps information of Which database instance run on which nodes and which service runs on which database.The process daemon OCSSd manages the configuration info in OCR and maintains the changes to cluster in the registry.

Automatic backup of OCR is done by CRSD process and every 3 hours.
Default location is CRS_home/cdata/cluster_name. But we can change default location of this backup of OCR.
We can check default location using following command.

$ocrconfig -showbackup

We can change this default location of physical OCR copies using following command.
$ocrconfig -backuploc

We can take export backup of OCR (Oracle Cluster Registry) also in online. but this is only possible if we are using more than 1 OCR files .

ocrconfig -export backup location
We can restore OCR via import command but for that we need to stop cluster .
ocrconfig -import

Oracle takes physical backup of OCR automatically.
No Cluster downtime or RAC database down time requires for PHYSICAL backup of OCR.
No Cluster downtime or RAC database down time requires for MANUAL export backup of OCR.
For recovery of OCR from any of above backup it should need to down ALL.

All procedure requires ROOT login.

Sunday, July 20, 2014

Oracle 11G cluster logfile location

CRS_HOME/log/hostname/crsd/ – The log files for the CRS daemon
CRS_HOME/log/hostname/cssd/ – The log files for the CSS daemon
CRS_HOME/log/hostname/evmd/ – The log files for the EVM daemon
CRS_HOME/log/hostname/client/ – The log files for the Oracle Cluster Registry (OCR)
CRS_HOME/log/hostname/racg/ – The log files for the Oracle RAC high availability component
CRS_HOME/log/hostname/racg – The log files for the Oracle RAC high availability component
CRS_HOME/log/hostanme/alert.log – The alert.log for Clusterware issues.

Convert Non Rac db to RAC DB in 11G

logging in sys user

1-sql >@?/rdbms/admin/catclust.sql   

2-Create undo tablespaces and redo log threads for other instances and enable the newly created threads

joshi1 >CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '+DATA' size 2048M;

Tablespace created

3-joshi1 >alter database add logfile thread 2 group 8 ('+DATA','+DATA') SIZE 105m;

Database altered.

4-joshi1 >alter database add logfile thread 2 group 9 ('+DATA','+DATA') SIZE 105m;

Database altered.

5-joshi1 >alter database enable public thread 2;

Database altered.
6-Shutdown the  instance and add below  RAC parameters to parameter file.

*.cluster_database = TRUE
*.cluster_database_instances = 2
*.undo_management=AUTO
joshi1.undo_tablespace=UNDOTBS
joshi1.instance_name=joshi1
joshi1.instance_number=1
joshi1.thread=1
joshi2.instance_name=joshi2
joshi2.instance_number=2
joshi2.thread=2
joshi2.undo_tablespace=UNDOTBS2
instance_number=2

7-Create  spfile from pfile

joshi1 >create spfile='+DATA' from pfile;

File created.

Start Database using spfile , for spfile location go into asm >'+DATA' >db_unique_name>parameterfile>spfile****

add spfile complete location in pfile and restart database and validate  new spfile location

logging into node 2 and create audit and dignostic dump location .
source instance 2 enviornment file .
add new spfile location into pfile inside $ORACLE_HOME/dbs.


8-Register the all RAC instances and database with crs

srvctl add database -d joshi -o /prod/product/oracle/product/11.2.0.2
srvctl add instance -i joshi1 -d joshi -n Rac1

srvctl add instance -i joshi2 -d joshi -n Rac2

9-Start all instances using srvctl

Rac1[oracle]_joshi1> srvctl start database -d joshi

Rac1[oracle]_joshi1> srvctl status database -d joshi
Instance joshi1 is running on node Rac1
Instance joshi2 is running on node Rac2

10-Configure local  RAC listener using netca

11.    Set  the local listener and remote listener parameters for all instance.

a.    For remote listener parameter ,please specify the scan hostname and scan port number
b.    For local listener, Please virtual IP address as hostname and local listener port number .

alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=XXXXX-VIP)(PORT=1629))))' scope=both sid='joshi1';
alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=XXXXX-VIP)(PORT=1629))))' scope=both sid='joshi2';
alter system set remote_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=XXX_SCAN)(PORT=1628))))' scope=both sid='*';

Manual database creation in oracle 11g

Manual Database creation steps as below.
1-Create environment files on all nodes for all instances..
(target instance name will be normally <sourceinstance_name>n,where n=1,2,.. Node of nodes)

oracle@Rac1[joshi1]> echo $ORACLE_SID
joshi1
oracle@Rac1[joshi1]> echo $ORACLE_HOME
/prod/product/oracle/11.2.0


2- a- Verify exist RAC recource detail.
        crs_stat -t
       
Rac1[oracle]_+ASM1> crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora.ARCH.dg    ora....up.type ONLINE    ONLINE    host...b020
ora.DATA.dg    ora....up.type ONLINE    ONLINE    host...b020
ora....ER.lsnr ora....er.type ONLINE    ONLINE    host...b020
ora....N1.lsnr ora....er.type ONLINE    ONLINE    host...b021
ora....N2.lsnr ora....er.type ONLINE    ONLINE    host...b020
ora....N3.lsnr ora....er.type ONLINE    ONLINE    host...b020
ora....C1.lsnr ora....er.type ONLINE    ONLINE    host...b020
ora....VOTE.dg ora....up.type ONLINE    ONLINE    host...b020
ora.asm        ora.asm.type   ONLINE    ONLINE    host...b020
ora....SM1.asm application    ONLINE    ONLINE    host...b020
ora....20.lsnr application    ONLINE    ONLINE    host...b020
ora....20.lsnr application    ONLINE    ONLINE    host...b020
ora....020.gsd application    OFFLINE   OFFLINE
ora....020.ons application    ONLINE    ONLINE    host...b020
ora....020.vip ora....t1.type ONLINE    ONLINE    host...b020
ora....SM2.asm application    ONLINE    ONLINE    host...b021
ora....21.lsnr application    ONLINE    ONLINE    host...b021
ora....21.lsnr application    ONLINE    ONLINE    host...b021
ora....021.gsd application    OFFLINE   OFFLINE
ora....021.ons application    ONLINE    ONLINE    host...b021
ora....021.vip ora....t1.type ONLINE    ONLINE    host...b021
ora.cvu        ora.cvu.type   ONLINE    ONLINE    host...b020
ora.gsd        ora.gsd.type   OFFLINE   OFFLINE
ora....network ora....rk.type ONLINE    ONLINE    host...b020
ora.oc4j       ora.oc4j.type  ONLINE    ONLINE    host...b020
ora.ons        ora.ons.type   ONLINE    ONLINE    host...b020
ora.scan1.vip  ora....ip.type ONLINE    ONLINE    host...b021
ora.scan2.vip  ora....ip.type ONLINE    ONLINE    host...b020
ora.scan3.vip  ora....ip.type ONLINE    ONLINE    host...b020
ora....arc1.db ora....se.type ONLINE    ONLINE    host...b020
       
   b- check the avilable node name for this cluster.
      olsnodes -n -i -s
   
Rac1[oracle]_+ASM1> olsnodes -n -i -s
Rac1    1       Rac1-vip        Active
Rac2    2       Rac2-vip        Active
   
3-Verify and make sure we are entho space on asm diskgroup.
Rac1[oracle]_+ASM1> asmcmd
ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576     65542    65275                0           65275              0             N  ARCH/
MOUNTED  EXTERN  N         512   4096  1048576    131084    80266                0           80266              0             N  DATA/
MOUNTED  EXTERN  N         512   4096  1048576     17408    17012                0           17012              0             Y  OCR_VOTE/


4-Create the directories specified in environment file on all nodes and for diagnostic_dest and audit_file_dest

5-Set environment to target instance on primary node -- from here we are going to start creating DB.

6-Copy the pfile from  anyof db to the  node, and update value for below database. 
 -Initialization parameter file
db_block_size=8192
cluster_database_instances=2
open_cursors=300
db_name=joshi
db_create_file_dest=+DATA
db_recovery_file_dest_size=2147483648
diagnostic_dest=/data01/app/oracle
processes=500
sga_target=3G
audit_file_dest=/data01/app/oracle/admin/joshi/adump
audit_trail=db
pga_aggregate_target=100M
undo_tablespace=UNDOTBS1
log_archive_dest=+ARCH
db_unique_name=joshi
--Need to change below parameter as per Env.
---diagnostic_dest=---
--audit_file_dest=---
--control_files=+DATA,+DATA 
---log_archive_dest=+ARCH
---db_create_file_dest='+DATA'
-----Above db_unique_name is Very importemnet parameter ASM will create file inside root of data Diskgroup , Carefully setup this parametere value .
7-Start the instance to nomount state
oracle@Rac1[joshi1]> sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Sun Jul 20 13:50:28 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.


SQL*Plus tells us that connected to an idle instance. That means that it is not yet started.
So, lets start the instance. .
SQL> startup nomount
ORACLE instance started.

Total System Global Area 3206836224 bytes
Fixed Size                  2232640 bytes
Variable Size             654315200 bytes
Database Buffers         2499805184 bytes
Redo Buffers               50483200 bytes


CREATE DATABASE joshi
MAXINSTANCES 32
MAXLOGHISTORY 1
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
DATAFILE SIZE 300M AUTOEXTEND ON NEXT  1024M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE SIZE 120M AUTOEXTEND ON NEXT  1024M MAXSIZE UNLIMITED
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE SIZE 20M AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED
SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE SIZE 200M AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITED
DEFAULT TABLESPACE USERS DATAFILE SIZE 100M
CHARACTER SET UTF8
LOGFILE GROUP 1  SIZE 500M,
GROUP 2  SIZE 500M

 -- If something goes wrong with the creation, Oracle will write an error into the alert log.
8. Adding  CONTROL_FILES location in pfile.

set linesize 2048;
column ctl_files NEW_VALUE ctl_files;
select concat('control_files=''', concat(replace(value, ', ', ''','''), '''')) ctl_files from v$parameter where name ='control_files';

control_files='+DATA/joshi/controlfile/current.302.853422869'



Now lets run below script for creating data dictnery tables.

@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catblock.sql
@?/rdbms/admin/catproc.sql

 Run following script as SYSTEM user.

connect system/manager
@?/sqlplus/admin/pupbld.sql
@?/sqlplus/admin/help/hlpbld.sql helpus.sql

!wow here we created DB on ASM file system .

Here we are going to convert db on RAC

logging in sys user

1-sql >@?/rdbms/admin/catclust.sql   

2-Create undo tablespaces and redo log threads for other instances and enable the newly created threads

joshi1 >CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '+DATA' size 2048M;

Tablespace created

3-joshi1 >alter database add logfile thread 2 group 8 ('+DATA','+DATA') SIZE 105m;

Database altered.

4-joshi1 >alter database add logfile thread 2 group 9 ('+DATA','+DATA') SIZE 105m;

Database altered.

5-joshi1 >alter database enable public thread 2;

Database altered.
6-Shutdown the  instance and add below  RAC parameters to parameter file.

*.cluster_database = TRUE
*.cluster_database_instances = 2
*.undo_management=AUTO
joshi1.undo_tablespace=UNDOTBS
joshi1.instance_name=joshi1
joshi1.instance_number=1
joshi1.thread=1
joshi2.instance_name=joshi2
joshi2.instance_number=2
joshi2.thread=2
joshi2.undo_tablespace=UNDOTBS2
instance_number=2

7-Create  spfile from pfile

joshi1 >create spfile='+DATA' from pfile;

File created.

Start Database using spfile , for spfile location go into asm >'+DATA' >db_unique_name>parameterfile>spfile****

add spfile complete location in pfile and restart database and validate  new spfile location

logging into node 2 and create audit and dignostic dump location .
source instance 2 enviornment file .
add new spfile location into pfile inside $ORACLE_HOME/dbs.


8-Register the all RAC instances and database with crs

srvctl add database -d joshi -o /prod/product/oracle/product/11.2.0.2
srvctl add instance -i joshi1 -d joshi -n Rac1

srvctl add instance -i joshi2 -d joshi -n Rac2

9-Start all instances using srvctl

Rac1[oracle]_joshi1> srvctl start database -d joshi

Rac1[oracle]_joshi1> srvctl status database -d joshi
Instance joshi1 is running on node Rac1
Instance joshi2 is running on node Rac2

10-Configure local  RAC listener using netca

11.    Set  the local listener and remote listener parameters for all instance.

a.    For remote listener parameter ,please specify the scan hostname and scan port number
b.    For local listener, Please virtual IP address as hostname and local listener port number .

alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=XXXXX-VIP)(PORT=1629))))' scope=both sid='joshi1';
alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=XXXXX-VIP)(PORT=1629))))' scope=both sid='joshi2';
alter system set remote_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=XXX_SCAN)(PORT=1628))))' scope=both sid='*';

Manual Database creation in ASM file system

Manual Database creation steps as below.

1-Create environment files on all nodes for all instances..
(target instance name will be normally <sourceinstance_name>n,where n=1,2,.. Node of nodes)

oracle@Rac1[joshi1]> echo $ORACLE_SID
joshi1
oracle@Rac1[joshi1]> echo $ORACLE_HOME
/prod/product/oracle/11.2.0


2- a- Verify exist RAC recource detail.
        crs_stat -t
      
Rac1[oracle]_+ASM1> crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora.ARCH.dg    ora....up.type ONLINE    ONLINE    host...b020
ora.DATA.dg    ora....up.type ONLINE    ONLINE    host...b020
ora....ER.lsnr ora....er.type ONLINE    ONLINE    host...b020
ora....N1.lsnr ora....er.type ONLINE    ONLINE    host...b021
ora....N2.lsnr ora....er.type ONLINE    ONLINE    host...b020
ora....N3.lsnr ora....er.type ONLINE    ONLINE    host...b020
ora....C1.lsnr ora....er.type ONLINE    ONLINE    host...b020
ora....VOTE.dg ora....up.type ONLINE    ONLINE    host...b020
ora.asm        ora.asm.type   ONLINE    ONLINE    host...b020
ora....SM1.asm application    ONLINE    ONLINE    host...b020
ora....20.lsnr application    ONLINE    ONLINE    host...b020
ora....20.lsnr application    ONLINE    ONLINE    host...b020
ora....020.gsd application    OFFLINE   OFFLINE
ora....020.ons application    ONLINE    ONLINE    host...b020
ora....020.vip ora....t1.type ONLINE    ONLINE    host...b020
ora....SM2.asm application    ONLINE    ONLINE    host...b021
ora....21.lsnr application    ONLINE    ONLINE    host...b021
ora....21.lsnr application    ONLINE    ONLINE    host...b021
ora....021.gsd application    OFFLINE   OFFLINE
ora....021.ons application    ONLINE    ONLINE    host...b021
ora....021.vip ora....t1.type ONLINE    ONLINE    host...b021
ora.cvu        ora.cvu.type   ONLINE    ONLINE    host...b020
ora.gsd        ora.gsd.type   OFFLINE   OFFLINE
ora....network ora....rk.type ONLINE    ONLINE    host...b020
ora.oc4j       ora.oc4j.type  ONLINE    ONLINE    host...b020
ora.ons        ora.ons.type   ONLINE    ONLINE    host...b020
ora.scan1.vip  ora....ip.type ONLINE    ONLINE    host...b021
ora.scan2.vip  ora....ip.type ONLINE    ONLINE    host...b020
ora.scan3.vip  ora....ip.type ONLINE    ONLINE    host...b020
ora....arc1.db ora....se.type ONLINE    ONLINE    host...b020
       
   b- check the avilable node name for this cluster.
      olsnodes -n -i -s
   
Rac1[oracle]_+ASM1> olsnodes -n -i -s
Rac1    1       Rac1-vip        Active
Rac2    2       Rac2-vip        Active
   
3-Verify and make sure we are entho space on asm diskgroup.
Rac1[oracle]_+ASM1> asmcmd
ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576     65542    65275                0           65275              0             N  ARCH/
MOUNTED  EXTERN  N         512   4096  1048576    131084    80266                0           80266              0             N  DATA/
MOUNTED  EXTERN  N         512   4096  1048576     17408    17012                0           17012              0             Y  OCR_VOTE/


4-Create the directories specified in environment file on all nodes and for diagnostic_dest and audit_file_dest

5-Set environment to target instance on primary node -- from here we are going to start creating DB.

6-Copy the pfile from  anyof db to the  node, and update value for below database. 
 -Initialization parameter file
db_block_size=8192
cluster_database_instances=2
open_cursors=300
db_name=joshi
db_create_file_dest=+DATA
db_recovery_file_dest_size=2147483648
diagnostic_dest=/data01/app/oracle
processes=500
sga_target=3G
audit_file_dest=/data01/app/oracle/admin/joshi/adump
audit_trail=db
pga_aggregate_target=100M
undo_tablespace=UNDOTBS1
log_archive_dest=+ARCH
db_unique_name=joshi
--Need to change below parameter as per Env.
---diagnostic_dest=---
--audit_file_dest=---
--control_files=+DATA,+DATA 
---log_archive_dest=+ARCH
---db_create_file_dest='+DATA'
-----Above db_unique_name is Very importemnet parameter ASM will create file inside root of data Diskgroup , Carefully setup this parametere value .
7-Start the instance to nomount state
oracle@Rac1[joshi1]> sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Sun Jul 20 13:50:28 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.


SQL*Plus tells us that connected to an idle instance. That means that it is not yet started.
So, lets start the instance. .
SQL> startup nomount
ORACLE instance started.

Total System Global Area 3206836224 bytes
Fixed Size                  2232640 bytes
Variable Size             654315200 bytes
Database Buffers         2499805184 bytes
Redo Buffers               50483200 bytes


CREATE DATABASE joshi
MAXINSTANCES 32
MAXLOGHISTORY 1
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
DATAFILE SIZE 300M AUTOEXTEND ON NEXT  1024M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE SIZE 120M AUTOEXTEND ON NEXT  1024M MAXSIZE UNLIMITED
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE SIZE 20M AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED
SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE SIZE 200M AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITED
DEFAULT TABLESPACE USERS DATAFILE SIZE 100M
CHARACTER SET UTF8
LOGFILE GROUP 1  SIZE 500M,
GROUP 2  SIZE 500M

 -- If something goes wrong with the creation, Oracle will write an error into the alert log.
8. Adding  CONTROL_FILES location in pfile.

set linesize 2048;
column ctl_files NEW_VALUE ctl_files;
select concat('control_files=''', concat(replace(value, ', ', ''','''), '''')) ctl_files from v$parameter where name ='control_files';
control_files='+DATA/joshi/controlfile/current.302.853422869'

9-Now lets run below script for creating data dictnery tables.

@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catblock.sql
@?/rdbms/admin/catproc.sql

 Run following script as SYSTEM user.

connect system/manager
@?/sqlplus/admin/pupbld.sql
@?/sqlplus/admin/help/hlpbld.sql helpus.sql

!wow here we created DB on ASM file system .

Saturday, July 19, 2014

11G RAC Commands

11G RAC Commands

Commands                                         Function 

crs_stat:                                           Displays CRS resource status
crsctl check crs                                  Check CRS status
crsctl stop  crs                                  Stops the CRS and all services
crsctl stop resource ora.crsd             Stop resource
crsctl start resource ora.crsd             Start resource
crsctl stop cluster -all                        Stop all cluster
crsctl start cluster -all                        Start all cluster
crsctl check cluster -all                     Check all cluster
crsctl disable crs*                         Prevents CRS from autostarting on server bounce
crsctl enable crs*                         Enables CRS autostart on reboot
crs_stop all                                Stops all registered CRS resources
crs_start all                                Starts all registered resources
srvctl status scan                             SCAN STATUS
ocrcheck                                        To check ocr file
olsnodes -n -p -i                             nodes in the cluster

srvctl stop diskgroup -g data
srvctl stop diskgroup -g backup==Stop diskgroups and ASM instance
srvctl stop asm
srvctl -                                             srvctl help
crvctl -h                                           crsctl help

OPTION                         MEANING
-d                                    Database name
-h                                    Print Usage
-i                                     Comma-separated list of instance names
-n                                     Node names or comma-separated node list
-o                               $ORACLE_HOME to locate lsnrctl (node option) and oracle binaries (other options)
-c                                  Connect string for connecting to the Oracle instance using SQL*Plus
-s                                  servicename


Friday, July 18, 2014

RMAN Commands List

RMAN Commands List

--To Connect in rman tool
rman target/
[oracle@]$ rman target/

Recovery Manager: Release 10.2.0.4.0 - Production on Sat Jul 19 01:20:04 2014

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: CRMPRD (DBID=382866142)

-- Check the RMAN configuration
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/product/10.2.0/db_1/dbs/snapcf_sync.f'; # default

--To check backup detail  :
RMAN> list backup;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
11      Full    163.90M    DISK        00:00:28     07-JUL-14    
        BP Key: 11   Status: AVAILABLE  Compressed: NO  Tag: TAG20140707T003026
        Piece Name: /shared/sync/backup/07july/0epcokp2_1_1.bkp
  List of Datafiles in backup set 11
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  2       Full 716451     07-JUL-14 /shared/sync/data/undo01.dbf
  7       Full 716451     07-JUL-14 /shared/sync/data/system04.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
12      Full    290.83M    DISK        00:00:32     07-JUL-14    
        BP Key: 12   Status: AVAILABLE  Compressed: NO  Tag: TAG20140707T003026
        Piece Name: /shared/sync/backup/07july/0dpcokp2_1_1.bkp

RMAN> list backup summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
11      B  F  A DISK        07-JUL-14       1       1       NO         TAG20140707T003026
12      B  F  A DISK        07-JUL-14       1       1       NO         TAG20140707T003026
13      B  F  A DISK        07-JUL-14       1       1       NO         TAG20140707T003026
14      B  F  A DISK        07-JUL-14       1       1       NO         TAG20140707T003026
15      B  F  A DISK        07-JUL-14       1       1       NO         TAG20140707T003026
16      B  F  A DISK        07-JUL-14       1       1       NO         TAG20140707T003026
17      B  F  A DISK        07-JUL-14       1       1       NO         TAG20140707T004125
18      B  F  A DISK        13-JUL-14       1       1       NO         TAG20140713T071838
19      B  F  A DISK        13-JUL-14       1       1       NO         TAG20140713T071838
20      B  F  A DISK        13-JUL-14       1       1       NO         TAG20140713T071838
21      B  F  A DISK        13-JUL-14       1       1       NO         TAG20140713T071838
22      B  F  A DISK        13-JUL-14       1       1       NO         TAG20140713T071838
23      B  F  A DISK        13-JUL-14       1       1       NO         TAG20140713T071838
24      B  F  A DISK        13-JUL-14       1       1       NO         TAG20140713T115629
25      B  F  A DISK        14-JUL-14       1       1       NO         TAG20140714T021344
26      B  F  A DISK        18-JUL-14       1       1       NO         TAG20140718T091235

-- To check datafile details via rman
RMAN> report schema;

Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    325      SYSTEM               ***     /shared/sync/data/system01.dbf
2    200      UNDOTBS              ***     /shared/sync/data/undo01.dbf
3    400      SYSAUX               ***     /shared/sync/data/sysaux01.dbf
4    200      TBS_1                ***     /shared/sync/data/users.dbf
5    4096     USERS                ***     /shared/sync/data/users01.dbf
6    4096     USERS                ***     /shared/sync/data/users02.dbf
7    500      SYSTEM               ***     /shared/sync/data/system04.dbf
8    2048     TMP                  ***     /shared/sync/data/tmp.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMPTS1              20          /shared/sync/data/temp_tbs.dbf
2    1024     TEMP                 1024        /shared/sync/data/temp01.dbf

Retention poilcy command
For all your daily backups maintain a normal retention policy i.e
Rman > configure retention policy for recovery window of 35 days

--- Taking full db backup if db is no archive mode please run it from mount state.
Backup Commands

run
{
allocate channel d1 device type disk format '/shared/sync/backup/07july/%U.bkp';
allocate channel d2 device type disk format '/shared/sync/backup/07july/%U.bkp';
allocate channel d3 device type disk format '/shared/sync/backup/07july/%U.bkp';
allocate channel d4 device type disk format '/shared/sync/backup/07july/%U.bkp';
BACKUP DATABASE ;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
}
--By default rman will not take arch file and control file backup . in using below signal command we can take all 3 file backup
RMAN> backup database include current controlfile plus archive log;
The above command backs up the target database and the current control file and archive log files
--- Controlfile backup
RMAN> BACKUP CURRENT CONTROLFILE;
-- Here we are going to take  INCREMENTAL LEVEL 0 and 1 backup . by default rman will take  differential backup.we can take this type backup if db is archive log mode only .
RMAN> BACKUP INCREMENTAL LEVEL=0 DATABASE;
RMAN> BACKUP INCREMENTAL LEVEL=1 DATABASE;

-- The below command verifies all file available if any backup file is not available it will make  as unavailable.
RMAN> crosscheck backup;

Recover and restore commands.
RMAN> restore database;
RMAN> recover database

Delete arch file which was created 1 hours before .

DELETE ARCHIVELOG ALL COMPLETED BEFORE "SYSDATE-1/24";

How to create and drop tablespace

How to create New Tablespace

create tablespace users datafile '/shared/sync/data/users_o1.dbf' size 2G;

How to drop tablespace

DROP TABLESPACE users;

How to restore a lost datafile while the database is open

How to restore a lost datafile while the database is open

firstly conncet to the db
sqlplus "/as sysdba"

SQL> select file_name from dba_data_files where TABLESPACE_NAME = 'TMP';

FILE_NAME
--------------------------------------------------------------------------------
/shared/sync/data/tmp.dbf

Of course to restore a tablespace you need to have a valid backup so I'm going to execute a backup tablespace
command for the TMP tablespace using RMAN:

RMAN> backup tablespace TMP;

Starting backup at 18-JUL-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=87 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00008 name=/shared/sync/data/tmp.dbf
channel ORA_DISK_1: starting piece 1 at 18-JUL-14
channel ORA_DISK_1: finished piece 1 at 18-JUL-14
piece handle=/oracle/product/10.2.0/db_1/dbs/0qpdmjg3_1_1 tag=TAG20140718T091235 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 18-JUL-14

--A media failure happened and I've lost all datafiles belonging to TMP tablespace:
rm tmp.dbf
then
rman target/
RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    325      SYSTEM               ***     /shared/sync/data/system01.dbf
2    200      UNDOTBS              ***     /shared/sync/data/undo01.dbf
3    400      SYSAUX               ***     /shared/sync/data/sysaux01.dbf
4    200      TBS_1                ***     /shared/sync/data/users.dbf
5    4096     USERS                ***     /shared/sync/data/users01.dbf
6    4096     USERS                ***     /shared/sync/data/users02.dbf
7    500      SYSTEM               ***     /shared/sync/data/system04.dbf
8    0        TMP                  ***     /shared/sync/data/tmp.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMPTS1              20          /shared/sync/data/temp_tbs.dbf
2    1024     TEMP                 1024        /shared/sync/data/temp01.dbf

Above we can see tmp tablespace size is 0.

Next step is to put those datafiles offline
RMAN> sql 'alter database datafile 8 offline';
Restore  your datafile

RMAN> restore datafile 8;

Starting restore at 18-JUL-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=88 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00008 to /shared/sync/data/tmp.dbf
channel ORA_DISK_1: reading from backup piece /oracle/product/10.2.0/db_1/dbs/0qpdmjg3_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle/product/10.2.0/db_1/dbs/0qpdmjg3_1_1 tag=TAG20140718T091235
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 18-JUL-14

now recover your datafile
RMAN> recover datafile 8;

Starting recover at 18-JUL-14
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:04

Finished recover at 18-JUL-14

Let datafiles be available to all the users, putting them online.
RMAN> sql 'alter database datafile 8 online';

report schema command displays your datafile
RMAN> report schema;

Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    325      SYSTEM               ***     /shared/sync/data/system01.dbf
2    200      UNDOTBS              ***     /shared/sync/data/undo01.dbf
3    400      SYSAUX               ***     /shared/sync/data/sysaux01.dbf
4    200      TBS_1                ***     /shared/sync/data/users.dbf
5    4096     USERS                ***     /shared/sync/data/users01.dbf
6    4096     USERS                ***     /shared/sync/data/users02.dbf
7    500      SYSTEM               ***     /shared/sync/data/system04.dbf
8    2048     TMP                  ***     /shared/sync/data/tmp.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMPTS1              20          /shared/sync/data/temp_tbs.dbf
2    1024     TEMP                 1024        /shared/sync/data/temp01.dbf

Thursday, July 17, 2014

multiplex Control Files in Oracle

How to multiplex Control Files in Oracle

Control File is a physical component of every Oracle Database.Control File includes information like database name,
name and location of data files and redo log files
If the control files are corrupt, can you start your database?
No, you can’t do it. So what should be done to avoid such type of corruption?
As a DBA, it’s your responsibility to multiplex control files to protect your organization in case of possible
data loss due to media failure or control file corruption.
Here below step to multiplexing contol file.

firstly connect to the database.
then see where the exiting control files are located.
SQL> show parameter control_files


NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time     integer 7
control_files     string /shared/sync/data/control02.ct
l, /shared/sync/data/control01
.ctl

check db _name
show parameter db_name

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name     string sync

shutdown the database

then in os level copy the control file in new location
cp /shared/sync/data/control02.ctl /shared/sync/data/control03.ctl

startup nomount

alter system set control_files='/shared/sync/data/control02.ctl','/shared/sync/data/control01.ctl','/shared/sync/data/control03.ctl' 
scope=spfile;
--In above step we added new location of cotrol file in pfile.

shutdwon immediate 
startup
check controlfile

SQL> show parameter control

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time     integer 7
control_files     string /shared/sync/data/control02.ct
l, /shared/sync/data/control01
.ctl, /shared/sync/data/contro
l03.ctl
-- above we are seeing new control file is added on parameter file.





 

Sunday, June 15, 2014

ASH Information from the Data Dictionary Views

Oracle  have couple of views to get that information

1. V$ACTIVE_SESSION_HISTORY
2. DBA_HIST_ACTIVE_SESS_HISTORY

The later is used to store older historical information and not the scope of this post

So if you are interested in looking at waits caused in the database for let say in past 15 minutes, run the script below


SQL> SELECT a.event, sum(a.wait_time + a.time_waited) total_waits
           FROM v$active_session_history a
           WHERE a.sample_time between sysdate-1/24/4 AND sysdate
           GROUP BY a.event
           ORDER BY 2 desc;   2    3    4    5

EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
log file switch (private strand flush incomplete)  1.6297E+10
row cache lock  5340549172
 2955957596
buffer busy waits   912116327
log file sync   592089888
db file sequential read   336323512
free buffer waits   183243839
buffer exterminate   174974945
read by other session    94306477
log file parallel write    68019177
db file scattered read    65003116

EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
log buffer space    58490925
log file switch completion    13633690
control file parallel write     4929383
log file sequential read     4043063
enq: CF - contention     3171815
enq: TX - index contention      631448
log file single write      170984
cursor: pin S wait on X      136428
latch: cache buffers chains 707
SQL*Net message to client   4
null event   0

Issue the following query to get more session specific information that were using the most CPU in last 30 minutes

SQL> SELECT * FROM
           (
           SELECT s.username, s.module, s.sid, s.serial#, s.sql_id,count(*)
           FROM v$active_session_history h, v$session s
           WHERE h.session_id = s.sid
           AND   h.session_serial# = s.serial#
           AND   session_state= 'ON CPU' AND
           sample_time > sysdate - interval '30' minute
           GROUP BY s.username, s.module, s.sid, s.serial#,s.sql_id
           ORDER BY count(*) desc
            )
           where rownum <= 5;

USERNAME       MODULE       SID    SERIAL# SQL_ID      COUNT(*)
------------------------------ ------------------------------------------------ ---------- ---------- ------------- ----------
XXX       javaw.exe       872 44013 0ncs9wftnk030   637
XXX       javaw.exe       679 25937 27rsanhkvzbyv   504
         1

Now to find out the SQL text of the query causing the high CPU by providing SQL_ID from above, issue the following statement

SQL> SELECT SQL_TEXT FROM V$SQL
           WHERE sql_id = '0ncs9wftnk030';
 
  SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
UPDATE image  SET count = count+ :1  WHERE CC_id = :2 AND Image_Id = :3
UPDATE image  SET count = count+ :1  WHERE CC_id = :2 AND Image_Id = :3

Thursday, February 27, 2014

Rman backup type

RMAN is very flexible and it offers many different types of backups.  We need to start with a list of backup types:

Full backup:  A full backup backs up all data files in the database, block-by-block, a standalone backup with you need to recover to the point in time when the full backup was collected.

 Level 1 backup:  A level 1 backup includes only those blocks that have been changed since the  last backup taken . Remember a last  backup may be either a level 0 or a level 1 backup.

·Level 0 backup:  A level 0 incremental backup is physically identical to a full backup and it includes every data  block in the file except empty blocks. The only difference is that the level 0 backup is recorded as an incremental backup in the RMAN repository, so it can be used as the parent for a level 1 backup.

Incremental backup:  An incremental backup can be either level 0 or level 1.
There are two types of incremental backups, “differential” and “cumulative”.
Differential vs. incremental cumulative backups.

Differential Incremental Backup:  When using differential incremental backup (the default type of incremental backup), RMAN looks for changed data blocks which were changed after last level 1 incremental backup. It there’s no level 1 backup made before it, it takes a backup of the changed data blocks which were made after level 0 incremental backup.

Cumulative Incremental Backup:  In a cumulative incremental backup RMAN takes backup of all changed data blocks after level 0 or level 1 incremental backup.  Like a differential backup, incremental backups also back up only the changed  data blocks, but an incremental backup only backs up the data that has changed since the last backup.  If the last backup was  also an incremental backup, the current incremental backups only records “changes to the changes”, a much smaller set of block  changes, and hence, a much smaller recovery time than a differential backup

VI editor in Linux

There are many ways to edit files in Unix and for me one of the best ways is using screen-oriented text editor vi.
This editor enable you to edit lines in context with other lines in the file.

Now a days you would find an improved version of vi editor which is called VIM. Here VIM stands for Vi Improved.
Its usually available on all the flavors of Unix system.
Its implementations are very similar across the board.
It requires very few resources.
It is more user friendly than any other editors like ed or ex.

Moving within a File:
To move around within a file without affecting your text, you must be in command mode (press Esc twice). Here are some of the commands you can use to move around one character at a time:

Command Description
k Moves the cursor up one line.
j Moves the cursor down one line.
h Moves the cursor to the left one character position.
l Moves the cursor to the right one character position.
There are following two important points to be noted:

The vi is case-sensitive, so you need to pay special attention to capitalization when using commands.

Most commands in vi can be prefaced by the number of times you want the action to occur. For example, 2j moves cursor two lines down the cursor location.

There are many other ways to move within a file in vi. Remember that you must be in command mode (press Esc twice). Here are some more commands you can use to move around the file:

Command Description
0 or | Positions cursor at beginning of line.
$ Positions cursor at end of line.
w Positions cursor to the next word.
b Positions cursor to previous word.
( Positions cursor to beginning of current sentence.
) Positions cursor to beginning of next sentence.
E Move to the end of Blank delimited word
{ Move a paragraph back
} Move a paragraph forward
[[ Move a section back
]] Move a section forward
n| Moves to the column n in the current line
1G Move to the first line of the file
G Move to the last line of the file
nG Move to nth line of the file
:n Move to nth line of the file
fc Move forward to c
Fc Move back to c
H Move to top of screen
nH Moves to nth line from the top of the screen
M Move to middle of screen
L Move to botton of screen
nL Moves to nth line from the bottom of the screen
:x Colon followed by a number would position the cursor on line number represented by x
Control Commands:
There are following useful command which you can use along with Control Key:

Command Description
CTRL+d Move forward 1/2 screen
CTRL+d Move forward 1/2 screen
CTRL+f Move forward one full screen
CTRL+u Move backward 1/2 screen
CTRL+b Move backward one full screen
CTRL+e Moves screen up one line
CTRL+y Moves screen down one line
CTRL+u Moves screen up 1/2 page
CTRL+d Moves screen down 1/2 page
CTRL+b Moves screen up one page
CTRL+f Moves screen down one page
CTRL+I Redraws screen
Editing Files:
To edit the file, you need to be in the insert mode. There are many ways to enter insert mode from the command mode:


Command Description
i Inserts text before current cursor location.
I Inserts text at beginning of current line.
a Inserts text after current cursor location.
A Inserts text at end of current line.
o Creates a new line for text entry below cursor location.
O Creates a new line for text entry above cursor location.
Deleting Characters:
Here is the list of important commands which can be used to delete characters and lines in an opened file:

Command Description
x Deletes the character under the cursor location.
X Deletes the character before the cursor location.
dw Deletes from the current cursor location to the next word.
d^ Deletes from current cursor position to the beginning of the line.
d$ Deletes from current cursor position to the end of the line.
D Deletes from the cursor position to the end of the current line.
dd Deletes the line the cursor is on.
As mentioned above, most commands in vi can be prefaced by the number of times you want the action to occur. For example, 2x deletes two character under the cursor location and 2dd deletes two lines the cursor is on.

I would highly recommend to exercise all the above commands properly before proceeding further.

Change Commands:
You also have the capability to change characters, words, or lines in vi without deleting them. Here are the relevant commands:

Command Description
cc Removes contents of the line, leaving you in insert mode.
cw Changes the word the cursor is on from the cursor to the lowercase w end of the word.
r Replaces the character under the cursor. vi returns to command mode after the replacement is entered.
R Overwrites multiple characters beginning with the character currently under the cursor. You must use Esc to stop the overwriting.
s Replaces the current character with the character you type. Afterward, you are left in insert mode.
S Deletes the line the cursor is on and replaces with new text. After the new text is entered, vi remains in insert mode.
Copy and Past Commands:
You can copy lines or words from one place and then you can past them at another place using following commands:

Command Description
yy Copies the current line.
yw Copies the current word from the character the lowercase w cursor is on until the end of the word.
p Puts the copied text after the cursor.
P Puts the yanked text before the cursor.
Advanced Commands:
There are some advanced commands that simplify day-to-day editing and allow for more efficient use of vi:

Command Description
J Join the current line with the next one. A count joins that many lines.
<< Shifts the current line to the left by one shift width.
>> Shifts the current line to the right by one shift width.
~ Switch the case of the character under the cursor.
^G Press CNTRL and G keys at the same time to show the current filename and the status.
U Restore the current line to the state it was in before the cursor entered the line.
u Undo the last change to the file. Typing 'u' again will re-do the change.
J Join the current line with the next one. A count joins that many lines.
:f Displays current position in the file in % and file name, total number of file.
:f filename Renames current file to filename.
:w filename Write to file filename.
:e filename Opens another file with filename.
:cd dirname Changes current working directory to dirname.
:e # Use to toggle between two opened files.
:n In case you open multiple files using vi, use :n to go to next file in the series.
:p In case you open multiple files using vi, use :p to go to previous file in the series.
:N In case you open multiple files using vi, use :N to go to previous file in the series.
:r file Reads file and inserts it after current line
:nr file Reads file and inserts it after line n.

You can use vi editor to edit an existing file or to create a new file from scratch. You can also use this editor to just read
a text file

Ex mode commands:
  q       Quit                        set <x>   Enable option
  q!      Quit, discard changes       set no<v> Disable option
  r <f>   Read in file <f>            set all   Show all options
  sh      Invoke shell
  vi      Vi mode
  wq      Write and quit
  w <f>   Write file <f>
  w! <f>  Overwrite file <f>

Tuesday, February 18, 2014

Linux command part 2

1. echo: Print message on the terminal
  echo “<message>”
$ echo "Welcome to the workshop"
Welcome to the workshop                 

2-cat: command is used to display the contents of a small file
on terminal

3-gzip: command is used to compress the file, and gunzip is
used to de-compress it.
 usage: gzip <file name>

4-gunzip :The compression ratio depends on the type, size and
nature of the file
 usage: gunzip <file name with.gz>

5- head: command displays the top of the file, when used
without any option it will display first 10 lines of the file

6 tail-Similarly tail command displays the end of the file. By
default it will display last 10 lines of the file

7-exit-To leave the remote computer.

8-rmdir-Delete a empty folder.

9-ps-ps shows you your running jobs.

10-vim – Vi Improved, a programmers text editor   

11- cp – copy files and directories

12-find – search for files in a directory hierarchy

13-history – prints recently used commands  

14-grep ­ print lines matching a pattern   

15-wc ­ print the number of newlines, words, and bytes in files

16-sort – sort lines of text files   

17- chmod – change file access permissions

18-chown – change file owner and group  

19 su –  change user ID or become super­user

20-passwd – update  a user’s authentication tokens(s)

21-who – show who is logged on  

22-kill – to kill a process(using signal mechanism)

23- ssh – SSH client (remote login program)                                    
    “ssh is a program for logging into a remote machine and for         
   executing commands on a remote machine
24-scp – secure copy (remote file copy program)                            
          “scp copies files between hosts on a network” 
25-fdisk – partition manipulator

26-mount – mount a file system                                                  
    Usage: mount ­t type device dir   

27-umount – unmount file systems                                             
    Usage: umount [OPTIONS] dir | device

28- du – estimate file space usage                                                           
     Usage:  du [OPTION]... [FILE]..

29-df – report filesystem disk space usage                                            
    Usage: df [OPTION]... [FILE]... 

30-reboot – reboot the system   

31-• poweroff – power off the system   

32-locate – find or locate a file

33-kate – KDE Advanced Text Editor  

34-• gedit ­ A text Editor. Used to create and edit files.
35-jobs – displays the names and ids of background jobs  


How to create user in MY SQL

Create  a new MySQL user Account mysql > CREATE USER ' newuser '@'localhost' IDENTIFIED BY ' password '...