SET SQLPLUS PROMT VARIABLES

SET  SQLPLUS  ENVIRONMENT
==========================================


set termout off
col user_name new_value user_name
col db_name new_value db_name
set pages 200 lines 200
rem select lower(user) user_name, ltrim(rtrim(lower(global_name))) db_name from global_name;
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
rem sqlprompt "&user_name@_CONNECT_IDENTIFIER> "
rem sqlprompt "_CONNECT_IDENTIFIER &user_name> "
SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER _DATE> "
set termout on
set trimspool on
set trimout on
set serveroutput on size 1000000
set timing on
set echo on feedback on time on timing on serveroutput on sqlblank on



====================

SQL> set termout off
col user_name new_value user_name
col db_name new_value db_name
set pages 200 lines 200
rem select lower(user) user_name, ltrim(rtrim(lower(global_name))) db_name from global_name;
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
rem sqlprompt "&user_name@_CONNECT_IDENTIFIER> "
rem sqlprompt "_CONNECT_IDENTIFIER &user_name> "
SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER _DATE> "
set termout on
set trimspool on
set trimout on
set serveroutput on size 1000000
set timing on
set echo on feedback on time on timing on serveroutput on sqlblank onSQL> SQL> SQL> SQL> SQL>
Session altered.

SQL> SQL> SQL> SYS@orcl1 05-JUL-2017 13:45:58> SYS@orcl1 05-JUL-2017 13:45:58> SYS@orcl1 05-JUL-2017 13:45:58> SYS@orcl1 05-JUL-2017 13:45:58> SYS@orcl1 05-JUL-2017 13:45:59> SYS@orcl1 05-JUL-2017 13:45:59>
13:46:00 SYS@orcl1 05-JUL-2017 13:46:00>
13:46:00 SYS@orcl1 05-JUL-2017 13:46:00>
13:46:00 SYS@orcl1 05-JUL-2017 13:46:00>
13:46:00 SYS@orcl1 05-JUL-2017 13:46:00>
13:46:01 SYS@orcl1 05-JUL-2017 13:46:01>

Remove file using inode number in linux

Remove  directory/file using inode number  in linux
-----------------------------------------------------------------

[oracle@stby oracle]$ ls -lrt
total 32
drwxrwxr-x.  3 oracle oinstall 4096 Feb 26 07:43 product
drwxrwxr-x. 11 oracle oinstall 4096 Feb 26 09:50 diag
drwxr-xr-x.  2 oracle oinstall 4096 Feb 26 09:53 checkpoints
drwxr-xr-x.  3 oracle oinstall 4096 Feb 26 10:31 cfgtoollogs
drwxr-xr-x.  2 oracle oinstall 4096 Mar  2 22:01 admin
drwxr-xr-x.  2 oracle oinstall 4096 Mar  2 22:02 fast_recovery_area
drwxr-xr-x.  2 oracle oinstall 4096 Mar  2 22:02 oradata
drwxr-xr-x.  2 oracle oinstall 4096 Mar  2 22:12 -p
[oracle@stby oracle]$

[oracle@stby oracle]$ ls -li
total 32
143126 drwxr-xr-x.  2 oracle oinstall 4096 Mar  2 22:01 admin
143086 drwxr-xr-x.  3 oracle oinstall 4096 Feb 26 10:31 cfgtoollogs
143085 drwxr-xr-x.  2 oracle oinstall 4096 Feb 26 09:53 checkpoints
143089 drwxrwxr-x. 11 oracle oinstall 4096 Feb 26 09:50 diag
131827 drwxr-xr-x.  2 oracle oinstall 4096 Mar  2 22:02 fast_recovery_area
140990 drwxr-xr-x.  2 oracle oinstall 4096 Mar  2 22:02 oradata
140979 drwxr-xr-x.  2 oracle oinstall 4096 Mar  2 22:31 -p
140980 drwxrwxr-x.  3 oracle oinstall 4096 Feb 26 07:43 product
[oracle@stby oracle]$
[oracle@stby oracle]$ find . -inum 140979 -exec rm -rf  {} \;
find: `./-p': No such file or directory
[oracle@stby oracle]$ ls -li
total 28
143126 drwxr-xr-x.  2 oracle oinstall 4096 Mar  2 22:01 admin
143086 drwxr-xr-x.  3 oracle oinstall 4096 Feb 26 10:31 cfgtoollogs
143085 drwxr-xr-x.  2 oracle oinstall 4096 Feb 26 09:53 checkpoints
143089 drwxrwxr-x. 11 oracle oinstall 4096 Feb 26 09:50 diag
131827 drwxr-xr-x.  2 oracle oinstall 4096 Mar  2 22:02 fast_recovery_area
140990 drwxr-xr-x.  2 oracle oinstall 4096 Mar  2 22:02 oradata
140980 drwxrwxr-x.  3 oracle oinstall 4096 Feb 26 07:43 product
[oracle@stby oracle]$


find . -inum 140979 -exec rm -rf  {} \;

Remove file using below command 

find . -inum 140979 -exec rm -i {} \;

[oracle@stby oracle]$ ls -li
total 28
143126 drwxr-xr-x.  2 oracle oinstall 4096 Mar  2 22:01 admin
140979 -rw-r--r--.  1 oracle oinstall    0 Mar  2 22:37 a.txt
143086 drwxr-xr-x.  3 oracle oinstall 4096 Feb 26 10:31 cfgtoollogs
143085 drwxr-xr-x.  2 oracle oinstall 4096 Feb 26 09:53 checkpoints
143089 drwxrwxr-x. 11 oracle oinstall 4096 Feb 26 09:50 diag
131827 drwxr-xr-x.  2 oracle oinstall 4096 Mar  2 22:02 fast_recovery_area
140990 drwxr-xr-x.  2 oracle oinstall 4096 Mar  2 22:02 oradata
140980 drwxrwxr-x.  3 oracle oinstall 4096 Feb 26 07:43 product
[oracle@stby oracle]$
[oracle@stby oracle]$ find . -inum 140979 -exec rm -i {} \;
rm: remove regular empty file `./a.txt'? y
[oracle@stby oracle]$
[oracle@stby oracle]$ ls -li
total 28
143126 drwxr-xr-x.  2 oracle oinstall 4096 Mar  2 22:01 admin
143086 drwxr-xr-x.  3 oracle oinstall 4096 Feb 26 10:31 cfgtoollogs
143085 drwxr-xr-x.  2 oracle oinstall 4096 Feb 26 09:53 checkpoints
143089 drwxrwxr-x. 11 oracle oinstall 4096 Feb 26 09:50 diag
131827 drwxr-xr-x.  2 oracle oinstall 4096 Mar  2 22:02 fast_recovery_area
140990 drwxr-xr-x.  2 oracle oinstall 4096 Mar  2 22:02 oradata
140980 drwxrwxr-x.  3 oracle oinstall 4096 Feb 26 07:43 product
[oracle@stby oracle]$

