ORA-01031: insufficient privileges

In a scenario when there are three user U1, U2, U3. User 1 have create a table and granted select access to user 2.
User 2 have create a view on this table and sysdba have granted select access on this view to User 3.

Now user 3 have access to select date from view create by user 2 because access is granted by sysdba, but even then User 3 can not select from the view and getting error insufficient privileges.

Explanation:
U2 can select from the table hence U2 can create the view on it and select from the view, but when U3 tries to select from the view it checks as U2 and see if U2 can grant select access on the underlying table which is owned by U1.

Because U1 just granted select access to U2 but did not mention "WITH GRANT OPTION" hence U3 can not select from the view and getting the permission error.

To fix the issue grant select to U2 on table create by U1 with "WITH GRANT OPTION" and this will fix the issue.

The example is described as below:

SQL> create user &user identified by &pass default tablespace harvey
SQL> temporary tablespace temp2;
Enter value for user: u1
Enter value for pass: u1
old   1: create user &user identified by &pass default tablespace harvey temporary tablespace temp2
new   1: create user u1 identified by u1 default tablespace harvey temporary tablespace temp2
User created.
SQL> /
Enter value for user: u2
Enter value for pass: u2
old   1: create user &user identified by &pass default tablespace harvey temporary tablespace temp2
new   1: create user u2 identified by u2 default tablespace harvey temporary tablespace temp2
User created.
SQL> /
Enter value for user: u3
Enter value for pass: u3
old   1: create user &user identified by &pass default tablespace harvey temporary tablespace temp2
new   1: create user u3 identified by u3 default tablespace harvey temporary tablespace temp2
User created.

SQL> conn / as sysdba
Connected.
SQL> create table u1.t1 as select * from scott.emp
  2  /
Table created.
SQL> conn u1/u1
Connected.
SQL> select count(*) from t1;
  COUNT(*)
----------
        14
SQL> grant select on t1 to u2;
Grant succeeded.
SQL> conn u2/u2
Connected.
SQL> create view v2t1 as select * from u1.t1;
View created.
SQL>
SQL> grant select on v2t1 to u3;
grant select on v2t1 to u3
                *
ERROR at line 1:
ORA-01720: grant option does not exist for 'U1.T1'

SQL> conn / as sysdba
Connected.
SQL> grant select on u2.v2t1 to u3;
Grant succeeded.
SQL> conn u3/u3
Connected.
SQL> select count(*) from u2.v2t1;
select count(*) from u2.v2t1
                        *
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> conn u1/u1
Connected.
SQL> grant select on t1 to u2 with grant option;
Grant succeeded.
SQL> conn u3/u3
Connected.
SQL> select count(*) from u2.v2t1;
  COUNT(*)
----------
        14

