all things dba: oracle

Posts tonen met het label oracle. Alle posts tonen

vrijdag 7 november 2014

"ORA-29024: Certificate validation failure" when calling https-site with utl_http

Problem when using UTL_HTTP for HTTPS-sites

When using the UTL_HTTP-package for accessing https-sites you might get the error

ORA-29024: Certificate validation failure – message

SQL> select utl_http.request ('https://www.ssllabs.com/ssltest');
select utl_http.request ('https://www.ssllabs.com/ssltest') from dual
*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1722
ORA-29024: Certificate validation failure
ORA-06512: at line 1

To avoid this you need to configure an Oracle Wallet :

Retrieve the certificate from the site. In Chrome click on the lock-icon :

A popup-screen will appear. Click on the Connection-tab and then on ‘Certificate information’

On the certificate-screen select the Details-tab and click on the ‘Copy to File…’ button

The ‘Certificate Export Wizard’ will start up. Click ‘Next’

Select ‘Cryptographic Message Syntax .. – PKCS #7’. The format might depend on the site you are accessing.

Select where to store the certificate and click ‘Next’.

Copy the certificate-file to the server hosting the Oracle database.

Next we need to create a wallet :

orapki wallet create -wallet /u01/app/oracle/admin/DB1/wallet -pwd Password -auto_login

and add the certificate to the wallet :

oracle [ /u01/app/oracle/admin/DB1/wallet ]$ orapki wallet add -wallet /u01/app/oracle/admin/DB1/wallet -trusted_cert -cert /tmp/ssllabs.p7b -pwd Password

Oracle PKI Tool : Version 11.2.0.3.0 - Production

oracle [ /u01/app/oracle/admin/DB1/wallet ]$

Now we can test if the we can access the site :

SQL> select utl_http.request ('https://www.ssllabs.com/ssltest',NULL,'file:/u01/app/oracle/admin/DB1/wallet','Password') from dual;

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"

"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html>

<head>

<title>Qualys SSL Labs - Projects / SSL Server Test</title>

…

SQL>

And it works :-)

zaterdag 25 oktober 2014

Configure hugepages on linux for Oracle

Introduction

Due to some performance issues with our Oracle RAC 11gR2 cluster running on OUL 5 we decided to increase the physical memory of both nodes in our cluster from 24G to 96G each. The memory of our main production database was also increase from 14G to 48G per instance. Since we did not want to run into issues with regards to memory pages (default 4KB) we also introduced hugepages.

Steps

1 – After installing the memory in the server and rebooting check if all memory is visible from the OS

root [ ~ ]# dmidecode -t 17 | grep Size

Size: 16384 MB

Size: No Module Installed

2 – Next we need to figure out how much HugePages we need to reserve. To do this we have to check what the size is of a Hugepage. This is by default 2048 kB :

oracle [ ~ ]$ grep Hugepagesize /proc/meminfo

Hugepagesize: 2048 kB

oracle [ ~ ]$

We want to assign 48 Gig to our Oracle instance PRODDB2

ð (48G * 1024) * 1024 è 50331648 (size in kB)

ð 50331648 / 2048 è 24576 (minimum nr of hugepages we need to configure)

The number of hugepages the os needs to reserve has to be configured in /etc/sysctl.conf

# HugePages

vm.nr_hugepages=24580

We configured 4 more hugepages then required….just in case

Next reload the /etc/sysctl.conf file using sysctl -p

sysctl –p

Check if the amount of hugepages is actually configured :

root [ ~ ]# grep Huge /proc/meminfo

HugePages_Total: 24580

HugePages_Free: 24580

HugePages_Rsvd: 0

Hugepagesize: 2048 kB

That's it ...now we can use HugePages.

donderdag 23 oktober 2014

How to create a physical standby database

Assumptions

This document has been tested on OUL 5 with Oracle 11.2.0.2 and 11.2.0.3
The Primary database PRIMARY_DB is in archivelog-mode and force-logging is enabled. This database is running on node1.
The Physical standby database is STANDBY_DB and runs on node2

Configuration

On Primary database

Set following parameters :

ALTER SYSTEM SET log_archive_config='DG_CONFIG=(PRIMARY_DB,STANDBY_DB)';

ALTER SYSTEM SET log_archive_dest_1='LOCATION=+ORA_FRA_DG1 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIMARY_DB';

ALTER SYSTEM SET log_archive_dest_2='SERVICE=STANDBY_DB NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY_DB' SCOPE=MEMORY;

ALTER SYSTEM SET log_archive_dest_state_1='ENABLE';

ALTER SYSTEM SET fal_server='STANDBY_DB';

ALTER SYSTEM SET fal_client='PRIMARY_DB';

ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=MEMORY;

Make sure that all required entries are present in the tnsnames.ora file on BOTH servers :

PRIMARY_DB =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (COMMUNITY = tcp.world)(PROTOCOL = TCP)(Host = node1)(Port = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = PRIMARY_DB)

    )

  )

