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.

ORA-12906 cannot drop default temporary tablespace

In this post I have shown how to solve the error ORA-12906: cannot drop default temporary tablespace.

Let's have a look about temporary tablespace assigned to users ARJU, PROD and SCOTT.

SQL> select username, temporary_tablespace from dba_users where username in ('ARJU','SCOTT','PROD');

USERNAME                       TEMPORARY_TABLESPACE
------------------------------ ------------------------------
ARJU                           TEMP
PROD                           TEMP
SCOTT                          TEMP

So These users are assigned to TEMP temporary tablespace.
Now have a look at database default tablespace.
SQL> select PROPERTY_VALUE from database_properties
where property_name = 'DEFAULT_TEMP_TABLESPACE';

PROPERTY_VALUE
--------------------------------------------------------------------------------
TEMP

Whenever you try to drop database default tablespace it fails with error ORA-12906.
SQL> drop tablespace temp;
drop tablespace temp
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace

In order to solve this problem you must assign database default tenporary tablespace to a new one.
To do this create a new tempoary tablespace temp2 and then assign it to database default tablespace.

To create a new one,
SQL> create temporary tablespace temp2 tempfile '/oradata2/temp2.dbf' size 10M;
Tablespace created.

To make this new one to database default temporary tablespace,
SQL> alter database default temporary tablespace temp2;
Database altered.

After chaging database default temporary tablespace you will notice all user's temporary tablespace are also changed.

SQL> select username, temporary_tablespace from dba_users where username in ('ARJU','SCOTT','PROD');

USERNAME                       TEMPORARY_TABLESPACE
------------------------------ ------------------------------
ARJU                           TEMP2
PROD                           TEMP2
SCOTT                          TEMP2

You can easily drop by,
SQL> drop tablespace temp;
Tablespace dropped.

Related Documents
Free space in Temporary Tablespace
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

Dropping all objects in a schema

If you decide to delete all objects of a certain schema then better drop the schema. It will save timing as well as easy process. However inside a schema a user can manually drop all his objects. Below is the procedure about how a user can drop all object under his schema.

1)Connect to database as the user that you want to drop all his objects.
Suppose I want to drop test schema objects.
$sqlplus test/test

2)Script for dropping all objects.
declare
cursor c_get_objects is
select object_type,'"'||object_name||'"'||decode(object_type,'TABLE' ,' cascade constraints',null) obj_name from user_objects
where object_type in ('TABLE','VIEW','PACKAGE','SEQUENCE','PROCEDURE','FUNCTION',
'SYNONYM', 'MATERIALIZED VIEW')
order by object_type;
cursor c_get_objects_type is select object_type, '"'||object_name||'"' obj_name from user_objects where object_type in ('TYPE');
begin
for object_rec in c_get_objects loop
execute immediate ('drop '||object_rec.object_type||' ' ||object_rec.obj_name);
end loop;
for object_rec in c_get_objects_type loop
begin execute immediate ('drop '||object_rec.object_type||' ' ||object_rec.obj_name);
end;
end loop;
end;
/

3)Purge the recyclebin objects.
SQL>PURGE RECYCLEBIN;

4)Drop the queues if you use advanced queuing.

declare
cursor active_queues is select OBJECT_NAME from user_objects where OBJECT_TYPE = 'QUEUE';
cursor active_queue_tables is select OBJECT_NAME from user_objects where OBJECT_TYPE = 'TABLE';
begin
for v_queue in active_queues loop
DBMS_AQADM.STOP_QUEUE (queue_name => v_queue.object_name);
DBMS_AQADM.DROP_QUEUE (queue_name => v_queue.object_name);
end loop;
for v_table in active_queue_tables loop
DBMS_AQADM.DROP_QUEUE_TABLE (queue_table => v_table.object_name, force => TRUE);
end loop;
end;
/

5)Check for if any objects inside schema.
Be sure no rows return after issuing following query.

SQL> select * from user_objects;
no rows selected

How to generate External Table from sql loader

In many cases we might want to use external table to load data rather than Sql*Loader because in external table we can load data parallel. In this post I will show how we can generate external table easily with help of sql loader. With this method we can easily get rid of writing too many codes for external tables.

Suppose my data file is 3.txt which is under C drive on my windows machine and contains a single record,
1, momin

The next step is to create a control file for SQL loader. I named it control.ctl made it as follows,
LOAD DATA
infile 'c:\3.txt'
into table std
fields terminated by ','
(id , name)