ORA-01114: IO error writing block to file 202 (block # 4445)

Getting error while creating the and index

ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-01114: IO error writing block to file 202 (block # 4445)
ORA-27063: number of bytes read/written is incorrect
Additional information: 245760
Additional information: 253952
ORA-01114: IO error writing block to file 202 (block # 4445)
ORA-27063: number of bytes read/written is incorrect
Additional information: 245760
Additional information: 253952
ORA-01114: IO error writing block to file 202 (block # 4445)
ORA-27063: number of bytes read/written is incorrect
Additional information: 245760
Additional information: 253952
ORA-01114: IO error writing block to file 202 (block # 4445)
ORA-27063: number of bytes read/written is incorrect
Additional information: 245760
Additional information: 253952
Process ID: 15076
Session ID: 492 Serial number: 1471

The most probable reason for this error under my circumstances is that, create index command use tamp tablespace and one of the datafile of temp tablespace is not able to extend itself because the disk have no space left. 

In case your TEMP tablespace have two tempfile temp01.dbf and temp02.dbf, first file is on the disk with less space than the maxsize of the file and second file on the disk with more space then the maxsize.

Now you are creating an index and it is using temp01.dbf to as tempspace then once the file have filled the db disk then it will give above error, but it will not start using temp02.dbf.

Incase you query have started using temp02.dbf, because this file can grow and it have enough space on dbdisk and once this file is completely used then it will start using temp01.dbf until it have filled the db disk and hit the error.

ORA-00060: Deadlock detected

Deadlock explained

Deadlock is a state in database when no further action can be done and sessions are in a state that they can not be committed, below is the example of how database deadlock occurs:

SQL> select c from t;
C
--
X
Y

Description of ORA-00060 is:

$ oerr ORA 00060
00060, 00000, "deadlock detected while waiting for resource"
// *Cause:  Transactions deadlocked one another while waiting for resources.
// *Action: Look at the trace file to see the transactions and resources
//          involved. Retry if necessary.
$ 

This is just an example of how deadlock can occur. It can occur using any other DML operation as well. Deadlock is not an oracle database issue but this is a feature so that sessions do not struct in limbo.

In the above example I am not committing any where. Some explanation for the above scenario:

• SNO 1, session 13 I am updating a table T and not committing.
• SNO 2, session 401 updating another row in the same table with something else.
• Both of the above commands will finish and there will be no issue.
• SNO 3, session 401 trying to update what was updated in SNO 1, the transaction in this session will not finish but wait for session 13 to commit the transaction. Which means session 401 will not receive SQL prompt to run anything else. This means session 401 is waiting for session 13 and there is a lock by session 13 on the row.
• SNO 4, session 13 update the row which was updated by session 2 in SNO 3. Which means another waiting event is created and now session 13 is waiting for session 401 to commit. But as said in previous point session 401 can not commit because it can not get the SQL prompt until session 13 from commits transaction in SNO 1 and now session 13 can not commit transaction in SNO 1 because it is not getting the SQL prompt. This situation of waiting for each other to commit is called deadlock.
• SNO 5, an error occurred in session 401 for the transaction which caused the database lock and eventually became deadlock. At this point a trace file is created in alert log which have more information.

*** 2011-08-20 09:33:48.253
DEADLOCK DETECTED ( ORA-00060 )

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-0006000d-0000a3d0        27     401     X             26      13           X
TX-0001000c-00007adc        26      13     X             27     401           X

session 401: DID 0001-001B-00000005     session 13: DID 0001-001A-00000005
session 13: DID 0001-001A-00000005      session 401: DID 0001-001B-00000005

Rows waited on:
  Session 401: obj - rowid = 00011F19 - AAAR8ZAAFAADZkeAAA
  (dictionary objn - 73497, file - 5, block - 891166, slot - 0)
  Session 13: obj - rowid = 00011F19 - AAAR8ZAAFAADZkeAAB
  (dictionary objn - 73497, file - 5, block - 891166, slot - 1)

----- Information for the OTHER waiting sessions -----
Session 13:
  sid: 13 ser: 11 audsid: 511371 user: 85/HDHILLON flags: 0x45
  pid: 26 O/S info: user: oracle, term: UNKNOWN, ospid: 4106
    image: oracle@hdhillon-dt2 (TNS V1-V3)
  client details:
    O/S info: user: oracle, term: pts/2, ospid: 4057
    machine: hdhillon-dt2 program: sqlplus@hdhillon-dt2 (TNS V1-V3)
    application name: SQL*Plus, hash value=3669949024
  current SQL:
  update t set c='E' where c='Y'

----- End of information for the OTHER waiting sessions -----

Information for THIS session:

----- Current SQL Statement for this session (sql_id=13f3ds0x0bq54) -----
update t set c='D' where c='X'
===================================================

failed: ORA-24327: need explicit attach before authenticating a user (DBD ERROR: OCISessionBegin) at

If you are using Oracle and your web perl application is getting the following error:

failed: ORA-24327: need explicit attach before authenticating a user (DBD ERROR: OCISessionBegin) at

the issue could be you have change the ORACLE_SID in listener.ora and tnsnames.ora

Please restart your listener to fix the issue.

Backend WLS or EM application seems to be down

When trying to access the web interface of Oracle 11g Release 1 Grid Control after restart of the server I am getting the following message:

Backend WLS or EM application seems to be down 

In the first try I receive the following error:


$  ./Oracle/Middleware/oms11g/bin/emctl start oms
Oracle Enterprise Manager 11g Release 1 Grid Control
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
Starting WebTier...
WebTier Successfully Started
Starting Oracle Management Server...
Oracle Management Server Could Not Be Started
Oracle Management Server is not functioning because of the following reason:
Connection to the repository failed. Verify that the repository connection information provided is correct.
Please check /home/oracle/Oracle/gc_inst/em/EMGC_OMS1/sysman/log/emctl.log for error details

This is very clear message and rectified by starting the listener.


To resolve the main issue I did the following:

$ pwd
/home/oracle
$  ./Oracle/Middleware/oms11g/bin/emctl start oms
Oracle Enterprise Manager 11g Release 1 Grid Control
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
Starting WebTier...
WebTier Successfully Started
Starting Oracle Management Server...
Oracle Management Server Could Not Be Started
Oracle Management Server is Up
$ 

And it worked, now I can access the Oracle 11g Release 1 Grid Control's web interface.

Few other things which you can do is:

Check if the listener is running and you can connect to database.

Another is check status of oms with details option

$ ./Oracle/Middleware/oms11g/bin/emctl status oms -details
Oracle Enterprise Manager 11g Release 1 Grid Control
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
Enter Enterprise Manager Root (SYSMAN) Password :
Console Server Host : *******************
HTTP Console Port   : ****
HTTPS Console Port  : ****
HTTP Upload Port    : ****
HTTPS Upload Port   : ****
OMS is not configured with SLB or virtual hostname
Agent Upload is locked.
OMS Console is locked.
Active CA ID: 1

If you are still getting the same error then make sure that the listener is running.


lsnrctl status listener

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 11-JUL-2012

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=orcl)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=gcdb)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=XXXXXX)(Port=XXXX)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused


stop and restart the listener and try stopping and starting the listener again


Oracle Grid Control 12c

[oracle@oem01 bin]$ ./emctl stop oms -all
Oracle Enterprise Manager Cloud Control 12c Release 2
Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.
Stopping WebTier...
WebTier Successfully Stopped
Stopping Oracle Management Server...
Oracle Management Server Successfully Stopped
AdminServer Successfully Stopped
Oracle Management Server is Down
[oracle@oem01 bin]$
[oracle@oem01 bin]$
[oracle@oem01 bin]$ ./emctl start oms
Oracle Enterprise Manager Cloud Control 12c Release 2
Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.
Starting Oracle Management Server...
Starting WebTier...
WebTier Successfully Started
Oracle Management Server Successfully Started
Oracle Management Server is Up
[oracle@oem01 bin]$
[oracle@oem01 bin]$
[oracle@oem01 bin]$
[oracle@oem01 bin]$ ./emctl status oms
Oracle Enterprise Manager Cloud Control 12c Release 2
Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.
WebTier is Up
Oracle Management Server is Up
[oracle@oem01 bin]$
[oracle@oem01 bin]$
[oracle@oem01 bin]$
[oracle@oem01 bin]$ ./emctl status oms -details
Oracle Enterprise Manager Cloud Control 12c Release 2
Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.
Enter Enterprise Manager Root (SYSMAN) Password :
Console Server Host        : oem01.localdomain
HTTP Console Port          : 7789
HTTPS Console Port         : 7801
HTTP Upload Port           : 4890
HTTPS Upload Port          : 4901
EM Instance Home           : /u01/app/oracle/Middleware2/gc_inst/em/EMGC_OMS1
OMS Log Directory Location : /u01/app/oracle/Middleware2/gc_inst/em/EMGC_OMS1/sysman/log
OMS is not configured with SLB or virtual hostname
Agent Upload is locked.
OMS Console is locked.
Active CA ID: 1
Console URL: https://oem01.localdomain:7801/em
Upload URL: https://oem01.localdomain:4901/empbs/upload

WLS Domain Information
Domain Name      : GCDomain
Admin Server Host: oem01.localdomain

Managed Server Information
Managed Server Instance Name: EMGC_OMS1
Managed Server Instance Host: oem01.localdomain
WebTier is Up
Oracle Management Server is Up
[oracle@oem01 bin]$

Oracle Grid Control installation steps

There are very few places which will tell you on how to install Oracle Grid Control. Of course you will be able to find something in Oracle Documentation. I tried following many things and did the installation of Oracle Grid Control Installation on Linux.

For the complete installation of Oracle Grid Control we need three things:

1. One Oracle database which will be used by Grid Control
2. Oracle Middleware weblogic server
3. Oracle Grid Control

In this installation example I have used:

Oracle database 11gR1

Oracle Middleware weblogic server version 10.3.2.0.

Oracle Enterprise Manager 11g Grid Control Release 1 (11.1.0.1.0)

Oracle Grid Control can be downloaded from here 

If you want to see Oracle Official documentation for this release then click here

As described above first steps is to have the oracle database for grid control and I have called this gcdb, below are some of the screen shots which I feel are important to show:

  I have used only 300M as SGA, please note that this is only a test system.

Once the installation of Oracle database is done then there is a need for the installation of Oracle Middleware.

After the completion of Oracle Middleware I did the installation of Oracle Grid Controll. In this installation I did not check the pre-request document correctly and got few issues and those issues are discussed in this blog and I have demonstrated on how I have resolved them.

 

Above issue was resolve using:

$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1
$ cd bin
$ ./emca -deconfig dbcontrol db -repos drop -SYS_PWD ****** -SYSMAN_PWD ******
STARTED EMCA at Apr 29, 2011 10:00:53 AM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Enter the following information:
Database SID: gcdb
Listener port number: 1521
Do you wish to continue? [yes(Y)/no(N)]: Y
Apr 29, 2011 10:01:17 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/gcdb/emca_2011_04_29_10_00_53.log.
Apr 29, 2011 10:01:17 AM oracle.sysman.emcp.EMDBPreConfig performDeconfiguration
WARNING: EM is not configured for this database. No EM-specific actions can be performed.
Apr 29, 2011 10:01:17 AM oracle.sysman.emcp.ParamsManager checkListenerStatusForDBControl
WARNING: Error initializing SQL connection. SQL operations cannot be performed
Apr 29, 2011 10:01:17 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) ...
Apr 29, 2011 10:02:54 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Apr 29, 2011 10:02:54 AM
$

Next Error I received was because initial parameters were not set properly

These were altered in initial parameter and then database was restarted. But the error were not over as I did not read the pre-requirement document nicely :) 

 This was because the filesize of the undo tablespace was less than 200MB this was a simple fix:

alter database datafile '/u01/app/oracle/oradata/gcdb/undotbs01.dbf'  resize 300M;

# cd /home/oracle/Oracle/Middleware/oms11g/
# ./allroot.sh
Starting to execute allroot.sh .........
Starting to execute /home/oracle/Oracle/Middleware/oms11g/root.sh ......
Running Oracle 11g root.sh script...
The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /home/oracle/Oracle/Middleware/oms11g
Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file "dbhome" already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]:
The file "oraenv" already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]:
The file "coraenv" already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]:
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
Adding entry to /etc/oratab file...
Finished execution of  /home/oracle/Oracle/Middleware/oms11g/root.sh ......

Starting to execute /home/oracle/Oracle/Middleware/agent11g/root.sh ......
Running Oracle 11g root.sh script...
The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /home/oracle/Oracle/Middleware/agent11g
Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file "dbhome" already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]:
The file "oraenv" already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]:
The file "coraenv" already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]:
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
Finished product-specific root actions.
Adding entry to /etc/oratab file...
Finished execution of  /home/oracle/Oracle/Middleware/agent11g/root.sh ......
# 

