Friday, July 29, 2011

Moving Schema to another Tablespace using "move" command.


Purpose of Moving?

We move schema or tables to other tablespace in-order to remove fragmentation.

Let’s do this:

We here move the Scott schema which is currently present in users tablespace.

SQL>Create tablespace mytab datafile ‘D:\oracle\product\10.2.0\oradata\mytab.dbf’ size 5m;

Tablespace created

Now check the current tablespace of Scott:

SQL> select owner, tablespace_name from dba_tables where owner='SCOTT';

OWNER                          TABLESPACE_NAME
------------------------------ ------------------------------
SCOTT                          USERS

Set spool on:

SQL> spool  D:\move.sql

SQL> select 'alter table ' || table_name || ' move tablespace mytab ;' from dba_
tables where owner='SCOTT' ;

'ALTERTABLE'||TABLE_NAME||'MOVETABLESPACEMYTAB;'
------------------------------------------------------------------
alter table DEPT move tablespace mytab ;
alter table EMP move tablespace mytab ;
alter table BONUS move tablespace mytab ;
alter table SALGRADE move tablespace mytab ;
alter table TEST_LOGMNR move tablespace mytab ;

SQL> spool off

A script is created as move.sql containing the list of tables to be move.

Now, run this script

SQL>@move.sql

The table move will change the ROWIDs of the table rows, and as such the indexes, which are based on ROWIDs, will become invalid (UNUSABLE).Therfeore, we need to rebuild them too.

An advantage of using the table move procedure is all constraints are preserved.

SQL>spool D:\index.sql

SQL> select 'Alter index ' || index_name || ' rebuild tablespace mytab ;' from dba_indexes where owner='SCOTT' ;

SQL>conn scott/tiger

SQL>@index.sql

Now check the tablespace of user scott

SQL> select owner, tablespace_name from dba_tables where owner='SCOTT';

OWNER                          TABLESPACE_NAME
------------------------------ ------------------------------
SCOTT                          MYTAB

ENJOY:-)


Wednesday, July 27, 2011

Checking Oracle Processes In Windows Using Command Line


In unix there is command called “ps“. Using ps command Oracle DBA can check all type of processes which are running in system. Ps command is very useful to administrating and monitoring oracle in unix/linux systems for all Oracle DBA.

Alternate of ps command in windows:

Using “tasklist” command Oracle DBA can able to trace all running processes in windows including oracle,sqlplus,exp,imp and others. Tasklist command is very closer to ps command. Using tasklist command Oracle DBA can able to access process ids, session ids, cpu time, and modules of running processes in Oracle on Windows.

c:\>tasklist /?

TASKLIST [/S system [/U username [/P [password]]]]
         [/M [module] | /SVC | /V] [/FI filter] [/FO format] [/NH]

Description:
    This command line tool displays a list of application(s) and
    associated task(s)/process(es) currently running on either a local or
    remote system.

Parameter List:
   /S     system           Specifies the remote system to connect to.

   /U     [domain\]user    Specifies the user context under which
                           the command should execute.

   /P     [password]       Specifies the password for the given
                           user context. Prompts for input if omitted.

   /M     [module]         Lists all tasks that have DLL modules loaded
                           in them that match the given pattern name.
                           If the module name is not specified,
                           displays all modules loaded by each task.

   /SVC                    Displays services in each process.

   /V                      Specifies that the verbose information
                           is to be displayed.

   /FI    filter           Displays a set of tasks that match a
                           given criteria specified by the filter.

   /FO    format           Specifies the output format.
                           Valid values: "TABLE", "LIST", "CSV".

   /NH                     Specifies that the "Column Header" should
                           not be displayed in the output.
                           Valid only for "TABLE" and "CSV" formats.

   /?                      Displays this help/usage.

