Redirect non-www to www and vice versa using .htacccss

In this post with simply two lines of code I will show how to redirect all non-www traffic to www traffic as well as how to redirect all www traffic to non-www by editing .htaccess file.

Redirect non-www to www
Edit .htaccess file and add following lines,

RewriteEngine On
RewriteCond %{HTTP_HOST} !^www\.
RewriteRule ^(.*)$ http://www.%{HTTP_HOST}/$1 [R=301,L]

This will redirect any non-www to www site regardless of domain name.

Redirect all www to non-www
Edit .htaccess file and add following lines,

RewriteEngine On
RewriteCond %{HTTP_HOST} !^domain-name\.com$ [NC]
RewriteRule ^(.*)$ http://domain-name.com/$1 [R=301,L]

Here we explicitly mentioned domain name.

In the example replace domain-name with your domain. For example for site http://arju-on-it.com my .htaccess of redirect all www to non-www will look like below,

RewriteEngine On
RewriteCond %{HTTP_HOST} !^arju-on-it\.com$ [NC]
RewriteRule ^(.*)$ http://arju-on-it.com/$1 [R=301,L]

ORA-01012: not logged on

Problem Description
No other user can connect to database. Whenever user with sysdba privilege try to login to database it shows connected but it does not allow to happen any query to database instead it fails with ORA-01012: not logged on as below.

oracle:/home/oracle CIDMP> $sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Oct 28 00:12:16 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected.

SQL> select open_mode from v$database;
select open_mode from v$database
*
ERROR at line 1:
ORA-01012: not logged on

Sometimes connecting as sysdba shows database is in idle instance but whenever you issue startup it says ORA-01081: cannot start already-running ORACLE - shut it down first. 

SQL> conn / as sysdba
Connected to an idle instance.

SQL> desc v$instance
ERROR:
ORA-01012: not logged on

SQL> conn / as sysdba
Connected to an idle instance.

SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first

Cause of the Problem
Note that this problem is not same as SP2-0640: Not connected which is sql*plus message and it raised whenever you try to run query without log in to database. Just like below.

SQL> conn arju/a
Connected.
SQL> conn arju/e
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> select instance from v$thread;
SP2-0640: Not connected

"The ORA-01012: not logged on" error occurred due to heavy load in the database. If there is maximum number of sessions connected to the database(Which is in turn defined by PROCESSES parameter) and database is flooded with concurrent load then database does not allow sysdba privileged user as well as other users to be connected to the database. If sysdba privileged user try to connect to database then above error ORA-01012 comes.

Solution of the problem
The solution is free up the sessions. You can do it in whatever ways you want. Like,
1)Shut down application server sessions: You can shut down application server and thus will release the sessions.

2)Shut down database server: Shuttting down database server will shutdown database and all sessions will be release.

3)Kill oracle process: You can kill oracle process and then you need to start the oracle database again which will also work.

Related Documents
http://arjudba.blogspot.com/2008/07/ora-00018-ora-00020-maximum-number-of.html
http://arjudba.blogspot.com/2009/01/ora-00054-resource-busy-and-acquire.html
http://arjudba.blogspot.com/2008/07/process-and-runtime-limits.html
http://arjudba.blogspot.com/2008/04/user-resource-limits-in-oracle.html

An example of ORA-01536 when quota exhaust on dependent objects

I am inspired to write this topic after getting a post inside the forum http://arju-on-it.com/blog/forum/server-administration/ora-01536-space-quota-exceeded-for-tablespace-mon_tbs.
In the example I have shown the reason why ORA-01536: space quota exceeded for tablespace error comes.

In this post I will give you the example when ORA-01536 occurs due to the dependent object on the table.
1)Create user test1 and grant unlimited quotes on default tablespace.
SQL> create user test1 identified by t default tablespace users;

User created.

SQL> grant create session, create table to test1;

Grant succeeded.

SQL> alter user test1 quota unlimited on users;

User altered.

2)Create user test2 and grant 1M quota.
SQL> create user test2 identified by t default tablespace users;

User created.

SQL> grant create session, create table, create trigger to test2;

Grant succeeded.

SQL> alter user test2 quota 1M on users;

