Step by step: how to create a physical standby database with Oracle 10g

I recently had to create an Oracle 10g standby database to support a simultaneous move and upgrade of the database.

Below are the steps I followed to set it up.

Prerequisites for these steps are
* you have a working tape backup in place, and that you can perform redirected restores.
* you have the same software installed on both servers, in my case it was Oracle EE version 10.2.0.4

By "redirected restores" I mean that you are able to restore database backups through rman to a host different from the one where the backup was taken.
I will not explain the details around details such as standby redo log files, log transportation methods etc; there are plenty of good sources for this to be found on the internet.


1. prepare the primary database parameters:

LOG_ARCHIVE_CONFIG='DG_CONFIG=(proddb01,stby01)'
LOG_ARCHIVE_DEST_1='LOCATION=/u01/oracle/admin/proddb01/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=proddb01'
LOG_ARCHIVE_DEST_2='SERVICE=stby01 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stby01'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=DEFER
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
STANDBY_FILE_MANAGEMENT=AUTO

# The following parameters will only be applicable when the primary is turned into a standby
# We put them there to maker life easier in case of such event
FAL_SERVER=stby01
FAL_CLIENT=proddb01
DB_FILE_NAME_CONVERT='stby01','proddb01'
LOG_FILE_NAME_CONVERT='/u04/oradata/stby01/archive/','/u01/oracle/admin/proddb01/archive/'

2. create standby redo log files on primary database:

First, check the number of redo log groups currently in use:

SQL> select group# ,members, status from v$log;

    GROUP#    MEMBERS STATUS

---------- ---------- ----------------
         4          1 ACTIVE
         5          1 ACTIVE
         6          1 CURRENT

Oracle recommends to add standby redo log file groups with the same size as the largest member in any group. They also recommend that the number of standby redo log groups is the same as the number of redo log groups + 1.
In my case, this means that I will add 4 standby redo log groups of 512M size each:

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u02/oradata/proddb01/stb_redo7.log') size 512M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 8 ('/u02/oradata/proddb01/stb_redo8.log') size 512M;
SQL> select group# ,members, status from v$log;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 9 ('/u02/oradata/proddb01/stb_redo9.log') size 512M;

Database altered.

SQL>  ALTER DATABASE ADD STANDBY LOGFILE GROUP 10 ('/u02/oradata/proddb01/stb_redo10.log') size 512M;

Database altered.
Verify that they were created:
SQL> select group#,thread#,sequence#,archived,status from v$standby_log;

    GROUP#    THREAD#  SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
         7          0          0 YES UNASSIGNED
         8          0          0 YES UNASSIGNED
         9          0          0 YES UNASSIGNED
        10          0          0 YES UNASSIGNED
3. enable force logging:
ALTER DATABASE FORCE LOGGING;
4. restart primary database
STARTUP MOUNT
Assuming that you have a valid tape backup solution in place, backup your current control file "for standby" using rman:
rman target / catalog uid/pwd@rmancat
run {
allocate channel c1 type 'sbt_tape' maxpiecesize 4000M;
BACKUP CURRENT CONTROLFILE FOR STANDBY;
release channel c1;
5. Open the database, and switch the current logfile 

This must be done so that the last log file is indeed older than your backup controfile that you created in the previous step:
SQL> ALTER DATABASE OPEN;
SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';  # so backup is consistent and recoverable
6. Finally, backup the latest archived redo log to tape:
rman target / catalog uid/pwd@rmancat
run {
allocate channel c1 type 'sbt_tape' maxpiecesize 4000M;
BACKUP ARCHIVELOG ALL NOT BACKED UP 1 TIMES;
release channel c1;

7. Set up SQL*net connection
...by adding an entry in the primary database's tnsnames.ora:
stby01 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = psu0erdb01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = stby01)
    )
  )
8. Prepare the standby database parameters. 

# The db_name must from now on match your primary database db_name
db_name='proddb01'
# The db_unique_name is the name to which you will typically refer to the standby database
db_unique_name='stby01'
log_archive_config='DG_CONFIG=(proddb01,stby01)'
log_archive_format=log%t_%s_%r.arc
log_archive_dest_1='LOCATION=/u04/oradata/ stby01/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME= stby01'
log_archive_dest_2='SERVICE=proddb01 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=proddb01'
log_archive_dest_state_1=enable
log_archive_dest_state_2=defer
remote_login_passwordfile='EXCLUSIVE'
standby_file_management=auto
fal_server=proddb01
fal_client=stby01
db_file_name_convert='proddb01','stby01'
log_file_name_convert='proddb01','stby01'

 
9. shutdown the to-become standby database, open it in nomount-mode:
shutdown immediate
startup nomount pfile='your_pfile.ora'
Your standby database is now ready to be cloned from your primary. 