Now invoke the Sql loader as below. Note that addition word that need is external_table=generate_only. With this keyword only externa table will be created but will not load any data actually on table.

sqlldr user_id/password control=c:\control.ctl.txt log=5.txt external_table=generate_only

After invoking I open the log file and important contents from logfile is,

CREATE DIRECTORY statements needed for files
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS 'C:\'
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS 'c:\'


CREATE TABLE statement for external table:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_STD"
(
"ID" NUMBER,
"NAME" VARCHAR2(10)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8MSWIN1252
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'3.bad'
LOGFILE 'c:\5.txt_xt'
READSIZE 1048576
FIELDS TERMINATED BY "," LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"ID" CHAR(255)
TERMINATED BY ",",
"NAME" CHAR(255)
TERMINATED BY ","
)
)
location
(
'3.txt'
)
)REJECT LIMIT UNLIMITED


INSERT statements used to load internal tables:
------------------------------------------------------------------------
INSERT /*+ append */ INTO STD
(
ID,
NAME
)
SELECT
"ID",
"NAME"
FROM "SYS_SQLLDR_X_EXT_STD"


statements to cleanup objects created by previous statements:
------------------------------------------------------------------------
DROP TABLE "SYS_SQLLDR_X_EXT_STD"
DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000

Related Documents
How to load data using external table into oracle database
How to Load or copy data from SQL Server or excel to Oracle

Archive Destination Settings fails with ORA-32017 and ORA-16179

Problem Description
While enabling my archived log file to more than one location whenever I set log_archive_dest_1 it fails with error ORA-32017 and ORA-16179.
SQL> alter system set log_archive_dest_1='c:\test';
alter system set log_archive_dest_1='c:\test'
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-16179: incremental changes to "log_archive_dest_1" not allowed with SPFILE

Cause of The Problem
To set LOG_ARCHIVE_DEST_n you must include either the LOCATION or the SERVICE attribute for each destination to specify where to archive the redo data. But in the command this is not used.

Solution of The Problem
While setting LOG_ARCHIVE_DEST_n there are several usage notes like,

•Either the LOCATION or the SERVICE attribute must be specified. There is no default.

•With the LOCATION attribute local destination is specified. You can set local disk path or USE_DB_RECOVERY_FILE_DEST that will serve as the flash recovery area using the DB_RECOVERY_FILE_DEST initialization parameter.

•With the SERVICE attribute you can specify remote destination.

•SERVICE attribute is specified with a valid Oracle Net service name (SERVICE=net_service_name) that identifies the remote Oracle database instance to which the redo data will be sent.

Example:
To a local disk destination,
SQL> alter system set log_archive_dest_1='LOCATION=c:\test';
System altered.

SQL> alter system set log_archive_dest_2='LOCATION=g:\';
System altered.

Using flash recovery area,
ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='LOCATION=USE_DB_RECOVERY_FILE_DEST';

Setting to a remote location,
ALTER SYSTEM SET LOG_ARCHIVE_DEST_4='SERVICE=stby1';
In the tnsnames.ora stby1 is defined to a remote location where archived redo logs to be located.

Related Documents
ORA-16018 And ORA-16019 LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n

What will be the Archived Redo Log Destination in Oracle

How to recover or recreate temporary tablespace in 10g

In database you may discover that your temporary tablespace is deleted from OS or it might got corrupted. In order to get it back you might think about recover it. The recovery process is simply recover the temporary file from backup and roll the file forward using archived log files if you are in archivelog mode.

Another solution is simply drop the temporary tablespace and then create a new one and assign new one to the database users.

In order to do that follow the steps here.
1)Find out the temporary datafiles.

SQL> col file_name format a50
SQL> set linesize 200
SQL> select file_name,file_id, tablespace_name from dba_temp_files;
FILE_NAME                                             FILE_ID TABLESPACE_NAME
-------------------------------------------------- ---------- ------------------------------
/oradata2/temp2.dbf                                         1 TEMP2
/oradata2/temp.dbf                                          2 TEMP
/oradata2/temp3.dbf                                         4 TEMP3

2)Make the affected temporary files offline.
SQL> Alter database tempfile 1,2,4 offline;
Database altered.

3)Create a new temporary tablespace and make it database default tablespace.
SQL> create temporary tablespace temp01 tempfile '/oradata2/temp.dbf' size 10M;
Tablespace created.

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

3)Check for users who are not pointed to this default temporary tablespaces. Make this default for those users also.

