Monday, 16 May 2016

Connecting to Another Schema using a Proxy Connection



In certain situations the Oracle DBA still needs to connect to the database using application schemas.  activities such as adding or dropping database links , execution of query etc.

One common workaround that DBAs have commonly used involves temporarily changing the user password, connecting, and then changing the password back using the previously recorded password hash.  However there are numerous problems with this approach:
1.             The schema may be locked
2.             The password may be controlled by a PROFILE that may also need to be adjusted
3.             Account intrusion detection tools may detect the connection
4.             The connection may not be properly audited via Oracle or external autditing tools
5.             The application may unsuccessfully try to connect while the password is temporarily changed causing an application failure!


Solution
The proxy connection allows the user to connect to another schema using their own username and password.  The schema being proxied is simply provided in square brackets. proxy connection can be made the GRANT CONNECT THROUGH privilege is required.

The following example illustrates how a proxy connection is configured and used:
in my e.g i am creating one user with name  admin who want to access all object of scott user by logging admin ID with his own password.
SQL>
SQL> create user admin identified by admin;    
User created.
SQL> grant create session to admin;
Grant succeeded.
SQL> conn admin[scott]/admin  //method to login with proxy account in [] you have to pass the user ID.
ERROR:
ORA-28150: proxy not authorized to connect as client
Warning: You are no longer connected to ORACLE.

SQL> conn /as sysdba
Connected.

SQL> alter user scott grant connect through admin;
User altered.

SQL> conn admin[scott]/admin   //pass the schema name while logging with your ID.
Connected.

SQL> show user                 //now u ca see even u have pass the credentials for admin but showing as scott
USER is "SCOTT"

SQL> select * from tab;                 //can see all the table of scott
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE 
                      TABLE
SQL> select count(1) from emp;  //can query to those table also.
  COUNT(1)
----------
        14

SQL>

Friday, 13 May 2016

Online rename and relocation of an online data file on DG environment.

 Now no need to perform number of steps to move or renaming of datafile. with 12c you can move the datafile online simply using ALTER DATABASE MOVE DATAFILE command ,while the data is being transferred, the user can perform DML and DDL tasks and you may can do migration from non-ASM to ASM and vice versa.  
Rename a data file:
SQL> select banner from v$version where rownum<=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

//Select the DB role

SQL> select database_role  from v$database;
DATABASE_ROLE
----------------
PRIMARY

//get the file location which you want to move as in my e.g i am going to perform on users datafile .

SQL> select name from  v$datafile where file#=6;
NAME
--------------------------------------------------
C:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF

//Check the max sequence number of primary DB

SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
            22
// connect to standby 

QL> conn sys/xxxx@standby as sysdba
Connected.

//Check the applied sequence number .

