Friday, July 12, 2013

CPU Used By This Instance

CPU Used By The Instance


(Tested on an Oracle 10 database.)

The "CPU used by this session" statistic in V$SYSSTAT shows how much CPU time the database has used since it was started. It is recorded in hundredths of a second. First set timed_statistics to false. This stops Oracle incrementing the statistic:

SQL> alter system set timed_statistics = false
  2  /

System altered.

SQL> col value format a10
SQL> l
  1  select value from v$parameter
  2* where name = 'timed_statistics'
SQL> /

VALUE
----------
FALSE

SQL>

Check the starting value:

SQL> l
  1  select value/100 SECONDS from v$sysstat
  2* where name = 'CPU used by this session'
SQL> /

   SECONDS
----------
  14756.13

SQL>

Do some work:

SQL> select count(*) from dba_objects;

  COUNT(*)
----------
     44734

SQL>


Check the statistic again. It will not have changed:

SQL> select value/100 SECONDS from v$sysstat
  2  where name = 'CPU used by this session';

   SECONDS
----------
  14756.13

SQL>

Now set timed_statistics to true:

  1* alter system set timed_statistics = true
SQL> /

System altered.

SQL>

Check the starting value again. It has already gone up a little:

  1  select value/100 SECONDS from v$sysstat
  2* where name = 'CPU used by this session'
SQL> /

   SECONDS
----------
  14756.35

SQL>

Do some work again:

SQL> select count(*) from dba_objects;

  COUNT(*)
----------
     44734

SQL>

Check the statistic again. This time it will have increased:

SQL> select value/100 SECONDS from v$sysstat
  2  where name = 'CPU used by this session';

   SECONDS
----------
  14756.96

SQL>

Sync standby database with production using RMAN with missing archive log at primary

Sync standby database with production using RMAN with missing archive log at primary

There are scenarios when physical standby database is out of sync and the required archive file is missing from production server. In such cases we can use RMAN to recover standby database to any SCN.  This is one of the way to sync the standby database with production.
Bring standby database in sync with Primary Database using RMAN. Steps as follows
1. Get current SCN of standby database.
select current_scn from v$database;
CURRENT_SCN
———–
5793471
2. Take incremental backup from last sequence change at standby (standby SCN from above query) using RMAN
rman target  sys/pass
RMAN> BACKUP DEVICE TYPE DISK INCREMENTAL FROM SCN 5793471 DATABASE FORMAT ‘C:\temp\rmanSCN%U’;
3. create a new standby controlfile from production
sqlplus> alter database create standby crontrolfile ‘c:\stdbyscn.ctl’;
4. cancel managed recovery on standby
sqlplus> recover managed standby database cancel;
5. copy incremental backup  and standby control file to standby server server (path c:\rmanbkp) and register it to standby control file
rman target  sys/syspwd
rman> catalog start with ‘c:\rmanbkp’;
6. once registed with standby control file recover  standby database  from the incremental backup using RMAN with noredo (with noredo option recovery process will recover from incremental backup instead of redolog and archivelog)
rman> recover database noredo;
7. shutdown the standby and replace the controlfile with backed up before  
8. mount the database and put  it in managed recovery mode
sqlplus> startup mount
sqlplus> recover managed standby database disconnect;
9.  Verify whether scn of production and standby is matching  

Oracle Database Editions

Oracle Database Editions

Oracle Database Editions 
Oracle Corporation subdivides its product into varying "editions" - apparently for marketing and license-tracking reasons. In approximate order of decreasing scale, the editions are listed below.


1)Enterprise Edition (EE): Includes more features than the 'Standard Edition', especially in the areas of performance and security. Oracle Corporation licenses this product on the basis of users or of processors, typically for servers running 4 or more CPUs. EE has no memory limits, and can utilize clustering using Oracle RAC software.


2)Standard Edition (SE): Contains base database functionality. Oracle Corporation licenses this product on the basis of users or of processors, typically for servers running from one to four CPUs. If the number of CPUs exceeds 4 CPUs, the user must convert to an Enterprise license. SE has no memory limits, and can utilize clustering with Oracle RAC at no additional charge.

3)Standard Edition One: Introduced with Oracle 10g, has some additional feature-restrictions. Oracle Corporation markets it for use on systems with one or two CPUs. It has no memory limitations.

4)Express Edition: ('Oracle Database XE'), introduced in 2005, offers Oracle 10g free to distribute on Windows and Linux platforms (with a footprint of only 150 MB and restricted to the use of a single CPU, a maximum of 4 GB of user data and 1 GB of memory). Support for this version comes exclusively through on-line forums and not through Oracle support.

5)Oracle Personal Edition: Provides the functionality of the "high end" Enterprise Edition but marketed to (and licensed for) single-user developers working on personal workstations.

6)Oracle Database Lite: Intended to run on mobile devices. The database, partially located on the mobile device, can synchronize with a server-based installation.