Filters:
    Filter Name     Valid Operators           Valid Value(s)
    -----------     ---------------           --------------
    STATUS          eq, ne                    RUNNING | NOT RESPONDING
    IMAGENAME       eq, ne                    Image name
    PID             eq, ne, gt, lt, ge, le    PID value
    SESSION         eq, ne, gt, lt, ge, le    Session number
    SESSIONNAME     eq, ne                    Session name
    CPUTIME         eq, ne, gt, lt, ge, le    CPU time in the format
                                              of hh:mm:ss.
                                              hh - hours,
                                              mm - minutes, ss - seconds
    MEMUSAGE        eq, ne, gt, lt, ge, le    Memory usage in KB
    USERNAME        eq, ne                    User name in [domain\]user
                                              format
    SERVICES        eq, ne                    Service name
    WINDOWTITLE     eq, ne                    Window title
    MODULES         eq, ne                    DLL name

Examples:
    TASKLIST
    TASKLIST /M
    TASKLIST /V
    TASKLIST /SVC
    TASKLIST /M wbem*
    TASKLIST /S system /FO LIST
    TASKLIST /S system /U domain\username /FO CSV /NH
    TASKLIST /S system /U username /P password /FO TABLE /NH
    TASKLIST /FI "USERNAME ne NT AUTHORITY\SYSTEM" /FI "STATUS eq running"

Consider a case in which we have 20 databases on the server and we need to find out how many oracle services are currently running:

Well you may type the below command to know this:

c:\>tasklist -svc

Enjoy:-)


Tuesday, July 26, 2011

My blog backup


It is always safe to take the backup of your blog so that everything is secured from being deleted.

Below is the link of my backup:

My backup

Monday, July 25, 2011

What is an Oracle Profile

