Minimalistic Oracle

Thursday, June 19, 2014

What does the TERRITORY component of the NLS_LANG parameter control?

From OTN:

"The territory component of the NLS_LANG parameter controls the operation of a subset of globalization support features.
It specifies conventions such as the default date, monetary, and numeric formats. Each supported territory has a unique name, for example, AMERICA, FRANCE, or CANADA.
If the territory is not specified, then the value is derived from the language value."

For examples please see the Globalization Support Guide

Wednesday, June 18, 2014

How to work around ORA-12705 when connecting to your database

You get:

ERROR:
 ORA-12705: Cannot access NLS data files or invalid environment specified

Solution:
Incorrect or misspelled NLS_LANG.

For example

NLS_LANG=AMERICAN_NORWAY.UTF-8

is incorrect.

However

export NLS_LANG=AMERICAN_NORWAY.UTF8

is correct.

How to install the instant client from Oracle on a Linux application server

This is how I quite quickly installed sqlplus on a Linux appserver on request from my customer:

1. Download the two files

instantclient-basic-linux.x64-11.2.0.4.0.zip
instantclient-sqlplus-linux.x64-11.2.0.4.0.zip

from the otn network.

2. Create Directory for the installation files

mkdir  /u01/oracle/product/admin/install

3. Ftp the files over to the server

4. Put the installation files in the install Directory:

mv /tmp/instantclient*.zip /u01/oracle/product/admin/install

5. Unzip the files:

unzip instantclient-basic-linux.x64-11.2.0.4.0.zip -d /u01/oracle/product
unzip instantclient-sqlplus-linux.x64-11.2.0.4.0.zip -d /u01/oracle/product

The files will be unzipped to the Directory /u01/oracle/Product/instantclient_11_2

6. Create a Directory for networking:

mkdir $ORACLE_HOME/network/admin

7. set up the .bash_profile:

export ORACLE_BASE=/u01/oracle
export ORACLE_HOME=/u01/oracle/product/instantclient_11_2
export PATH=$PATH:$HOME/bin:$ORACLE_HOME
export LD_LIBRARY_PATH=/u01/oracle/product/instantclient_11_2
export NLS_LANG=NORWEGIAN_NORWAY.UTF8
export TNS_ADMIN=$ORACLE_HOME/network/admin

8. Create file tnsnames.ora, add an entry for each of the databases you'd like to Access via sqlplus.

All done!

Note that you do need *both* the basic and the sqlplus instantclient zip files to be unpacked into the same directory. If not, you will be missing Libraries in your installation and you'll receive the following error when launching sqlplus:

sqlplus: error while loading shared libraries: libclntsh.so.11.1: cannot open shared object file: No such file or Directory

Friday, June 13, 2014

Workaround for RMAN-11003 and ORA-32010 during cloning

At the end of the duplication procedure, my script threw the following error:

RMAN-06162: sql statement: alter system reset  db_unique_name scope=spfile
 RMAN-08031: released channel: c1
 RMAN-08031: released channel: c2
 RMAN-00571: ===========================================================
 RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
 RMAN-00571: ===========================================================
 RMAN-03002: failure of Duplicate Db command at 06/12/2014 17:40:14
 RMAN-05501: aborting duplication of target database
 RMAN-03015: error occurred in stored script Memory Script
 RMAN-03009: failure of sql command on clone_default channel at 06/12/2014 17:40:14
 RMAN-11003: failure during parse/execution of SQL statement: alter system reset  db_unique_name scope=spfile
 ORA-32010: ORA-32010: cannot find entry to delete in SPFILE 

 Recovery Manager complete.

After a brief search I found that my init.ora was the culprit: I had accidently left out single quotes in the db_file_name_convert parameter:

*.db_file_name_convert=('/u02/oradata/proddb01/datafile','/u02/oradata/testdb01','/u02/oradata/proddb01/tempfile',/u02/oradata/testdb01')

Oracle Support Dok ID 1384262.1 "ORA-32010: cannot find entry to delete in SPFILE" points this out.

The spfile was not in $OH/dbs physically, although Oracle believes it does:

SQL> show parameter spfile

NAME        TYPE        VALUE
 ---------- ----------- ---------------------------------------------
 spfile     string      /u01/oracle/product/11204/dbs/spfiletestdb01.ora

To prove it, I attempted to create an spfile:

SQL> create spfile from pfile;
 create spfile from pfile
 *
 ERROR at line 1:
 ORA-32002: cannot create SPFILE already being used by the instance

When attempting to mount:

SQL> alter database mount;
 alter database mount
 *
 ERROR at line 1:
 ORA-01103: database name PRODDB01 in control file is not TESTDB01

So the control files are incorrect, written to during the duplication, and RMAN was unfortunately interrupted before the duplication procedure completed.

Solution:
Recreate the controlfile from the Source database, and open the auxiliary database with this new controlfile instead of the one that was generated during duplication.

1. Log onto the source server on which the PRODDB01 database is hosted, and execute

SQL> alter database backup controlfile to trace as '/tmp/proddb01_cntrfile.sql';

2. Transfer the file /tmp/proddb01_cntrfile.sql to the testserver
3. Shutdown the auxiliary database
4. Edit the script:
* Go to the section that says "Set #2. RESETLOGS case". You can strip away anything above this section, as you won't be needing it.
* Use the physical paths valid on the auxiliary server
* Exchange "REUSE DATABASE" with "SET DATABASE"
From Source database:

STARTUP NOMOUNT
 CREATE CONTROLFILE REUSE DATABASE "PRODDB01" RESETLOGS  ARCHIVELOG
     MAXLOGFILES 16
     MAXLOGMEMBERS 3
     MAXDATAFILES 200
     MAXINSTANCES 8
     MAXLOGHISTORY 1168
 LOGFILE
   GROUP 1 '/u02/oradata/proddb01/redo1.log'  SIZE 1000M BLOCKSIZE 512,
   GROUP 2 '/u02/oradata/proddb01/redo2.log'  SIZE 1000M BLOCKSIZE 512,
   GROUP 3 '/u02/oradata/proddb01/redo3.log'  SIZE 1000M BLOCKSIZE 512,
   GROUP 4 '/u02/oradata/proddb01/redo4.log'  SIZE 1000M BLOCKSIZE 512
 -- STANDBY LOGFILE
 DATAFILE
   '/u02/oradata/proddb01/datafile/system.428.688578763',
   '/u02/oradata/proddb01/datafile/undotbs1.489.688574787',
   '/u02/oradata/proddb01/datafile/sysaux.436.688579377'
.
.
.
CHARACTER SET AL32UTF8
;

The new edited version:

STARTUP NOMOUNT
 CREATE CONTROLFILE REUSE
 SET DATABASE "TESTDB01" RESETLOGS  ARCHIVELOG
     MAXLOGFILES 16
     MAXLOGMEMBERS 3
     MAXDATAFILES 200
     MAXINSTANCES 8
     MAXLOGHISTORY 1168
 LOGFILE
   GROUP 1 '/u02/oradata/testdb01/redo1.log'  SIZE 1024M BLOCKSIZE 512,
   GROUP 2 '/u02/oradata/testdb01/redo2.log'  SIZE 1024M BLOCKSIZE 512,
   GROUP 3 '/u02/oradata/testdb01/redo3.log'  SIZE 1024M BLOCKSIZE 512,
   GROUP 4 '/u02/oradata/testdb01/redo4.log'  SIZE 1024M BLOCKSIZE 512
 -- STANDBY LOGFILE
 DATAFILE
  '/u02/oradata/testdb01/system.428.688578763',
   '/u02/oradata/testdb01/undotbs1.489.688574787',
   '/u02/oradata/testdb01/sysaux.436.688579377',
   '/u02/oradata/testdb01/users.476.688577593',
   '/u02/oradata/testdb01/undotbs1.510.688564845',
   '/u02/oradata/testdb01/sl_part_data_2006.473.688577939',
.
.
.
CHARACTER SET AL32UTF8
;

5. Run the script on the newly cloned database:

SQL> sqlplus / as sysdba @proddb01_cntrfile.sql
Oracle instance started

 Total System Global Area    5344731136 bytes

 Fixed Size                     2255784 bytes
 Variable Size               1459618904 bytes
 Database Buffers            3875536896 bytes
 Redo Buffers                   7319552 bytes

Control file created.

6. Mount the database

SQL> alter database mount;

Database mounted.

7. Open the database

SQL> alter database open resetlogs;

Database opened.

Finally I had to handle TEMP tablespaces, as the alert log would tell you:

Dictionary check beginning
 Tablespace 'TEMP' #3 found in data dictionary,
 but not in the controlfile. Adding to controlfile.
 *********************************************************************
 WARNING: The following temporary tablespaces contain no files.
          This condition can occur when a backup controlfile has
          been restored.  It may be necessary to add files to these
          tablespaces.  That can be done using the SQL statement:

          ALTER TABLESPACE  ADD TEMPFILE

          Alternatively, if these temporary tablespaces are no longer
          needed, then they can be dropped.
            Empty temporary tablespace: TEMP

Add a file to the temporary tablespace using the REUSE clause:

SQL> alter tablespace temp add tempfile '/u02/oradata/testdb01/temp01.dbf' size 1024M reuse autoextend on next 32M maxsize unlimited;

Thursday, June 12, 2014

How to solve RMAN-06217 during cloning from active database

RMAN-03002: failure of Duplicate Db command at 06/12/2014 12:28:00
 RMAN-05501: aborting duplication of target database
 RMAN-06217: not connected to auxiliary database with a net service name

Workaround is to connect to the auxiliary database using username and password, followed by the TNS alias.
So instead of

 connect target sys/<password>@PRODDB01
 connect rmanuser/<password>@RMANCAT
 connect auxiliary /

use

 connect target sys/<password>@PRODDB01
 connect rmanuser/<password>@RMANCAT
 connect auxiliary sys/<password>@AUX01

Examples of RMAN duplicate database scripts

I prefer to set the parameters db_file_name_convert and log_file_name_convert whenever possible, to simplify the redirection of restored files.

Here is an example when parameter log_file_name_convert is not set. We are also specifying the auxiliary channels with vendor specific tape settings:


run { 
allocate auxiliary channel t1 type sbt PARMS="BLKSIZE=1048576" maxpiecesize 32G maxopenfiles 64; 
allocate auxiliary channel t2 type sbt PARMS="BLKSIZE=1048576" maxpiecesize 32G maxopenfiles 64;
send 'NB_ORA_POLICY=mypolicy,NB_ORA_CLIENT=myclient-bkp.mydomain,NB_ORA_SCHED=myschedule'; 
set until scn 5886541624244; 
duplicate target database to AUXDB1 
logfile group 1 ('/data/oracle/u01/AUXDB1/redo01.log') size 128M, 
        group 2 ('/data/oracle/u02/AUXDB1/redo02.log') size 128M, 
        group 3 ('/data/oracle/u03/AUXDB1/redo03.log') size 128M;
}

Another example, when both db_file_name_convert and log_file_name_convert are set.
In smaller environments, you may not need to bother specifying different backup polices etc, you simply specify 'SBT_TAPE' and the


run {
allocate auxiliary channel c1 type 'SBT_TAPE';
allocate auxiliary channel c2 type 'SBT_TAPE';
set until time "to_date('01.05.2014 18:00:00','DD.MM.YYYY HH24:MI:SS')";
duplicate target database to AUXDB1; 
}

How to set the db_file_name_convert and log_file_name_convert parameters

In this example, I want to redirect the files to a slightly different path during a clone from prod to test.

In prod, files are to be found under:

* /u02/oradata/proddb01/datafile

In test, I want to be placed under:

* /u02/oradata/testdb01

Furthermore, some tempfiles are placed differently than regular datafiles in prod. In test I do not need or want multiple destinations; all files should be placed under /u02/oradata/testdb01. Therefore, my db_file_name_convert parameter must have multiple pairs of source and target locations.

For the log files, they could all be placed under similar locations, so the redirection string can simply contain the only thing that will differ: the ORACLE_SID.

When using a pfile:

db_file_name_convert=('/u02/oradata/proddb01/datafile','/u02/oradata/testdb01',
                      '/u02/oradata/proddb01/tempfile',/u02/oradata/testdb01')

log_file_name_convert=('proddb01','testdb01')

When using an spfile:

alter system set db_file_name_convert='/u02/oradata/proddb01/datafile','/u02/oradata/testdb01',
                                      '/u02/oradata/proddb01/tempfile',/u02/oradata/testdb01' scope=spfile;

alter system set log_file_name_convert='proddb01','testdb01' scope=spfile;

It is also supported to use the log_file_name_convert multiple times in the parameter file, like this:

log_file_name_convert=('/u01/app/oracle/oradata/proddb01/onlinelog/','/u04/oradata/testdb01/onlinelog/')
log_file_name_convert=('/u01/app/oracle/flash_recovery_area/proddb01/onlinelog/','/u04/oradata/testdb01/onlinelog/')

Oracle Docs:
db_file_name_convert
log_file_name_convert