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

Monday 25 June 2012


Oracle Enterprise Manager Configuration

1.      create the repository

$ emca -config dbcontrol db -repos create

                
Dec 2, 2005 7:35:01 PM oracle.sysman.emcp.EMReposConfig createRepository
                INFO: Creating the EM repository (this may take a while) ...
                Dec 2, 2005 7:35:01 PM oracle.sysman.emcp.EMReposConfig createRepository
                CONFIG: ORA-20001: SYSMAN already exists..
                ORA-06512: at line 17

oracle.sysman.assistants.util.sqlEngine.SQLFatalErrorException: ORA-20001: SYSMAN already exists..
                ORA-06512: at line 17

                                at oracle.sysman.assistants.util.sqlEngine.SQLEngine.executeImpl(SQLEngine.java:1474)
                                at oracle.sysman.assistants.util.sqlEngine.SQLEngine.executeScript(SQLEngine.java:848)
                at oracle.sysman.assistants.util.sqlEngine.SQLPlusEngine.executeScript(SQLPlusEngine.java:267)
at oracle.sysman.assistants.util.sqlEngine.SQLPlusEngine.executeScript(SQLPlusEngine.java:308)
                                at oracle.sysman.emcp.EMReposConfig.createRepository(EMReposConfig.java:422)
                                at oracle.sysman.emcp.EMReposConfig.invoke(EMReposConfig.java:192)
                                at oracle.sysman.emcp.EMReposConfig.invoke(EMReposConfig.java:134)
                                at oracle.sysman.emcp.EMConfig.perform(EMConfig.java:171)
                                at oracle.sysman.emcp.EMConfigAssistant.invokeEMCA(EMConfigAssistant.java:486)
                at oracle.sysman.emcp.EMConfigAssistant.performConfiguration(EMConfigAssistant.java:1142)
                                at oracle.sysman.emcp.EMConfigAssistant.statusMain(EMConfigAssistant.java:470)
                                at oracle.sysman.emcp.EMConfigAssistant.main(EMConfigAssistant.java:419)
                Dec 2, 2005 7:35:01 PM oracle.sysman.emcp.EMReposConfig invoke
                SEVERE: Error creating the repository
                Dec 2, 2005 7:35:01 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Refer to the log file at /opt/app/oracle/oracle/product/10.2.0/db_1/cfgtoollogs/emca/DBACLASS/emca_repos_create_<date>.log for more details.
                Dec 2, 2005 7:35:01 PM oracle.sysman.emcp.EMConfig perform
                SEVERE: Error creating the repository
Refer to the log file at /opt/app/oracle/oracle/product/10.2.0/db_1/cfgtoollogs/emca/DBACLASS/emca_2005-12-02_07-34-42-PM.log for more details.
                Dec 2, 2005 7:35:01 PM oracle.sysman.emcp.EMConfig perform
                CONFIG: Stack Trace:
                oracle.sysman.emcp.exception.EMConfigException: Error creating the repository
                                at oracle.sysman.emcp.EMReposConfig.invoke(EMReposConfig.java:204)
                                at oracle.sysman.emcp.EMReposConfig.invoke(EMReposConfig.java:134)
                                at oracle.sysman.emcp.EMConfig.perform(EMConfig.java:171)
                                at oracle.sysman.emcp.EMConfigAssistant.invokeEMCA(EMConfigAssistant.java:486)
                                at oracle.sysman.emcp.EMConfigAssistant.performConfiguration(EMConfigAssistant.java:1142)
                                at oracle.sysman.emcp.EMConfigAssistant.statusMain(EMConfigAssistant.java:470)



2.       As the database is created manually, the SYSMAN user and some other users like MGMT_USER and synonyms already exists in the database. So, they should be dropped   before the repository is created. If the user is not dropped the previous error will occur.

 Drop the SYSMAN user along with the other user and roles as follows:

                   SQL> drop user sysman cascade;
                   User dropped.

                   SQL> DROP USER mgmt_view;
                   User dropped.

                   SQL> DROP PUBLIC SYNONYM mgmt_target_blackouts;
                   Synonym dropped.

                   SQL> DROP ROLE mgmt_user;
                   Role dropped.

                   SQL> DROP PUBLIC SYNONYM setemviewusercontext;
                   Synonym dropped.



