Pages

Tuesday 18 September 2012


Data guard configuration using RMAN backup

Versions:
CentOs-5.5
Oracle 10.2.0

Databases information:
Primary database name:ACDBP
Primary db unique_name=ACDBP
Standby  database name=ACDBP
Standby db_unique_name=ACDBS
1.       Create the primary database
2.       Set the parameter file for the primary database:
initACDBP.ora

db_cache_size=125M
java_pool_size=8M
large_pool_size=8M
shared_pool_size=100M
streams_pool_size=20M
audit_file_dest='/opt/app/oracle/admin/ACDBP/adump'
background_dump_dest='/opt/app/oracle/admin/ACDBP/bdump'
compatible='10.2.0'
control_files='/opt/app/oracle/oradata/ACDBP/control01.ctl','/opt/app/oracle/admin/ACDBP/controlfile/control02.ctl'
core_dump_dest='/opt/app/oracle/admin/ACDBP/cdump'
db_block_size=8192
db_domain=''
db_file_multiblock_read_count=16
db_name='ACDBP'
db_recovery_file_dest='/opt/app/oracle/flash_recovery_area'
db_recovery_file_dest_size=2048M
job_queue_processes=10
open_cursors=100
pga_aggregate_target=70M
processes=50
remote_login_passwordfile='EXCLUSIVE'
sga_target=536M
undo_management='AUTO'
undo_tablespace='UNDOTBS1'
user_dump_dest='/opt/app/oracle/admin/ACDBP/udump'
service_names=ACDBP
#data guard parameters
db_unique_name='ACDBP'
fal_client='ACDBP'
fal_server='ACDBS'
log_archive_config='DG_CONFIG=(ACDBP,ACDBS)'
log_archive_dest_1='LOCATION=/opt/app/oracle/admin/ACDBP/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ACDBP'
log_archive_dest_2='SERVICE=ACDBS LGWR ASYNC VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ACDBS'
#log_archive_dest_2='SERVICE=ACDBS lgwr sync affirm reopen=10 #VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ACDBS'
log_archive_dest_state_1='ENABLE'
log_archive_dest_state_2='ENABLE'
log_archive_format='%t_%s_%r.dbf'
log_archive_max_processes=2
ACDBP.standby_archive_dest='/opt/app/oracle/admin/ACDBP/arch'
standby_file_management='AUTO'




Create the appropriate directories for the stand by database and set the
parameter file for the standby database.

initACDBS.ora

db_cache_size=125M
java_pool_size=8M
large_pool_size=8M
shared_pool_size=100M
streams_pool_size=20M
audit_file_dest='/opt/app/oracle/admin/ACDBS/adump'
background_dump_dest='/opt/app/oracle/admin/ACDBS/bdump'
compatible='10.2.0'
control_files='/opt/app/oracle/oradata/ACDBS/standby1.ctl'
core_dump_dest='/opt/app/oracle/admin/ACDBS/cdump'
db_block_size=8192
db_domain=''
db_file_multiblock_read_count=16
db_name='ACDBP'
db_recovery_file_dest='/opt/app/oracle/flash_recovery_area'
db_recovery_file_dest_size=2048M
job_queue_processes=10
open_cursors=100
pga_aggregate_target=70M
processes=50
remote_login_passwordfile='EXCLUSIVE'
sga_target=536M
undo_management='AUTO'
undo_tablespace='UNDOTBS1'
user_dump_dest='/opt/app/oracle/admin/ACDBS/udump'
service_names=ACDBS
#data guard parameters
db_unique_name='ACDBS'
fal_client='ACDBS'
fal_server='ACDBP'
log_archive_config='DG_CONFIG=(ACDBS,ACDBP)'
log_archive_dest_1='LOCATION=/opt/app/oracle/admin/ACDBS/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ACDBS'
#log_archive_dest_2='SERVICE=ACDBS LGWR ASYNC VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) #DB_UNIQUE_NAME=ACDBS'
log_archive_dest_2='SERVICE=ACDBP LGWR ASYNC VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ACDBP'
log_archive_dest_state_1='ENABLE'
log_archive_dest_state_2='ENABLE'
log_archive_format='%t_%s_%r.dbf'
db_file_name_convert='/opt/app/oracle/oradata/ACDBP/datafile','/opt/app/oracle/oradata/ACDBS/datafile'
log_file_name_convert='/opt/app/oracle/oradata/ACDBP/logfile','/opt/app/oracle/oradata/ACDBS/logfile','/opt/app/oracle/admin/ACDBP/logfile','/opt/app/oracle/admin/ACBS/logfile'
log_archive_max_processes=2
standby_archive_dest='/opt/app/oracle/admin/ACDBS/arch'
standby_file_management='AUTO'


