How to move all the datafiles, redo logs, tempfiles, undo datafile and controlfiles in a database in 11g/12c

How to move all the datafiles, redo logs, tempfiles, undo datafile and controlfiles in a database in 11g/12c

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.

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

1-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     

QL> alter tablespace example offline;

Tablespace altered.

SQL> alter tablespace michael 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

Using RMAN command :

RMAN> COPY DATAFILE ‘+DATA1/node/datafile/users.259.666184225’ TO ‘+DATA2/users.dbf’;

-Rename the datafile :

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.

RMAN> SWITCH DATAFILE ‘+DATA2/users.dbf’ TO COPY;

SQL> RECOVER DATAFILE ‘+DATA2/users.dbf’;
Media recovery complete.

SQL> ALTER DATABASE DATAFILE ‘+DATA2/users.dbf’ ONLINE;
Database altered.

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                                         

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

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

12C datafile rename :

Starting with Oracle 12.1, you can now use the command ALTER DATABASE MOVE DATAFILE in order to rename, relocate, or copy a datafile when the datafiles or the database are online. One step only is required for any of these actions and the database remains entirely available in read and write for users, without any data loss.

You should however be aware of some rules:

• By default, Oracle automatically deletes old data file after moving them and prevents the user from overwriting an existing file.
• When you move a data file, Oracle first makes a copy of the datafile. Then, when the file is successfully copied, pointers to the datafile are updated and the old file is removed from the file system. This is why the operation requires twice the size of the files to be copied as free space.

Let’s have a look at some examples for the renaming or relocating of a datafile:

1) Renaming a datafile

SQL> ALTER DATABASE MOVE DATAFILE '/u01/oradata/DBTEST/demo1.dbf' TO

'/u01/oradata/DBTEST/demo01.dbf';

SQL> ALTER DATABASE MOVE DATAFILE '/u01/oradata/DBTEST/demo01.dbf'

TO '/u02/oradata/DBTEST/demo01.dbf';

2) Relocating a datafile

The available options are:

• KEEP: to keep the old datafile, used to make a copy of the file. Note that the pointer will be updated to the new file,the  old file only remains as unused on the filesystem. Note that on Windows, independently of the fact that the KEEP option is used or not, the old data file is not automatically deleted by Oracle. The user has to delete it manually after the copy is successfully performed.
• SQL> ALTER DATABASE MOVE DATAFILE '/u01/oradata/DBTEST/demo01.dbf' TO '/u02/oradata/DBTEST/demo01.dbf'

 REUSE: to overwrite an existing file.

• SQL> ALTER DATABASE MOVE DATAFILE '/u01/oradata/DBTEST/demo01.dbf' TO '/u02/oradata/DBTEST/demo01.dbf' reuse;

Moving a datafile online works in both ARCHIVE and NO ARCHIVE modes. You have no option to enable in order to use this feature, which is great. It does not work with an OFFLINE datafile.
On the backup & recovery side, a flashback database does not revert the operation. A datafile is definitively moved. But after moving a datafile, like for any other operation on the database structure, you will have to perform a new full backup of the database. Otherwise, you will have to move the datafile(s) back before being able to restore your database…
Finally, some files like temporary files, redo log files, and control files cannot be moved using this command, which is very helpful but can still be improved…

Reference: https://docs.oracle.com/cd/B28359_01/server.111/b28310/dfiles005.htm#ADMIN11434

https://setijoagus.wordpress.com/2008/10/13/oracle-11g-rac-asm-move-rename-datafile/

http://harvarinder.blogspot.com/2011/02/how-to-move-all-datafiles-redo-logs.html

http://blog.dbi-services.com/oracle-database-12c-moving-a-datafile-is-now-possible-online/

Aliases for DBA shortcut