cd $ORACLE_HOME/dbhome_1/sysman/admin/emdrep/bin
./RepManager hostname 1521 gcdb  -sys_password ******* -repos_user sysman -repos_password ******* -action drop
Getting temporary tablespace from database...
Found temporary tablespace: TEMP
Checking SYS Credentials ... rem error switch
OK.
rem error switch
Dropping the repository..
Checking for Repos User ... Exists.
Repos User exists..
Clearing EM Contexts ... Failed.
Dropping EM users ...
Done.
Dropping Repos User ... Done.
Dropping Roles/Synonymns/Tablespaces ... Done.
ORA-06550: line 1, column 7:
ORA-06550: line 1, column 7:
ORA-06550: line 22, column 44:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 18, column 11:
ORA-04031: unable to allocate 148 bytes of shared memory ("shared
ORA-06512: at line 155
Dropping Repository failed. Please check file /u01/app/oracle/product/11.2.0/dbhome_1/sysman/log/emrepmgr.log.23942 for detailed errors.

This was because the SGA was only 300M so I changed the SGA to 800M and restarted the database and then ran the script once again. 

./RepManager hostname 1521 gcdb  -sys_password ******* -repos_user sysman -repos_password ******* -action drop
Getting temporary tablespace from database...
Found temporary tablespace: TEMP
Checking SYS Credentials ... rem error switch
OK.
rem error switch
Dropping the repository..
Checking for Repos User ... Exists.
Repos User exists..
Clearing EM Contexts ... Failed.
Dropping EM users ...
Done.
Dropping Repos User ... Done.
Dropping Roles/Synonymns/Tablespaces ... Done.
ORA-06550: line 1, column 7:
ORA-06550: line 1, column 7:
ORA-06550: line 22, column 44:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 18, column 11:
Dropping Repository failed. Please check file /u01/app/oracle/product/11.2.0/dbhome_1/sysman/log/emrepmgr.log.24404 for detailed errors

That's it then I clicked on retry. 

The installation of the Oracle Grid Control is done. Now you have to open ports in firewall so that other machines can access the Grid Control interface.

I hope this will help and if there are questions then feel free to send message in comments.

TNS-01150: The address of the specified listener name is incorrect

Error is occurring while starting the listener.
Below is the error that I am getting while starting the listener:

$ lsnrctl start listener
LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 20-APR-2011 06:06:46
Copyright (c) 1991, 2010, Oracle.  All rights reserved.
Starting /u01/app/oracle/product/11.2.0.2/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.2.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0.2/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/jeetdev/listener/alert/log.xml
TNS-01150: The address of the specified listener name is incorrect
Listener failed to start. See the error message(s) above...
$
$ lsnrctl status listener
LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 20-APR-2011 06:16:46
Copyright (c) 1991, 2010, Oracle.  All rights reserved.
TNS-01150: The address of the specified listener name is incorrect
NL-00303: syntax error in NV string
$

In order to check on what is happening my first check would be to see if the listener.ora file have got the correct information and check if the opening and closing brackets are present. My current listener file is:

LISTENER=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=jeetdev)(PORT=1521))
    )
 
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0.2)
      (SID_NAME=orcl))
  )
ADR_BASE_LISTENER = /u01/app/oracle

I have observed that there is a missing closing bracket in LISTENER for DESCRIPTION. What I have done is added that closing bracket and saved the listener file and then tried starting the listener again.

$ lsnrctl status listener
LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 20-APR-2011 06:17:20
Copyright (c) 1991, 2010, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jeetdev)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
$
$ lsnrctl start listener
LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 20-APR-2011 06:17:26
Copyright (c) 1991, 2010, Oracle.  All rights reserved.
Starting /u01/app/oracle/product/11.2.0.2/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.2.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0.2/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/jeetdev/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jeetdev)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jeetdev)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     listener
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                20-APR-2011 06:17:26
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.2/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/jeetdev/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jeetdev)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
$

Oracle how to find the max number of sessions ever reached in database

To find the number max number of sessions reached run the command below, note that this view will hold the information up to the  last restart of the database:


SQL> select * from v$resource_limit;

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
------------------------------ ------------------- --------------- ---------- ----------
processes                                       37              47        150        150
sessions                                        45              58        248        248
enqueue_locks                                   28             297       3080       3080
enqueue_resources                               19              50       1308  UNLIMITED
ges_procs                                        0               0          0          0
ges_ress                                         0               0          0  UNLIMITED
ges_locks                                        0               0          0  UNLIMITED
ges_cache_ress                                   0               0          0  UNLIMITED
ges_reg_msgs                                     0               0          0  UNLIMITED
ges_big_msgs                                     0               0          0  UNLIMITED
ges_rsv_msgs                                     0               0          0          0
gcs_resources                                    0               0          0          0
gcs_shadows                                      0               0          0          0
dml_locks                                        0               0       1088  UNLIMITED
temporary_table_locks                            0               3  UNLIMITED  UNLIMITED
transactions                                     2               2        272  UNLIMITED
branches                                         0               0        272  UNLIMITED
cmtcallbk                                        2               3        272  UNLIMITED
max_rollback_segments                           11              11        272      65535
sort_segment_locks                               0               8  UNLIMITED  UNLIMITED
k2q_locks                                        0               0        496  UNLIMITED
max_shared_servers                               1               1  UNLIMITED  UNLIMITED
parallel_max_servers                             0               0         40       3600

How to remove the sparcing in the oracle temp files

Created a script to remove the sparcing out of the temp datafiles of oracle. Please note that this script will use the existing location of the temp file so make sure that it have enough space:

$ vi removeS.sh
#!/bin/sh

# Script to expand the datafile to the maxsize
# Created by : Jeet
# Dated : 06-MARCH-2011

COMD_1="H"
TS_NAME=""
TAR=".tar"
count=0
ANOTHER_FILE="Y"

#Function to bring tablespace offline
tsOffline()
{
  echo 'connect / as sysdba'  >> ./tmp.file
  echo 'alter tablespace ' $TS_NAME ' tempfile offline;' >> ./tmp.file
  echo 'exit' >> ./tmp.file
  sqlplus -s /nolog < ./tmp.file
  rm ./tmp.file
}

tsOnline()
{
  echo 'connect / as sysdba'  >> ./tmp.file
  echo 'alter tablespace ' $TS_NAME ' tempfile online;' >> ./tmp.file
  echo 'exit' >> ./tmp.file
  sqlplus -s /nolog < ./tmp.file
  rm ./tmp.file
}

initInput()
{

while [[ $ANOTHER_FILE = "Y"  ||  $ANOTHER_FILE = "y" ]]
  do
    
    read -p "Enter file location........................................: " FILE_LOCATION
    FILE_ARR[$count]=$FILE_LOCATION
    count=`expr $count + 1`
    read -p "Do you want to enter another filename ? ( Y/y/N/n ) .......: " ANOTHER_FILE

done

  echo ""
  echo ""
  echo "**********************************************************"
  echo "Verify if the file name(s) is/are correct"
  echo "**********************************************************"
  echo "Entered file name(s): "

for i in ${FILE_ARR[@]}
do
  echo $i

done
  echo ""
  echo ""
  echo "if you want to re-enter tablespace name and file name then press any other character other than (Y/y/N/n)"
  read -p "Do you want to expand this file ( Y/y/N/n or any other Char ): " COMD_1
}

generateFileName()
{

for FILE_L in ${FILE_ARR[@]}
do

  # generate the file name
  F_NAME=`ls -tr $FILE_L | awk -F/ '{ print $NF }'`
  echo " "
  echo " "
  echo "File under operation is: $F_NAME"
  echo "Location of the file is: " $FILE_L
  #Change get the directory and work on that

  F_DIR=`dirname $FILE_L`
  cd $F_DIR

  # Start tablespace offline
  # Bringing tablespace offline
  tsOffline

  # Modification to the file
  fileMods

  # Tablespace online
  # Bringing tablespace online
  tsOnline
    
  echo "Operation on Done on File: " $FILE_L
done

}

fileMods()
{
  # Get the original size
  du -h $F_NAME

  # Start Tar
  TEMP_NAME=$F_NAME$TAR
  tar cvf $TEMP_NAME $F_NAME

  # Remove sparce file
  rm $F_NAME

  # Un Tar
  tar xvf $TEMP_NAME

  #Remove the tar file
  rm $TEMP_NAME

  # Get the new siz
  du -h $F_NAME
}

