Monday, December 19, 2011

Sample dataguard configuration for 10g database (prorcl/drorcl)

-- DR
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 5
'/u01/app/oracle/oradata/prorcl/stdby05.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 6
'/u01/app/oracle/oradata/prorcl/stdby06.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 7
'/u01/app/oracle/oradata/prorcl/stdby07.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 8
'/u01/app/oracle/oradata/prorcl/stdby08.log' size 50M;
alter system set db_unique_name='drorcl' scope=spfile;
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(prorcl,drorcl)';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=drorcl';
alter system set LOG_ARCHIVE_DEST_2= 'SERVICE=prorcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prorcl';
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE;
alter system set LOG_ARCHIVE_DEST_STATE_10=DEFER;
alter system set FAL_SERVER='prorcl';
alter system set FAL_CLIENT='drorcl';
alter system set DB_FILE_NAME_CONVERT='prorcl','prorcl' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT='prorcl','prorcl' scope=spfile;
alter system set STANDBY_FILE_MANAGEMENT=AUTO;
-----------------------------------------------------------------------------------
-- PR
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 5
'/u01/app/oracle/oradata/prorcl/stdby05.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 6
'/u01/app/oracle/oradata/prorcl/stdby06.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 7
'/u01/app/oracle/oradata/prorcl/stdby07.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 8
'/u01/app/oracle/oradata/prorcl/stdby08.log' size 50M;

alter system set db_unique_name='prorcl' scope=spfile;
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(prorcl,drorcl)';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prorcl';
alter system set LOG_ARCHIVE_DEST_2= 'SERVICE=drorcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=drorcl';
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE;
alter system set LOG_ARCHIVE_DEST_STATE_10=DEFER;
alter system set FAL_SERVER='drorcl';
alter system set FAL_CLIENT='prorcl';
alter system set DB_FILE_NAME_CONVERT='prorcl','prorcl' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT='prorcl','prorcl' scope=spfile;
alter system set STANDBY_FILE_MANAGEMENT=AUTO;

Sunday, December 18, 2011

ORA-16047: DGID mismatch between destination setting and standby

[oracle@vmxdb01b ~]$ more /u01/app/oracle/admin/prorcl/bdump/prorcl_arc0_5809.trc
/u01/app/oracle/admin/prorcl/bdump/prorcl_arc0_5809.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
System name: Linux
Node name: vmxdb01b.lab.dbaglobe.com
Release: 2.6.18-274.12.1.el5
Version: #1 SMP Tue Nov 29 13:37:46 EST 2011
Machine: x86_64
Instance name: prorcl
Redo thread mounted by this instance: 1
Oracle process number: 16
Unix process pid: 5809, image: oracle@vmxdb01b.lab.dbaglobe.com (ARC0)

*** SERVICE NAME:() 2011-12-18 23:47:14.471
*** SESSION ID:(156.1) 2011-12-18 23:47:14.471
kcrrwkx: nothing to do (start)
Redo shipping client performing standby login
*** 2011-12-18 23:47:14.590 64561 kcrr.c
Logged on to standby successfully
Client logon and security negotiation successful!
Error 16047 attaching to destination LOG_ARCHIVE_DEST_2 standby host 'drorcl'
ORA-16047: DGID mismatch between destination setting and standby
*** 2011-12-18 23:47:14.591 58941 kcrr.c
kcrrfail: dest:2 err:16047 force:0 blast:1
kcrrwkx: unknown error:16047
ORA-16055: FAL request rejected
ARCH: Connecting to console port...
ARCH: Connecting to console port...
kcrrwkx: nothing to do (end)

Root Cause:

Case mis-match between db_unique_name and log_archive_config


alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(prorcl,drorcl)';
db_name = prorcl
db_unique_name = PRORCL

Sunday, December 11, 2011

TNS-12599: TNS:cryptographic checksum mismatch

Symptoms:
After Enterprise manager Grid Control 12c installed, following message appears in alert log of repository database.


Fri Dec 09 14:11:21 2011


***********************************************************************

NI cryptographic checksum mismatch error: 12599.

VERSION INFORMATION:
TNS for Linux: Version 11.2.0.2.0 - Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.2.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.2.0 - Production
Time: 09-DEC-2011 14:11:21
Tracing not turned on.
Tns error struct:
ns main err code: 12599

TNS-12599: TNS:cryptographic checksum mismatch
ns secondary err code: 2526
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Fri Dec 09 14:19:45 2011

Cause:
This is due to Bug 9871805. The Enterprise Manager OMS connects to the 11gR1 and 11gR2 Repository database
using 10.2 JDBC Thin connection and AES256 encryption algorithm.
The 11gR1 and 11gR2 database does not support client JDBC connections using AES encryption, hence the TNS-
12599: TNS:cryptographic checksum mismatch

Solution:
To Workaround the issue please do the following:
On Repository database side, set the parameter
SQLNET.ENCRYPTION_TYPES_SERVER= (3DES168)
in sqlnet.ora file

11g New feature: listagg




SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> select department_id dept,first_name
2 from employees
3 where department_id<50
4 order by department_id,first_name;

DEPT FIRST_NAME
---------- --------------------------------------------------
10 Jennifer
20 Michael
20 Pat
30 Alexander
30 Den
30 Guy
30 Karen
30 Shelli
30 Sigal
40 Susan

10 rows selected.


SQL> select department_id dept,listagg(first_name,';') within group (order by first_name) first_name
2 from employees
3 where department_id<50
4 group by department_id
5 order by department_id;

DEPT FIRST_NAME
---------- --------------------------------------------------
10 Jennifer
20 Michael;Pat
30 Alexander;Den;Guy;Karen;Shelli;Sigal
40 Susan