#alias ls='/bin/ls -a --color=tty'
#alias ll='/bin/ls -al --color=tty'
#alias lsr='/bin/ls -ar --color=tty'
#alias lst='/bin/ls -at --color=tty'
alias jeet='cd /home/a1001036-a/doom'
alias ls='/bin/ls -a'
alias ll='/bin/ls -al'
alias lsr='/bin/ls -ar'
alias lst='/bin/ls -at'
alias ulb='cd /usr/local/bin'
alias ob='cd $ORACLE_BASE'
alias olb='cd $ORACLE_BASE/local/bin'
alias ol='cd $ORACLE_BASE/local'
alias ole='cd $ORACLE_BASE/local/etc'
alias oln='cd $ORACLE_BASE/local/network'
alias oh='cd $ORACLE_HOME'
alias adm='cd $ORACLE_BASE/admin/$ORACLE_SID'
alias ada='cd $ORACLE_BASE/admin'
alias ads='cd $ORACLE_BASE/admin/scripts'
alias adsb='cd $ORACLE_BASE/admin/scripts/backup'
alias adsm='cd $ORACLE_BASE/admin/scripts/monitor'
alias adsp='cd $ORACLE_BASE/admin/scripts/perfmgt'
alias adq='cd $ORACLE_BASE/admin/sql'
alias adl='cd $ORACLE_BASE/admin/log'
alias adla='cd $ORACLE_BASE/admin/log/app_batch'
alias adlb='cd $ORACLE_BASE/admin/log/backup'
alias adle='cd $ORACLE_BASE/admin/log/envmgmt'
alias adlm='cd $ORACLE_BASE/admin/log/monitor'
alias adln='cd $ORACLE_BASE/admin/log/network'
alias adump='cd $ORACLE_BASE/admin/$ORACLE_SID/adump'
alias bdump='cd $ORACLE_BASE/admin/$ORACLE_SID/bdump'
alias cdump='cd $ORACLE_BASE/admin/$ORACLE_SID/cdump'
alias udump='cd $ORACLE_BASE/admin/$ORACLE_SID/udump'
alias pfile='cd $ORACLE_BASE/admin/$ORACLE_SID/pfile'
alias parch='cd $ORACLE_BASE/admin/$ORACLE_SID/arch'
alias dbs='cd $ORACLE_HOME/dbs'
alias pushdir='cd $ORACLE_BASE/admin/push'
alias polb='cd $ORACLE_BASE/admin/push/oracle/local/bin'
alias pole='cd $ORACLE_BASE/admin/push/oracle/local/etc'
alias poln='cd $ORACLE_BASE/admin/push/oracle/local/network'
alias pads='cd $ORACLE_BASE/admin/push/oracle/admin/scripts'
alias padq='cd $ORACLE_BASE/admin/push/oracle/admin/sql'
alias oralink='cd $ORACLE_BASE/admin/oralink/${ORACLE_SID%%??}'
alias alert='tail -1000f $ORACLE_BASE/admin/$ORACLE_SID/bdump/alert_${ORACLE_SID}.log'
alias vialert='vi $ORACLE_BASE/admin/$ORACLE_SID/bdump/alert_${ORACLE_SID}.log'
alias plog='tail -f /etc/cmcluster/${ORACLE_SID#o}/${ORACLE_SID#o}_ctl.sh.log'
alias sp='sqlplus "/ as sysdba"'
alias spa='sqlplus "/ as sysasm"'
alias netbk='cd /usr/openv/netbackup/logs/user_ops/dbext/logs'
alias psg='ps -ef | grep '
alias psxg='ps -exf | grep '
alias pgrep='ps -ef | grep '
alias inst='ps -ef | grep -e ora_smon -e asm_smon | grep -v grep | awk '"'"'{print $NF}'"'"' | awk -F_ '"'"'{print $3}'"'"' |sort -u'
alias patches='$ORACLE_HOME/OPatch/opatch lsinventory | grep ^Patch'
alias clust="(export ORACLE_SID=crs;export ORAENV_ASK=NO;. oraenv;\$ORACLE_HOME/bin/cemutlo -n)"
alias asminst="export ORACLE_SID=`ps -ef | grep [a]sm_smon | awk -F _ '{print $NF}'`;ORAENV_ASK=NO;. oraenv;unset ORAENV_ASK"
alias opmnstat='printf "Running ";echo $ORACLE_HOME/opmn/bin/opmnctl status -fmt %cmp24%prt30%pid7R%sta8%uid10R%mem8R%utm9R%por|tee /dev/tty|bash'
alias opmncstat='printf "Running ";echo $ORACLE_HOME/opmn/bin/opmnctl @cluster status -fmt %cmp24%prt30%pid7R%sta8%uid10R%mem8R%utm9R%por|tee /dev/tty|bash'
alias opmnstartall='printf "Running ";echo $ORACLE_HOME/opmn/bin/opmnctl startall|tee /dev/tty|bash'
alias opmnstopall='printf "Running ";echo $ORACLE_HOME/opmn/bin/opmnctl stopall|tee /dev/tty|bash'
alias agstat="(export ORACLE_SID=agent10g;export ORAENV_ASK=NO;. oraenv;\$ORACLE_HOME/bin/emctl status agent)"
alias agstatsec="(export ORACLE_SID=agent10g;export ORAENV_ASK=NO;. oraenv;\$ORACLE_HOME/bin/emctl status agent -secure)"
alias agvkey="(export ORACLE_SID=agent10g;export ORAENV_ASK=NO;. oraenv;\$ORACLE_HOME/bin/emctl verifykey)"
alias agtz="(export ORACLE_SID=agent10g;export ORAENV_ASK=NO;. oraenv;\$ORACLE_HOME/bin/emctl config agent getTZ)"
alias agver="(export ORACLE_SID=agent10g;export ORAENV_ASK=NO;. oraenv;\$ORACLE_HOME/bin/emctl getversion)"
alias aghome="(export ORACLE_SID=agent10g;export ORAENV_ASK=NO;. oraenv;\$ORACLE_HOME/bin/emctl getemhome)"
alias agupl="(export ORACLE_SID=agent10g;export ORAENV_ASK=NO;. oraenv;\$ORACLE_HOME/bin/emctl upload agent)"
alias agstart="(export ORACLE_SID=agent10g;export ORAENV_ASK=NO;. oraenv;\$ORACLE_HOME/bin/emctl start agent)"
alias agstop="(export ORACLE_SID=agent10g;export ORAENV_ASK=NO;. oraenv;\$ORACLE_HOME/bin/emctl stop agent)"