# Show all tablespace names
showTS()
{
  echo 'connect / as sysdba'  >> ./tmp.file
  echo 'select distinct tablespace_name "Temporary TableSpace Name" from dba_temp_files order by tablespace_name;' >> ./tmp.file
  echo 'exit' >> ./tmp.file
  sqlplus -s /nolog < ./tmp.file
  rm ./tmp.file
}

# show all files under the tablespace
showTSFileNames()
{
  A="select file_name from dba_temp_files where tablespace_name =upper('"
  B="') order by file_name;"
  C=$A$TS_NAME$B
  echo 'connect / as sysdba' >> ./tmp.file
  echo $C >> ./tmp.file
  echo 'exit' >> ./tmp.file
  sqlplus -s /nolog < ./tmp.file
  rm ./tmp.file
}

# use input for the tablespace name
getUserTSInput()
{
  read -p 'Enter the name of the tablespace on which you want to work: ' TS_NAME
}


preCheck ()
{
  rm ./tmp.file
}


echo "=================================================="
echo "Utility is used to expand the file to its max size"
echo "=================================================="



while [[ $COMD_1 != "Y"  &&  $COMD_1 != "y"  &&  $COMD_1 != "N"  &&  $COMD_1 != "n" ]]
  do

  # Do a precheck if the temp file already exists then delete it else do nothing
  preCheck

  # Show the current tablespaces
  showTS

  # get user input to find the tablespace on which user want to work
  getUserTSInput

  # show all files under the tablespace
  showTSFileNames

  # as user to enter initial inputs
  initInput

  if [ $COMD_1 = "Y" ] || [ $COMD_1 = "y" ]
    then
      # Get the inputs from user
      generateFileName
      echo " "
      echo "Sparce files are converted to non sparce files"
      echo " "
    else
      if [ $COMD_1 = "N" ] || [ $COMD_1 = "n" ]
        then
          echo "Nothing is done try again later"
      else
        echo "Enter either Y/N "
    fi
  fi
done

Oracle move table from one tablespace to another

There could be a scenario when you will be required to move the table from one tablespace to another. Note that when you will be moving the table then you would link to move the indexes associated with this table as well.

Make sure that no one is using the table.

So below are the steps by which you can move the table and its index in one got to another tablespace:

SQL>  desc hdhillon.t;
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 OWNER                         NOT NULL VARCHAR2(30)
 OBJECT_NAME                   NOT NULL VARCHAR2(30)
 SUBOBJECT_NAME                         VARCHAR2(30)
 OBJECT_ID                     NOT NULL NUMBER
 DATA_OBJECT_ID                         NUMBER
 OBJECT_TYPE                            VARCHAR2(19)
 CREATED                       NOT NULL DATE
 LAST_DDL_TIME                 NOT NULL DATE
 TIMESTAMP                              VARCHAR2(19)
 STATUS                                 VARCHAR2(7)
 TEMPORARY                              VARCHAR2(1)
 GENERATED                              VARCHAR2(1)
 SECONDARY                              VARCHAR2(1)
 NAMESPACE                     NOT NULL NUMBER
 EDITION_NAME                           VARCHAR2(30)
SQL> select count (*) from hdhillon.t;
  COUNT(*)
----------
   1390900
SQL> select owner, index_name, INDEX_TYPE, table_name from dba_indexes where table_name ='T';
OWNER                          INDEX_NAME                     INDEX_TYPE                  TABLE_NAME
------------------------------ ------------------------------ --------------------------- ------------------------------
HDHILLON                       T_OWNER_INDEX                  NORMAL                      T
SQL> select table_name, owner, tablespace_name from dba_tables where table_name ='T';
TABLE_NAME                     OWNER                          TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
T                              HDHILLON                       MICHAEL
SQL> select index_name, owner, tablespace_name from dba_indexes where index_name ='T_OWNER_INDEX';
INDEX_NAME                     OWNER                          TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
T_OWNER_INDEX                  HDHILLON                       MICHAEL
SQL>
SQL> set lines 270 pages 1000
SQL> col sql format a90
SQL> select decode( segment_type, 'TABLE', 1, 2 ) order_col2,
  2        'alter ' || segment_type || ' ' || segment_name ||
  3        decode( segment_type, 'TABLE', ' move ', ' rebuild ' ) ||
  4        chr(10) ||
  5        ' tablespace &new_tablespace_name ' || chr(10) ||
  6        ' storage ( initial ' || initial_extent || ' next ' ||
  7          next_extent || chr(10) ||
  8        ' minextents ' || min_extents || ' maxextents ' ||
  9          max_extents || chr(10) ||');' sql
 10  from user_segments,
 11            (select table_name, index_name from user_indexes where table_name =upper('&&table_name'))
 12  where segment_type in ( 'TABLE', 'INDEX' )
 13  and segment_name in ( select table_name from user_indexes where table_name =upper('&&table_name'))
 14  or segment_name in ( select index_name from user_indexes where table_name =upper('&&table_name'))
 15  order by order_col2;
Enter value for new_tablespace_name: users
old   5:       ' tablespace &new_tablespace_name ' || chr(10) ||
new   5:       ' tablespace users ' || chr(10) ||
old  11:               (select table_name, index_name from user_indexes where table_name =upper('&&table_name'))
new  11:               (select table_name, index_name from user_indexes where table_name =upper('t'))
old  13: and segment_name in ( select table_name from user_indexes where table_name =upper('&&table_name'))
new  13: and segment_name in ( select table_name from user_indexes where table_name =upper('t'))
old  14: or segment_name in ( select index_name from user_indexes where table_name =upper('&&table_name'))
new  14: or segment_name in ( select index_name from user_indexes where table_name =upper('t'))
ORDER_COL2 SQL
---------- ------------------------------------------------------------------------------------------
         1 alter TABLE T move
            tablespace users
            storage ( initial 65536 next 1048576
            minextents 1 maxextents 2147483645
           );
         2 alter INDEX T_OWNER_INDEX rebuild
            tablespace users
            storage ( initial 65536 next 1048576
            minextents 1 maxextents 2147483645
           );
SQL>
SQL> alter TABLE T move
  2              tablespace users
  3              storage ( initial 65536 next 1048576
  4              minextents 1 maxextents 2147483645);
Table altered.
SQL> alter INDEX T_OWNER_INDEX rebuild
  2              tablespace users
  3              storage ( initial 65536 next 1048576
  4              minextents 1 maxextents 2147483645);
Index altered.
SQL> select table_name, owner, tablespace_name from dba_tables where table_name ='T';
TABLE_NAME                     OWNER                          TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
T                              HDHILLON                       USERS
SQL> select index_name, owner, tablespace_name from dba_indexes where index_name ='T_OWNER_INDEX';
INDEX_NAME                     OWNER                          TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
T_OWNER_INDEX                  HDHILLON                       USERS

Change the size of temp file

How to change the size of temp file.

SQL>  select FILE_NAME,  MAXBYTES/1024/1024 MB from dba_temp_files;
FILE_NAME                                                                          MB
------------------------------------------------------------ ----------
/u01/app/oracle/oradata/new_location/temp01.dbf                    3072
SQL>
SQL> alter database tempfile '/u01/app/oracle/oradata/new_location/temp01.dbf' autoextend on maxsize 4G;
Database altered.
SQL> select FILE_NAME,  MAXBYTES/1024/1024 MB from dba_temp_files;
FILE_NAME                                                                           MB
------------------------------------------------------------ ----------
/u01/app/oracle/oradata/new_location/temp01.dbf                    4096
SQL> 

Some tips about select COALESCE function in oracle

COALESCE  will return the first not null value. 

It requires at least two arguments

If the arguments entered are of different datatype then it will throw an error

SQL> select  coalesce(1,3,null,'jeet') from dual;
select  coalesce(1,3,null,'jeet') from dual
                          *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