SQL> select temporary_tablespace , username from dba_users where temporary_tablespace<>'TEMP01';

TEMPORARY_TABLESPACE           USERNAME
------------------------------ ------------------------------
TEMP                           TEST2
TEMP2                          ARJU

4)Explicitly assign temporary tablespace for users TEST2 and ARJU.
SQL> alter user arju temporary tablespace temp01;
User altered.

SQL> alter user test2 temporary tablespace temp01;
User altered.

3)Drop the old temporary tablespace.

SQL> drop tablespace temp;
Tablespace dropped.

SQL> drop tablespace temp2;
Tablespace dropped.

SQL> drop tablespace temp3;
Tablespace dropped.

Related Documents
Drop Temporary Tablespace Hangs
ORA-12906 cannot drop default temporary tablespace
Free space in Temporary Tablespace
Implicitly Assigned temp tablespace changes after database default tablespace change
Information about Temporary Segments.
The operation that require sort area or Temporary Tablespace

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;

Clusterware Installation fails at the end of CRS on Red Hat Linux 5

Problem Description
While installing oracle clusterware on Red Hat Enterprise Linux 5.0 at then end of the installation of Cluster Ready Services OUI prompts for the $CRS_HOME/root.sh script to be run on all
of the nodes in the cluster. The fact is when the root.sh script is run on the last node in
the cluster, the script calls the VIPCA utility which fails in RHEL 5. The same failure occurs on Red Hat Enterprise Linux 5.0, and SUSE Linux Enterprise Linux 10.

Solution of The Problem
As a workaround to solve the problem before running the root.sh script on the last node in the cluster,
alter the $CRS_HOME/bin/vipca script commenting out lines 119 through 123:

arch=’uname -m’
# if [ "$arch" = "i686" -o "$arch" = "ia64" -o "$arch" = "x86_64" ]
# then
# LD_ASSUME_KERNEL=2.4.19
# export LD_ASSUME_KERNEL
# fi

After commenting out run root.sh and it should be able to call VIPCA successfully.
But don't comment out the line 118 that is arch=’uname -m’ as it is needed by the root.sh script to set the arch variable.
Related Documents
Installing Clusterware through OUI fails Cheking OS version in RHL-5

How to get port number list of EM and isqlplus

You can get your portlist information of enterprise manger or isqlplus or others web services in the location of $ORACLE_HOME/install/portlist.ini.

On my system the output is,
bash-3.00$ cat /oracle/app/oracle/product/10.2.0/db_1/install/portlist.ini
iSQL*Plus HTTP port number =5560
Enterprise Manager Console HTTP Port (DBASE) = 1158
Enterprise Manager Agent Port (DBASE) = 3938
Enterprise Manager Console HTTP Port (arjudba) = 5500
Enterprise Manager Agent Port (arjudba) = 1830
Enterprise Manager Console HTTP Port (arju) = 5501
Enterprise Manager Agent Port (arju) = 1831

But note that the ports that are listed in portlist.ini file were assigned during the installation. This file is not updated if port numbers are changed after the installation.

Related Documents
How to Notify or send email Event in Oracle from EM
In EM connection fails with ERROR: NMO not setuid-root (Unix-only)

ORA-32010: cannot find entry to delete in SPFILE

Error Description
With ALTER SYSTEM SET ..... SID='*' we can set the parameter value to all instances of the database( Like in RAC). But to set parameter only to a single instance of a database we use ALTER SYSTEM SET ..... SID='instance_name';

Similarly to reset a parameter value from all instances we have to use ALTER SYSTEM RESET ... sid='*' and to reset a parameter value from a single instance we have to use ALTER SYSTEM RESET ..... sid='instance_name'.

Now while we reset value from a single instance it fails with error ORA-32010.
SQL> alter system reset open_cursors scope=both sid='arju';
alter system reset open_cursors scope=both sid='arju'
*
ERROR at line 1:
ORA-32010: cannot find entry to delete in SPFILE

Cause and Solution of The Problem
Inside the spfile if you notice the parameter is set for all instances. That is with *. Oracle try to finds/looks in spfile for parameter with name arju.open_cursors but could not find that and so can't reset that and error comes.
Here is that,

SQL> create pfile='/oradata2/mynewpfile' from spfile;
File created.

SQL> !vi /oradata2/mynewpfile
*.open_cursors=300

However if there is an entry inside spfile with arju.open_cursors then it would work fine and the ALTER SYSTEM RESET open_cursors scope=both sid='arju' would work fine.