User altered.

3)Create a table inside test1 schema.

SQL> conn test1/t
Connected.

SQL> create table tab1 as select level a1, level a2 from dual connect by level <10;

Table created.

SQL> conn test2/t
Connected.
SQL> create table tab1 as select level a1, level a2 from dual connect by level <1;

Table created.

SQL> conn test1/t
Connected.
SQL> grant select on tab1 to test2;

Grant succeeded.

5)Create a trigger inside test2 schema which inserts based on test1 insert.
SQL> conn / as sysdba
Connected.

SQL> create or replace trigger test2.t after insert on test1.tab1
2 begin
3 insert into test2.tab1 select * from test1.tab1;
4 end;
5 /

Trigger created.

6)Connect to test1 and insert more rows.
SQL> conn test1/t
Connected.

SQL> insert into tab1 select level a1, level a2 from dual connect by level <100;

99 rows created.

SQL> insert into tab1 select level a1, level a2 from dual connect by level <10000;

9999 rows created.

SQL> insert into tab1 select level a1, level a2 from dual connect by level <100000;
insert into tab1 select level a1, level a2 from dual connect by level <100000
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USERS'
ORA-06512: at "TEST2.T", line 2
ORA-04088: error during execution of trigger 'TEST2.T'

7)Log on as dba user and check for dependent objects. Grant quota of the owner of the dependent objects tablespaces.

SQL> conn / as sysdba
Connected.

SQL> select NAME,TYPE, OWNER from dba_dependencies where REFERENCED_NAME='TAB1' AND
2 REFERENCED_OWNER='TEST1';

NAME TYPE OWNER
------------------------------ ----------------- ------------------------------
T TRIGGER TEST2

See trigger definition and as trigger inserts into TAB1 table which resides in USERS tablespace so grant more quote to TEST2 user on tablespace USERS.

SQL> ALTER USER TEST2 QUOTA 10M on USERS;

User altered.

SQL> conn test1/t
Connected.

SQL> insert into tab1 select level a1, level a2 from dual connect by level <100000;

99999 rows created.

And it is ok now.

Related Documents

ORA-30041: Cannot grant quota on the tablespace

Changing a DBA user to a normal user in oracle

Create user in oracle

Minimum privilege needed to take data pump export

A user can do work in his schema with only Create Session Privilege.

How to determine the software version of the database and the Data Pump client

In order to know your oracle database version issue,

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

Alternatively, you can query from product_component_version.

SQL> col product for a40
SQL> col version for a11
SQL> col status for a15
SQL> select * from product_component_version;

PRODUCT                                  VERSION     STATUS
---------------------------------------- ----------- ---------------
NLSRTL                                   11.1.0.6.0  Production
Oracle Database 11g Enterprise Edition   11.1.0.6.0  Production
PL/SQL                                   11.1.0.6.0  Production
TNS for 32-bit Windows:                  11.1.0.6.0  Production

In order to see your Export data pump client version issue from command prompt,

C:\>expdp help=y

Export: Release 10.2.0.1.0 - Production on Wednesday, 04 February, 2009 1:33:23

Copyright (c) 2003, 2005, Oracle. All rights reserved.

In order to see your Import data pump client version issue from command prompt,
C:\>impdp help=y

Import: Release 10.2.0.1.0 - Production on Wednesday, 04 February, 2009 1:32:44

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Both in case of Data Pump Export and Import first few digits show it version.

Related Documents

New features in Oracle database 11g of data pump utility

New features of oracle perfomance in 10.2g

List of Oracle Database Version Release.

Timeline of Oracle RDBMS Major Release Version.

How to determine database compatibility level

COMPATIBILITY allows you to use a new release of Oracle, while at the same time guaranteeing backward compatibility with an earlier release.

In order to determine your current database compatibility level you can query from view database_compatible_level.

SQL> col value for a11
SQL> col description for a50
SQL> select * from database_compatible_level;

VALUE DESCRIPTION
----------- --------------------------------------------------
11.1.0.0.0 Database will be completely compatible with this s
oftware version

Alternatively you can check your initialization parameter COMPATIBLE setting.
On Sql*plus simply issue,

