How to recreate the control file and rename the database and the datafiles

To change a database name or file names, it can be practical to recreate the control file, and in some cases your only option.

In this example, I will rename a database and its file structure from "OID11UI" to "OID11U1".

Start by generating a file which holds the current layout of all the database's files:

SQL> alter database backup controlfile to trace as '/u01/oracle/product/11204/dbs/cntr_trace.sql' resetlogs;

Database altered.

The generated file typically look like this:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "OID11UI" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u02/oradata/OID11UI/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u02/oradata/OID11UI/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u02/oradata/OID11UI/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u02/oradata/OID11UI/system01.dbf',
  '/u02/oradata/OID11UI/sysaux01.dbf',
  '/u02/oradata/OID11UI/undotbs01.dbf',
  '/u02/oradata/OID11UI/users01.dbf'
CHARACTER SET WE8MSWIN1252
;
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/OID11UI/temp01.dbf'
     SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
-- End of tempfile additions.

Exchange the word "REUSE" against the word "SET":

CREATE CONTROLFILE SET DATABASE "OID11U1"

Optionally, on the first line, add a pointer to a correct parameter file:

STARTUP NOMOUNT PFILE='/u01/oracle/product/11204/dbs/initOID11U1.ora'

Change all references to "OID11UI" (the old name):

STARTUP NOMOUNT PFILE='/u01/oracle/product/11204/dbs/initOID11U1.ora'
CREATE CONTROLFILE SET DATABASE "OID11U1" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u02/oradata/OID11U1/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u02/oradata/OID11U1/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u02/oradata/OID11U1/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u02/oradata/OID11U1/system01.dbf',
  '/u02/oradata/OID11U1/sysaux01.dbf',
  '/u02/oradata/OID11U1/undotbs01.dbf',
  '/u02/oradata/OID11U1/users01.dbf'
CHARACTER SET WE8MSWIN1252
;
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/OID11U1/temp01.dbf'
     SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
-- End of tempfile additions.

Change the folder name that oracle uses:

usu0oid01:OID11UI>cd /u02/oradata
usu0oid01:OID11UI>mv OID11UI OID11U1

Change the control_files directive in the parameter file:
Before

*.control_files='/u02/oradata/OID11UI/control01.ctl','/u02/oradata/OID11UI/control02.ctl'

After:

*.control_files='/u02/oradata/OID11U1/control01.ctl','/u02/oradata/OID11U1/control02.ctl'

Remove the old controlfiles physically from disk:

cd /u02/oradata/OID11U1
rm *.ctl

Shutdown the database:

SQL> shutdown abort
ORACLE instance shut down.

Change the oracle profile in the operating system:

cd
vi .profile

Before:

export ORACLE_SID=OID11UI

After:

export ORACLE_SID=OID11U1

Source the new profile, and check that the ORACLE_SID environmental variable is correctly set:

. .profile
usu0oid01:OID11U1>echo $ORACLE_SID
OID11U1

Start sqlplus and run script:

usu0oid01:OID11UI>sqlplus / as sysdba @cntr_trace.sql

SQL*Plus: Release 11.2.0.4.0 Production on Wed May 28 14:00:50 2014

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

Connected to an idle instance.

ORACLE instance started.

Total System Global Area 1720328192 bytes
Fixed Size                  2247072 bytes
Variable Size            1107297888 bytes
Database Buffers          603979776 bytes
Redo Buffers                6803456 bytes

Control file created.

Database altered.

Tablespace altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

Check the location of the data files after the change:

SQL> select file_name from dba_data_files union select member from v$logfile  union  select name from v$controlfile;

FILE_NAME
--------------------------------------------------
/u02/oradata/OID11U1/control01.ctl
/u02/oradata/OID11U1/control02.ctl
/u02/oradata/OID11U1/redo01.log
/u02/oradata/OID11U1/redo02.log
/u02/oradata/OID11U1/redo03.log
/u02/oradata/OID11U1/sysaux01.dbf
/u02/oradata/OID11U1/system01.dbf
/u02/oradata/OID11U1/undotbs01.dbf
/u02/oradata/OID11U1/users01.dbf

Lag en spfile (anbefales):

SQL> create spfile from pfile; 

File created.

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

Startup the database for normal use:

SQL> startup

How to use adrci to tail the alert log

oracle@myhost:[PRODDB01]# adrci

ADRCI: Release 11.2.0.4.0 - Production on Wed May 28 15:06:44 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/u01/oracle"

Display which homes that exists, and set the homelocation to point to the database's home:

adrci> show homes

ADR Homes:
diag/rdbms/PRODDB01/PRODDB01
diag/tnslsnr/myhost/listener
adrci> set home diag/rdbms/PRODDB01/PRODDB01

Tail the log:

adrci> show alert -tail -f

Other handy comments related to the alert log:

show alert
show alert -tail 50
show alert -p "module_id='DBMS_SCHEDULER'"
show alert -p "module_id != 'DBMS_SCHEDULER'"
show alert -p "module_id LIKE '%SCHEDULER%'"