3.       Configure the listener and tns entries on both primary and standby database
servers:

Primary listener.ora and tnsnames.ora:
listener.ora:
LISTENER =
 (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = IPC)(KEY = ACDBP))
     )
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.210)(PORT = 1521))
     )
    )
 )

SID_LIST_LISTENER =
 (SID_LIST =
    (SID_DESC =
     (ORACLE_HOME = /opt/app/oracle/product/10.2.0)
     (SID_NAME = ACDBP)
    )
 
    (SID_DESC =
     (ORACLE_HOME = /opt/app/oracle/product/10.2.0)
     (SID_NAME = PLSExtProc)
     (PROGRAM = extproc)
    )
    )

tnsnames.ora:

ACDBS =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.211)(PORT = 1521))
    )
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = ACDBS)
    )
 )

Standby listener .ora and tnsnames.ora:
listener.ora:
LISTENER =
 (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = IPC)(KEY = ACDBS))
     )
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.211)(PORT = 1521))
     )
    )
 )

SID_LIST_LISTENER =
 (SID_LIST =
    (SID_DESC =
     (ORACLE_HOME = /opt/app/oracle/product/10.2.0)
     (SID_NAME = ACDBS)
    )
 
    (SID_DESC =
     (ORACLE_HOME = /opt/app/oracle/product/10.2.0)
     (SID_NAME = PLSExtProc)
     (PROGRAM = extproc)
    )
    )

tnsnames.ora:
ACDBP =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.210)(PORT = 1521))
    )
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = ACDBSP)
    )
 )

4.       Enable the archive for the primary database.
SQL>shut immediate;
SQL>startup mount;
SQL>alter database archivelog;
SQL>alter database open;


5.       Create the standby logfiles in the primary database.

ALTER DATABASE ADD STANDBY LOGFILE GROUP 3 ('/opt/app/oracle/admin/ACDBP/logfile/redo_stand_1a.log','/opt/app/oracle/oradata/ACDBP/logfile/redo_stand_1b.log') size 10M;
   /
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/opt/app/oracle/admin/ACDBP/logfile/redo_stand_1a.log','/opt/app/oracle/oradata/ACDBP/logfile/redo_stand_1b.log') size 10M;
  /



a.       Launch RMAN to connect to the ACDBP database and check the current
RMAN configuration(On production server)

$rman target /
RMAN>show all;


b.      Backup the current production database to create a standby database:

RMAN> backup database include current controlfile for standby plus archivelog;

c.       Manually copy the backup sets from production to the standby database
server. (Location of backups must match on both production and DR).


$cd /u01/opt/app/oracle/flash_recovery_area/ACDBP/backupsets/date
$mkdir –p /u01/opt/app/oracle/flash_recovery_area/ACDBP/backupsets/date (on standby server)
$scp * oracle@192.168.1.211: /u01/opt/app/oracle/flash_recovery_area/ACDBP/backupsets/date



d.      On Standby database server startup ACDBS database in nomount
mode( standby server)
$export ORACLE_SID=ACDBS
$sqlplus / as sysdba
SQL>startup nomount
SQL>exit


e.      Create the standby database using RMAN (This assumes the database file
structures will be identical on both servers)(Standby server):

$RMAN target ‘sys/abacus@ACDBP’ auxiliary /
RMAN>duplicate target database for standby nofilenamecheck dorecover;


f.        Put the ACDBS database into managed standby mode:
SQL> shutdown immediate;
SQL> startup nomount;
SQL> alter database mount standby database;
SQL> alter database recover managed standby database disconnect;

g.       On the production database switch logs to initiate replication:


SQL> alter system switch logfile;




The configuration of Dataguard is now complete.


Note: Monitor  the alertlog file of the production for the redolog shipping
status and the heartbeat status.



nofilenamecheck: The NOFILENAMECHECK option is required when the standby and primary datafiles and logs have the same names.

Dorecover:   By default, RMAN does not recover the standby database after creating it. If you do not specify the DORECOVER option of the DUPLICATE command, then RMAN automates these steps of the standby creation procedure during duplication:

A.        RMAN establishes connections both to the primary and standby databases, and the recovery catalog (if used).
B.        RMAN queries the repository, which is either the primary control file or the recovery catalog, to identify the backups of primary database datafiles and the standby control file.
C.        If you use a media manager, then RMAN contacts the media manager on the standby host to request the backup data.
D.       RMAN restores the standby control file to the standby host, thereby creating the standby control file.
E.        RMAN restores the primary datafile backups and copies to the standby host, thereby creating the standby database datafiles.
F.        RMAN leaves the standby database mounted, but does not place the standby database in manual or managed recovery mode. RMAN disconnects and does not perform media recovery of the standby database. Note that you should not register the standby database in the recovery catalog.












ORA-29833: indextype does not exist


ORA-29833: indextype does not exist

Environment: 
Oracle version: Oracle 10gR2 (10.2.0.4.0 )
OS version: RHEL 6.2 

In my environment, there was a database migration as the hardware and OS is upgraded.  So I installed Oracle 10g in the new server and created a database with the same name(as existing database). I tried to export the existing database and imported in the new database. While comparing the new database schemas with the existing schemas, there are some indexes missing in the new database schema.

                I tried to import that schema again, but it is of no use. I tried to create them manually. Then I encountered with the below error:

SQL> CREATE INDEX CPSI.TRACKS$TRACK_NAME ON CPSI.TRACKS
  2  (TRACK_NAME)
  3  INDEXTYPE IS CTXSYS.CONTEXT
  4  PARAMETERS('sync');

ERROR at line 2:
 ORA-29833: indextype does not exist



Cause:
There is no index type by the specified name. and my database is not installed with Oracle Text. We can find this by checking if the database has CTXSYS default user.


Solution

This can be resolved by installing Oracle Text.

Issue:
------
Step 1: Ensure the schema CTXSYS does not exist, if exists ignore the execution of “step 2”. 
Step 2: Add the below mentioned statement above the existing statement(also mentioned below) in the catctx.sql file which is in the $ORACLE_HOME/ctx/admin" location.

Existing statement : Rem CTXDEF.sql - ctx default object creation
Statement to be added: grant execute on CTX_DDL to ctxsys ;
In SYS user login, execute the script to create CTXSYS schema and its related objects after confirming below parameters to this script
ctxsys - password of CTXSYS schema
SYSAUX                - default tablespace
TEMP - default temp tablespace (Replace if your database default temporary tablespace is different)

SQL>@$ORACLE_HOME/ctx/admin/catctx.sql ctxsys SYSAUX TEMP NOLOCK

Step 3: In SYS user login execute the below script to recompile user objects
SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql

Step 4: In CTXSYS user login, execute the below script to create default preference for language ENGLISH

SQL>@$ORACLE_HOME/ctx/admin/defaults/drdefus.sql



Step 5 : In SYS User login ,execute the below script to give grant permission
SQL>grant ctxapp to DBusername identified by Password; --( username CPSI in my case)
SQL>grant select on ctxsys.dr$preference to DBusername; --( username CPSI in my case)
SQL>grant execute on CTX_DDL to DBusername; --( username CPSI in my case)

Step 6: Login as DB user and execute the below drop index script if any already exists in same name
SQL>DROP INDEX LEI_CATALOG_SEARCH_SFIELD

Step 7: Login as DB user and create the below domain index
SQL> CREATE INDEX CPSI.TRACKS$TRACK_NAME ON CPSI.TRACKS
  2  (TRACK_NAME)
  3  INDEXTYPE IS CTXSYS.CONTEXT
  4  PARAMETERS('sync');

Make sure that you should get the following output if you run the below queries(if your version is 10.2.0.4.0 )

SQL> select comp_name, status, substr(version,1,10) as version from dba_registry where comp_id = 'CONTEXT';

COMP_NAME                    STATUS   VERSION
---------------------------- -------- ----------
Oracle Text                  VALID    10.2.0.4.0

SQL>
SQL> select * from ctxsys.ctx_version;

VER_DICT   VER_CODE
---------- ----------
10.2.0.4.0 10.2.0.4.0


SQL>
SQL> select substr(ctxsys.dri_version,1,10) VER_CODE from dual;


VER_CODE
----------
10.2.0.4.0

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

  COUNT(*)
----------
       313


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


OBJECT_TYPE           COUNT(*)
------------------- ----------
FUNCTION                     2
INDEX                       46
INDEXTYPE                    4
LIBRARY                      1
LOB                          1
OPERATOR                     6
PACKAGE                     67
PACKAGE BODY                56
PROCEDURE                    2
SEQUENCE                     3
TABLE                       37
TYPE                        27
TYPE BODY                    7
VIEW                        54


14 rows selected.

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

no rows selected

SQL>
SQL> spool off

Refer DOC ID:979705.1 for more info in metalink