SQL> show parameter compatible

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.1.0.0.0

You can also query from v$parameter,
SQL> select value from v$parameter where name='compatible';

VALUE
-----------
11.1.0.0.0

SQL> select value from v$spparameter where name='compatible';

VALUE
-----------
11.1.0.0.0

ORA-02082: a loopback database link must have a connection qualifier

Problem Description
You are trying to create or drop a database link to the same database name. This may be true if you have a database that was cloned from another database on a different machine and now you try to create or drop a database link with the name of the original database. Below is an example.

SQL> create database link tiger;
create database link tiger
*
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier


SQL> drop database link tiger;
drop database link tiger
*
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier

Cause of the Problem
This is an expected behavior if database global name match with the database link creation name. Now let's see the global_name of the database.

SQL> select * from global_name;

GLOBAL_NAME
---------------------------------------
TIGER.REGRESS.RDBMS.DEV.US.ORACLE.COM

We see the global name of the database is started with tiger(db_name) and the default db_domain is REGRESS.RDBMS.DEV.US.ORACLE.COM.

Now whenever we try to create a database link named TIGER (without any domain) it takes name as TIGER.REGRESS.RDBMS.DEV.US.ORACLE.COM (original database link name+ default domain) which is equivalent to global_name of the database and thus error will occur because database link name must not be equal to the global database name.

Solution of the Problem
Two different solution of this problem.
1)Change the database link name so that it is different from global database name.

SQL> create database link tiger.net connect to user_name identified by password using 'TNS_NAME';

Database link created.

2)Change the global name of the database, create/drop database link and then back to global name of the database to the original name.
i)Error when creating database link name with same of global_name.

SQL> create database link tiger connect to user_name identified by password using 'TNS_NAME';
create database link tiger connect to user_name identified by password using 'TNS_NAME'
*
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier

ii)Change the global database name.
SQL> alter database rename global_name to test;
Database altered.

iii)Now dropping the database link tiger will work as now it (TIGER.REGRESS.RDBMS.DEV.US.ORACLE.COM) is not same as the global name (TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM)

SQL> select * from global_name;

GLOBAL_NAME
---------------------------------------
TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM

SQL> drop database link tiger;
Database link dropped.

iv)Also creating the database link with named Tiger (by default take as TIGER.REGRESS.RDBMS.DEV.US.ORACLE.COM) will work.
SQL> create database link tiger connect to user_name identified by password using 'TNS_NAME';

Database link created.

You can check it by,

SQL>  col host for a10
SQL>  set lines 140
SQL>  col owner for a10
SQL>  col db_link for a40
SQL> select * from dba_db_links where host='TNS_NAME';

OWNER      DB_LINK                                  USERNAME         HOST       CREATED
---------- ---------------------------------------- ---------------- ---------- ---------
MAXIMSG    TIGER.REGRESS.RDBMS.DEV.US.ORACLE.COM    USER_NAME        TNS_NAME   24-JAN-09
MAXIMSG    TIGER.NET                                USER_NAME        TNS_NAME   24-JAN-09

v)Back to the original global database name.
SQL> alter database rename global_name to tiger;

Database altered.

Related Documents
Troubleshooting ORA-2085 "database link %s connects to %s"
ORA-02070: database does not support in this context

ORA-00845: MEMORY_TARGET not supported on this system

Problem Description
While creating a startup database using dbca the database creation GUI gives error message in a pop up window,
ORA-00845: MEMORY_TARGET not supported on this system
from where you can ignore the error message.
The similar scenario also occur whenever you try to start your database then startup shows error message like below.

SQL> STARTUP
ORA-00845: MEMORY_TARGET not supported on this system

Cause of the Problem
•Starting from Oracle 11g the automatic memory management feature is now defined with parameter MEMORY_TARGET and MEMMORY_MAX_TARGET.

•On linux file system the shared memory need to be mounted on /dev/shm directory on the operating system.

•And the size of /dev/shm needs to be greater than MEMORY_TARGET or MEMMORY_MAX_TARGET.

•The AMM (Automatic Memory Management) now in 11g manages both SGA and PGA together by MMAN process.