STANDBY_DB =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (COMMUNITY = tcp.world)(PROTOCOL = TCP)(Host = node2)(Port = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = STANDBY_DB )

    )

  )

Check connectivity using tnsping. Also make sure that the the tnsnames in the GRID-home has all required entries.

Put following entries in the listener.ora and restart the listener. The listener.ora on standby_server should have following 2 blocks. The second block is required for the dgmrl :

(SID_DESC =

 (GLOBAL_DBNAME = STANDBY_DB )

 (ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/db_1)

 (SID_NAME = FLEETTST_STBY)

)

(SID_DESC =

 (GLOBAL_DBNAME = STANDBY_DB_DGMGRL)

 (ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/db_1)

 (SID_NAME = FLEETTST_STBY)

)

Reload the listener on the standby server

[oracle@node2 admin]$ su - grid

Password:

[grid@node2 ~]$ . oraenv

ORACLE_SID = [grid] ? +ASM

The Oracle base has been set to /u01/app/oracle

[grid@node2 ~]$ lsnrctl reload listener

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 14-MAR-2014 15:42:39

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbnode5)(PORT=1521)))

The command completed successfully

[grid@node2 ~]$

Copy the password-file from the source database to the standby-database and rename the file to reflect the SID of the standby database :

On the primary_server

scp -p orapwPRIMARY_DB node2:/u01/app/oracle/product/11.2.0.2/db_1/dbs

On the standby_server

mv orapwPRIMARY_DB orapwSTANDBY_DB

create an initial init.ora file for the standby database with the following content. Make sure that the DB_BLOCK_SIZE is the same as on the primary-database :

DB_NAME=PRIMARY_DB

DB_UNIQUE_NAME=STANDBY_DB

DB_BLOCK_SIZE=8192

And startup the database in nomount mode :

SQL> startup nomount pfile=’?/dbs/initSTANDBY_DB.ora’

Create the standby database using rman :

rman target sys/<Password>@PRIMARY_DB auxiliary sys/<Password>@STANDBY_DB

rman

run {

allocate channel prmy1 type disk;

allocate channel prmy2 type disk;

allocate channel prmy3 type disk;

allocate channel prmy4 type disk;

allocate auxiliary channel stby type disk;

duplicate target database for standby from active database

spfile

  parameter_value_convert 'PRIMARY_DB','STANDBY_DB'

  set db_create_file_dest='+ORA_DATA_DG_DG1'

  set db_create_online_log_dest_1='+ORA_REDO_DG_DG1'

  set db_create_online_log_dest_2='+ORA_FRA_DG_DG1'

  set db_recovery_file_dest='+ORA_FRA_DG_DG1'

  set db_unique_name='STANDBY_DB'

  set db_file_name_convert='+ORA_DATA_DG1','+ORA_DATA_DG_DG1'

  set log_file_name_convert='+ORA_REDO_DG1','+ORA_REDO_DG_DG1'

  set control_files='+ORA_DATA_DG_DG1','+ORA_FRA_DG_DG1'

  set log_archive_max_processes='5'

  set fal_client='STANDBY_DB'

  set fal_server='PRIMARY_DB'

  set standby_file_management='AUTO'

  set log_archive_config='dg_config=(PRIMARY_DB,STANDBY_DB)'

  set log_archive_dest_1='LOCATION=+ORA_FRA_DG_DG1 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STANDBY_DB'

  set log_archive_dest_2='service=PRIMARY_DB ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=PRIMARY_DB'

;

}

Example used to create rcat_stby :

run {

allocate channel prmy1 type disk;

allocate channel prmy2 type disk;

allocate channel prmy3 type disk;

allocate channel prmy4 type disk;

allocate auxiliary channel stby type disk;

duplicate target database for standby from active database

spfile

  parameter_value_convert 'RCAT','RCAT_STBY'

  set db_create_file_dest='+ORA_DATA_DG_DG1'

  set db_recovery_file_dest='+ORA_FRA_DG_DG1'

  set db_unique_name='RCAT_STBY'

  set db_file_name_convert='+ORA_DATA_DG1','+ORA_DATA_DG_DG1'

  set log_file_name_convert='+ORA_REDO_DG1','+ORA_REDO_DG_DG1','+ORA_FRA_DG1','+ORA_FRA_DG_DG1'

  set log_archive_max_processes='5'

  set fal_client='RCAT_STBY'

  set fal_server='RCAT'

  set standby_file_management='AUTO'

  set log_archive_config='dg_config=(RCAT,RCAT_STBY)'

  set log_archive_dest_1='LOCATION=+ORA_FRA_DG_DG1 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=RCAT_STBY'

  set log_archive_dest_2='service=RCAT ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=RCAT'

  set control_files='+ORA_DATA_DG_DG1'

  set db_create_online_log_dest_1='+ORA_REDO_DG_DG1'

  set db_create_online_log_dest_2='+ORA_FRA_DG_DG1' 

;

}

When the above is finished the standby database should be created. Last step is to start up the apply process :