3.       Now create the repository and configure the database control at a time with the below command.
                It prompts for the passwords. The passwords should be given in the double quotes.

$ emca -config dbcontrol db -repos create
               
STARTED EMCA at Dec 2, 2005 9:21:32 PM
                EM Configuration Assistant, Version 10.2.0.1.0 Production
                Copyright (c) 2003, 2005, Oracle.  All rights reserved.

                Enter the following information:
                Database SID: DBACLASS
                Database Control is already configured for the database DBACLASS
                You have chosen to configure Database Control for managing the database DBACLASS
                This will remove the existing configuration and the default settings and perform a fresh configuration
                Do you wish to continue? [yes(Y)/no(N)]: y
                Listener port number: 1521
                Password for SYS user: 
                Password for DBSNMP user: 
                Password for SYSMAN user: 
                Email address for notifications (optional): venkat.tadi@abacus-concepts.com
                Outgoing Mail (SMTP) server for notifications (optional):
                -----------------------------------------------------------------

                You have specified the following settings

                Database ORACLE_HOME ................ /opt/app/oracle/oracle/product/10.2.0/db_1

                Database hostname ................ dg2.abacus.com
                Listener port number ................ 1521
                Database SID ................ DBACLASS
                Email address for notifications ............... venkat.tadi@abacus-concepts.com
                Outgoing Mail (SMTP) server for notifications ...............

                -----------------------------------------------------------------
                Do you wish to continue? [yes(Y)/no(N)]: y
                Dec 2, 2005 9:22:26 PM oracle.sysman.emcp.EMConfig perform
                INFO: This operation is being logged at /opt/app/oracle/oracle/product/10.2.0/db_1/cfgtoollogs/emca/DBACLASS/emca_2005-12-02_09-21-32-PM.log.
                Dec 2, 2005 9:22:27 PM oracle.sysman.emcp.util.DBControlUtil stopOMS
                INFO: Stopping Database Control (this may take a while) ...
                Dec 2, 2005 9:22:30 PM oracle.sysman.emcp.EMReposConfig createRepository
                INFO: Creating the EM repository (this may take a while) ...
                Dec 2, 2005 9:24:14 PM oracle.sysman.emcp.EMReposConfig invoke
                INFO: Repository successfully created
                Dec 2, 2005 9:24:18 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
                INFO: Securing Database Control (this may take a while) ...
                Dec 2, 2005 9:26:50 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
                INFO: Database Control secured successfully.
                Dec 2, 2005 9:26:50 PM oracle.sysman.emcp.util.DBControlUtil startOMS
                INFO: Starting Database Control (this may take a while) ...
                Dec 2, 2005 9:28:28 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
                INFO: Database Control started successfully
                Dec 2, 2005 9:28:28 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
                INFO: >>>>>>>>>>> The Database Control URL is https://dg2.abacus.com:1158/em  <<<<<<<<<<<
                Enterprise Manager configuration completed successfully
                FINISHED EMCA at Dec 2, 2005 9:28:28 PM


           
             4.       Now the OEM is successfully installed and configured. To check status of the dbconsole:

                 $ emctl status dbconsole         
               
                TZ set to Asia/Calcutta
                Oracle Enterprise Manager 10g Database Control Release 10.2.0.4.0 
                Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.
                https://dg2.abacus.com:1158/em/console/aboutApplication
                Oracle Enterprise Manager 10g is running.
                ------------------------------------------------------------------
                Logs are generated in directory /opt/app/oracle/oracle/product/10.2.0/db_1/dg2.abacus.com_DBACLASS/sysman/log
               
               
                It shows that the OEM is running. We can check it from the client machine.


               
5.       Open the browser in the client system. and https://hostname:port/em/ in our case it is https://dg2.abacus.com:1158/em

      
  Screen1—log in page       



    Screen2—license agreement.
                             
   Click on the I agree button

  Screen3—