•The MEMORY_TARGET parameter in 11g comes for (SGA_TARGET+PGA_AGGREGATE_TARGET) which was in 10g.

•And MEMORY_MAX_TARGET parameter in 11g comes instead of SGA_MAX_TARGET parameter which was in 10g.

•The ORA-00845:can arises for the following two reasons on linux system.

1)If the shared memory which is mapped to /dev/shm directory is less than the size of MEMORY_TARGET or MEMORY_MAX_TARGET.
or,
2)If the shared memory is not mapped to /dev/shm directory.

Solution of the Problem
Make sure /dev/shm is properly mounted. You can see it by,
#df -h or #df -k command.
The output should be similar like

$ df -k
Filesystem            Size  Used Avail Use% Mounted on
...
shmfs                 1G    512M 512M  50% /dev/shm

We see here for /dev/shm we have assigned 1G memory. Now if you set MEMORY_TARGET more than 1G then above ORA-845 will arise. For example if you have MEMORY_TARGET or MEMORY_MAX_TARGET set to 12G then you can mount shared memory to 13g like below.
As a root user,
# mount -t tmpfs shmfs -o size=13g /dev/shm
In order to make the settings persistence so that it will affect after restarting machine add an entry in /etc/fstab similar to the following:

shmfs /dev/shm tmpfs size=13g 0

How to make global read only user

In many cases we need to make a read only user for the entire data dictionary and for all the tables of all schemas. Suppose in order to analysis the entire schema or to monitor performance of the database you might give only select privilege of the entire schema of a user as well as on the dictionary objects of database. But that user will not be able to delete or alter or modify anything of other schema objects. If your goal is make just read only user of a schema then have a look at
http://arjudba.blogspot.com/2008/09/create-read-only-user-for-schema.html

You can simply achive this goal by giving him three permission.
1)Create Session: With this privilege he will be able to logon in the the database.

2)Select any Table: With this privilege he will be able to select any table schema rather than SYS objects. Though if O7_DICTIONARY_ACCESSIBILITY parameter is set to TRUE then SELECT ANY TABLE privilege provides access to all SYS and non-SYS objects.

3)Select any dictionary: If O7_DICTIONARY_ACCESSIBILITY parameter is set to TRUE then this privilege is not required to select data dictionary objects or in other word SYS objects. If O7_DICTIONARY_ACCESSIBILITY parameter is set to false then this privilege is required to give select permission on the data dictionary objects.

Following is an example. User arju will be globally read only user.
SQL> create user arju identified by a;
User created.

SQL> grant create session,select any dictionary,select any table to arju;
Grant succeeded.

Related Documents
Use SELECT ANY DICTIONARY or SELECT_CATALOG_ROLE or SELECT ANY TABLE
What is O7_DICTIONARY_ACCESSIBILITY?
A user can do work in his schema with only Create Session Privilege.
Create Read only user for a Schema

Implicitly Assigned temp tablespace changes after database default tablespace change

In this post I have shown if you change your database default temporary tablespace then implicitly assigned users to previous temporary tablespace automatically assigned to the new database default temporary tablespace but the users to whom temporary tablespaces are explicitly assigned they are not shifted.

Let's start by seeing database default temporary tablespace which is temp4.
SQL> select PROPERTY_VALUE from database_properties
where property_name = 'DEFAULT_TEMP_TABLESPACE'; 2

PROPERTY_VALUE
--------------------------------------------------------------------------------
TEMP4

Now if you create user then that user will be automatically assigned to temporary tablespace temp4 if we don't assign any. We create two users named Test1 and Test2. Create Test1 with no temp clause but create test2 with temp clause to temporary tablespace temp.
SQL> create user test1 identified by t;
User created.

SQL> create user test2 identified by t temporary tablespace temp;
User created.

As TEST1 is not assigned any tablespace, so database default temporary tablespace is assigned to it. We can see it by,

SQL> select username, temporary_tablespace from dba_users where username in ('TEST1','TEST2');

USERNAME                       TEMPORARY_TABLESPACE
------------------------------ ------------------------------
TEST1                          TEMP4
TEST2                          TEMP

Now create a new temporary tablespace temp3.