We can test is by modifying pfile manually and then spfile.
SQL> shutdown abort
ORACLE instance shut down.

SQL> create spfile from pfile='/oradata2/mynewpfile';
File created.

SQL> startup
ORACLE instance started.

Total System Global Area 234881024 bytes
Fixed Size 2019800 bytes
Variable Size 121638440 bytes
Database Buffers 104857600 bytes
Redo Buffers 6365184 bytes
Database mounted.
Database opened.

SQL> alter system reset open_cursors scope=both sid='arju';
System altered.

We could also be able to do the test in the spfile itself. Like,

SQL> alter system reset open_cursors scope=both sid='arju';
alter system reset open_cursors scope=both sid='arju'
*
ERROR at line 1:
ORA-32010: cannot find entry to delete in SPFILE

SQL> alter system set open_cursors=400 scope=both sid='arju';
System altered.


SQL> alter system reset open_cursors scope=both sid='arju';
System altered.

However if you want to want to reset the memort value for instance * from instance arju then it will fail with error ORA-32009.

SQL> alter system reset open_cursors scope=both sid='*';
alter system reset open_cursors scope=both sid='*'
*
ERROR at line 1:
ORA-32009: cannot reset the memory value for instance * from instance arju

In that case we can be able to only reset it from spfile like,
SQL> alter system reset open_cursors scope=spfile sid='*';
System altered.

Related Documents
Pfile and Spfile in Oracle.
How to avoid of recreating pfile or spfile if any error in spfile

How to debug Backup, Restore Session in RMAN

In many cases in RMAN we need to debug the session to find and investigate the session about what is happening there. Also to obtain and check the correct diagnostic evidence we sometimes need the debug the rman session.

The most common use of debugging RMAN session is whenever we fail RMAN operation. In fact if any failure in RMAN operation RMAN log and debug files are NOT generated by default and we explicitly need to enable it.

We can log and debug session whichever operation we do in RMAN. Like Backup, Restore, TSPITR, DBPITR, Duplicate Database, Restore to a new host etc.

The commnad is simple,
The syntax is,
$ rman target [un/pwd@target_db] catalog [un/pwd@catalog_db] debug trace rman.trc log rman.log
RMAN>[RMAN Commands Here]

Where un indicates username and pwd indicates password.

In my system I used debugging and logging RMAN session by,
$ rman target / log=/backup03/webkey/rmanlog.txt trace=/backup03/webkey/rmantrace.log
RMAN> debug on
RMAN> @/backup03/webkey/rman_script
RMAN> debug off;
RMAN> exit;


Where my restore commands was inside the /backup03/webkey/rman_script file. And don't confuse with the sequence 2,3,4 here printed. It is line number inside my script of backup.

Later we can see the log information from file rmanlog.txt by,
$ less /backup03/webkey/rmanlog.txt (On Unix System)

We can see debug information from rmantrace.log by,
$ less /backup03/webkey/rmantrace.log (On my Unix System)
.
.
.
.

DBGMISC: command to be compiled and executed is: DEBUG [03:13:27.795] (krmice)
DBGMISC: command after this command is: NONE [03:13:27.795] (krmice)
DBGMISC: current incarnation does not matter for DEBUG [03:13:27.795] (krmice)
Debugging turned off

Above is the sample output of the file of /backup03/webkey/rmantrace.log

Restore operation fails with RMAN-11003 ORA-01511 ORA-01516

Error Description And Symptoms
I am performing disaster recovery operation. The new host path are different than from original host. So for the datafile I used SET NEWNAME .. clause. And for the online redo log file I used SQL "ALTER DATABASE RENAME FILE .. " clause to rename the online logfiles to a valid location on to a new host. I have already ran the restore srcipt in previous but it fails to rename third online redo logfiles due to invalid location. And after fixing location whenever I ran the script again it fails with serveral RMAN and Oracle error as below.
RMAN> @/backup03/webkey/rman_script