How to resolve PRVF-0002: could not retrieve local node name

 Error:  PRVF-0002: could not retrieve local node name


Solution:
-------------

first check hostname in server

[root@pri etc]# hostname
pri.localdomain
[root@pri etc]#

Old host file

[root@pri etc]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
[root@pri etc]#
[root@pri etc]#


Now make entry of server ip and hostname in host file


[root@pri etc]#
[root@pri etc]#
[root@pri etc]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.1.12 pri  pri.localdomain
[root@pri etc]#
[root@pri etc]#

after  restart oracle oui

Move or Rename the Tempfile in Oracle ASM

Move or Rename the Tempfile in Oracle
==================================


Step1:-  Login as sysdba

Step2: Check Tempfile status corresponding to tablepace  using below query

 SQL> SELECT v.file#, t.file_name, v.status
   FROM dba_temp_files t, v$tempfile v
   WHERE t.file_id = v.file#
   and t.TABLESPACE_NAME='TEMP1';
    
     FILE# FILE_NAME                                STATUS
---------- ---------------------------------------- -------
         2 +DATA2/orcl/tempfile/temp1.269.935535665 ONLINE

SQL>

Step3: Make offline file which want to move using below query

SQL>
SQL> alter database tempfile '+DATA2/orcl/tempfile/temp1.269.935535665' offline;

Database altered.

SQL>
SQL>

Step 4: Check status of file

SQL> SELECT v.file#, t.file_name, v.status
   FROM dba_temp_files t, v$tempfile v
   WHERE t.file_id = v.file#
   and t.TABLESPACE_NAME='TEMP1';  2    3    4

     FILE# FILE_NAME                                STATUS
---------- ---------------------------------------- -------
         2 +DATA2/orcl/tempfile/temp1.269.935535665 OFFLINE



Step 5:- login  in asm instance and copy file to  one diskgroup to another diskgroup


[grid@rac1 ~]$ . oraenv
ORACLE_SID = [grid] ? +ASM1
The Oracle base has been set to /u01/app/grid
[grid@rac1 ~]$
[grid@rac1 ~]$ asmcmd
ASMCMD> ls
DATA1/
DATA2/
ASMCMD>
ASMCMD>
ASMCMD> cp +DATA2/orcl/tempfile/temp1.269.935535665 +DATA1/ORCL/DATAFILE/temp1_new
copying +DATA2/orcl/tempfile/temp1.269.935535665 -> +DATA1/ORCL/DATAFILE/temp1_new
ASMCMD>
ASMCMD>
ASMCMD> cd +DATA1/ORCL/DATAFILE/
ASMCMD> ls -l
Type      Redund  Striped  Time             Sys  Name
                                            N    temp1_new => +DATA1/ASM/TEMPFILE/temp1_new.256.935624893

Step 6:  Rename file to new location  using below command 

SQL> alter database rename file '+DATA2/orcl/tempfile/temp1.269.935535665' to '+DATA1/ASM/TEMPFILE/temp1_new.256.935624893';

Database altered.

SQL>

Step 7: Make online file using below command 

SQL> alter database tempfile '+DATA1/asm/tempfile/temp1_new.256.935624893' online;

Database altered.

SQL>
SQL>  SELECT v.file#, t.file_name, v.status
   FROM dba_temp_files t, v$tempfile v
   WHERE t.file_id = v.file#
   and t.TABLESPACE_NAME='TEMP1';  2    3    4

     FILE# FILE_NAME                                                    STATUS
---------- ------------------------------------------------------------ -------
         2 +DATA1/asm/tempfile/temp1_new.256.935624893                  ONLINE

SQL>



=======
Make sure file is offline otherwise get below error
----------------------------------------------------------------
SQL> Alter database rename file '+DATA2/orcl/tempfile/temp1.269.935535665' to '+DATA1/ASM/TEMPFILE/temp1_new.256.935624893';
alter database rename file '+DATA2/orcl/tempfile/temp1.269.935535665' to '+DATA1/ASM/TEMPFILE/temp1_new.256.935624893';
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01121: cannot rename database file 202 - file is in use or recovery
ORA-01110: data file 202: '+DATA2/orcl/tempfile/temp1.269.935535665'