SQL> select max(sequence#),applied from v$archived_log group by applied;
MAX(SEQUENCE#) APPLIED
-------------- ---------
            22 YES
//check the file location of db file 6 as in my case its on same path.

SQL> select name from  v$datafile where file#=6;
NAME
--------------------------------------------------
C:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF

// now login on primary and perform the movement operation.

SQL> conn sys/xxxx@primary as sysdba
Connected.

// performning he movement command .

SQL> alter database move datafile 'C:\app\Administrator\oradata\orcl\USERS01.DBF
' to 'C:\APP\ADMINISTRATOR\ORADATA\users01.dbf';
Database altered.

//as we can see file got change to its parent folder from oradata/orcl to oradata.

SQL> select name from  v$datafile where file#=6;
NAME
--------------------------------------------------
C:\APP\ADMINISTRATOR\ORADATA\USERS01.DBF

//Check the max sequence number of primary DB

SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
           22
// not switch the logfile to check the synch status.
SQL> alter system switch logfile;
System altered.

SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
            23
//login to standby and see the synch status and file path at standby server .

SQL> conn sys/xxxx@standby as sysdba
Connected.

SQL> select name from  v$datafile where file#=6;
NAME
--------------------------------------------------
C:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF

SQL> select max(sequence#),applied from v$archived_log group by applied;
MAX(SEQUENCE#) APPLIED
-------------- ---------
            23 NO
            22 YES
SQL> select max(sequence#),applied from v$archived_log group by applied;
MAX(SEQUENCE#) APPLIED
-------------- ---------
            23 YES
//As we can see DB got synced but file path is same this is new things in 12c, physical file movement wont go from redo you have to perform MOVE DATAFILE command at standby also. 

SQL> select name from  v$datafile where file#=6;
NAME
--------------------------------------------------
C:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF
SQL>

Now if you want to move the file at standby also need to perform move datafile command at standby db.
========================Moving the file at stanbdy====================
SQL> alter database move datafile 'C:\app\Administrator\oradata\orcl\USERS01.DBF
 ' to 'C:\APP\ADMINISTRATOR\ORADATA\users01.dbf';
alter database move datafile 'C:\app\Administrator\oradata\orcl\USERS01.DBF ' to
 'C:\APP\ADMINISTRATOR\ORADATA\users01.dbf'
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files

//As MRP is running need to stop the same.

SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database move datafile 'C:\app\Administrator\oradata\orcl\USERS01.DBF
 ' to 'C:\APP\ADMINISTRATOR\ORADATA\users01.dbf';

Database altered.

// now users db file on both site are on same path.

//Below few more task(12c New feature ) you may can perform on-line if required  

Migrate a data file from non-ASM to ASM:

SQL> ALTER DATABASE MOVE DATAFILE '/u01/data/users_01.dbf' TO '+DG_DATA';

Migrate a data file from one ASM disk group to another:

SQL> ALTER DATABASE MOVE DATAFILE '+DG_DATA/DBNAME/DATAFILE/users_01.dbf ' TO '+DG_DATA_NEW';

Overwrite the data file with the same name, if it exists at the new location:

SQL> ALTER DATABASE MOVE DATAFILE '/u01/data/users_01.dbf' TO '/u01/data_new/users_01.dbf' REUSE;


Copy the file to a new location whilst retaining the old copy in the old location:

SQL> ALTER DATABASE MOVE DATAFILE '/u00/data/users_01.dbf' TO '/u00/data_new/users_01.dbf' KEEP;

You can monitor the progress while a data file being moved by querying the v$session_longops dynamic view. Additionally, you can also refer the alert.log of the database where Oracle writes the details about action being taken place.

Wednesday, 23 March 2016

Apply the patch on HA(AlwaysON DB 2012)

Apply the patch on HA(AlwaysON DB 2012)


My test environment consists of two SQL Server 2012 SP1 servers named Primary (principal) and  standby (mirror) that contain one mirrored databases and I'm upgrading to SP2.

Here is a look at the two mirrored instances.

Dashboard report from Primary instance:
Step 1
Always backup all system and user databases before applying patches.
Step 2
Remote Desktop into the "Mirror" server (standby in our example) and download/copy the patch to the server.
Run the query select @@version to get the service pack details as we can see current version is 2012 with sp1 of standby instance.

Step 3
Stop all SQL Services on the "Mirror" server.

Step 4
Run the patch on the "Mirror" server as run as administrator option.
Step 5 : all the component verification passed  click on next
Step 6 : accept the license terms and click on next.
Step 7: Select the feature which you want to get upgrade in my case i want all and click on next

Step 8: selected service will be checked at this stage will fail if any service is in use in my case all service are stop so all passed click on next

Step 9: Here are you ready to update click on upgrade
 Step 10: this process will take time as all component are being upgrade .
Step 11: All the component got upgrade can close the window.

Once the patch is complete, reboot the "Mirror" server.

Step 12: Check the Always on dashboard report and can see DB got again synched if not much change happen on primary during upgrade activity else db will be in recovery mode until recovery not get complete after recovery db mode will be synchronized. 
Step 13:Can see now DB got upgraded to SP2 using
select @@version             


SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
Now STANBDY got upgraded to sp2.

Step 14: Login to the primary instance  check the dashboard report can see both site are in synched.
Step 15: do the failover for Primary to standby ...right click on Availability group and select the failover option.

Step 16: perform the failover click on next
Step 17: Select the replica to which you want to make as active (new primary) click on next.


step 18: click on connect option
Step 19: will get the login prompt select the correct instance and click on connect option.
Step 20: Will get the window as below and click on next option.
Step 21:Verify the details like which server is current primary and standby click on finish for failover.

Step 22:will take few min to get complete then close the window now standby instance become active instance and primary(old) instance will become passive instance which we can proceed for upgrade.
Step 23: can see in always on Availability group showing as secondary
Step 24: Stop all SQL Services on the primary(old) instance.
Step 25: Run the installer as run as administrator option.

Step 26: accept the license terms and click on next.
 Step 27: Select the feature which you want to get upgrade in my case i want all and click on next
Step 28: selected service will be checked at this stage will fail if any service is in use in my case all service are stop so all passed click on next
Step 29: Here are you ready to update click on upgrade
Step 30:this process will take time as all component are being upgrade .
Step 31: All the component got upgrade can close the window.
Step 32: Once the patch is complete, reboot the server.

Step 33: Check the Always on dashboard report and can see DB got again synched if not much change happen on primary during upgrade activity else db will be in recovery mode until recovery not get complete after recovery db mode will be synchronized.
and we can see DB got upgraded to SP2 and primary AlwaysON HA availability group showing as secondary you do failover again to make ..Primary instance as primary and standby as secondary .  
Do test in you test environment before apply on production , please let us know if facing any issue. 




Friday, 17 July 2015

WORKLOAD REPOSITORY COMPARE PERIOD REPORT

Mostly DBA are getting the SR that on some specific day DB was running fine and today it's not
to overcome this issue we don't have worry we can generate the AWR-COMPARE report by providing the snap value as we do for normal AWR report but in this we have to give two range 1st pair of 1st day and 2nd pair of 2nd day snapshots after that oracle will take care to generate the COMPARE awr report  ...use below mentioned step


SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL>--call to awrddrpti.sq sql from $ORACLE_HOME/rdbms/admin/awrddrpti.sql
SQL> @C:\app\sqladmin\product\12.1.0\dbhome_1\RDBMS\ADMIN\awrddrpt.sql

Current Instance
~~~~~~~~~~~~~~~~

   DB Id       DB Id    DB Name      Inst Num Inst Num Instance
----------- ----------- ------------ -------- -------- ------------
 1409205695  1409205695 ORCL                1        1 orcl


Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: html                          ======>type the HTML to get the report in HTML

Type Specified:  html


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 1409205695        1 ORCL         orcl         ALWAYSON1
* 1409205695        1 ORCL         orcl         NODE4

Database Id and Instance Number for the First Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Using 1409205695 for Database Id for the first pair of snapshots
Using          1 for Instance Number for the first pair of snapshots


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.


Enter value for num_days: 5                                  ==> give the number days to display the snap range

Listing the last 5 days of Completed Snapshots

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
orcl         ORCL                 1 03 Jun 2015 17:00      1

                                  2 04 Jun 2015 14:34      1

                                  3 04 Jun 2015 16:00      1
                                  4 04 Jun 2015 17:00      1
                                  5 04 Jun 2015 18:00      1
                                  6 04 Jun 2015 19:00      1



Specify the First Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 3                            ===>Enter the begin snap of 1 day since when started issue
First Begin Snapshot Id specified: 3

Enter value for end_snap: 4                               ===>Enter the end snap of 1 day when perf was acceptable
First End   Snapshot Id specified: 4




Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 1409205695        1 ORCL         orcl         ALWAYSON1
* 1409205695        1 ORCL         orcl         NODE4




Database Id and Instance Number for the Second Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Using 1409205695 for Database Id for the second pair of snapshots
Using          1 for Instance Number for the second pair of snapshots


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.


Enter value for num_days2: 5                                     ====> Enter the days number to display the snap value if suppose before 5th days issue was ok then in 1st pair give 5 to get the 1st day value and in 2nd pair you have to give 5 gain to get the value of 5th day.

Listing the last 5 days of Completed Snapshots

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
orcl         ORCL                 1 03 Jun 2015 17:00      1

                                  2 04 Jun 2015 14:34      1

                                  3 04 Jun 2015 16:00      1
                                  4 04 Jun 2015 17:00      1
                                  5 04 Jun 2015 18:00      1
                                  6 04 Jun 2015 19:00      1



Specify the Second Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap2: 5                          ===>give the begin snap of 5th day when issue started
Second Begin Snapshot Id specified: 5

Enter value for end_snap2: 6
Second End   Snapshot Id specified: 6               ===>give the end snap of 5th day when issue was OK



Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrdiff_1_3_1_5.html  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name: awr_dif.html     ===>Specify the report name with full path eg. c:\myreport.html(windows) or /u01/oracle/myreport.html(unix)

Same you can generate from SQL statement.

select snap_id, end_interval_time        ====> use this query to get the snap vallue
from dba_hist_snapshot
where end_interval_time > trunc(sysdate-1)
order by snap_id;

======> Use this query to generate the report  1st value is DBid and 2nd instance number and 3rd is begin snap and 4th is end snap of 1st pair and 5th is dbid and 6th is inst number 7th being snap of 1st pair and 8th is end snap...
select * from TABLE(DBMS_WORKLOAD_REPOSITORY.awr_diff_report_html(4034329550,1,8947,8951,
                                                                  4034329550,1,8971,8975));

Enjoy with Oracle.

Thursday, 4 June 2015

Oracle Critical Patch Update On 12.1.0.1

April 2015 Oracle Critical Patch Update On 12.1.0.1

1st Check the conflict against OH and among patches and check for applicable with point 1,2,3. 
1)opatch prereq CheckConflictAgainstOHWithDetail -oh C:\app\sqladmin\product\12.1.0\dbhome_1 -phBaseDir c:\20558101
2)opatch prereq CheckConflictAmongPatches -oh C:\app\sqladmin\product\12.1.0\dbhome_1 -phBaseDir c:\20558101
3)opatch prereq CheckApplicable -oh C:\app\sqladmin\product\12.1.0\dbhome_1 -phBaseDir c:\20558101

Apply the patch on RDBMS home.

opatch napply -local -oh C:\app\sqladmin\product\12.1.0\dbhome_1 -id 20558101 -phBaseDir c:\20558101
=========================================================
C:\app\sqladmin\product\12.1.0\dbhome_1\OPatch>opatch lsinv
Oracle Interim Patch Installer version 12.1.0.1.0
Copyright (c) 2012, Oracle Corporation.  All rights reserved.


Oracle Home       : C:\app\sqladmin\product\121~1.0\dbhome_1
Central Inventory : C:\Program Files\Oracle\Inventory
   from           : n/a
OPatch version    : 12.1.0.1.0
OUI version       : 12.1.0.1.0
Log file location : C:\app\sqladmin\product\121~1.0\dbhome_1\cfgtoollogs\opatch\
opatch2015-06-04_14-45-02PM_1.log

Lsinventory Output file location : C:\app\sqladmin\product\121~1.0\dbhome_1\cfgt
oollogs\opatch\lsinv\lsinventory2015-06-04_14-45-02PM.txt

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

Installed Top-level Products (1):

Oracle Database 12c                                                  12.1.0.1.0
There are 1 products installed in this Oracle Home.


There are no Interim patches installed in this Oracle Home.


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


OPatch succeeded.

C:\app\sqladmin\product\12.1.0\dbhome_1\OPatch>
C:\app\sqladmin\product\12.1.0\dbhome_1\OPatch>
=====================================================
C:\app\sqladmin\product\12.1.0\dbhome_1\OPatch>opatch prereq CheckConflictAgainstOHWithDetail -oh C:\app\sqladmin\product\12.1.0\dbhome_1 -phBaseDir c:\20558101
Oracle Interim Patch Installer version 12.1.0.1.0
Copyright (c) 2012, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : C:\app\sqladmin\product\12.1.0\dbhome_1
Central Inventory : C:\Program Files\Oracle\Inventory
   from           : n/a
OPatch version    : 12.1.0.1.0
OUI version       : 12.1.0.1.0
Log file location : C:\app\sqladmin\product\12.1.0\dbhome_1\cfgtoollogs\opatch\opatch2015-06-04_14-48-39PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.
=============================================
C:\app\sqladmin\product\12.1.0\dbhome_1\OPatch>opatch prereq CheckConflictAmongPatches -oh C:\app\sqladmin\product\12.1.0\dbhome_1 -phBaseDir c:\20558101
Oracle Interim Patch Installer version 12.1.0.1.0
Copyright (c) 2012, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : C:\app\sqladmin\product\12.1.0\dbhome_1
Central Inventory : C:\Program Files\Oracle\Inventory
   from           : n/a
OPatch version    : 12.1.0.1.0
OUI version       : 12.1.0.1.0
Log file location : C:\app\sqladmin\product\12.1.0\dbhome_1\cfgtoollogs\opatch\opatch2015-06-04_14-48-55PM_1.log

Invoking prereq "checkconflictamongpatches"

Prereq "checkConflictAmongPatches" passed.

OPatch succeeded.

C:\app\sqladmin\product\12.1.0\dbhome_1\OPatch>opatch prereq CheckApplicable -oh C:\app\sqladmin\product\12.1.0\dbhome_1 -phBaseDir c:\20558101

Oracle Interim Patch Installer version 12.1.0.1.0
Copyright (c) 2012, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : C:\app\sqladmin\product\12.1.0\dbhome_1
Central Inventory : C:\Program Files\Oracle\Inventory
   from           : n/a
OPatch version    : 12.1.0.1.0
OUI version       : 12.1.0.1.0
Log file location : C:\app\sqladmin\product\12.1.0\dbhome_1\cfgtoollogs\opatch\opatch2015-06-04_14-49-07PM_1.log

Invoking prereq "checkapplicable"
Patch 20558101: Optional component(s) missing : [ oracle.rdbms.ic, 12.1.0.1.0 ]
, [ oracle.has.cfs, 12.1.0.1.0 ] , [ oracle.has.crs, 12.1.0.1.0 ] , [ oracle.has
.cvu, 12.1.0.1.0 ] , [ oracle.usm, 12.1.0.1.0 ]

Prereq "checkApplicable" for patch 20558101 passed.

OPatch succeeded.

C:\app\sqladmin\product\12.1.0\dbhome_1\OPatch>
C:\app\sqladmin\product\12.1.0\dbhome_1\OPatch>opatch napply -local -oh C:\app\sqladmin\product\12.1.0\dbhome_1 -id 20558101 -phBaseDir c:\20558101

Oracle Interim Patch Installer version 12.1.0.1.0
Copyright (c) 2012, Oracle Corporation.  All rights reserved.

Oracle Home       : C:\app\sqladmin\product\12.1.0\dbhome_1
Central Inventory : C:\Program Files\Oracle\Inventory
   from           : n/a
OPatch version    : 12.1.0.1.0
OUI version       : 12.1.0.1.0
Log file location : C:\app\sqladmin\product\12.1.0\dbhome_1\cfgtoollogs\opatch\opatch2015-06-04_15-35-51PM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   20558101

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = 'C:\app\sqladmin\product\12.1.0\dbhome_1')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '20558101' to OH 'C:\app\sqladmin\product\12.1.0\dbhome_1'
ApplySession: Optional component(s) [ oracle.rdbms.ic, 12.1.0.1.0 ] , [ oracle.h
as.cfs, 12.1.0.1.0 ] , [ oracle.has.crs, 12.1.0.1.0 ] , [ oracle.has.cvu, 12.1.0
.1.0 ] , [ oracle.usm, 12.1.0.1.0 ]  not present in the Oracle Home or a higher
version is found.

Patching component oracle.rdbms, 12.1.0.1.0...

Patching component oracle.rdbms.deconfig, 12.1.0.1.0...

Patching component oracle.rdbms.dbscripts, 12.1.0.1.0...

Patching component oracle.rdbms.rsf, 12.1.0.1.0...

Patching component oracle.ldap.rsf, 12.1.0.1.0...

Patching component oracle.ldap.rsf.ic, 12.1.0.1.0...

Patching component oracle.rdbms.util, 12.1.0.1.0...

Patching component oracle.ordim.client, 12.1.0.1.0...

Patching component oracle.ordim.server, 12.1.0.1.0...

Patching component oracle.rdbms.rman, 12.1.0.1.0...

Patching component oracle.network.rsf, 12.1.0.1.0...

Patching component oracle.xdk.rsf, 12.1.0.1.0...

Patching component oracle.xdk, 12.1.0.1.0...

Patching component oracle.nlsrtl.rsf, 12.1.0.1.0...

Patching component oracle.sdo.locator.jrf, 12.1.0.1.0...

Patching component oracle.sdo.locator, 12.1.0.1.0...

Patching component oracle.odbc.ic, 12.1.0.1.0...

Patching component oracle.precomp.common, 12.1.0.1.0...

Patching component oracle.rdbms.plsql, 12.1.0.1.0...

Patching component oracle.network.client, 12.1.0.1.0...

Patching component oracle.oraolap, 12.1.0.1.0...

Patching component oracle.rdbms.install.common, 12.1.0.1.0...

Patching component oracle.rdbms.rsf.ic, 12.1.0.1.0...

Patching component oracle.swd.oui.core.min, 12.1.0.1.0...

Patching component oracle.precomp.common.core, 12.1.0.1.0...

Patching component oracle.precomp.lang, 12.1.0.1.0...

Patching component oracle.precomp.rsf, 12.1.0.1.0...

Patching component oracle.ctx, 12.1.0.1.0...

Patching component oracle.xdk.parser.java, 12.1.0.1.0...

Patching component oracle.ordim.jai, 12.1.0.1.0...

Patching component oracle.ovm, 12.1.0.1.0...

Patching component oracle.ntoledb.odp_net_2, 12.1.0.1.0...

Patching component oracle.clrintg.ode_net_2, 12.1.0.1.0...

Patching component oracle.aspnet_2, 12.1.0.1.0...

Patching component oracle.has.common, 12.1.0.1.0...

Patching component oracle.has.common.cvu, 12.1.0.1.0...

Patching component oracle.has.db, 12.1.0.1.0...

Patching component oracle.has.deconfig, 12.1.0.1.0...

Patching component oracle.has.rsf, 12.1.0.1.0...

Verifying the update...
Patch 20558101 successfully applied.
Log file location: C:\app\sqladmin\product\12.1.0\dbhome_1\cfgtoollogs\opatch\opatch2015-06-04_15-35-51PM_1.log

OPatch succeeded.

C:\app\sqladmin\product\12.1.0\dbhome_1\OPatch>
====================================================
Go with readme for Patch Post-Installation Instructions below steps is for non-CDB/PDB.

  1. Start the OracleService<SID> and Oracle Listener Services if already not started. Ensure that all database instances running out of the ORACLE_HOME that you just patched are started.
  2. Go with below step
With Oracle 11g the latest patch is applied within the database using:
SQL> @catbundle.sql psu apply

Oracle 12c introduces a new utility called “datapatch” which replaces the need to run the 11g command shown above.
As with Oracle 11g you first install the patch into the Oracle Home with all services & databases down. But with Oracle Database 12c after restarting the database simply run datapatch from the OPatch directory:

cd $ORACLE_HOME/OPatch
./datapatch -verbose

C:\app\sqladmin\product\12.1.0\dbhome_1\OPatch>datapatch -verbose
SQL Patching tool version 12.1.0.1.0 on Thu Jun  4 15:47:51 2015
Copyright (c) 2014, Oracle.  All rights reserved.

Connecting to database...OK
Determining current state...
Currently installed SQL Patches:
Currently installed C Patches: 20558101
Adding patches to installation queue and performing prereq checks...
Installation queue:
  Nothing to roll back
  The following patches will be applied: 20558101
Installing patches...
Patch installation complete.  Total patches installed: 1
Validating logfiles...
Patch 20558101 apply: SUCCESS
  logfile: C:\app\sqladmin\product\12.1.0\dbhome_1\sqlpatch\20558101/20315702_apply_ORCL_orcl_2015Jun04_15_58_30.log (no errors)
  catbundle generate logfile: C:\app\sqladmin\cfgtoollogs\catbundle\catbundle_PSU_ORCL_orcl_GENERATE_2015Jun04_15_58_36.log (no errors)
  catbundle apply logfile: C:\app\sqladmin\cfgtoollogs\catbundle\catbundle_PSU_ORCL_orcl_APPLY_2015Jun04_15_58_42.log (no errors)
SQL Patching tool complete on Thu Jun  4 16:07:30 2015
C:\app\sqladmin\product\12.1.0\dbhome_1\OPatch>

Please take the backup for db and home before execution of Patching activity.

Thursday, 28 May 2015

Useful OS commands for DBA


Finding OS Version and Bit

OS Version

uname -a
AIX <ServerName> 3 5 00C8E96B4C00

uname
AIX

oslevel -r
5300-06

OS Bit

lsconf|grep -i kernel
Kernel Type: 64-bit

prtconf

/usr/bin/isainfo –kv

getconf LONG_BIT 

getconf -a | grep KERN
uname -m


1024 bytes =  1KB  (4 letters)
1024 *1024 = 1048576 = 1MB (7 letters)
1024 *1024 * 1024 = 1073741824 = 1GB (10 letters)



lssrc -a |grep sendmail

ps –ef|grep sendmail



ps -ef|awk '{print $1}'|sort -n|uniq -c

Process count

ps -ef|awk '{print $1 }'|sort|uniq -c |sort -n

ps -ef|wc -l

ps -ef|grep oracle|wc -l

ps -x|wc –l


UNIX command examples  

                   
!!!CAUTION!!!

  1. ALL ARE EXAMPLES ONLY
  1. The Use of rm –f command must be thoroughly checked before including it in the find command syntax.
  1. The Use of gzip –f command must be thoroughly checked before including it in the find command syntax.
  1. Please understand the use of below commands before executing them in production.
  1. Always edit the below find commands as per requirement.
  1. We will not own the responsibility incase if the below commands are used wrongly by the buyer causing severe damage to the files.
  1. These commands can cause severe damage to the files if used Wrongly in wrong situation.
  1. These commands are examples only and should be used with Caution and buddy check.
  1. Understand/Test & Edit the commands appropriately and use.
Unix commands

To find files from day a week ago
find ./ -name "*" \( -mtime -7 -a -mtime +5 \)  -type f -exec ls -l {} \;

To specify displayed information
find . -type f -name "*.trc" -printf "%f   %c  %a %t  \n"

To find files owned by specified user on desired mountpoint ( /var in this exam.)
find /var -xdev -user oracle -type f -exec ls -l {} \;

Files bigger than specified size in [k for kilobytes | c for bytes ]
find . -type f -size +1024k -exec ls -l {} \;

To compute size of files
ls -ltr * | awk '{ sum += $5 } { print "Size in GB " sum/1024/1024/1024 }' | tail -1

Other commands syntax
uname -a                                               -- To find the OS of the box

df -h                                                        -- To check total, used, avialable free space of all Filesystems
df -k

pwd                                                        -- Print name of current/working directory
                                                                    To check u r in the correct folder or not , else use cd command and change the directory

df -h .                                                      -- To check total, used, avialable free space of the current path FS
df -k .                                                          (59419872 = around 59GB)
df -g .

df -g /opt/oracle/apps/admin/UKPROD  

df -hP | grep /SID/oracle   --To check total, used, avialable free space of the given path FS
df -kP | grep /SID/oracle                    (59419872 = around 59GB)

du -sh .                                                   --to check total size of current directory
du -sk .                                                    (output interms of KB)(23695680 = 23GB)

du -sh *                                                  --to list sizes of all files , folders
du -sk *

du -sh * | sort -n                                  --to list sizes of all files , folders
du -sk * | sort -n

du -sk *|sort -n|tail                             --to list sizes of all files , folders (bigger 10 files)
du -sh *|sort -n|tail

du -sk * | sort -n | egrep 'tblsp_SID.txt|tmproot|REFRESH|clone_base|oradata|xxonline'

du -ch PROD_df_LEVEL0_04-02-2007*     --in the last line can find string* files total size

ls -l l779750[7-9].req

ls -lrt *LEVEL0*
ls -ltr *failed*

ls -ltr SID_arch* | head ; ls -ltr SID_arch* | tail

ls -ltrh

ls -lS                                                        -> list of files/folder SIZEwise sorting (to c bigger files, need to scroll)

ls -lSr                                                      ->list of files/folder SIZEwise reverse sorting (can c bigger files immediately)

ls -lt                                                         -> last accessed date wise sort (old files u can c immediately)

ls -ltr                                                       -> last accessed date wise reverse sort  (to c old files, u need to scroll)
                                                                -- file/directory, owner of file, size, last accessed date, file name

ls -lSrh | tail -30                    -> size wise list of 30 bigger files

ls -lSr |tail

ls -lt *TMP| grep  " May  5"

fuser <filename>                                 -- to find any process is accessing the file
lsof <filename>                                     -- to find any process is accessing the file


Command to check size for a particular string for all platform

ls -ltr *LEVEL0* | awk '{ sum += $5 } { print "Size in GB " sum/1024/1024/1024 }' |tail -1  

ls -ltr SID_df_LEVEL0_18-10-2006* | awk '{ sum += $5 } { print "Size in GB " sum/1024/1024/1024 }' |tail -1



find ./ -name "*.zip*" -mtime +60 -exec ls -ltr {} \;  - searching for old patches

find ./ -name "*log*" -mtime +60 -exec ls -ltr {} \;   - searching for old log files

find . -size +100000000c -xdev -exec ls -l {} \;       - searching for morethan 100MB size files

find ./ -name "*" -size +30000k -exec ls -ltr {} \;

BECAREFUL OF USING THIS COMMANDS
===         -----------------------------------                                                                                                 ===
=== find /opt/oracle/apps/admin/SID/log -mtime +60 -type f -exec ls -l {} \; | wc -l                                                                                                                                                                                              ===
=== find ./ -name "*.trc" -mtime +60 -exec ls -ltr {} \; - listing 20 days old .trc files===
=== find ./ -name "error_log*" -mtime +10 -exec ls -ltr {} \; |wc -l                                                   ===
=== find ./ -name "*.trc" -mtime +10 -exec gzip -f {} \;                - zipping 20 days old .trc files===
=== find ./ -name "*.trc*" -mtime +60 -exec ls -l {} \;                   - Purging 20 days old .trc files===
=== gzip -f `ls -lt *.trc | grep  " Apr " | awk '{print $9}'`                -zipping Apr month .trc files              ===
=== gzip -f `ls -ltr | grep  " Sep 29 10" | awk '{print $9}'` - zipiing sep 29th 10AM files(all)===
=== ls -l `ls -lt *.trc | grep  " Jan " | awk '{print $9}'`   -Purging Jan month .trc files     ===
=== ls -l *trc `ls -l  |grep " Aug "|awk '{print $9}'`                                                                                         ===        
===                                                                                                         ===
=== ls -ltr *LEVEL0* | awk '{ sum += $5 } { print "Size in GB " sum/1024/1024/1024}' |tail -1   --Calculate size
=== ls -ltr PROD_df_LEVEL1_27-09-2007* | awk '{ sum += $5 } { print "Size in GB " sum/1024/1024/1024 }' |tail -1
===
=== find ./ -name "*trc" -size +300k  -mtime +3 -exec ls -ltr {} \;                                                               ===
=== find ./ -name "*trc" -size +300k  -mtime +3 -exec gzip {} \;                                                 ===
=== gzip -f `ls -l |grep " Sep " | awk '{print $9}'`                                                                                            ===                                                                                        
=== gzip -f `ls -l| grep  " Sep 1"| awk '{print $9}'`                                                                                          ===
=== gzip <file name>                                                                                                                                                           ===
===                                                                                                                                                                                         ===
=== rm <file name>   Ex: rm ias.tar                                   -- to remove single file                        ===
=== ls -l SID_df_LEVEL0_30-09-2006*                                            -- to remove multiple files                  ===
===                                                                                                                                                                                         ===
=== find ./ -name "*.*" -mtime +60 -exec ls -ltr {} \;   - searching for old files                         ===                                                                                        
=== find . -mtime +15 -exec gzip -f {} \;              -- zipping 15 days old files                               ===
===                                                                                                                                                                                         ===
=== find . -mtime +30 -exec ls -l {} \;                        -- purge/delete 30 days old file                ===
===                                                                                                                                                                                         ===
=== ls -lt *log* | grep  " Mar " | awk '{print $9}'                                                                                            ===
===                                                                                                                                                                                         ===
=== ls -l `ls -lt *log* | grep  " Feb " | awk '{print $9}'`                                                                   ===
=== ls -l `ls -lt |grep " Jul " | awk '{print $9}'`                                                                                  ===
===                                                                                                                                                                                         ===
=== find ./ -name "*.out" -mtime +60 -exec ls -ltr {} \;  - searching for old files of extn .out===
===                                                                                                                                                                                         ===
=== find . -size +100000000c -xdev -exec ls -l {} \;  - searching for morethan 100MB size files ===
=== find ./ -name "*" -size +30000k -exec ls -ltr {} \;                                                                                    ===
===                                                                                                                                                                                         ===
=== ls -ltr SID_df_LEVEL0_28-10-2006* | awk '{ sum += $5 } { print "Size in GB " sum/1024/1024/1024 }' |tail -1   - total size of this failed LEVEL0 backup
===                                                                                                                                                                                         ===        
===================================================================================================
to purge files with directories inside use : ls -l 
ls -lr oradata_beforePMP - DANGEROUS command  - to purge along with folder forcefully 
===================================================================================================
find . -size +50000000c -print|xargs ls -l

Finding Archivelogs applied lastly

select min(COMPLETION_TIME) last_appl from v$archived_log
where (THREAD#=1 and SEQUENCE#=28040)
or (THREAD#=2 and SEQUENCE#=24386)
or (THREAD#=3 and SEQUENCE#=24259)

LAST_APPL
---------------
08-jun-07 19:12

Removing Old Archivelog files of a date (Replace ls with rm command)

alter session set nls_date_format='dd-mon-rr hh24:mi';
set lines 180
set pagesize 9999
select 'ls -l ' || name
from v$archived_log
where applied = 'YES'
and to_char(COMPLETION_TIME,'rrrrmmdd') between '20070602' and '20070604';

'RM-F'||NAME
---------------------------------------------------
ls -l /SID/arch/SID1/SID_2_23688.arc
ls -l /SID/arch/SID1/SID_1_27359.arc
ls -l /SID/arch/SID1/SID_3_23578.arc
...
ls -l /SID/arch/SID1/SID_2_23992.arc
ls -l /SID/arch/SID1/SID_2_23993.arc
ls -l /SID/arch/SID1/SID_3_23883.arc
ls -l /SID/arch/SID1/SID_1_27663.arc

917 rows selected.

RMAN Sofar done

SELECT sid, serial#, context, sofar, totalwork,
round(sofar/totalwork*100,2) "% Complete"
FROM v$session_longops
WHERE opname LIKE 'RMAN%'
AND opname NOT LIKE '%aggregate%'
AND totalwork != 0
AND sofar <> totalwork
/

find /backup/sid -name "SID_arch_*" -mmin +6000 -exec ls -l {} \; | awk '{sum += $5} {print
"Size GB:" sum/1024/1024/1024 }' | tail -1

Size GB:10.8984

find /backup/sid -name "SID_arch_*" -mmin +6030 -exec ls -l {} \;
Examples (Throughly test yourself and understand before use)
df -hP|grep <codetree>
du -sh *
ls -ltr|tail
ls -lSr|tail
ls -ltr|head
find . -iname *out -mtime +30 -exec ls -l {} \;
find . -mtime +3 -exec gzip -f {} \;
find . -mtime +60 -exec ls -l {} \;
find . -size +100000000c -xdev -exec ls -lrth {} \;

du -sj --->to sum up sizes
nohup find /SID/oracle/product/102/admin/SID_Host/bdump -size +100000000c -name "*.trc*" -mtime +15 -xdev -exec ls -l {} \; &

find . -size +100000000c -name "*log*" -mtime +1 -xdev -exec gzip -f {} \;

find . -size +100000000c -xdev -exec ls -ltr {} \;

find / -size +100000000c -xdev -type f -exec ls -lh {} \;

find . ! -name . -prune -size +100000000c -xdev -exec ls -lrth {} \;

find . ! -name . -prune -size +100000000c -xdev -exec du -sh {} \;

find . ! -name . -prune -name "core-*" -mtime +30 -exec ls -l {} \;

find /tmp ! -name . -prune -name "*.t" -mtime +15 -exec ls -ltrh {} \;

find /tmp ! -name . -prune -name "*.t" -mtime +15 -exec ls -l {} \;

find /tmp -name "*.tmp.gz" -mtime +15 -exec ls -ltrh {} \;

find /tmp -name "*.t" -mtime +1 -exec gzip  -f {} \;

find . ! -name . -prune -name "*.t" -mtime +3 -exec gzip -f {} \;

find . ! -name . -prune -name "*.tmp" -mtime +15 -exec ls -ltrh {} \;

find . ! -name . -prune -name "*.tmp" -mtime +15 -exec ls -l {} \;

find . ! -name . -prune -name "*.tmp" -mtime +3 -exec gzip -f {} \;

find / -xdev -size '+5000k'|xargs ls -lh | grep -v dev |grep aptrvrbi

find . -xdev -size '+5000k'|xargs ls -lh | grep -v dev |grep iasrvrbi

find /tmp -xdev -size '+5000k'|xargs ls -l | grep -v dev|head -500

find . -xdev -size '+50000k'|xargs ls -ltr | grep -v dev

find . -xdev -size '+5000k'|xargs ls -ltrh | grep -v dev

find . ! -name . -prune -xdev -size '+5000k'|xargs ls -lh | grep -v dev

find . ! -name . -prune -xdev -size '+5000k'|xargs ls -ltrh | grep -v dev

find . ! -name . -prune -name "*.tmp" -mtime +30 -exec ls -ltr {} \;

find . ! -name . -prune -name "*.tmp" -mtime +30 -exec ls -l {} \;

find . ! -name . -prune -name "*.tmp" -mtime +30 -exec ls -l {} \;

find . -mtime +30 -size +100000000c -xdev -exec ls -ltrh {} \;

find . -size +100000000c -xdev -exec ls -ltr {} \;

find . -size +10000000c -xdev -exec ls -ltrh {} \;

find . -size +10000000c -xdev -exec ls -ltr {} \;

find . -size +10000000c -xdev -exec du -sk {} \;

find / -xdev -size '+10000k'|xargs ls -ld | grep -v dev |grep SID --->10000k files

find ./ -name "o1*.out" -size +3000k  -mtime +10 -exec gzip {} \;

du -sk *|sort -n|tail -15

*applcsf log/out/tmp

*product *iAS/Apache/Apache *806 network/admin/log

*APPLMGR *common/admin log/out

find /SID/applmgr/common/admin/log/SID_oradev -mtime +60 -type f -exec ls -l {} \;

nohup find /SID/applcsf/log/SID_ERP01 -mtime +30 -exec ls -l {} \; &


nohup find /SID/applcsf/tmp -mtime +7 -exec gzip -f{} \; &

nohup find /SID/applcsf/log -mtime +7 -exec gzip -f {} \; &


nohup find /SID/applcsf/out -mtime +7 -exec gzip -f {} \; &

nohup find /SID/applcsf/tmp -mtime +3 -exec gzip -f {} \; &

nohup find /SID/applcsf/log -mtime +3 -exec gzip -f {} \; &

nohup find /SID/applcsf/ -mtime +3 -exec gzip -f {} \; &

nohup find /SID/applcsf/out -mtime +7 -exec gzip -f {} \; &

nohup find /SID/applcsf/out/SID_Host -mtime +7 -exec gzip -f {} \; &
find . -mtime +30 -exec ls -l {} \; --> purging files more than 30days old

find . -mtime +10 -exec gzip -f {} \; --> zipping files more than 7days old

find /tmp -name "*.t" -mtime +3 -exec gzip -f {} \;

find ./ -name "*.tmp" -mtime +15 -exec ls -l {} \;

find ./ -name "*.trc" -mtime +1 -exec gzip -f {} \;

find . -name '*trw' -mmin +240 -exec gzip  {} \; 
find /SID/3rdparty/apps/jboss-4.0.4.GA/server/default/log -name "*.log.*" -mtime +30 -exec ls -l {} \;

find . -mtime +7 -exec ls -lrt {} \;

find ./ -name "*core*" -mtime +1 -exec ls -ltr {} \;

df -k | sort -n | tail

nohup find /SID/applcsf/log/SID_Host -mtime +30 -exec ls -l {} \; &

find . -name "*.t" -exec ls -l {} \;

find /tmp -name "*.t" -mtime +3 -exec gzip -f {} \;

find /tmp -name "*.t" -mtime +30 -exec ls -l {} \;

find /tmp -name "*.t" -mtime +15 -exec ls -ltrh {} \;

find /tmp -name "*.TMP" -mtime +30 -exec ls -l {} \;

find /tmp -name "O*.t" -mtime +30 -user USER -maxdepth 1 -exec ls -l {} \;
   
find /tmp -name "O*.t" -mtime +7 -user USER -exec gzip -f {} \;

find . -name "*.t.Z" -mtime +30 -exec ls -l {} \;

find . -name "*.t" -exec gzip -f {} \;

nohup find . -name "*log*" -mtime +60 -exec ls -l {} \; &

nohup find . -name "*log*" -mtime +7 -exec gzip -f {} \; &

nohup find . -name "*log*" -mtime +30 -exec ls -l {} \; &

find . -name "*log*" -mtime +10 -exec gzip -f {} \;

find ./ -name "Events*" -mtime +30 -exec ls -l {} \;

find ./ -name "Events*" -mtime +30 -exec ls -ltrh {} \;

find ./ -name "Events*" -mtime +30 -exec ls -l {} \;

find . -name "*default-web-access.log.txt*" -mtime +30 -exec gzip -f {} \;

find ./ -name "XWII_BASE_APPS_*" -exec ls -l {} \;

*** find . ! -name . -prune -name "*.t" -mtime +30 -exec ls -ltr {} \; --->To find ".t" files without descending sub directories...

find . ! -name . -prune -name "*.t" -mtime +7 -exec gzip -f {} \;

find . -name "Exaaa*" -mtime +10 -exec ls -l {} \;

find ./ -name "*.trw" -mtime +60 -exec ls -l {} \;

nohup find ./ -name "*.trc*" -mtime +3 -exec gzip -f {} \; &

nohup find ./ -name "*.trw" -mtime +3 -exec gzip -f {} \; &

find ./ -name "*.out*" -mtime +30 -exec ls -l {} \;

find ./ -name "*.out" -mtime +30 -exec ls -l {} \;

find ./ -name "*.trc" -mtime +0 -exec ls -l {} \;

nohup find ./ -name "*.trc" -mtime +0 -exec gzip -f {} \;

nohup find ./ -name "*.trc*" -mtime +60 -exec ls -l {} \; &

nohup find ./ -name "*trc" -mtime +3 -exec gzip -f {} \; &

find ./ -name "*trc" -mtime +30 -exec ls -l {} \;

find . ! -name . -prune -size +100000000c -xdev -mtime +3 -exec ls -lrth {} \;

ls -l *.trc  |grep " Dec  7" | awk '{print $9}'

gzip -f `ls -l *.trc  |grep " Dec  7" | awk '{print $9}`

find /SID/backup/RMAN -name "data_full_*" -mtime +0 -type f -exec ls -l {} \;

zip error_log_pls.zip error_log_pls; >error_log_pls

zip stuck_rec.dbg.zip stuck_rec.dbg; >stuck_rec.dbg

zip stuck_rec.dbg.zip stuck_rec.dbg >/SID/backup/stuck_rec.dbg.zip

zip apps_pbgd1i.log.zip apps_pbgd1i.log; >apps_pbgd1i.log

zip SID.zip SID >/SID/applcsf/mailtmp/appclo1i.zip

zip mod_jserv.log_25sep2008.zip mod_jserv.log; >mod_jserv.log

zip Events148.log.zip Events148.log; >Events148.log

ls -l `ls -ltr *.dbf | grep  "Apr  3" | awk '{print $9}'`

ls -ltrh *.dbf | grep  "Apr  3"

ls -ltr *p*.zip* | awk '{ sum += $5 } { print "Size in GB " sum/1024/1024/1024}' |tail -1

nohup find /SID/applcsf/log -mtime +30 -exec ls -l {} \; &

nohup find /SID/applcsf/out -mtime +30 -exec ls -l {} \; &

nohup find /SID/applcsf/tmp -mtime +30 -exec ls -l {} \; &
find /tmp  \( -name '*.t' \) -mtime +3 -size +1000  -exec ls -l  {} \;

find ./ -name "*.trc" -mtime +1 -exec gzip -f {} \;
find ./ -name "tmp_*" -mtime +30 -exec ls -ltr {} \;

Finding directory sizes

du -k |sort  -nr |head -20
du -g |sort  -nr |head -20

find ./ -name "*.trc" -mtime +7 -exec ls -ltr {} \;

find . -size +100000000c -xdev -exec gzip {} \;
find . -size +100000000c -xdev -type f -exec ls -lh {} \;

find /appl/oracle/admin/SID/udump -name "*.trc*" -mtime +90 -exec ls -ltrh {} \;

find <path> -type f -print|xargs ls –l

Eg:

find /appl/formsA/oracle/product/dev6.0/reports60/server/cache -type f -print|xargs ls -l

find /data/a01/SID -name "*.arc" -mtime +5 -exec rm {} \;

find . -mtime +730 -type f –print  -exec tar -cvf /tempspace/repservr_cache_2years.tar . \;

find /tempspace -mtime +730 -type f –print  -exec tar -cvf /tempspace/repservr_cache_2years.tar {} \;

find /data/a01/SID -name "*.arc*" -mtime +5 -exec rm {} \;

find /data2/prod_export -name "comp_export_SID *.log" -mtime +30 -exec rm {} \;


Linux

ps -eo pid,user,vsz,rss,s,comm | sort -n -k 3,4
ps -eo pid,user,vsz,rss,args | sort -n -k 3,4 | tail -20

prstat -s rss
sar -W
swapon -s

SunOS

prstat -s rss
swap -l
- returns dev(vice)/low/blocks/free in 512-bytes blocks

ps -eo pid,user,vsz,rss,s,comm | sort -n -k 3,4  | tail -20
AIX

ps -efl | sort -n -k 10,10 | tail -50

ps -eo pid,user,vsz,rss,s,cmd | sort -n -k 3,4  | tail -20

ps -eo pid,user,vsz,comm | sort -n -k 3,3  | tail -20

SIZE

ps -eo pid,user,vsz,rss,s,comm | sort -n -k 3,4

vmstat
swapon -s

swap
usr/sbin/lsps -a

Real memory
usr/sbin/lsattr -HE -l sys0 -a realmem

HP-UX

ps -efl | sort -n -k 10,10 | tail -50
swapinfo

PCH

V linuxu:
pridat sloupec swap, dat do souboru a seradit:

top -b -n 1 >top.txt
cat top.txt | egrep "M|G" | sort -r -k 5,5 | more


V SUNu

ps -efl|sort -rk 10,10| head
Desaty sloupec je pouzita pamet ve strankach. Prikaz pagesize vypise velikost stranky, vynasobit a je to.

prstat -s rss

top -o size

swapinfo -t

swap -s

vmstat 4 6

HP-UX

swapinfo

swapinfo -m   --->Memory information (interms of MB)

vmstat -S

vmstat -s

sar -w 5 5

show parameter sga_max_size

free -m

ps -ef  |wc -l

No of processors

cat /proc/cpuinfo| grep processor| wc -l

cat /proc/cpuinfo | grep processor

Linux

free -m

Sun

prstat -t

prstat -s rss

/usr/sbin/prtconf | grep "Memory size"

df -k|grep swap

sar -w 5 5

prstat -t             prstat -u pdb2i25 -s size
top -o size
swap -s
free
swapon -s
vmstat 4 4
ps auxw |tail -10--hpunix

lsattr -E -l sys0 -a realmem  --- ram on aix
lsps -s  -- swap space on aix

vmstat
swap -l
prtconf | grep Mem
swap SUNOS:16106834.6

vmstat -p 3
mpstat

ps -eo pid,pcpu,args | sort +1n                         %cpu
ps -eo pid,vsz,args | sort +1n                             kilobytes of virtual memory

/usr/ucb/ps aux |more                                       Output is sorted with highest users (processes) of CPU and memory at the top

free --->swap information in kbytes
free -m -->swap information in mbytes
free -g -->swap information in gbytes

Solaris

$ /usr/sbin/prtconf grep -i "Memory size"
$ swap -s
$ df -k
$ /usr/local/bin/top
$ vmstat 5 100
$ sar -u 2 100
$ iostat -D 2 100
$ mpstat 5 100

For example:

$ man vmstat

Here is some sample output from these commands:

$ prtconf grep -i "Memory size"

Memory size: 4096 Megabytes

$ swap -s
total: 7443040k bytes allocated + 997240k reserved = 8440280k used, 2777096k available

$ df -k
Filesystem kbytes used avail capacity Mounted on
/dev/dsk/c0t0d0s0 4034392 2171569 1822480 55% /
/proc 0 0 0 0% /proc
fd 0 0 0 0% /dev/fd
mnttab 0 0 0 0% /etc/mnttab
/dev/dsk/c0t0d0s3 493688 231339 212981 53% /var
swap 2798624 24 2798600 1% /var/run
swap 6164848 3366248 2798600 55% /tmp
/dev/vx/dsk/dcdg01/vol01
25165824 23188748 1970032 93% /u01
/dev/vx/dsk/dcdg01/vol02
33554432 30988976 2565456 93% /u02
...

$ top

last pid: 29570; load averages: 1.00, 0.99, 0.95 10:19:19
514 processes: 503 sleeping, 4 zombie, 6 stopped, 1 on cpu
CPU states: 16.5% idle, 17.9% user, 9.8% kernel, 55.8% iowait, 0.0% swap
Memory: 4096M real, 46M free, 4632M swap in use, 3563M swap free

PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND
29543 usupport 1 35 0 2240K 1480K cpu2 0:00 0.64% top-3.5b8-sun4u
13638 usupport 11 48 0 346M 291M sleep 14:00 0.28% oracle
13432 usupport 1 58 0 387M 9352K sleep 3:56 0.17% oracle
29285 usupport 10 59 0 144M 5088K sleep 0:04 0.15% java
13422 usupport 11 58 0 391M 3968K sleep 1:10 0.07% oracle
6532 usupport 1 58 0 105M 4600K sleep 0:33 0.06% oracle
...

$ vmstat 5 100
procs memory page disk faults cpu
r b w swap free re mf pi po fr de sr f0 s1 s1 s1 in sy cs us sy id
0 1 72 5746176 222400 0 0 0 0 0 0 0 0 11 9 9 4294967196 0 0 -19 -6 -103
0 0 58 2750504 55120 346 1391 491 1171 3137 0 36770 0 37 39 5 1485 4150 2061 18 8 74
0 0 58 2765520 61208 170 272 827 523 1283 0 3904 0 36 40 2 1445 2132 1880 1 3 96
0 0 58 2751440 58232 450 1576 424 1027 3073 0 12989 0 22 26 3 1458 4372 2035 17 7 76
0 3 58 2752312 51272 770 1842 1248 1566 4556 0 19121 0 67 66 12 2390 4408 2533 13 11 75
...

$ iostat -c 2 100
cpu
us sy wt id
15 5 13 67
19 11 52 18
19 8 44 29
12 10 48 30
19 7 40 34
...

$ iostat -D 2 100
sd15 sd16 sd17 sd18
rps wps util rps wps util rps wps util rps wps util
7 4 9.0 6 3 8.6 5 3 8.1 0 0 0.0
4 22 16.5 8 41 37.9 0 0 0.7 0 0 0.0
19 34 37.0 20 24 37.0 12 2 10.8 0 0 0.0
20 20 29.4 24 37 51.3 3 2 5.3 0 0 0.0
28 20 40.8 24 20 42.3 1 0 1.7 0 0 0.0
...
$ mpstat 2 100
CPU minf mjf xcal intr ithr csw icsw migr smtx srw syscl usr sys wt idl
0 115 3 255 310 182 403 38 72 82 0 632 16 6 12 66
1 135 4 687 132 100 569 40 102 68 0 677 14 5 13 68
2 130 4 34 320 283 552 43 94 63 0 34 15 5 13 67
3 129 4 64 137 101 582 44 103 66 0 51 15 5 13 67
HP-UX 11.0:

top
Glance/GlancePlus
sam
/etc/swapinfo -t
/usr/sbin/swapinfo -t
ipcs -mop

Would it be safe to say that to view memory usage by user, execute the
following:

UNIX95= ps -e -o ruser,pid,vsz=Kbytes

...and to view shared memory usage, such as for Oracle processes, using the
following:

ipcs -bmop

$ grep Physical /var/adm/syslog/syslog.log
$ df -k
$ sar -w 2 100
$ sar -u 2 100
$ /bin/top
$ vmstat -n 5 100
$ iostat 2 100
$ top

For example:

$ grep Physical /var/adm/syslog/syslog.log
Nov 13 17:43:28 rmtdchp5 vmunix: Physical: 16777216 Kbytes, lockable: 13405388 Kbytes, available: 15381944 Kbytes

$ sar -w 1 100

HP-UX rmtdchp5 B.11.00 A 9000/800 12/20/02

14:47:20 swpin/s bswin/s swpot/s bswot/s pswch/s
14:47:21 0.00 0.0 0.00 0.0 1724
14:47:22 0.00 0.0 0.00 0.0 1458
14:47:23 0.00 0.0 0.00 0.0 1999
14:47:24 0.00 0.0 0.00 0.0 1846
...

$ sar -u 2 100 # This command generates CPU % usage information.

HP-UX rmtdchp5 B.11.00 A 9000/800 12/20/02

14:48:02 %usr %sys %wio %idle
14:48:04 20 2 1 77
14:48:06 1 1 0 98
...
$ iostat 2 100

device bps sps msps

c1t2d0 36 7.4 1.0
c2t2d0 32 5.6 1.0
c1t0d0 0 0.0 1.0
c2t0d0 0 0.0 1.0
...

AIX:

$ /usr/sbin/lsattr -E -l sys0 -a realmem
$ /usr/sbin/lsps -s
$ vmstat 5 100
$ iostat 2 100
$ /usr/local/bin/top # May not be installed by default in the server

For example:

$ /usr/sbin/lsattr -E -l sys0 -a realmem

realmem 33554432 Amount of usable physical memory in Kbytes False

NOTE: This is the total Physical + Swap memory in the system.
Use top or monitor command to get better breakup of the memory.

$ /usr/sbin/lsps -s

Total Paging Space Percent Used
30528MB 1%

Linux [RedHat 7.1 and RedHat AS 2.1]:

$ dmesg grep Memory
$ vmstat 5 100
$ /usr/bin/top

For example:

$ dmesg grep Memory
Memory: 1027812k/1048568k available (1500k kernel code, 20372k reserved, 103k d)$ /sbin/swapon -s

Tru64

$ vmstat -P grep -i "Total Physical Memory ="
$ /sbin/swapon -s
$ vmstat 5 100


For example

$ vmstat -P grep -i "Total Physical Memory ="
Total Physical Memory = 8192.00 M

$ /sbin/swapon -s

Swap partition /dev/disk/dsk1g (default swap):
Allocated space: 2072049 pages (15.81GB)
In-use space: 1 pages ( 0%)
Free space: 2072048 pages ( 99%)
Total swap allocation:
Allocated space: 2072049 pages (15.81GB)
Reserved space: 864624 pages ( 41%)
In-use space: 1 pages ( 0%)
Available space: 1207425 pages ( 58%)

Please take at least 10 snapshots of the "top" command to get an idea
aboud most OS resource comsuming processes in the server and the different
snapshot might contain a few different other processes and that will indicate
that the use of resouces are varying pretty quickly amound many processes.

AIX:
/usr/sbin/lsattr -E -l sys0 -a realmem
/usr/sbin/lsps -s

HP-UX:
grep Physical /var/adm/syslog/syslog.log
/usr/sbin/swapinfo -t

Linux:
cat /proc/meminfo | grep MemTotal
/sbin/swapon -s

Solaris:
/usr/sbin/prtconf | grep "Memory size"
/usr/sbin/swap -s

Tru64:
vmstat -P| grep -i "Total Physical Memory ="
/sbin/swapon -s

LONG BIT

getconf LONG_BIT

Huge Pages

grep -i huge /etc/sysctl.conf