10. Create an rman script:

in file duplicate_for_standby.sh
export ORACLE_SID=stby01
export ORACLE_HOME=/u01/oracle/product/10204
export NLS_DATE_FORMAT='DD.MM.YYYY HH24:MI:SS'
export NLS_LANG=AMERICAN_NORWAY.WE8ISO8859P15
rman target sys/password@proddb01 catalog uid/pwd@rmancat auxiliary / cmdfile='duplicate_for_standby.cmd' log='duplicate_for_standby.log' trace='duplicate_for_standby.trc'
exit
In file duplicate_for_standby.cmd:
run {
ALLOCATE AUXILIARY CHANNEL c1 TYPE 'SBT_TAPE';
ALLOCATE AUXILIARY CHANNEL c2 TYPE 'SBT_TAPE';
ALLOCATE AUXILIARY CHANNEL c3 TYPE 'SBT_TAPE';
ALLOCATE AUXILIARY CHANNEL c4 TYPE 'SBT_TAPE';
ALLOCATE AUXILIARY CHANNEL c5 TYPE 'SBT_TAPE';
ALLOCATE AUXILIARY CHANNEL c6 TYPE 'SBT_TAPE';
duplicate target database for standby
dorecover;
}
exit

chmod 755 duplicate_for_standby.sh
11. Start the cloning script in the background:
nohup ./duplicate_for_standby.sh &
Follow the progress with 
tail -f duplicate_for_standby.log
As the directive "dorecover" indicates, rman will create a clone from the primary database, recover it, and leave the recovered database in mounted state.

12. Enable log transport services on the primary database:
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;
As a recap, this will instruct Oracle to ship logs to LOG_ARCHIVE_DEST_2, which is defined as a service that you have made known to your primary server through an entry in tnsnames.ora:
LOG_ARCHIVE_DEST_2='SERVICE=stby01 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stby01'

13. Check that your standby is applying logs. 
You can use the script found here

Pay particularly attention that the following section looks like this:
Is the MRP process running?
PROCESS   STATUS
--------- ------------

MRP0      WAIT_FOR_LOG
Crosscheck that with a look at the current archived log status:
set lines 200
col name format a60
alter session set nls_language='american';
alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss';
SELECT SEQUENCE#,NAME,STANDBY_DEST,ARCHIVED,APPLIED,DELETED,STATUS,COMPLETION_TIME
FROM V$ARCHIVED_LOG
ORDER BY SEQUENCE# ASC
/
The primary and standby should give similar views of the archived log situation:
Standby:
SEQUENCE# NAME                                                      STA ARC APP DEL S COMPLETION_TIME
---------- -------------------------------------------------------- --- --- --- --- - -------------------

75072 /u04/oradata/stby01/archive/log1_75072_681750063.arc          NO  YES YES NO  A 22.10.2014 12:26:07
75073 /u04/oradata/stby01/archive/log1_75073_681750063.arc          NO  YES YES NO  A 22.10.2014 12:26:37
Primary. Notice how the archived logs are sent to two destinations at every log switch:
SEQUENCE# NAME                                                      STA ARC APP DEL S COMPLETION_TIME
---------- -------------------------------------------------------- --- --- --- --- - -------------------

75072 stby01                                                        YES YES YES NO  A 22.10.2014 12:26:06
75072 /u01/oracle/admin/proddb01/archive/1_75072_681750063.arc      NO  YES NO  NO  A 22.10.2014 12:26:08
75073 stby01                                                        YES YES YES NO  A 22.10.2014 12:26:37
75073 /u01/oracle/admin/proddb01/archive/1_75073_681750063.arc      NO  YES NO  NO  A 22.10.2014 12:26:37
From the standby database's alert log, it's clear that logs are being applied:
Wed Oct 22 13:26:13 2014
Media Recovery Log /u04/oradata/stby01/archive/log1_75074_681750063.arc
Wed Oct 22 13:26:33 2014
Media Recovery Waiting for thread 1 sequence 75075 (in transit)
Wed Oct 22 13:26:39 2014
Primary database is in MAXIMUM PERFORMANCE mode
RFS[30]: Successfully opened standby log 7: '/u02/oradata/stby01/stb_redo7.log'
Wed Oct 22 13:26:43 2014
Media Recovery Log /u04/oradata/stby01/archive/log1_75075_681750063.arc
Media Recovery Waiting for thread 1 sequence 75076 (in transit)
Documentation used 