It is a set of limits on database resources. If you assign the profile to a user, then that user cannot exceed these limits. To create a profile, you must have the CREATE PROFILE system privilege. A user with no profile assigned receives, by default, a profile named DEFAULT
It is based on two parameters Resource_parameters and Password_parameters Resource parameters: SESSIONS_PER_USER 
SESSIONS_PER_USER is used to determine the maximum number of sessions (connections to the database) a user can have simultaneously. If a user has reached the limit set in the SESSIONS_PER_USER resource of his or her profile, then the next login (and any subsequent ones) produce an error condition. Error ORA-02391 will occur when it is validated.
CPU_PER_SESSION 
Each query a user issues consumes an amount of CPU time, which varies dependent upon the query. By setting this resource item, the DBA limits the amount of CPU time a user can consume from a single database session. After reaching the CPU limit, the user can perform no further activity in that session. The user must disconnect from the database and then reconnect to reset this CPU accumulator. It is expressed in hundredth of seconds.
Alter profile app_user limit CPU_PER_SESSION 100; Means the user consumes 1 second of CPU time for any query. CPU_PER_CALL
This resource, like CPU_PER_SESSION, represents the total amount of CPU time (in minutes) available to the user. However, this resource restricts the user on a per-call (SQL statement) basis rather than a per-session basis. Whenever a SQL statement reaches its limit, it ends with an error condition. Unlike CPU_PER_SESSION, however, the user has no need to disconnect from the database. When using CPU_PER_CALL, the user is free to issue another query as long as it does not exceed the total amount of time specified in CPU_PER_CALL. Error ORA-02392 error will occur when it is validated. CONNECT_TIME Specify the total elapsed time limit for a session, expressed in minutes. Error ORA-02399 will occur when it is validated. IDLE_TIME
Specify the permitted periods of continuous inactive time during a session, expressed in minutes. Long-running queries and other operations are not subject to this limit. Error ORA-02396 error will occur when it is validated. LOGICAL_READS_PER_SESSION What CPU_PER_SESSION is to LOGICAL_READS_PER_SESSION, CPU_PER_CALL is to LOGICAL_READS_PER_CALL. The value of this parameter restricts the number of database blocks that can be read during a single CPU call (SQL statement). If the number of blocks that the database attempts to logically read exceeds the limit set, the operation is abandoned. The user may issue another SQL statement and have no problems unless the logical reads in this statement exceed the value. Error ORA-02394 error will occur when it is validated. LOGICAL_READS_PER_CALL What CPU_PER_SESSION is to LOGICAL_READS_PER_SESSION, CPU_PER_CALL is to LOGICAL_READS_PER_CALL. The value of this parameter restricts the number of database blocks that can be read during a single CPU call (SQL statement). If the number of blocks that the database attempts to logically read exceeds the limit set, the operation is abandoned. The user may issue another SQL statement and have no problems unless the logical reads in this statement exceed the value. Error ORA-02395 will occur when it is validated PRIVATE_SGA_PER_SESSION Specify the amount of private space a session can allocate in the shared pool of the system global area (SGA). | COMPOSITE_LIMIT Specify the total resource cost for a session, expressed in service units.Oracle Database calculates the total service units as a weighted sum of CPU_PER_SESSION,CONNECT_TIME, LOGICAL_READS_PER_SESSION, and PRIVATE_SGA. Password_parameters
FAILED_LOGIN_ATTEMPTS:
Specify the number of failed attempts to log in to the user account before the account is locked.
PASSWORD_LIFE_TIME:
Specify the number of days the same password can be used forauthentication. If you also set a value for PASSWORD_GRACE_TIME,the password expires if it is not changed within the grace period, and furtherconnections are rejected. If you do notset a value for PASSWORD_GRACE_TIME,its default of UNLIMITED willcause the database to issue a warning but let the user continue to connect indefinitely.
PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX:
PASSWORD_REUSE_TIME specifies the number of days before which apassword cannot be reused. PASSWORD_REUSE_MAX specifies the number of password changes required before the current password can be reused. For example, if you specifyPASSWORD_REUSE_TIME to 30 and PASSWORD_REUSE_MAX to 10, then the user can reuse the password after 30 days if the password has already been changed 10 times.
PASSWORD_LOCK_TIME
Specify the number of days an account will be locked after the specified number of consecutive failed login attempts.
PASSWORD_VERIFY_FUNCTION:
The PASSWORD_VERIFY_FUNCTION clauselets a PL/SQL password complexity verification script be passed as an argument to the CREATE PROFILE statement
After setting all above parameters you are now ready to create the profile:
First set the resource_limit parameter to true.
CREATE PROFILE pro LIMIT PASSWORD_REUSE_MAX 10 PASSWORD_REUSE_TIME 30; CREATE PROFILE app_user LIMIT SESSIONS_PER_USER UNLIMITED CPU_PER_SESSION UNLIMITED CPU_PER_CALL 3000 CONNECT_TIME 45 LOGICAL_READS_PER_SESSION DEFAULT LOGICAL_READS_PER_CALL 1000 PRIVATE_SGA 15K COMPOSITE_LIMIT 5000000;
The user can have any number of concurrent sessions.
In a single session, the user can consume an unlimited amount of CPU time.
A single call made by the user cannot consume more than 30 seconds of CPU time
A single session cannot last for more than 45 minutes.
In a single session, the number of data blocks read from memory and disk is subject to the limit specified in the DEFAULT profile.
A single call made by the user cannot read more than 1000 data blocks from memory and disk.
A single session cannot allocate more than 15 kilobytes of memory in the SGA. In a single session, the total resourcecost cannot exceed 5 million service units. The formula for calculating the total resource cost is specified by the ALTERRESOURCE COST statement.
Since the app_user profile omits a limit for IDLE_TIME and for password limits, the user is subject to the limits on these resources specified in the DEFAULT profile Example: SQL>ALTER SYSTEM SET RESOURCE_LIMIT = true SCOPE=MEMORY; System altered. SQL> CREATE PROFILE save_cpu_time LIMIT cpu_per_session 1; Profile created. SQL>ALTER USER SCOTT PROFILE save_cpu_time; User altered. SQL> CONNECT scott/tiger Connected. SQL>CREATE TABLE bigemp AS SELECT * FROM emp; Table created. SQL> INSERT INTO bigemp SELECT * FROM bigemp; INSERT INTO bigemp SELECT * FROM bigemp * ERROR at line 1: ORA-02392: exceeded session limit on CPU usage, you are being logged off ENJOY:-)

Thursday, July 14, 2011

What are Privileges?


A privilege is a right to execute a particular type of SQL statement


Types of Privileges:

System privileges

Object privileges




System privileges:

A system privilege allows a user to perform a particular database operation or class of database operations.

Select name from system_privilege_map;

O7_DICTIONARY_ACCESSIBILITY:

It controls restrictions on system privileges. The default for O7_DICTIONARY_ACCESSIBILITY is FALSE.  If the parameter is set to TRUE, access to objects in the SYS schema is allowed.
When this parameter is not set to FALSE, the ANY privilege applies to the data dictionary, and a malicious user with ANY privilege could access or alter data dictionary tables.

Object privileges:

Privileges can be assigned to the following types of database objects:
·         Tables
select, insert, update, delete, alter, debug, flashback, on commit refresh, query rewrite, references, all
·         Views
select, insert, update, delete, under, references, flashback, debug
·         Sequence
alter, select
·         Packages, Procedures, Functions (Java classes, sources...)
execute, debug
·         Materialized Views
delete, flashback, insert, select, update
·         Directories
read, write
·         Libraries
execute
·         User defined types
execute, debug, under
·         Operators
execute
·         Indextypes
execute

ENJOY:-)

Tuesday, July 5, 2011

Migrate database to ASM configuration.

Migration to ASM is a very simple task.Please follow the below steps one by one to so: -
Step:1

1. First need to set below parameter for controlfile,datafile or FRA.
NOTE: I have disk group  "DAT1"
SQL> alter system set control_files='+DAT1' scope=spfile;
System altered.
SQL> alter system set db_create_file_dest='+DAT1' scope=spfile;
System altered.
SQL> alter system set db_recovery_file_dest='+DAT1' scope=spfile;
System altered.

Step: 2 Restart DATABASE server to take above parameter value.
SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.
Total System Global Area 263639040 bytes

Fixed Size 1332552 bytes

Variable Size 218106552 bytes

Database Buffers 37748736 bytes

Redo Buffers 6451200 bytes


Step:3 Connect with RMAN session & restore controlfile on ASM system.


C:\>RMAN target=sys
Recovery Manager: Release 11.1.0.6.0 - Production on Thu Apr 17 10:12:53 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
target database Password:connected to target database: TEST (not mounted)


Restore Controlfile.

RMAN> restore controlfile from 'C:\app\vishwanath\oradata\test\CONTROL01.ctl';
Starting restore at 17-APR-08using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=151 device type=DISK
channel ORA_DISK_1: copied control file copyoutput file name=+DAT1/test/controlfile/current.256.652270419Finished restore at 17-APR-08


Step:4 Mount Oracle Database and take backup of database.


RMAN> alter database mount;
database mountedreleased channel: ORA_DISK_1

RMAN> backup as copy database format '+DAT1';


Starting backup at 17-APR-08allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=151 device type=DISKchannel ORA_DISK_1: starting datafile copyinput datafile file number=00001 name=C:\APP\VISHWANATH\ORADATA\TEST\SYSTEM01.dbf output file name=+DAT1/test/datafile/system.257.652270565 tag=TAG20080417T101550 RECID=1 STAMP=652270748channel ORA_DISK_1: datafile copy complete, elapsed time: 00:03:06channel ORA_DISK_1: starting datafile copyinput datafile file number=00002 name=C:\APP\VISHWANATH\ORADATA\TEST\SYSAUX01.dbf output file name=+DAT1/test/datafile/sysaux.258.652270761 tag=TAG20080417T101550 RECID=2 STAMP=652270908channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:35channel ORA_DISK_1: starting datafile copyinput datafile file number=00003 name=C:\APP\VISHWANATH\ORADATA\TEST\UNDOTBS01.dbf output file name=+DAT1/test/datafile/undotbs1.259.652270927 tag=TAG20080417T101550 RECID=3 STAMP=652270952channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35channel ORA_DISK_1: starting datafile copycopying current control fileoutput file name=+DAT1/test/controlfile/backup.260.652270971 tag=TAG20080417T101550 RECID=4 STAMP=652270975channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07channel ORA_DISK_1: starting datafile copyinput datafile file number=00004 name=C:\APP\VISHWANATH\ORADATA\TEST\USERS01.dbf output file name=+DAT1/test/datafile/users.261.652270989 tag=TAG20080417T101550 RECID=5 STAMP=652270991channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setincluding current SPFILE in backup setchannel ORA_DISK_1: starting piece 1 at 17-APR-08channel ORA_DISK_1: finished piece 1 at 17-APR-08piece handle=+DAT1/test/backupset/2008_04_17/nnsnf0_tag20080417t101550_0.262.652271003 tag=TAG20080417T101550 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished backup at 17-APR-08