function pinst { export PDB=`echo $PDB | sed "s/.$/$1/"`; echo "PDB is now $PDB";}
function penv {
        export ORAENV_ASK=NO;
        inst | grep $1 && export ORACLE_SID=$1 || export ORACLE_SID=NONE
        . oraenv;
        unset ORAENV_ASK;
        if [ ${1: -1} == "1" ] ; then
                . setpenv $1 system;
        else
                inst=${1: -1}
                newinst=${1%$inst}1
                . setpenv $newinst system > /dev/null;
                pinst $inst > /dev/null
                echo "Perl DB scripts will connect to system@$PDB"
        fi
}
function getpwn { getpw "$@";echo; }
alias oramaint='sudo /opt/oracle/local/bin/oramaint'
alias lsdisk='sudo /usr/local/bin/lsdisk'
alias setx="export XAUTHORITY=/home/`/usr/bin/who -m | /usr/bin/awk '{print $1}'`/.Xauthority"
alias svs='lsnrctl services `ps -ef | grep [S]CAN | awk '\''{print $(NF-1)}'\'' | head -1` | grep ^Service | awk '\''{print $2}'\'' | sort -u | tr -d '\''"'\'''
alias sd='cd /u01/app/oracle/admin/scripts'

Redo log corruption

Recovering After the Loss of Online Redo Log Files: Scenarios

If a media failure has affected the online redo logs of a database, then the appropriate recovery procedure depends on the following:

• The configuration of the online redo log: mirrored or non-mirrored
• The type of media failure: temporary or permanent
• The types of online redo log files affected by the media failure: current, active, unarchived, or inactive

Table 19-1 displays V$LOG status information that can be crucial in a recovery situation involving online redo logs.

Table 19-1 STATUS Column of V$LOG

Status	Description
UNUSED	The online redo log has never been written to.
CURRENT	The online redo log is active, that is, needed for instance recovery, and it is the log to which the database is currently writing. The redo log can be open or closed.
ACTIVE	The online redo log is active, that is, needed for instance recovery, but is not the log to which the database is currently writing.It may be in use for block recovery, and may or may not be archived.
CLEARING	The log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, then the status changes to UNUSED.
CLEARING_CURRENT	The current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header.
INACTIVE	The log is no longer needed for instance recovery. It may be in use for media recovery, and may or may not be archived.

Recovering After Losing a Member of a Multiplexed Online Redo Log Group

If the online redo log of a database is multiplexed, and if at least one member of each online redo log group is not affected by the media failure, then the database continues functioning as normal, but error messages are written to the log writer trace file and the alert_SID.log of the database.

Solve the problem by taking one of the following actions:

• If the hardware problem is temporary, then correct it. The log writer process accesses the previously unavailable online redo log files as if the problem never existed.
• If the hardware problem is permanent, then drop the damaged member and add a new member by using the following procedure.

Note:

The newly added member provides no redundancy until the log group is reused.

1. Locate the filename of the damaged member in V$LOGFILE. The status is INVALID if the file is inaccessible:

2.  SELECT GROUP#, STATUS, MEMBER

3.  FROM V$LOGFILE

4.  WHERE STATUS='INVALID';

5.   

6.  GROUP#    STATUS       MEMBER

7.  -------   -----------  ---------------------

8.  0002      INVALID      /oracle/oradata/trgt/redo02.log

9.   

10. Drop the damaged member. For example, to drop member redo01.log from group 2, issue:

11. ALTER DATABASE DROP LOGFILE MEMBER '/oracle/oradata/trgt/redo02.log';

12.  

13. Add a new member to the group. For example, to add redo02.log to group 2, issue:

14. ALTER DATABASE ADD LOGFILE MEMBER '/oracle/oradata/trgt/redo02b.log'

15.   TO GROUP 2;

16.  

If the file you want to add already exists, then it must be the same size as the other group members, and you must specify REUSE. For example:

ALTER DATABASE ADD LOGFILE MEMBER '/oracle/oradata/trgt/redo02b.log'

  REUSE TO GROUP 2;

Recovering After the Loss of All Members of an Online Redo Log Group

If a media failure damages all members of an online redo log group, then different scenarios can occur depending on the type of online redo log group affected by the failure and the archiving mode of the database.

If the damaged log group is active, then it is needed for crash recovery; otherwise, it is not.

If the group is . . .	Then . . .	And you should . . .
Inactive	It is not needed for crash recovery	Clear the archived or unarchived group.
Active	It is needed for crash recovery	Attempt to issue a checkpoint and clear the log; if impossible, then you must restore a backup and perform incomplete recovery up to the most recent available redo log.
Current	It is the log that the database is currently writing to	Attempt to clear the log; if impossible, then you must restore a backup and perform incomplete recovery up to the most recent available redo log.

Your first task is to determine whether the damaged group is active or inactive.

1. Locate the filename of the lost redo log in V$LOGFILE and then look for the group number corresponding to it. For example, enter:

2.  SELECT GROUP#, STATUS, MEMBER FROM V$LOGFILE;

3.   

4.  GROUP#    STATUS       MEMBER

5.  -------   -----------  ---------------------

6.  0001                    /oracle/dbs/log1a.f

7.  0001                    /oracle/dbs/log1b.f

8.  0002      INVALID       /oracle/dbs/log2a.f

9.  0002      INVALID       /oracle/dbs/log2b.f

10. 0003                    /oracle/dbs/log3a.f

11. 0003                    /oracle/dbs/log3b.f

12.  

13. Determine which groups are active. For example, enter:

14. SELECT GROUP#, MEMBERS, STATUS, ARCHIVED

15. FROM V$LOG;

16.  

17. GROUP#  MEMBERS           STATUS     ARCHIVED

18. ------  -------           ---------  -----------

19.  0001   2                 INACTIVE   YES

20.  0002   2                 ACTIVE     NO

21.  0003   2                 CURRENT    NO

22.  

23. If the affected group is inactive, follow the procedure in Losing an Inactive Online Redo Log Group. If the affected group is active (as in the preceding example), then follow the procedure in "Losing an Active Online Redo Log Group".

Losing an Inactive Online Redo Log Group

If all members of an online redo log group with INACTIVE status are damaged, then the procedure depends on whether you can fix the media problem that damaged the inactive redo log group.

If the failure is . . .	Then . . .
Temporary	Fix the problem. LGWR can reuse the redo log group when required.
Permanent	The damaged inactive online redo log group eventually halts normal database operation. Reinitialize the damaged group manually by issuing the ALTER DATABASE CLEAR LOGFILE statement as described in this section.

Clearing Inactive, Archived Redo

You can clear an inactive redo log group when the database is open or closed. The procedure depends on whether the damaged group has been archived.

To clear an inactive, online redo log group that has been archived, use the following procedure:

1. If the database is shut down, then start a new instance and mount the database:

2.  STARTUP MOUNT

3.   

4. Reinitialize the damaged log group. For example, to clear redo log group 2, issue the following statement:

5.  ALTER DATABASE CLEAR LOGFILE GROUP 2;

Clearing Inactive, Not-Yet-Archived Redo

Clearing a not-yet-archived redo log allows it to be reused without archiving it. This action makes backups unusable if they were started before the last change in the log, unless the file was taken offline prior to the first change in the log. Hence, if you need the cleared log file for recovery of a backup, then you cannot recover that backup. Also, it prevents complete recovery from backups due to the missing log.

To clear an inactive, online redo log group that has not been archived, use the following procedure:

1. If the database is shut down, then start a new instance and mount the database:

2.  STARTUP MOUNT

3.   

4. Clear the log using the UNARCHIVED keyword. For example, to clear log group 2, issue:

5.  ALTER DATABASE CLEAR LOGFILE UNARCHIVED GROUP 2;

6.   

If there is an offline datafile that requires the cleared log to bring it online, then the keywords UNRECOVERABLE DATAFILE are required. The datafile and its entire tablespace have to be dropped because the redo necessary to bring it online is being cleared, and there is no copy of it. For example, enter:

ALTER DATABASE CLEAR LOGFILE UNARCHIVED GROUP 2 UNRECOVERABLE DATAFILE;

7. Immediately back up the whole database with an operating system utility, so that you have a backup you can use for complete recovery without relying on the cleared log group. For example, enter:

8.  % cp /disk1/oracle/dbs/*.f /disk2/backup

9.   

10. Back up the database's control file with the ALTER DATABASE statement. For example, enter:

11. ALTER DATABASE BACKUP CONTROLFILE TO '/oracle/dbs/cf_backup.f';

Failure of CLEAR LOGFILE Operation

The ALTER DATABASE CLEAR LOGFILE statement can fail with an I/O error due to media failure when it is not possible to:

• Relocate the redo log file onto alternative media by re-creating it under the currently configured redo log filename
• Reuse the currently configured log filename to re-create the redo log file because the name itself is invalid or unusable (for example, due to media failure)

In these cases, the ALTER DATABASE CLEAR LOGFILE statement (before receiving the I/O error) would have successfully informed the control file that the log was being cleared and did not require archiving. The I/O error occurred at the step in which the CLEAR LOGFILE statement attempts to create the new redo log file and write zeros to it. This fact is reflected in V$LOG.CLEARING_CURRENT.

Losing an Active Online Redo Log Group

If the database is still running and the lost active redo log is not the current log, then issue the ALTER SYSTEM CHECKPOINT statement. If successful, then the active redo log becomes inactive, and you can follow the procedure in"Losing an Inactive Online Redo Log Group". If unsuccessful, or if your database has halted, then perform one of procedures in this section, depending on the archiving mode.

The current log is the one LGWR is currently writing to. If a LGWR I/O fails, then LGWR terminates and the instance crashes. In this case, you must restore a backup, perform incomplete recovery, and open the database with theRESETLOGS option.

To recover from loss of an active online log group in NOARCHIVELOG mode:

1. If the media failure is temporary, then correct the problem so that the database can reuse the group when required.
2. Restore the database from a consistent, whole database backup (datafiles and control files) as described in "Restoring Datafiles Before Performing Incomplete Recovery". For example, enter:

3.  % cp /disk2/backup/*.dbf $ORACLE_HOME/oradata/trgt/

4.   

5. Mount the database:

6.  STARTUP MOUNT

7.   

8. Because online redo logs are not backed up, you cannot restore them with the datafiles and control files. In order to allow the database to reset the online redo logs, you must first mimic incomplete recovery:

9.  RECOVER DATABASE UNTIL CANCEL

10. CANCEL

11.  

12. Open the database using the RESETLOGS option:

13. ALTER DATABASE OPEN RESETLOGS;

14.  

15. Shut down the database consistently. For example, enter:

16. SHUTDOWN IMMEDIATE

17.  

18. Make a whole database backup.

To recover from loss of an active online redo log group in ARCHIVELOG mode:

If the media failure is temporary, then correct the problem so that the database can reuse the group when required. If the media failure is not temporary, then use the following procedure.

1. Begin incomplete media recovery, recovering up through the log before the damaged log.
2. Ensure that the current name of the lost redo log can be used for a newly created file. If not, then rename the members of the damaged online redo log group to a new location. For example, enter:

3.  ALTER DATABASE RENAME FILE "?/oradata/trgt/redo01.log" TO "/tmp/redo01.log";

4.  ALTER DATABASE RENAME FILE "?/oradata/trgt/redo01.log" TO "/tmp/redo02.log";

5.   

6. Open the database using the RESETLOGS option:

7.  ALTER DATABASE OPEN RESETLOGS;

Note:

All updates executed from the endpoint of the incomplete recovery to the present must be re-executed.

Loss of Multiple Redo Log Groups

If you have lost multiple groups of the online redo log, then use the recovery method for the most difficult log to recover. The order of difficulty, from most difficult to least difficult, follows:

1. The current online redo log
2. An active online redo log
3. An unarchived online redo log
4. An inactive online redo log