SQL> create temporary tablespace temp3 tempfile '/oradata2/temp3.dbf' size 10M;
Tablespace created.

Let's chnage the database default temporary tablespace to temp3.

SQL> alter database default temporary tablespace temp3;
Database altered.

Now we will see TEST1 temporary tablespace change but TEST2 temporary tablespace does not.
SQL> select username, temporary_tablespace from dba_users where username in ('TEST1','TEST2');

USERNAME                       TEMPORARY_TABLESPACE
------------------------------ ------------------------------
TEST1                          TEMP3
TEST2                          TEMP

Related Documents
ORA-12906 cannot drop default temporary tablespace
Drop Temporary Tablespace Hangs
The operation that require sort area or Temporary Tablespace
Information about Temporary Segments.

All Purge commands in Oracle -Delete an object permanently

Whenever we drop any objects other than objects reside in system tablespace using DROP command the objects became unavailable and reside in recyclebin. In fact they reside in the same tablespace where it was and thus does not free any space in the tablespace. In order to remove the objects permanently from the recyclebin we have to use PURGE command. With PURGE command we can do,
•Remove a table or index from your recycle bin and release all of the space associated with the object, or
•To remove the entire recycle bin, or
•To remove part of all of a dropped tablespace from the recycle bin.

However during drop you may wish not to put objects in recyclebin. With DROP command you may wish to drop it permanently not residing in recyclebin. Like,

SQL> create table t(a number);
Table created.

SQL> purge table t;
purge table t
*
ERROR at line 1:
ORA-38307: object not in RECYCLE BIN
As PURGE command is only to drop objects from recyclebin so purge t fails with ORA-38307.

In order to drop t along with recyclebin use,
SQL> drop table t purge;
Table dropped.

To see the all contents of the recyclebin query from DBA_RECYCLEBIN. A user can see his own schma recylebin by querying from USER_RECYCLEBIN. A synonym of USER_RECYCLEBIN is RECYCLEBIN.

The list of Purge Commands are listed below.
1)Purge Table/Index:
•Use
PURGE TABLE table_name
to purge table and to purge index use
PURGE INDEX index_name.
To purge test table which reside in recyclebin now or in in other which was dropped previously by DROP TABLE TEST command and now in recyclebin issue,
PURGE TABLE TEST;

•In the name you can specify either the original user-specified name or the system-generated name in the recylebin.

•While purging objects from recylebin if you specify the user-specified name, and if the recycle bin contains more than one object of that name, then the database purges the object that has been in the recycle bin the longest.

•System generated name in recylebin are unique. So if you purge an object with system generated name specified object is purged.

•When the database purges a table, all table partitions, LOBs and LOB partitions,
indexes, and other dependent objects of that table are also purged.

2)PURGE RECYCLEBIN: To delete/remove objects of current users from the recylebin use PURGE RECYCLEBIN. Other schema users recyclebin objects will be intact. Command is
SQL>PURGE RECYCLEBIN;

3)PURGE DBA_RECYCLEBIN: To clean up all user's recylebin you can use PURGE DBA_RECYCLEBIN statement. Only user having SYSDBA system privilege can do this task. Command is simply,
SQL>CONN / AS SYSDBA
SQL>PURGE DBA_RECYCLEBIN;

4)PURGE TABLESPACE tablespace_name: With PURGE TABLESPACE tablespace clause you can purge all objects residing in the specified tablespace from the recycle bin. To clean up all objects that reside in recylebin of USERS tablespace use,
SQL>PURGE TABLESPACE USERS;

5)PURGE TABLESPACE tablespace USER username: With this clause you can reclaim space in a tablespace for a specified user. The specified user's objects from the specified tablespace recyclebin will be cleaned up. This operation is useful when a particular user is running low on disk quota for the specified tablespace.

To clean up recylebin of user ARJU from TABLESPACE USERS use,
SQL> PURGE TABLESPACE USERS USER ARJU;

Related Documents
Drop Table in Oracle
Flashback Table and Flashback Drop

http://arjudba.blogspot.com/2008/06/drop-table-in-oracle.html
http://arjudba.blogspot.com/2008/05/create-user-in-oracle.html
http://arjudba.blogspot.com/2008/06/create-temporary-table-in-oracle.html
Create, Alter , Rename, Modify Table SQL