SQL>alter database recover managed standby database disconnect from session;

Configuration and usage of dgmgrl

When first starting dgmgrl and creating the configuration you will get the following error :

oracle [ /u01/app/oracle/product/11.2.0.3/dbhome_1/dbs ]$ dgmgrl

DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/<Password>

Connected.

DGMGRL> show configuration

Error:

ORA-16525: the Data Guard broker is not yet available

Configuration details cannot be determined by DGMGRL

DGMGRL> create configuration 'PRIMARY_DB_DR' as

> primary database is PRIMARY_DB

> connect identifier is PRIMARY_DB;

Error:

ORA-16525: the Data Guard broker is not yet available

ORA-06512: at "SYS.DBMS_DRS", line 157

ORA-06512: at line 1

DGMGRL> exit

This is because dgmgrl should be activated on both primary and standby database :

oracle [ /u01/app/oracle/product/11.2.0.3/dbhome_1/dbs ]$ sqlplus / as sysdba

SQL> show parameter broker

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

dg_broker_config_file1               string      /u01/app/oracle/product/11.2.0

                                                 .3/dbhome_1/dbs/dr1PRIMARY_DB.da

                                                 t

dg_broker_config_file2               string      /u01/app/oracle/product/11.2.0

                                                 .3/dbhome_1/dbs/dr2PRIMARY_DB.da

                                                 t

dg_broker_start                      boolean     FALSE

SQL> alter system set dg_broker_start=true;

System altered.

SQL>

Next you will be able to create the initial configuration :

oracle [ /u01/app/oracle/product/11.2.0.3/dbhome_1/dbs ]$ dgmgrl

DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/<Password>

Connected.

DGMGRL> show configuration

ORA-16532: Data Guard broker configuration does not exist

Configuration details cannot be determined by DGMGRL

DGMGRL> create configuration 'PRIMARY_DB_DR'

> as

> primary database is 'PRIMARY_DB'

> connect identifier is 'PRIMARY_DB';

Configuration "PRIMARY_DB_DR" created with primary database "PRIMARY_DB"

DGMGRL> show configuration

Configuration - PRIMARY_DB_DR

  Protection Mode: MaxPerformance

  Databases:

    PRIMARY_DB - Primary database

Fast-Start Failover: DISABLED

Configuration Status:

DISABLED

DGMGRL> add database 'STANDBY_DB'

> as

> connect identifier is 'STANDBY_DB';

Database "STANDBY_DB" added

DGMGRL> show configuration

Configuration - PRIMARY_DB_DR

  Protection Mode: MaxPerformance

  Databases:

    PRIMARY_DB - Primary database

    STANDBY_DB - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

DISABLED

DGMGRL> enable configuration;

Enabled.

DGMGRL> show configuration

Configuration - PRIMARY_DB_DR

  Protection Mode: MaxPerformance

  Databases:

    PRIMARY_DB - Primary database

    STANDBY_DB - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

DGMGRL> show database PRIMARY_DB

Object "primary_db" was not found

DGMGRL> show database 'PRIMARY_DB'

Database - PRIMARY_DB

  Role:            PRIMARY

  Intended State:  TRANSPORT-ON

  Instance(s):

    PRIMARY_DB

Database Status:

SUCCESS

DGMGRL> show database 'STANDBY_DB';

Database - STANDBY_DB

  Role:            PHYSICAL STANDBY

  Intended State:  APPLY-ON

  Transport Lag:   0 seconds

  Apply Lag:       0 seconds

  Real Time Query: OFF

  Instance(s):

    STANDBY_DB

Database Status:

SUCCESS

Next you should be able to perform a switchover :

DGMGRL> switchover to 'PRIMARY_DB';

Performing switchover NOW, please wait...

New primary database "PRIMARY_DB" is opening...

Operation requires shutdown of instance "STANDBY_DB" on database "STANDBY_DB"

Shutting down instance "STANDBY_DB"...

ORACLE instance shut down.

Operation requires startup of instance "STANDBY_DB" on database "STANDBY_DB"

Starting instance "STANDBY_DB"...

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is "PRIMARY_DB"

DGMGRL> show configuration

Configuration - PRIMARY_DB_DR

  Protection Mode: MaxPerformance

  Databases:

    PRIMARY_DB      - Primary database

    FLEETTST_STBY - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

DGMGRL> exit

Known issues

DGMGRL> show configuration

Configuration - PRIMARY_DB_DR

  Protection Mode: MaxPerformance

  Databases:

    PRIMARY_DB    - Primary database

    STANDBY_DB    - Physical standby database

      Warning: ORA-16826: apply service state is inconsistent with the DelayMins property

Fast-Start Failover: DISABLED

Configuration Status:

WARNING

DGMGRL>

On the standby database issue the following :

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.

SQL>

Step by Step Guide on Creating Physical Standby Using RMAN DUPLICATE...FROM ACTIVE DATABASE (Doc ID 1075908.1)

all things dba

MenuGadget