RMAN> run{
2> set newname for datafile 1 to '/backup03/webkey/system01.dbf';
3> set newname for datafile 2 to '/backup03/webkey/undotbs01.dbf';
4> set newname for datafile 3 to '/backup03/webkey/sysaux01.dbf';
5> set newname for datafile 4 to '/backup03/webkey/users01.dbf';
6> set newname for datafile 5 to '/backup03/webkey/data01.dbf';
7> set newname for datafile 6 to '/backup03/webkey/data02.dbf';
8> set newname for datafile 7 to '/backup03/webkey/indx01.dbf';
9> set newname for datafile 8 to '/backup03/webkey/indx02.dbf';
10> set newname for datafile 9 to '/backup03/webkey/tools01.dbf';
11> set newname for datafile 10 to '/backup03/webkey/splex_data.dbf';
12> set newname for datafile 11 to '/backup03/webkey/splex_indx.dbf';
13> set newname for datafile 12 to '/backup03/webkey/columnbia.dbf';
14> set newname for datafile 13 to '/backup03/webkey/data03.dbf';
15> set newname for datafile 14 to '/backup03/webkey/data03b.dbf';
16> set newname for datafile 15 to '/backup03/webkey/data_01_16K_01.dbf';
17> set newname for datafile 16 to '/backup03/webkey/indx_01_16K_01.dbf';
18> SQL "ALTER DATABASE RENAME FILE ''/d00/oradata/db101db/redo01a.log''
19> TO ''/backup03/webkey/redo01a.log'' ";
20> SQL "ALTER DATABASE RENAME FILE ''/d01/oradata/db101db/redo01b.log''
21> TO ''/backup03/webkey/redo01b.log'' ";
22> SQL "ALTER DATABASE RENAME FILE ''/d02/oradata/db101db/redo01c.log''
23> TO ''/backup03/webkey/redo01c.log'' ";
24> SQL "ALTER DATABASE RENAME FILE ''/d00/oradata/db101db/redo02a.log''
25> TO ''/backup03/webkey/redo02a.log'' ";
26> SQL "ALTER DATABASE RENAME FILE ''/d01/oradata/db101db/redo02b.log''
27> TO ''/backup03/webkey/redo02b.log'' ";
28> SQL "ALTER DATABASE RENAME FILE ''/d02/oradata/db101db/redo02c.log''
29> TO ''/backup03/webkey/redo02c.log'' ";
30> SQL "ALTER DATABASE RENAME FILE ''/d00/oradata/db101db/redo03a.log''
31> TO ''/backup03/webkey/redo03a.log'' ";
32> SQL "ALTER DATABASE RENAME FILE ''/d01/oradata/db101db/redo03b.log''
33> TO ''/backup03/webkey/redo03b.log'' ";
34> SQL "ALTER DATABASE RENAME FILE ''/d02/oradata/db101db/redo03c.log''
35> TO ''/backup03/webkey/redo03c.log'' ";
36> SET UNTIL TIME '06-JUN-08 15:15:00';
37> RESTORE DATABASE;
38> SWITCH DATAFILE ALL;
39> RECOVER DATABASE;
40> }
executing command: SET NEWNAME
using target database control file instead of recovery catalog

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

sql statement: ALTER DATABASE RENAME FILE ''/d00/oradata/db101db/redo01a.log'' TO ''/backup03/webkey/redo01a.log''
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 09/08/2008 02:30:42
RMAN-11003: failure during parse/execution of SQL statement: ALTER DATABASE RENAME FILE '/d00/oradata/db101db/redo01a.log' TO '/backup03/webkey/redo01a.log'
ORA-01511: error in renaming log/data files
ORA-01516: nonexistent log file, datafile, or tempfile "/d00/oradata/db101db/redo01a.log"

In previous ran of this script it could rename successfully of the first two online redo logfile and it is recorded on the controlfile but the restore operation fails. Now subsequent run of the script will fail because no log file actually exist in the controlfile as they already renamed.

In previous run m,y online redo logfile is rename from /d00/oradata/db101db/redo01a.log to /backup03/webkey/redo01a.log. In the next run(in this example) it again try to search for online redo log in the location /d00/oradata/db101db/redo01a.log inside the controlfile but did not find any as they already modified. So error appear of nonexistent log file, datafile, or tempfile.

Solution of The Problem
While make database in mount state log on to SQL*Plus and query the member column of v$logfile and see the location of the member. Suppose only 1 member is modified then in the SQL*Plus you can issue,
SQL>ALTER DATABASE RENAME FILE '/backup03/webkey/redo01a.log' TO '/d00/oradata/db101db/redo01a.log';
to rename reflected new location to old one and thus you can re-run the script without modifying any line.