http://arjudba.blogspot.com/2008/05/ora-08189-cannot-flashback-row-movement.html

Drop Temporary Tablespace Hangs

Problem Description
The DROP temporary tablespace operations take long time and in fact it hangs. If you take a 10046 trace of the session it shows "enqueue" wait.

Cause of The Problem In the section http://arjudba.blogspot.com/2008/05/operation-that-require-sort-area-or.html I discussed about the operation that needs sort space. Whenever an operation is using sort space an entry is found in the v$sort_usage. After the operation finishes entry from $sort_usage vanishes. But dead connections (while running a query) may leave entries in v$session (status inactive) and in v$sort_usage. The query about the users who is Performing Sort operation in Temp Segments can be found in http://arjudba.blogspot.com/2008/05/information-about-temporary-segments.html

Solution of The Problem
1.Create a new temporary tablespace and assign all users to this new tablespace. You can easily do this task on unix system as,
CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE '/oradata2/temp02.dbf' SIZE 100m;
Change it for all by
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;

2.Find out the all the sessions that are not active and have an entry in V$sort_usage.
You can do it by,
SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
a.username,a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr;

3.Kill those session.
Using
alter system kill session 'SID_NUMBER, SERIAL#NUMBER'; kill those session that are not being used actually.
where SID_NUMBER and SERIAL#NUMBER is found in step 2.

4. Now dropping the previous tablespace
DROP TABLESPACE previous_temp_tbs;

Relationship between Transaction and Undo Segments.

•When a transaction starts, Oracle will assign the corresponding transaction to only one undo segment. It is not possible for the undo data generated by one transaction to cut across multiple undo segments.

•It is need to know that undo segments are not fixed in size. So if a transaction does manage to fill its undo segment, Oracle will automatically add another extent to the segment so that the transaction can continue.

•It is possible for multiple transactions to share one undo segment, but in normal running this should not occur.Oracle will automatically generate new undo segments on demand, in an attempt to
ensure that it is never necessary for transactions to share undo segments.

•If Oracle has found it necessary to extend its undo segments or to generate additional segments, when the workload drops Oracle will shrink and drop the segments, again automatically.

Related Documents:
--------------------

How Undo Space Allocation Works

Managing Archive Destination LOG_ARCHIVE_DEST, LOG_ARCHIVE_DEST_n

You can choose whether to archive redo logs to a single destination or multiplex them. The LOG_ARCHIVE_DEST, LOG_ARCHIVE_DEST_n and DB_RECOVERY_FILE_DEST parameter specify where the archived redo log files will be stored.

1)If you want to archive only to a single destination, you specify that destination in the LOG_ARCHIVE_DEST initialization parameter.

2)If you want to multiplex the archived logs, you can choose whether to archive to up to ten locations (using the LOG_ARCHIVE_DEST_n parameters)

3)If you choose to archive only two local location you can choose a primary and a secondary destination (using LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST).

4)Note that, LOG_ARCHIVE_DEST is incompatible with the LOG_ARCHIVE_DEST_n parameters. So you can't set LOG_ARCHIVE_DEST and LOG_ARCHIVE_DEST_n at the same time. LOG_ARCHIVE_DEST must be defined as the null string ("") or (' ') when any LOG_ARCHIVE_DEST_n parameter has a value other than a null string.

Method 1: Using the LOG_ARCHIVE_DEST Parameter:
---------------------------------------------------
Only set LOG_ARCHIVE_DEST parameter.

Method 2: Using the LOG_ARCHIVE_DEST_n Parameter
---------------------------------------------------
Use the LOG_ARCHIVE_DEST_n parameter (where n is an integer from 1 to 10) to specify from one to ten different destinations for archival. Each numerically suffixed parameter uniquely identifies an individual destination.

Format: LOG_ARCHIVE_DEST_n='{LOCATION|SERVICE} destination'

If you use the LOCATION keyword, specify a valid path name for your operating system.

If you specify SERVICE, the database translates the net service name through the tnsnames.ora file to a connect descriptor.