RMAN> switch database to copy;


datafile 1 switched to datafile copy "+DAT1/test/datafile/system.257.652270565"
datafile 2 switched to datafile copy "+DAT1/test/datafile/sysaux.258.652270761"
datafile 3 switched to datafile copy "+DAT1/test/datafile/undotbs1.259.652270927"
datafile 4 switched to datafile copy "+DAT1/test/datafile/users.261.652270989"


Step:5 Again connect to sqlplus session and perform incomplete recovery


C:\>sqlplus sys/oracle as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Thu Apr 17 10:26:24 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> recover database using backup controlfile until cancel;

ORA-00279: change 1071679 generated at 04/17/2008 10:11:10 needed for thread 1ORA-00289: suggestion : +DAT1ORA-15173: entry 'ARCHIVELOG' does not exist in directory 'TEST'ORA-00280: change 1071679 for thread 1 is in sequence #14
Specify log: {=suggested filename AUTO CANCEL}

CANCEL

Media recovery cancelled.


Step:6 OPEN database with RESETLOGS option.


SQL> alter database open resetlogs;
Database altered.


Step:7 Drop old tempfile and create new tempfile in existing temp tablespace


SQL> alter database tempfile 'c:\app\vishwanath\oradata\test\temp01.dbf'  drop including datafiles;
Database altered.


SQL> alter tablespace temp add tempfile size 512m 2 autoextend on next 250m maxsize unlimited;
Tablespace altered.


SQL> select tablespace_name, file_name, bytes from dba_temp_files;
TABLESPACE_NAME FILE_NAME BYTES
------------------------------ ------------------------------ ----------
TEMP +DAT1/test/tempfile/temp.266. 536870912 652271571


Step:8 Recreate All redolog group on ASM diskgroup


SQL> select a.group#, a.member, b.bytes

2 from v$logfile a, v$log b where a.group# = b.group#;


GROUP# MEMBER BYTES

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

3 C:\APP\VISHWANATH\ORADATA\TEST\REDO03.LOG 52428800
2 C:\APP\VISHWANATH\ORADATA\TEST\REDO02.LOG 52428800
1 C:\APP\VISHWANATH\ORADATA\TEST\REDO01.LOG 52428800


SQL> select group#,status from v$log;
GROUP# STATUS

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

1 CURRENT

2 UNUSED

3 UNUSED


SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.


SQL> select group#,status from v$log;
GROUP# STATUS

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

1 ACTIVE

2 ACTIVE

3 CURRENT


SQL> alter database drop logfile group 1;

alter database drop logfile group 1*ERROR at line 1:ORA-01624: log 1 needed for crash recovery of instance test (thread 1)ORA-00312: online log 1 thread 1: 'C:\APP\VISHWANATH\ORADATA\TEST\REDO01.LOG'


When you get above error message then set checkpoint with below command.

SQL> alter system checkpoint global;
System altered.
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database add logfile group 1 size 10m;
Database altered.


SQL> alter system checkpoint global;
System altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database add logfile group 2 size 10m;
Database altered.


SQL> alter system checkpoint global;
System altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database add logfile group 3 size 10m;
Database altered.


SQL> column member format a30

SQL> select a.group#, a.member, b.bytes

2 from v$logfile a, v$log b where a.group# = b.group#;
GROUP# MEMBER BYTES

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

3 +DAT1/test/onlinelog/group_3. 10485760 277.652273117
2 +DAT1/test/onlinelog/group_2. 10485760 274.652273019
1 +DAT1/test/onlinelog/group_1. 10485760 271.652272977
1 +DAT1/test/onlinelog/group_1. 10485760 272.652272979
2 +DAT1/test/onlinelog/group_2. 10485760 275.652273021
3 +DAT1/test/onlinelog/group_3. 10485760 278.652273119
6 rows selected.


Step:9 Recreate SPFILE on ASM diskgroup


SQL> create pfile='c:\initTEST.ora' from spfile;
File created.
SQL> create spfile='+DAT1/spfileTEST.ora' from pfile='c:\initTEST.ora';
File created.

ENJOY