If you notice that are redolog files are renamed successfully to the new location then you can simply omit/comment the lines of rename redo log command. Like in below.
tdev> vi /backup03/webkey/rman_script
run{
set newname for datafile 1 to '/backup03/webkey/system01.dbf';
set newname for datafile 2 to '/backup03/webkey/undotbs01.dbf';
set newname for datafile 3 to '/backup03/webkey/sysaux01.dbf';
set newname for datafile 4 to '/backup03/webkey/users01.dbf';
set newname for datafile 5 to '/backup03/webkey/data01.dbf';
set newname for datafile 6 to '/backup03/webkey/data02.dbf';
set newname for datafile 7 to '/backup03/webkey/indx01.dbf';
set newname for datafile 8 to '/backup03/webkey/indx02.dbf';
set newname for datafile 9 to '/backup03/webkey/tools01.dbf';
set newname for datafile 10 to '/backup03/webkey/splex_data.dbf';
set newname for datafile 11 to '/backup03/webkey/splex_indx.dbf';
set newname for datafile 12 to '/backup03/webkey/columnbia.dbf';
set newname for datafile 13 to '/backup03/webkey/data03.dbf';
set newname for datafile 14 to '/backup03/webkey/data03b.dbf';
set newname for datafile 15 to '/backup03/webkey/data_01_16K_01.dbf';
set newname for datafile 16 to '/backup03/webkey/indx_01_16K_01.dbf';
# SQL "ALTER DATABASE RENAME FILE ''/d00/oradata/db101db/redo01a.log''
# TO ''/backup03/webkey/redo01a.log'' ";
# SQL "ALTER DATABASE RENAME FILE ''/d01/oradata/db101db/redo01b.log''
# TO ''/backup03/webkey/redo01b.log'' ";
# SQL "ALTER DATABASE RENAME FILE ''/d02/oradata/db101db/redo01c.log''
# TO ''/backup03/webkey/redo01c.log'' ";
# SQL "ALTER DATABASE RENAME FILE ''/d00/oradata/db101db/redo02a.log''
# TO ''/backup03/webkey/redo02a.log'' ";
# SQL "ALTER DATABASE RENAME FILE ''/d01/oradata/db101db/redo02b.log''
# TO ''/backup03/webkey/redo02b.log'' ";
# SQL "ALTER DATABASE RENAME FILE ''/d02/oradata/db101db/redo02c.log''
# TO ''/backup03/webkey/redo02c.log'' ";
# SQL "ALTER DATABASE RENAME FILE ''/d00/oradata/db101db/redo03a.log''
# TO ''/backup03/webkey/redo03a.log'' ";
# SQL "ALTER DATABASE RENAME FILE ''/d01/oradata/db101db/redo03b.log''
# TO ''/backup03/webkey/redo03b.log'' ";
# SQL "ALTER DATABASE RENAME FILE ''/d02/oradata/db101db/redo03c.log''
# TO ''/backup03/webkey/redo03c.log'' ";
SET UNTIL TIME '06/06/2008 15:15:00';
RESTORE DATABASE;
SWITCH DATAFILE ALL;
RECOVER DATABASE;
}
"/backup03/webkey/rman_script" 41 lines, 2179 characters

And then run the script again.

Why create between pfile and spfile works if shutdown database

Sometimes it may arises question that while database is shutdown then you can still connect to database as sysdba and now issue create spfile from pfile or create pfile from spfile and it works fine. But database is shutdown and then how oracle maintains where is spfile and pfile.

The answer is create pfile and spfile can only can done whenever you connect as sysdba. And if you connect as sysdba then a dedicated server is established which does that create operation.