"Creating a Physical Standby Database"
"Creating a Standby Database with Recovery Manager"

How to view stored scripts in RMAN

Three different ways to print scripts stored in your recovery catalog:

list all script names;
list global script names;
print script my_script;

Script example: create database

CREATE DATABASE "mydb"
    MAXINSTANCES 8
    MAXLOGHISTORY 1
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
DATAFILE '/u02/oradata/mydb/system01.dbf' SIZE 512M REUSE AUTOEXTEND ON NEXT  32M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL 
SYSAUX DATAFILE '/u02/oradata/mydb/sysaux01.dbf' SIZE 512M REUSE AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED 
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u02/oradata/mydb/temp01.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE '/u02/oradata/mydb/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('/u03/oradata/mydb/redo01.log') SIZE 2048M,
             GROUP 2 ('/u03/oradata/mydb/redo02.log') SIZE 2048M,
             GROUP 3 ('/u03/oradata/mydb/redo03.log') SIZE 2048M
USER SYS IDENTIFIED BY "&&sysPassword"
USER SYSTEM IDENTIFIED BY "&&systemPassword";

Note 1:
You can exchange "SMALLFILE" with "BIGFILE" for any tablespace, as long as they are locally managed with automatic segment space management.

There are three exceptions to this rule: locally managed undo tablespaces, temporary tablespaces, and the SYSTEM tablespace does not have to use automatic segment space management; they *must* use manual segment space management but can still be created as BIGFILE. In one of my databases, I have the following setup:

SELECT tablespace_name,bigfile,extent_management,segment_space_management 
FROM dba_tablespaces;

TABLESPACE_NAME                          BIGFILE   EXTENT_MANAGEMENT              SEGMENT_SPACE_MANA
---------------------------------------- --------- ------------------------------ ------------------
SYSTEM                                   NO        LOCAL                          MANUAL
SYSAUX                                   NO        LOCAL                          AUTO
UNDOTBS1                                 YES       LOCAL                          MANUAL
TEMP                                     YES       LOCAL                          MANUAL
USERS                                    NO        LOCAL                          AUTO

Note 2:
The NATIONAL CHARACTER SET clause specifies the "national character set used to store data in columns specifically defined as NCHAR, NCLOB, or NVARCHAR2. Valid values are AL16UTF16 and UTF8. The default is AL16UTF16."

Why aren't other schemas' procedures showing in TOADs schema browser?

Problem: User SCOTT has been granted SELECT on a number of tables belonging to another schema, OBM.
They all show up neatly in TOADs schema browser.

However, procedures and sequences are not visible. Why?

Answer: lack of privileges.

Solution: Try granting the following as user sys:

GRANT SELECT ON OBM.sequence1 to SCOTT;
GRANT DEBUG ON OBM.proceure1 to SCOTT;

Refresh the schema browser - voila!

To generate a list of objects with grant statements:

SELECT 'GRANT DEBUG ON ' || OWNER || '.' || OBJECT_NAME || ' TO SCOTT;' 
FROM DBA_PROCEDURES 
WHERE OWNER = 'OBM' 
AND OBJECT_TYPE='PROCEDURE';

SELECT 'GRANT SELECT ON ' || SEQUENCE_OWNER || '.' || SEQUENCE_NAME || ' TO SCOTT;' 
FROM DBA_SEQUENCES 
WHERE SEQUENCE_OWNER = 'OBM';

Alternatively, if acceptable in your environment you could grant select and debug on all sequences and procedures, respectively:

GRANT SELECT ANY SEQUENCE TO SCOTT;
GRANT DEBUG ANY PROCEDURE TO SCOTT;

How to find hidden parameters in the database

set lines 200
col description format a70
col value format a20
col name format a30
SELECT name,value,description from SYS.V$PARAMETER WHERE name  LIKE '\_%' ESCAPE '\';
SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2  WHERE  UPPER(name) ='EVENT' AND  isdefault='FALSE';
SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2 WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE';

If desirable, you can generate a "reset" script, which preserves your hidden parameters, like this:

set lines 200
set pages 0
set feedback off
set verify off
set echo off
set heading off
set trimspool on
spool original_hidden_params.sql
select '-- original hidden parameter values' from dual;
select 'alter system set "' || name || '"=' || value || ' scope=spfile;' from SYS.V$PARAMETER WHERE name  LIKE '\_%' ESCAPE '\';
select 'exit' from dual;
spool off
spool reset_hidden_params.sql
select '-- reset hidden parameter' from dual;
select 'alter system reset "' || name || '" scope=spfile;' from SYS.V$PARAMETER WHERE name  LIKE '\_%' ESCAPE '\';
select 'exit' from dual;
exit

To set a hidden parameter in Your instance, see this post.

How to install Oracle Text

Based on "Manual Installation, Deinstallation and Verification of Oracle Text 11gR2 (Doc ID 970473.1)" from My Oracle Support

1. Install the CTXSYS schema:

SQL> connect SYS/password as SYSDBA
SQL> spool text_install.txt
SQL> @?/ctx/admin/catctx.sql change_on_install SYSAUX TEMP NOLOCK

2. Install the language-specific default preferences.
There is script which creates language-specific default preferences for every language Oracle Text supports in $O_H/ctx/admin/defaults Directory

Grep for your language, and you'll find your script easily:

myserver>grep Norwegian *
drdefn.sql:Rem      default preference for Norwegian

Execute it as follows:

SQL> connect "CTXSYS"/"change_on_install"
SQL> @?/ctx/admin/defaults/drdefn.sql "NORWEGIAN";
SQL> connect SYS/password as SYSDBA
SQL> alter user ctxsys account lock password expire;
SQL> spool off

3. Verify your installation by running:

connect SYS/password as SYSDBA

set pages 1000
col object_name format a40
col object_type format a20
col comp_name format a30
column library_name format a8
column file_spec format a60 wrap
spool text_install_verification.log

-- check on setup
select comp_name, status, substr(version,1,10) as version from dba_registry where comp_id = 'CONTEXT';
select * from ctxsys.ctx_version;
select substr(ctxsys.dri_version,1,10) VER_CODE from dual;

select count(*) from dba_objects where owner='CTXSYS';

-- Get a summary count
select object_type, count(*) from dba_objects where owner='CTXSYS' group by object_type;

-- Any invalid objects
select object_name, object_type, status from dba_objects where owner='CTXSYS' and status != 'VALID' order by object_name;

spool off

Valid output depends on your Oracle version, but for 11.2.0.3 it is:

COMP_NAME                      STATUS                                       VERSION
------------------------------ -------------------------------------------- ----------------------------------------
Oracle Text                    VALID                                        11.2.0.3.0
 

VER_DICT                                 VER_CODE
---------------------------------------- ----------------------------------------
11.2.0.3.0                               11.2.0.3.0
 

VER_CODE
----------------------------------------
11.2.0.3.0


  COUNT(*)
----------
       388


OBJECT_TYPE            COUNT(*)
-------------------- ----------
INDEX                        63
TYPE BODY                     6
INDEXTYPE                     4
PROCEDURE                     2
TYPE                         35
TABLE                        50
VIEW                         77
FUNCTION                      2
LIBRARY                       1
PACKAGE BODY                 63
OPERATOR                      6
PACKAGE                      74
LOB                           2
SEQUENCE                      3

How to create the PLUSTRACE role in order to use the AUTOTRACE feature in sqlplus

Create the PLAN_TABLE in the schema you want to use it for (in this example, SCOTT):

CONNECT SCOTT 
@$ORACLE_HOME/rdbms/admin/utlxplan.sql 

Create the PLUSTRACE role:

CONNECT / AS SYSDBA 
@$ORACLE_HOME/sqlplus/admin/plustrce.sql 

Grant the PLUSTRACE role to SCOTT:

CONNECT / AS SYSDBA 
grant plustrace to scott;

You are now able to use the autotrace feature of sqlplus for user SCOTT.

Source: Oracle Documentation

How to display the currently used ORACLE_HOME from sqlplus

Thanks to my former colleague Laurent Schneider for pointing out a way to display the currently used ORACLE_HOME from within sqlplus:

set lines 200
col oracle_home format a40
set autoprint on
var oracle_home varchar2(255)
exec dbms_system.get_env('ORACLE_HOME',:ORACLE_HOME);

PL/SQL procedure successfully completed.

ORACLE_HOME
------------------------------

/u01/oracle/product/11204

Note: To automatically display the value of a bind variable created with VARIABLE, use the SET AUTOPRINT command.
ON or OFF controls whether SQL*Plus automatically displays bind variables (referenced in a successful PL/SQL block or used in an EXECUTE command).

Source: Oracle Documentation