Oracle Database Flavours: 2012

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;
/

6. Creating a Standby Database from the Primary via RMAN

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.

More info about text index

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

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

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—

Pages

Tuesday, 18 September 2012

ORA-29833: indextype does not exist

Monday, 25 June 2012