You can easily find it. Suppose I am log on to my unix machine and invoke the status of process arju.
-bash-3.00$ ps -ef |grep arju
oracle 27060 1 0 04:58:09 ? 0:07 oraclearju (LOCAL=NO)
oracle 26970 1 0 04:54:43 ? 0:21 ora_ckpt_arju
oracle 26974 1 0 04:54:43 ? 0:00 ora_reco_arju
oracle 16423 16224 0 23:00:25 pts/3 0:00 grep arju
oracle 27024 1 0 04:55:00 ? 0:08 oraclearju (LOCAL=NO)
oracle 27028 1 0 04:55:15 ? 0:08 oraclearju (LOCAL=NO)
oracle 26972 1 0 04:54:43 ? 0:04 ora_smon_arju
oracle 26976 1 0 04:54:43 ? 0:21 ora_cjq0_arju
oracle 26982 1 0 04:54:44 ? 0:00 ora_d000_arju
oracle 27030 1 0 04:55:15 ? 0:37 oraclearju (LOCAL=NO)
oracle 26964 1 0 04:54:43 ? 0:02 ora_mman_arju
oracle 26994 1 0 04:54:49 ? 0:01 ora_arc1_arju
oracle 27002 1 0 04:54:50 ? 0:00 ora_qmnc_arju
oracle 27032 1 0 04:55:15 ? 0:10 oraclearju (LOCAL=NO)
oracle 26968 1 0 04:54:43 ? 0:09 ora_lgwr_arju
oracle 27022 1 0 04:55:00 ? 0:01 ora_q000_arju
oracle 26980 1 0 04:54:44 ? 0:35 ora_mmnl_arju
oracle 26984 1 0 04:54:44 ? 0:00 ora_s000_arju
oracle 26962 1 0 04:54:43 ? 0:03 ora_psp0_arju
oracle 26966 1 0 04:54:43 ? 0:06 ora_dbw0_arju
oracle 26978 1 0 04:54:43 ? 0:07 ora_mmon_arju
oracle 26960 1 0 04:54:43 ? 0:16 ora_pmon_arju
oracle 26992 1 0 04:54:49 ? 0:01 ora_arc0_arju
oracle 27040 1 0 04:55:52 ? 0:00 ora_q002_arju

Now I connect as sysdba and see the status.
-bash-3.00$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 8 23:00:31 2008

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> !ps -ef |grep arju
oracle 27060 1 0 04:58:09 ? 0:07 oraclearju (LOCAL=NO)
oracle 26970 1 0 04:54:43 ? 0:21 ora_ckpt_arju
oracle 26974 1 0 04:54:43 ? 0:00 ora_reco_arju
oracle 16433 1 0 23:00:33 ? 0:00 ora_m001_arju
oracle 16429 1 1 23:00:30 ? 0:00 ora_j000_arju
oracle 27024 1 0 04:55:00 ? 0:08 oraclearju (LOCAL=NO)
oracle 27028 1 0 04:55:15 ? 0:08 oraclearju (LOCAL=NO)
oracle 26972 1 0 04:54:43 ? 0:04 ora_smon_arju
oracle 26976 1 0 04:54:43 ? 0:21 ora_cjq0_arju
oracle 26982 1 0 04:54:44 ? 0:00 ora_d000_arju
oracle 27030 1 0 04:55:15 ? 0:37 oraclearju (LOCAL=NO)
oracle 26964 1 0 04:54:43 ? 0:02 ora_mman_arju
oracle 26994 1 0 04:54:49 ? 0:01 ora_arc1_arju
oracle 27002 1 0 04:54:50 ? 0:00 ora_qmnc_arju
oracle 27032 1 0 04:55:15 ? 0:10 oraclearju (LOCAL=NO)
oracle 26968 1 0 04:54:43 ? 0:09 ora_lgwr_arju
oracle 27022 1 0 04:55:00 ? 0:01 ora_q000_arju
oracle 26980 1 0 04:54:44 ? 0:35 ora_mmnl_arju
oracle 16431 16430 0 23:00:31 ? 0:00 oraclearju (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 26984 1 0 04:54:44 ? 0:00 ora_s000_arju
oracle 26962 1 0 04:54:43 ? 0:03 ora_psp0_arju
oracle 16436 16434 0 23:00:34 pts/3 0:00 grep arju
oracle 26966 1 0 04:54:43 ? 0:06 ora_dbw0_arju
oracle 26978 1 0 04:54:43 ? 0:07 ora_mmon_arju
oracle 26960 1 0 04:54:43 ? 0:16 ora_pmon_arju
oracle 26992 1 0 04:54:49 ? 0:01 ora_arc0_arju
oracle 27040 1 0 04:55:52 ? 0:00 ora_q002_arju
oracle 16434 16430 0 23:00:34 pts/3 0:00 /usr/bin/bash -c ps -ef |grep arju

Here you see a dedicated server is established as you connect as sysdba.
Now shutdown database and see the process status.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !ps -ef |grep arju
oracle 16540 16430 0 23:04:37 pts/3 0:00 /usr/bin/bash -c ps -ef |grep arju
oracle 16542 16540 0 23:04:37 pts/3 0:00 grep arju
oracle 16431 16430 1 23:00:31 ? 0:01 oraclearju (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

We still see the dedicated server as we still connect to as sysdba.
Now exit from connecting as sysdba and observe the result.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
-bash-3.00$ ps -ef |grep arju
oracle 16544 16224 0 23:04:44 pts/3 0:00 grep arju

Nothing here now.

Related Documents
How to avoid of recreating pfile or spfile if any error in spfile

Recover database Issue after missing spfile or pfile

How to know My Database Start with Spfile/Pfile?

How to Restore spfile from backup

Pfile and Spfile in Oracle.

How to restore an Spfile from autobackup older than 7 days

How to Start your database with non default spfile

How to avoid of recreating pfile or spfile if any error in spfile

Let us some talk about pfile and spfile. Suppose I have done some wrong-edit inside spfile using ALTER SYSTEM SET ... SCOPE=SPFILE from database. Now the next startup process of my database will fail with oracle errors. The conventional approach of solution to this problem is,
1)Connect to SQL*plus as sysdba
2)Create pfile from the spfile.
3)Edit the pfile and correct the parameter settings.
4)Create spfile from the edited correct pfile.
5)Startup your database with the spfile.