SQL> select  coalesce('a','as',null) from dual;
C
-
a
SQL> select COALESCE(null,null) from dual;
C
-

SQL> select COALESCE (null) from dual;
select COALESCE (null) from dual
       *
ERROR at line 1:
ORA-00938: not enough arguments for function
SQL> select COALESCE('a') from dual;
select COALESCE('a') from dual
       *
ERROR at line 1:
ORA-00938: not enough arguments for function

Getting "ORA-01722: invalid number " while using NVL function

This error occurs if the first and the second parameter are different datatype, example is show below:

SQL> select nvl(1,'jeet') from dual;
select nvl(1,'jeet') from dual
             *
ERROR at line 1:
ORA-01722: invalid number

SQL> select nvl(1,2) from dual;
  NVL(1,2)
----------
         1
SQL> select nvl('jeet','oracle') from dual;
NVL(
----
jeet
SQL>
SQL>  select nvl(sysdate,'characters') from dual;
 select nvl(sysdate,'characters') from dual
                    *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

SQL> select nvl(sysdate,123) from dual;
select nvl(sysdate,123) from dual
                   *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER

SQL> select nvl(sysdate, to_date('01-01-11','DD-MM-YY')) from dual;
NVL(SYSDA
---------
14-FEB-11

Easiest way to move oracle database files from one location to another

In this we will only copy the datafiles from one location to another and then create the symbolic link ot the new location.
Basically we are bluffing database, database from OS layer. Database will see that the datafiles are still at the same location.
Physically datafiles are located at different location and symbolic link is making it possible.
This technique helps moving datafiles on different disks without issuing any command in database.

This will be done with an outage and the only two database commands issued are shutdown immediate and startup.

The example of achieving this is demonstrated below:


1. Shutdown the database.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 

2. Copy the files to new location but with the same name of the containing folder. I am copying because I want to keep the backup of the database.

$ pwd
/u01/app/oracle/oradata/new_location
$ ls -lah
total 3.9G
drwxr-xr-x 2 oracle oinstall 4.0K Feb  7 13:48 .
drwxr-x--- 5 oracle oinstall 4.0K Feb  7 11:12 ..
-rw-r----- 1 oracle oinstall  11M Feb 14 09:35 control01.ctl
-rw-r----- 1 oracle oinstall 101M Feb 14 09:35 example01.dbf
-rw-r----- 1 oracle oinstall  11M Feb 14 09:35 example02.dbf
-rw-r----- 1 oracle oinstall 101M Feb 14 09:35 michael01.dbf
-rw-r----- 1 oracle oinstall 253M Feb 14 09:35 michael02.dbf
-rw-r----- 1 oracle oinstall  51M Feb 14 01:06 redo01.log
-rw-r----- 1 oracle oinstall  51M Feb 14 08:00 redo02.log
-rw-r----- 1 oracle oinstall  51M Feb 14 09:35 redo03.log
-rw-r----- 1 oracle oinstall 1.3G Feb 14 09:35 sysaux01.dbf
-rw-r----- 1 oracle oinstall 741M Feb 14 09:35 system01.dbf
-rw-r----- 1 oracle oinstall 501M Feb 14 09:35 undotbs01.dbf
-rw-r----- 1 oracle oinstall 826M Feb 14 09:35 users01.dbf
-rw-r----- 1 oracle oinstall  11M Feb 14 09:35 users02.dbf
$ mkdir /u01/app/oracle/oradata/orcl/new_location
$ pwd
/u01/app/oracle/oradata/new_location
$ cp -p * ../orcl/new_location/
$ ls -lah ../orcl/new_location/*
-rw-r----- 1 oracle oinstall  11M Feb 14 09:35 ../orcl/new_location/control01.ctl
-rw-r----- 1 oracle oinstall 101M Feb 14 09:35 ../orcl/new_location/example01.dbf
-rw-r----- 1 oracle oinstall  11M Feb 14 09:35 ../orcl/new_location/example02.dbf
-rw-r----- 1 oracle oinstall 101M Feb 14 09:35 ../orcl/new_location/michael01.dbf
-rw-r----- 1 oracle oinstall 253M Feb 14 09:35 ../orcl/new_location/michael02.dbf
-rw-r----- 1 oracle oinstall  51M Feb 14 01:06 ../orcl/new_location/redo01.log
-rw-r----- 1 oracle oinstall  51M Feb 14 08:00 ../orcl/new_location/redo02.log
-rw-r----- 1 oracle oinstall  51M Feb 14 09:35 ../orcl/new_location/redo03.log
-rw-r----- 1 oracle oinstall 1.3G Feb 14 09:35 ../orcl/new_location/sysaux01.dbf
-rw-r----- 1 oracle oinstall 741M Feb 14 09:35 ../orcl/new_location/system01.dbf
-rw-r----- 1 oracle oinstall 501M Feb 14 09:35 ../orcl/new_location/undotbs01.dbf
-rw-r----- 1 oracle oinstall 826M Feb 14 09:35 ../orcl/new_location/users01.dbf
-rw-r----- 1 oracle oinstall  11M Feb 14 09:35 ../orcl/new_location/users02.dbf

3. Rename the original directory to something else

$ pwd
/u01/app/oracle/oradata
$ mv new_location new_location_backup

4. Create the symbolic link on the old location for the new location

$ pwd
/u01/app/oracle/oradata
$ ln -s /u01/app/oracle/oradata/orcl/new_location
$ ls -la
total 44
drwxr-x--- 5 oracle oinstall 4096 Feb 14 09:41 .
drwxr-xr-x 9 oracle oinstall 4096 Nov 24 13:10 ..
lrwxrwxrwx 1 oracle oinstall   41 Feb 14 09:41 new_location -> /u01/app/oracle/oradata/orcl/new_location
drwxr-xr-x 2 oracle oinstall 4096 Feb  7 13:48 new_location_backup
drwxr-x--- 3 oracle oinstall 4096 Feb 14 09:35 orcl

5. Startup database

SQL> startup
ORACLE instance started.
Total System Global Area  535662592 bytes
Fixed Size                  1337720 bytes
Variable Size             390071944 bytes
Database Buffers          138412032 bytes
Redo Buffers                5840896 bytes
Database mounted.
Database opened.
$ cd /u01/app/oracle/oradata/orcl/new_location
$ ls -lah
total 3.9G
drwxr-xr-x 2 oracle oinstall 4.0K Feb 14 09:38 .
drwxr-x--- 3 oracle oinstall 4.0K Feb 14 09:41 ..
-rw-r----- 1 oracle oinstall  11M Feb 14 09:42 control01.ctl
-rw-r----- 1 oracle oinstall 101M Feb 14 09:41 example01.dbf
-rw-r----- 1 oracle oinstall  11M Feb 14 09:41 example02.dbf
-rw-r----- 1 oracle oinstall 101M Feb 14 09:41 michael01.dbf
-rw-r----- 1 oracle oinstall 253M Feb 14 09:41 michael02.dbf
-rw-r----- 1 oracle oinstall  51M Feb 14 09:41 redo01.log
-rw-r----- 1 oracle oinstall  51M Feb 14 09:41 redo02.log
-rw-r----- 1 oracle oinstall  51M Feb 14 09:42 redo03.log
-rw-r----- 1 oracle oinstall 1.3G Feb 14 09:41 sysaux01.dbf
-rw-r----- 1 oracle oinstall 741M Feb 14 09:41 system01.dbf
-rw-r----- 1 oracle oinstall 501M Feb 14 09:41 undotbs01.dbf
-rw-r----- 1 oracle oinstall 826M Feb 14 09:41 users01.dbf
-rw-r----- 1 oracle oinstall  11M Feb 14 09:41 users02.dbf

Note that database assume that the files are still at the same location because of the symbolic link

TABLESPACE_NAME      FILE_NAME                                          
-------------------- --------------------------------------------------
EXAMPLE              /u01/app/oracle/oradata/new_location/example01.dbf
EXAMPLE              /u01/app/oracle/oradata/new_location/example02.dbf
MICHAEL              /u01/app/oracle/oradata/new_location/michael01.dbf
MICHAEL              /u01/app/oracle/oradata/new_location/michael02.dbf
SYSAUX               /u01/app/oracle/oradata/new_location/sysaux01.dbf
SYSTEM               /u01/app/oracle/oradata/new_location/system01.dbf
UNDOTBS1             /u01/app/oracle/oradata/new_location/undotbs01.dbf
USERS                /u01/app/oracle/oradata/new_location/users01.dbf  
USERS                /u01/app/oracle/oradata/new_location/users02.dbf   

Oracle 11 - How to move all the datafiles, redo logs, tempfiles, undo datafile and controlfiles in a database

There are times when you need to move the whole oracle database to another location which includes moving all datafiles (system, undo, sysaux and other datafiles), redo logs, temp files and controlfiles.

I am demonstrating this in five steps with example. The steps are as follows (Please note that I am assuming that there are no queries running on the database and I am the only user connected to the database):

1. Moving all datafiles expect system datafiles
2. Moving oracle temp datafile
3. Moving oracle undo tablespace/datafile 
4. Moving oracle system datafiles
5. Moving oracle redo log files
6. Moving oracle controlfiles


Moving all datafiles expect system datafiles:



SQL> select files.tablespace_name, files.file_name
  2  from
  3  (select file_id, file_name, tablespace_name, bytes/1024/1024 mb, autoextensible,
  4  decode(autoextensible,'NO',bytes/1024/1024,decode(sign(maxbytes-bytes),-1,bytes/1024/1024,maxbytes/1024/1024)) maxmb
  5  from dba_data_files) files,
  6  (select file_id, sum(bytes)/1024/1024 freemb
  7  from dba_free_space
  8  group by file_id) free
 9  where files.file_id = free.file_id (+)
 10  and files.file_name like '%&Fname%'
 11  order by 1,2;
Enter value for fname:
old  10: and files.file_name like '%&Fname%'
new  10: and files.file_name like '%%'

TABLESPACE_NAME      FILE_NAME                                        
-------------------- --------------------------------------------------
EXAMPLE              /u01/app/oracle/oradata/orcl/example01.dbf      
EXAMPLE              /u01/app/oracle/oradata/orcl/example02.dbf      
MICHAEL              /u01/app/oracle/oradata/orcl/michael01.dbf      
MICHAEL              /u01/app/oracle/oradata/orcl/michael02.dbf      
SYSAUX               /u01/app/oracle/oradata/orcl/sysaux01.dbf        
SYSTEM               /u01/app/oracle/oradata/orcl/system01.dbf        
UNDOTBS1             /u01/app/oracle/oradata/orcl/undotbs01.dbf      
USERS                /u01/app/oracle/oradata/orcl/temp/users01.dbf    
USERS                /u01/app/oracle/oradata/orcl/temp/users02.dbf    

SQL> alter tablespace example offline;

Tablespace altered.

SQL> alter tablespace michael offline;

Tablespace altered.

SQL> alter tablespace users offline;

Tablespace altered.

SQL> SELECT NAME,

  2      FILE#,

  3      STATUS,

  4      CHECKPOINT_CHANGE# "CHECKPOINT"   

  5    FROM   V$DATAFILE;

NAME                                            FILE# STATUS  CHECKPOINT

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

/u01/app/oracle/oradata/orcl/system01.dbf           1 SYSTEM    11517939

/u01/app/oracle/oradata/orcl/sysaux01.dbf           2 ONLINE    11517939

/u01/app/oracle/oradata/orcl/undotbs01.dbf          3 ONLINE    11517939

/u01/app/oracle/oradata/orcl/temp/users01.dbf       4 OFFLINE   11536249

/u01/app/oracle/oradata/orcl/example01.dbf          5 OFFLINE   11536178

/u01/app/oracle/oradata/orcl/example02.dbf          6 OFFLINE   11536178

/u01/app/oracle/oradata/orcl/michael01.dbf          7 OFFLINE   11536204

/u01/app/oracle/oradata/orcl/temp/users02.dbf       8 OFFLINE   11536249

/u01/app/oracle/oradata/orcl/michael02.dbf          9 OFFLINE   11536204

$ cp /u01/app/oracle/oradata/orcl/temp/users01.dbf /u01/app/oracle/oradata/new_location/users01.dbf
$ cp /u01/app/oracle/oradata/orcl/example01.dbf /u01/app/oracle/oradata/new_location/example01.dbf
$ cp /u01/app/oracle/oradata/orcl/example02.dbf /u01/app/oracle/oradata/new_location/example02.dbf
$ cp /u01/app/oracle/oradata/orcl/michael01.dbf /u01/app/oracle/oradata/new_location/michael01.dbf
$ cp /u01/app/oracle/oradata/orcl/temp/users02.dbf /u01/app/oracle/oradata/new_location/users02.dbf
$ cp /u01/app/oracle/oradata/orcl/michael02.dbf /u01/app/oracle/oradata/new_location/michael02.dbf
$ cp /u01/app/oracle/oradata/orcl/sysaux01.dbf /u01/app/oracle/oradata/new_location/sysaux01.dbf


SQL> alter tablespace users rename datafile '/u01/app/oracle/oradata/orcl/temp/users01.dbf' to '/u01/app/oracle/oradata/new_location/users01.dbf';
SQL>
Tablespace altered.

SQL> alter tablespace &TS_NAME rename datafile '&old_location' to '&new_location';
Enter value for ts_name: users
Enter value for old_location: /u01/app/oracle/oradata/orcl/temp/users02.dbf
Enter value for new_location: /u01/app/oracle/oradata/new_location/users02.dbf
old   1: alter tablespace &TS_NAME rename datafile '&old_location' to '&new_location'
new   1: alter tablespace users rename datafile '/u01/app/oracle/oradata/orcl/temp/users02.dbf' to '/u01/app/oracle/oradata/new_location/users02.dbf'

Tablespace altered.

SQL> /
Enter value for ts_name: example
Enter value for old_location: /u01/app/oracle/oradata/orcl/example01.dbf
Enter value for new_location: /u01/app/oracle/oradata/new_location/example01.dbf
old   1: alter tablespace &TS_NAME rename datafile '&old_location' to '&new_location'
new   1: alter tablespace example rename datafile '/u01/app/oracle/oradata/orcl/example01.dbf' to '/u01/app/oracle/oradata/new_location/example01.dbf'

Tablespace altered.

SQL> /
Enter value for ts_name: example
Enter value for old_location: /u01/app/oracle/oradata/orcl/example02.dbf
Enter value for new_location: /u01/app/oracle/oradata/new_location/example02.dbf
old   1: alter tablespace &TS_NAME rename datafile '&old_location' to '&new_location'
new   1: alter tablespace example rename datafile '/u01/app/oracle/oradata/orcl/example02.dbf' to '/u01/app/oracle/oradata/new_location/example02.dbf'

Tablespace altered.

SQL> /
Enter value for ts_name: michael
Enter value for old_location: /u01/app/oracle/oradata/orcl/michael01.dbf
Enter value for new_location: /u01/app/oracle/oradata/new_location/michael01.dbf
old   1: alter tablespace &TS_NAME rename datafile '&old_location' to '&new_location'
new   1: alter tablespace michael rename datafile '/u01/app/oracle/oradata/orcl/michael01.dbf' to '/u01/app/oracle/oradata/new_location/michael01.dbf'

Tablespace altered.

SQL> /
Enter value for ts_name: michael
Enter value for old_location: /u01/app/oracle/oradata/orcl/michael02.dbf
Enter value for new_location: /u01/app/oracle/oradata/new_location/michael02.dbf
old   1: alter tablespace &TS_NAME rename datafile '&old_location' to '&new_location'
new   1: alter tablespace michael rename datafile '/u01/app/oracle/oradata/orcl/michael02.dbf' to '/u01/app/oracle/oradata/new_location/michael02.dbf'

Tablespace altered.

SQL>  alter tablespace &TS_NAME rename datafile '&old_location' to '&new_location';

Enter value for ts_name: sysaux

Enter value for old_location: /u01/app/oracle/oradata/orcl/sysaux01.dbf

Enter value for new_location: /u01/app/oracle/oradata/new_location/sysaux01.dbf

old   1:  alter tablespace &TS_NAME rename datafile '&old_location' to '&new_location'

new   1:  alter tablespace sysaux rename datafile '/u01/app/oracle/oradata/orcl/sysaux01.dbf' to '/u01/app/oracle/oradata/new_location/sysaux01.dbf'

Tablespace altered.

SQL> alter tablespace michael online;

Tablespace altered.

SQL> alter tablespace users online;

Tablespace altered.

SQL> alter tablespace example online;

Tablespace altered.

SQL> alter tablespace sysaux offline;

Tablespace altered.

SQL> SELECT NAME,
  2      FILE#,
  3      STATUS,
  4      CHECKPOINT_CHANGE# "CHECKPOINT"
  5    FROM   V$DATAFILE;

NAME                                               FILE# STATUS  CHECKPOINT
-------------------------------------------------------- ------- ----------
/u01/app/oracle/oradata/orcl/system01.dbf              1 SYSTEM    11517939
/u01/app/oracle/oradata/new_location/sysaux01.dbf              2 ONLINE    11517939
/u01/app/oracle/oradata/orcl/undotbs01.dbf             3 ONLINE    11517939
/u01/app/oracle/oradata/new_location/users01.dbf       4 ONLINE    11536743
/u01/app/oracle/oradata/new_location/example01.dbf     5 ONLINE    11536772
/u01/app/oracle/oradata/new_location/example02.dbf     6 ONLINE    11536772
/u01/app/oracle/oradata/new_location/michael01.dbf     7 ONLINE    11536718
/u01/app/oracle/oradata/new_location/users02.dbf       8 ONLINE    11536743
/u01/app/oracle/oradata/new_location/michael02.dbf     9 ONLINE    11536718

Moving oracle temp datafile:




SQL> SELECT FILE_NAME , TABLESPACE_NAME FROM dba_temp_files;

FILE_NAME                                          TABLESPACE_NAME
-------------------------------------------------- --------------------

SQL> CREATE TEMPORARY TABLESPACE temp2
  2  TEMPFILE '/u01/app/oracle/oradata/orcl/temp02.dbf' SIZE 5M REUSE
  3  AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
  4  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

SQL>ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

Database altered.

SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.


SQL> CREATE TEMPORARY TABLESPACE temp
  2  TEMPFILE '/u01/app/oracle/oradata/new_location/temp01.dbf' SIZE 100M REUSE
  3  AUTOEXTEND ON NEXT 100M MAXSIZE 500M
  4  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;


Tablespace created.


SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Database altered.


SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;


Tablespace dropped.


SQL> SELECT FILE_NAME,TABLESPACE_NAME  FROM dba_temp_files;

FILE_NAME                                          TABLESPACE_NAME  
-------------------------------------------------- --------------------
/u01/app/oracle/oradata/new_location/temp01.dbf     TEMP            


Moving oracle undo tablespace/datafile:





SQL> alter system set undo_tablespace = '';

System altered.

SQL> DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

Tablespace dropped.

SQL> create undo tablespace undotbs1 datafile '/u01/app/oracle/oradata/new_location/undotbs01.dbf' size 500M reuse autoextend on maxsize 2000M;    

Tablespace created.

SQL> alter system set undo_tablespace = 'UNDOTBS1';

System altered.

SQL> select files.tablespace_name, files.file_name

  2  from

  3  (select file_id, file_name, tablespace_name, bytes/1024/1024 mb, autoextensible, 

  4  decode(autoextensible,'NO',bytes/1024/1024,decode(sign(maxbytes-bytes),-1,bytes/1024/1024,maxbytes/1024/1024)) maxmb

  5  from dba_data_files) files,

  6  (select file_id, sum(bytes)/1024/1024 freemb

  7  from dba_free_space

  8  group by file_id) free

 9  where files.file_id = free.file_id (+)

 10  and files.file_name like '%&Fname%'

 11  order by 1,2;

Enter value for fname: 

old  10: and files.file_name like '%&Fname%'

new  10: and files.file_name like '%%'

TABLESPACE_NAME      FILE_NAME                                         

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

EXAMPLE              /u01/app/oracle/oradata/new_location/example01.dbf

EXAMPLE              /u01/app/oracle/oradata/new_location/example02.dbf

MICHAEL              /u01/app/oracle/oradata/new_location/michael01.dbf

MICHAEL              /u01/app/oracle/oradata/new_location/michael02.dbf

SYSAUX               /u01/app/oracle/oradata/new_location/sysaux01.dbf 

SYSTEM               /u01/app/oracle/oradata/orcl/system01.dbf         

UNDOTBS1             /u01/app/oracle/oradata/orcl/undotbs01.dbf        

USERS                /u01/app/oracle/oradata/new_location/users01.dbf  

USERS                /u01/app/oracle/oradata/new_location/users02.dbf  

Moving oracle system datafiles:


Moving system file is little tricky we can not bring the system tablespace offline and then copy the files from old to new location and then rename.


SQL> alter tablespace system offline;
alter tablespace system offline
*
ERROR at line 1:
ORA-01541: system tablespace cannot be brought offline; shut down if necessary

Below steps will recreate the system datafile at new location:


SQL> alter database backup controlfile to trace;

Database altered.

SQL> !ls -laht /u01/app/oracle/diag/rdbms/orcl/orcl/trace | head -10

total 10M

drwxr-x---  2 oracle oinstall  52K Feb  7 13:12 .

-rw-r-----  1 oracle oinstall 515K Feb  7 13:10 alert_orcl.log

-rw-r-----  1 oracle oinstall 7.3K Feb  7 13:10 orcl_ora_16748.trc

-rw-r-----  1 oracle oinstall  131 Feb  7 13:10 orcl_ora_16748.trm

-rw-r-----  1 oracle oinstall  843 Feb  7 13:06 orcl_ora_17506.trc

-rw-r-----  1 oracle oinstall   60 Feb  7 13:06 orcl_ora_17506.trm

-rw-r-----  1 oracle oinstall  881 Feb  7 13:05 orcl_dbrm_16711.trc

-rw-r-----  1 oracle oinstall   60 Feb  7 13:05 orcl_dbrm_16711.trm

-rw-r-----  1 oracle oinstall  840 Feb  7 13:03 orcl_mman_16717.trc

SQL> !cat  /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_16748.trc

Trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_16748.trc

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1

.

.

.

.

.

copy the command from startup mount to the semicomma (;)

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  NOARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 1168

LOGFILE

  GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log'  SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

  '/u01/app/oracle/oradata/orcl/system01.dbf',

  '/u01/app/oracle/oradata/new_location/sysaux01.dbf',

  '/u01/app/oracle/oradata/new_location/undotbs01.dbf',

  '/u01/app/oracle/oradata/new_location/users01.dbf',

  '/u01/app/oracle/oradata/new_location/example01.dbf',

  '/u01/app/oracle/oradata/new_location/example02.dbf',

  '/u01/app/oracle/oradata/new_location/michael01.dbf',

  '/u01/app/oracle/oradata/new_location/users02.dbf',

  '/u01/app/oracle/oradata/new_location/michael02.dbf'

CHARACTER SET WE8MSWIN1252

;

Then change the line  '/u01/app/oracle/oradata/orcl/system01.dbf' with the new location which is '/u01/app/oracle/oradata/new_location/system01.dbf' do the following steps.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> !cp /u01/app/oracle/oradata/orcl/system01.dbf /u01/app/oracle/oradata/new_location/system01.dbf

SQL> STARTUP NOMOUNT

ORACLE instance started.

Total System Global Area  535662592 bytes

Fixed Size                  1337720 bytes

Variable Size             385877640 bytes

Database Buffers          142606336 bytes

Redo Buffers                5840896 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  NOARCHIVELOG

  2      MAXLOGFILES 16

  3      MAXLOGMEMBERS 3

  4      MAXDATAFILES 100

  5      MAXINSTANCES 8

  6      MAXLOGHISTORY 1168

  7  LOGFILE

  8    GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log'  SIZE 50M BLOCKSIZE 512,

  9    GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log'  SIZE 50M BLOCKSIZE 512,

 10    GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log'  SIZE 50M BLOCKSIZE 512

 11  -- STANDBY LOGFILE

 12  DATAFILE

 13    '/u01/app/oracle/oradata/new_location/system01.dbf',

 14    '/u01/app/oracle/oradata/new_location/sysaux01.dbf',

 15    '/u01/app/oracle/oradata/new_location/undotbs01.dbf',

 16    '/u01/app/oracle/oradata/new_location/users01.dbf',

 17    '/u01/app/oracle/oradata/new_location/example01.dbf',

 18    '/u01/app/oracle/oradata/new_location/example02.dbf',

 19    '/u01/app/oracle/oradata/new_location/michael01.dbf',

 20    '/u01/app/oracle/oradata/new_location/users02.dbf',

 21    '/u01/app/oracle/oradata/new_location/michael02.dbf'

 22  CHARACTER SET WE8MSWIN1252

 23  ;

Control file created.

SQL> alter database open;

Database altered.

SQL> select files.tablespace_name, files.file_name

  2  from

  3  (select file_id, file_name, tablespace_name, bytes/1024/1024 mb, autoextensible, 

  4  decode(autoextensible,'NO',bytes/1024/1024,decode(sign(maxbytes-bytes),-1,bytes/1024/1024,maxbytes/1024/1024)) maxmb

  5  from dba_data_files) files,

  6  (select file_id, sum(bytes)/1024/1024 freemb

  7  from dba_free_space

  8  group by file_id) free

 9  where files.file_id = free.file_id (+)

 10  and files.file_name like '%&Fname%'

 11  order by 1,2;

Enter value for fname: 

old  10: and files.file_name like '%&Fname%'

new  10: and files.file_name like '%%'

TABLESPACE_NAME      FILE_NAME                                         

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

EXAMPLE              /u01/app/oracle/oradata/new_location/example01.dbf

EXAMPLE              /u01/app/oracle/oradata/new_location/example02.dbf

MICHAEL              /u01/app/oracle/oradata/new_location/michael01.dbf

MICHAEL              /u01/app/oracle/oradata/new_location/michael02.dbf

SYSAUX               /u01/app/oracle/oradata/new_location/sysaux01.dbf 

SYSTEM               /u01/app/oracle/oradata/new_location/system01.dbf 

UNDOTBS1             /u01/app/oracle/oradata/new_location/undotbs01.dbf

USERS                /u01/app/oracle/oradata/new_location/users01.dbf  

USERS                /u01/app/oracle/oradata/new_location/users02.dbf  

Moving oracle redo log files:

SQL> select     lf.member

  2  from       v$logfile lf

  3  ,  v$log lg

  4  where      lg.group# = lf.group#

  5  order      by 1;

MEMBER                                  

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

/u01/app/oracle/oradata/orcl/redo01.log 

/u01/app/oracle/oradata/orcl/redo02.log 

/u01/app/oracle/oradata/orcl/redo03.log 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> !cp /u01/app/oracle/oradata/orcl/redo01.log /u01/app/oracle/oradata/new_location/redo01.log

SQL> !cp /u01/app/oracle/oradata/orcl/redo02.log /u01/app/oracle/oradata/new_location/redo02.log

SQL> !cp /u01/app/oracle/oradata/orcl/redo03.log /u01/app/oracle/oradata/new_location/redo03.log

SQL> startup mount

ORACLE instance started.

Total System Global Area  535662592 bytes

Fixed Size                  1337720 bytes

Variable Size             385877640 bytes

Database Buffers          142606336 bytes

Redo Buffers                5840896 bytes

Database mounted.

SQL> 

SQL> 

SQL> alter database rename file '&old_redo_file' to '&new_redo_file';

Enter value for old_redo_file: /u01/app/oracle/oradata/orcl/redo01.log

Enter value for new_redo_file: /u01/app/oracle/oradata/new_location/redo01.log

old   1: alter database rename file '&old_redo_file' to '&new_redo_file'

new   1: alter database rename file '/u01/app/oracle/oradata/orcl/redo01.log' to '/u01/app/oracle/oradata/new_location/redo01.log'

Database altered.

SQL> /

Enter value for old_redo_file: /u01/app/oracle/oradata/orcl/redo02.log

Enter value for new_redo_file: /u01/app/oracle/oradata/new_location/redo02.log

old   1: alter database rename file '&old_redo_file' to '&new_redo_file'

new   1: alter database rename file '/u01/app/oracle/oradata/orcl/redo02.log' to '/u01/app/oracle/oradata/new_location/redo02.log'

Database altered.

SQL> /

Enter value for old_redo_file: /u01/app/oracle/oradata/orcl/redo03.log

Enter value for new_redo_file: /u01/app/oracle/oradata/new_location/redo03.log

old   1: alter database rename file '&old_redo_file' to '&new_redo_file'

new   1: alter database rename file '/u01/app/oracle/oradata/orcl/redo03.log' to '/u01/app/oracle/oradata/new_location/redo03.log'

Database altered.

SQL> alter database open;

Database altered.

SQL> select     lf.member

  2  from       v$logfile lf

  3  ,  v$log lg

  4  where      lg.group# = lf.group#

  5  order      by 1;

MEMBER                                          

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

/u01/app/oracle/oradata/new_location/redo01.log 

/u01/app/oracle/oradata/new_location/redo02.log 

/u01/app/oracle/oradata/new_location/redo03.log 

Moving oracle controlfiles:

SQL> select name from v$controlfile;

NAME

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

/u01/app/oracle/oradata/orcl/control01.ctl

/u01/app/oracle/flash_recovery_area/orcl/control02.ctl

SQL> show parameter control_files

NAME                                 TYPE        VALUE

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

control_files                        string      /u01/app/oracle/oradata/orcl/c

                                                 ontrol01.ctl, /u01/app/oracle/

                                                 flash_recovery_area/orcl/contr

                                                 ol02.ctl

SQL> alter system set control_files='/u01/app/oracle/oradata/new_location/control01.ctl','/u01/app/oracle/flash_recovery_area/orcl/control02.ctl' scope=spfile;

System altered.

SQL> show parameter control_files

NAME                                 TYPE        VALUE

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

control_files                        string      /u01/app/oracle/oradata/orcl/c

                                                 ontrol01.ctl, /u01/app/oracle/

                                                 flash_recovery_area/orcl/contr

                                                 ol02.ctl

SQL> select name from v$controlfile;

NAME

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

/u01/app/oracle/oradata/orcl/control01.ctl

/u01/app/oracle/flash_recovery_area/orcl/control02.ctl

SQL> alter system set control_files='/u01/app/oracle/oradata/new_location/control01.ctl','/u01/app/oracle/flash_recovery_area/orcl/control02.ctl' scope=spfile;

System altered.

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u01/app/oracle/oradata/orcl/c
                                                 ontrol01.ctl, /u01/app/oracle/
                                                 flash_recovery_area/orcl/contr
                                                 ol02.ctl
SQL> select name from v$controlfile;

NAME
----------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/flash_recovery_area/orcl/control02.ctl



All the files are moved to new directory


SQL> alter system set control_files='/u01/app/oracle/oradata/new_location/control01.ctl','/u01/app/oracle/flash_recovery_area/orcl/control02.ctl' scope=spfile;

System altered.

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u01/app/oracle/oradata/orcl/c
                                                 ontrol01.ctl, /u01/app/oracle/
                                                 flash_recovery_area/orcl/contr
                                                 ol02.ctl
SQL> select name from v$controlfile;

NAME
----------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/flash_recovery_area/orcl/control02.ctl

I hope above steps will help you.