If you want to extract the Streams related statements from the alert log, you would use:

show alert -p "message_text like '%STREAM%'"

See also this post for searching in adr using adrci

How to backup and delete a specific range of archivelogs

Use the "BACKKUP ARCHIVELOG FROM LOGSEQ .. UNTIL LOGSEQ" syntax:

RMAN> backup archivelog from logseq 3 until logseq 4 delete input;

Output will be similar to:

Starting backup at 27-MAY-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=3 RECID=3 STAMP=848499170
input archived log thread=1 sequence=4 RECID=4 STAMP=848499311
channel ORA_DISK_1: starting piece 1 at 27-MAY-14
channel ORA_DISK_1: finished piece 1 at 27-MAY-14
piece handle=/u04/archive/PRODDB01/backupset/2014_05_27/o1_mf_annnn_TAG20140527T115156_9r8r1wt7_.bkp tag=TAG20140527T115156 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u04/archive/PRODDB01/archivelog/2014_05_25/o1_mf_1_3_9r3qksmy_.arc RECID=3 STAMP=848499170
archived log file name=/u04/archive/PRODDB01/archivelog/2014_05_25/o1_mf_1_4_9r3qp6mt_.arc RECID=4 STAMP=848499311

A check in the V$ARCHIVED_LOG reveals what Oracle has done:

SQL>  select name,sequence#,archived,applied,deleted,status,IS_RECOVERY_DEST_FILE,compressed from v$archived_log;

NAME                                                                      SEQUENCE# ARC APPLIED   DEL S IS_ COM
------------------------------------------------------------------------ ---------- ---------- --- --------- --- - --- ---
                                                                                   1 YES NO        YES D YES NO
                                                                                   2 YES NO        YES D YES NO
                                                                                   3 YES NO        YES D YES NO
                                                                                   4 YES NO        YES D YES NO
/u04/archive/PRODDB01/archivelog/2014_05_25/o1_mf_1_5_9r3r0fo6_.arc                5 YES NO        NO  A YES NO
/u04/archive/PRODDB01/archivelog/2014_05_25/o1_mf_1_6_9r3rc6pf_.arc                6 YES NO        NO  A YES NO
/u04/archive/PRODDB01/archivelog/2014_05_25/o1_mf_1_7_9r3rmkpr_.arc                7 YES NO        NO  A YES NO
/u04/archive/PRODDB01/archivelog/2014_05_25/o1_mf_1_8_9r3s0vs3_.arc                8 YES NO        NO  A YES NO
/u04/archive/PRODDB01/archivelog/2014_05_25/o1_mf_1_9_9r3sbst4_.arc                9 YES NO        NO  A YES NO
/u04/archive/PRODDB01/archivelog/2014_05_25/o1_mf_1_10_9r3so0v9_.arc              10 YES NO        NO  A YES NO

How to set up a standard listener on port 1521

Recently I have seen cases where customers have defined multiple listeners in the $TNS_ADMIN/listener.ora file, but nontheless have started only the default listener called "LISTENER".

Unless you actually start the separate listeners, they will not do you any good. Each listener must be configured to listen to different ports and then started explicitly, as follows:

If desirable to run separate listeners, configure your listener.ora as follows:

SID_LIST_LISTENER_PRODDB01 =
  (SID_LIST =
     (SID_DESC =
       (SID_NAME = PRODDB01)
       (ORACLE_HOME = /u01/oracle/product/11202)
   )
)
 
LISTENER_PRODDB01 =
  (DESCRIPTION_LIST =
     (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT =1521))
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      )
  )
 
SID_LIST_LISTENER_PRODDB02 =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PRODDB02)
      (ORACLE_HOME = /u01/oracle/product/11202)
     )
  )
 
LISTENER_PRODDB02 =
  (DESCRIPTION_LIST =
     (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT=1526))
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1526))
     )
 )
 

Then start each listener:

LSNRCTL> set current_listener LISTENER_PRODDB01
Current Listener is LISTENER_PRODDB01
LSNRCTL> start
LSNRCTL> set current_listener LISTENER_PRODDB02
Current Listener is LISTENER_PRODDB01
LSNRCTL> start

After having started all LISTENERS, check that they are running (on unix):

ps -ef | grep LISTEN|grep -v grep

 oracle 7864350 1 0 15:41:47 - 0:12 /u01/oracle/product/11203/bin/tnslsnr LISTENER_PRODDB01 -inherit
 oracle 7864350 1 0 15:41:47 - 0:12 /u01/oracle/product/11203/bin/tnslsnr LISTENER_PRODDB02 -inherit

If you only specify one - 1 - listener per server, you can set it up to listen for incoming Connections for multiple databases, as specified in the SID_LIST:

SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC =
      (SID_NAME =PRODDB01)
      (ORACLE_HOME = /u01/oracle/product/11202)
    )
   (SID_DESC =
     (SID_NAME =PRODDB02)
     (ORACLE_HOME = /u01/oracle/product/11202)
   )
)

LISTENER =
 (DESCRIPTION_LIST =
    (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
     )
 )