But we can omit these steps easily by just creating a pfile and invoke the name of the spfile inside the pfile. After invoking spfile parameter inside pfile in the next line specify the name of the parameter which is cause problem of startup. In this case we gave multiple values of the parameter and oracle will use the last one. So override the invalid parameter value with the valid one and thus will startup the create. After startup we can set valid value in the spfile.

The following example will make you a clear idea.

Step 1:
I have set background_dump_dest parameter to an invalid location in the spfile.
SQL> alter system set background_dump_dest='/oradata2/testing' scope=spfile;
System altered.

Now shutdown the database and start it up.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-00444: background process "MMAN" failed while starting
ORA-07446: sdnfy: bad value '' for parameter .

Step 2:
Startup fails as we set invalid location to background_dump_dest. Now create a pfile with an entry of spfile and give the location of the spfile. After specify specify the parameter which cause problem(background_dump_dest) and use the valid value. This setting will override the setting inside spfile and thus will help to startup the database.

SQL> !vi /oradata2/mypfile.ora
spfile=/oracle/app/oracle/product/10.2.0/db_1/dbs/spfilearju.ora
background_dump_dest='/oradata1'

Step 3:
Now start the database with newly created pfile. In fact spfile.
SQL> startup pfile=/oradata2/mypfile.ora
ORACLE instance started.

Total System Global Area 234881024 bytes
Fixed Size 2019800 bytes
Variable Size 113249832 bytes
Database Buffers 113246208 bytes
Redo Buffers 6365184 bytes
Database mounted.
Database opened.

Step 4:
And it worked!!! Now set valid value to the parameter background_dump_dest inside spfile.

SQL>  show parameter background_dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest                 string      /oradata1

SQL> alter system set background_dump_dest='/oradata1' scope=spfile;
System altered.

In this way we can minimize our steps as well as downtime.

Related Documents
Recover database Issue after missing spfile or pfile

How to know My Database Start with Spfile/Pfile?

How to Restore spfile from backup

Pfile and Spfile in Oracle.

How to restore an Spfile from autobackup older than 7 days

How to Start your database with non default spfile

List of Parameters that must have identical in RAC database

In a RAC database there may contain several instances and the initialization parameters of all of the instances should not have identical setting. In fact the initialization parameters that are critical for database creation and affect certain database operations must have the same value for every instance in an Oracle RAC database.

Below is the list of initialization parameters that must have identical settings for all instances in a RAC database.

1)ACTIVE_INSTANCE_COUNT

2)ARCHIVE_LAG_TARGET

3)CLUSTER_DATABASE

4)CLUSTER_DATABASE_INSTANCES

5)COMPATIBLE

6)CONTROL_FILES

7)DB_BLOCK_SIZE

8)DB_DOMAIN

9)DB_FILES

10)DB_NAME

11)DB_RECOVERY_FILE_DEST

12)DB_RECOVERY_FILE_DEST_SIZE

13)DB_UNIQUE_NAME

14)DML_LOCKS (Only if set to zero).

15)INSTANCE_TYPE (RDBMS or ASM)

16)PARALLEL_MAX_SERVERS

17)REMOTE_LOGIN_PASSWORD_FILE

18)UNDO_MANAGEMENT

Related Documents

Pre-Installation RAC environement setup check with CVU

Configure Network for oracle RAC installation

Various Tools for Installing, Configuring Oracle RAC

Overview of Oracle Clusterware and Oracle RAC

Configure Shared Storage in Oracle RAC installation

Oracle RAC Software Components

Configure Raw Devices for ASM in RAC.

Requirements to check before install Oracle RAC

Configure server to install Oracle RAC