Like, alter system set log_archive_dest_1= 'LOCATION=/oradata1';
alter session set log_archive_dest_3='LOCATION=/oradata2';


If you set DB_RECOVERY_FILE_DEST then LOG_ARCHIVE_DEST_10 is implicitly set unless LOG_ARCHIVE_DEST_n is specified.

Method 3: Using LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST
------------------------------------------------------------
To specify a maximum of two locations, use the LOG_ARCHIVE_DEST parameter to specify a primary archive destination and the LOG_ARCHIVE_DUPLEX_DEST to specify an optional secondary archive destination.

All locations must be local.

To see archival destination issue,
archive log list and
select DEST_ID, DEST_NAME,DESTINATION from v$archive_dest;
show parameter LOG_ARCHIVE_DEST;

Related Documents
Handle 16018 and 16019 Error
Change database Archive mode

Relocate Datafiles in Oracle Database.

Rename/relocate datafiles operation vary based on the log mode of the database. Database may be in archival mode or noarchive mode. In order to know the log mode of the database issue,
SELECT LOG_MODE FROM V$DATABASE;
If you get ARCHIVELOG the follow A).
If you get NOARCHIVELOG then follow B).
A)Archival Mode Operation
1)Determine the datafile on which you will do the operation,

SQL> select file_name,file_id from dba_data_files where tablespace_name='TEST_MOVE';

FILE_NAME       FILE_ID
------------------------------------------   -----------------
/oradata/Arju/test_move.dbf                        5

2)Take the tablespace of the specified datafile offline. Like, in case of Archivelog use,

SQL> alter database datafile 5 offline;
Database altered.

To use the form of the ALTER DATABASE statement, the database must be in ARCHIVELOG mode. This requirement prevents you from accidentally losing the datafile, since taking the datafile offline while in NOARCHIVELOG mode is likely to result in losing the file.

3)Move the datafile by using OS(cp/scp/copy) command or by using DBMS_FILE_TRANSFER package to you desired location.

SQL> !scp /oradata/Arju/test_move.dbf /oradata/Arju/arju/after_move.dbf

4)Rename the datafiles within the database.

SQL> alter database rename file '/oradata/Arju/test_move.dbf' to '/oradata/Arju/arju/after_move.dbf';
Database altered.

5)Recover the Datafile if needed.

SQL> alter database recover datafile 5;
Database altered.

6)Query the Data Dictionary to see the new Location.

SQL> select file_name,file_id from dba_data_files where tablespace_name='TEST_MOVE';

FILE_NAME                                   FILE_ID
-------------------------------------------------
/oradata/Arju/arju/after_move.dbf                 5

7)Backup the database.

Noarchival Mode operation
1)Shutdown the database.
SQL>Shut immediate;

2)Copy the desired datafile to your new location.
You can use cp/scp/copy/dd command or any GUI tool to copy datafile to new location.

3)Mount the database.
SQL>startup mount

4)Rename the datafile to update the controlfile information.
SQL> ALTER DATABASE RENAME FILE 'old_location' TO 'new_location';

5)Open the database for normal use.
SQL>Alter database open;

Related Documents
http://arjudba.blogspot.com/2008/08/how-to-resize-datafile.html

http://arjudba.blogspot.com/2008/05/drop-datafile-in-oracle.html

http://arjudba.blogspot.com/2008/05/recover-lost-datafile-without-backup.html

ORA-01940: Cannot drop a user that is currently connected

Problem Description:

SQL> drop user strmadmin cascade;
drop user strmadmin cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected

Solution of The Problem:

Find out the connected user sid and serial# by,

SQL> select sid, serial# from v$session where username = 'STRMADMIN';

   SID    SERIAL#
---------- ----------
   268       1268
   315       1223

Before killing session you may wish to lock the account for further connection attempts. This is extremely necessary to drop a user who automatically establish session like to drop an application user or to drop a user who perform batch jobs.

SQL> Alter user strmadmin account lock;

Now kill the connected session.
SQL> alter system kill session '268,1268';
System altered.

SQL> alter system kill session '315,1223';
System altered.

And then drop the user.
SQL> drop user strmadmin cascade;
User dropped.

Related Documents
Drop User in Oracle