I prefer using a $TNS_ADMIN/listener.ora file consequently. It makes it easier to configure the listener later.
I recommomed the following lines to be added to 11gR2 listeners:

DIAG_ADR_ENABLED=on             <-- Use the ADR as base for all future logging and tracing of the listener
ADR_BASE_LISTENER = /u01/oracle <-- The value of Your $ORACLE_BASE variable goes here
LOGGING_LISTENER=off            <-- Turn logging on only if needed
TRACE_LEVEL_LISTENER=off        <-- Turn tracing on only if needed

What is the significance of using the clone.pl script to clone an existing ORACLE_HOME to a new one?

In short, when using the clone method, Oracle will relink your binaries and update your Inventory.
Afterwards, your new ORACLE_HOME can be treated individually like any other ORACLE_HOME.

From Oracle Support note 565009.1:

Cloning re-plays all the actions that were performed during the installation of the Oracle Home like relinking, updating the inventory, etc., and hence the cloned home can be patched using OPatch, and patchsets can be installed to it.

Simply copying the Oracle Home is not supported.

By [simply] copying the Oracle Home from one server to another, all the above mentioned actions are not performed and also it is not possible to apply patches or patchsets to a copied Oracle Home.

If the Oracle Home has to be deployed to multiple destinations with the same configuration, cloning is the only supported method.


For a step-by-step instruction on how to perform such a clone, please see an excellent article by Eric Jenkinsson found here.

How to use the glogin.sql file to set global sqlplus attributes

The file $ORACLE_HOME/sqlplus/admin/glogin.sql can be used to define global attributes for users of sqlplus on that particular host.

For example:

SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER SQL>"
SET LINESIZE 200
SET PAGSEIZE 200

On Production servers, you may consider adding a colored prompt, for example red:

SET SQLPROMPT "'^[[41m'_USER'@'_CONNECT_IDENTIFIER'^[[0m' SQL> "

How to create the libobk.a link in $ORACLE_HOME/lib

su - oracle
cd $ORACLE_HOME/lib
ln -s /usr/lib/libobk64.a libobk.a

How to confirm that the latest PSU has been applied to your ORACLE_HOME

Use opatch lsinventory to extract the information. In my case:

hostname:PRODDB01>opatch lsinventory | egrep -i 'PSU|DATABASE PATCH SET UPDATE'

Patch description:  "Database Patch Set Update : 11.2.0.4.2 (18031668)" 
Sub-patch  17478514; "Database Patch Set Update : 11.2.0.4.1 (17478514)"
Patch description:  "Database Patch Set Update : 11.2.0.3.7 (16619892)"
Sub-patch  16056266; "Database Patch Set Update : 11.2.0.3.6 (16056266)"
Sub-patch  14727310; "Database Patch Set Update : 11.2.0.3.5 (14727310)"
Sub-patch  14275605; "Database Patch Set Update : 11.2.0.3.4 (14275605)"
Sub-patch  13923374; "Database Patch Set Update : 11.2.0.3.3 (13923374)"
Sub-patch  13696216; "Database Patch Set Update : 11.2.0.3.2 (13696216)"
Sub-patch  13343438; "Database Patch Set Update : 11.2.0.3.1 (13343438)"

What is the difference between the views nls_database_parameters and nls_instance_parameters?

The NLS_DATABASE_PARAMETERS view will display what the NLS settings were when the database was created. These are fixed at the database level and cannot be changed.

The NLS_INSTANCE_PARAMETERS view reflects parameters set for your instance in the init.ora file or the server parameter file (spfile).


Source: James Koopman in Databasejournal.com

This matches my settings for a random database:

SQL> show parameter nls_
 
NAME                                  TYPE        VALUE
 ------------------------------------ ----------- ------------------------------
 nls_comp                             string      BINARY
 nls_language                         string      AMERICAN
 nls_length_semantics                 string      BYTE
 nls_nchar_conv_excp                  string      FALSE
 nls_territory                        string      AMERICA
 
SQL> SELECT * FROM NLS_INSTANCE_PARAMETERS WHERE value IS NOT NULL;
 
PARAMETER                       VALUE
 ------------------------------ ----------------------------------------
 NLS_LANGUAGE                   AMERICAN
 NLS_TERRITORY                  AMERICA
 NLS_COMP                       BINARY
 NLS_LENGTH_SEMANTICS           BYTE
 NLS_NCHAR_CONV_EXCP            FALSE
 
5 rows selected.

What are literals?

The terms "literal" and "constant" are synonymous and refer to a fixed data value.

There are:

•Text Literals
Examples: 'JACK', 'BLUE ISLAND', '101'.

•Numeric Literals
Examples: 5001, +256

•Datetime Literals
Example: DATE '1998-12-25'.

Note that the ANSI (American National Standards Institute) date literal contains no time portion.

•Interval Literals
Examples:
INTERVAL '123-2' YEAR(3) TO MONTH
INTERVAL '4 5:12:10.222' DAY TO SECOND(3)


Source: Oracle Documentation

How to instruct OUI to ignore prerequisites

./runinstaller -ignoreSysPrereqs