Wednesday, December 3, 2014

User Managed backup User Managed Backup --- Oracle


User Managed Backup


STEP:1

1. Physical Backup

1. Cold Backup (Consistent Backup)
2. Hot Backup (Inconsistent Backup)

2. Logical Backup

1. Export / Import
2. Expdp /Impdp (available on oracle10g onwards)

Now we see the physical backup.


Cold Backup: (Consistent Backup)

A cold (or off-line) backup is a backup performed while the database is off-line and unavailable to its users. Cold backups can be taken regardless if the database is in ARCHIVELOG or NOARCHIVELOG mode.It is easier to restore from off-line backups as no recovery (from archived logs) would be required to make the database consistent. 

Why you say cold backup is consistent backup?


After shutdown the database we have taken the cold backup. During this time all datafile headers SCN are same. When we restore the cold backup, no redo is needed in case of recovery.We had taken backup datafile, logfile, controlfile, parameter file & password file.

Cold Backup Steps:

1. Get the file path information using below query

Select name from v$datafile;
select member from v$logfile;
select name from v$controlfile;

2.Taken the password file & parameter file backup ( Optional)
3.Alter system switch logfile;
4.Shutdown immediate
5.Copy all the data files /control files /log files using os command & placed in backup path.
6.Startup the database.

Hot Backup (Inconsistent backup)

A hot (or on-line) backup is a backup performed while the database is open and available for use (read and write activity). Except for Oracle exports, one can only do on-line backups when the database is ARCHIVELOG mode.

Why you say Hot backup is inconsistent backup?

While database running we have taken the hot backup. During this time backup tablespace datafile headers SCN are not same with another tablespace datafiles. When we restore the hot backup, redo is needed for recovery.

Hot Backup Steps:

1.Get the file path information using below query.
Select tablespace_name, file_name from dba_data_files order by 1;

2. Put the tablespace in begin backup mode Using os command to copy the datafiles belongs to begin backup mode tablespace & placed in backup path.
3.Once copied the datafile, release the tablespace from begin backup mode to end backup
4.Repeat the steps 1-3 for all your tablespaces.
5.Taken the controlfile backup

Alter Database backup controlfile to ‘/u01/backup/control01.ctl’; ---> à Binary format

Below one is readable format. (Used for cloning)

Alter Database backup controlfile to trace;
Alter database backup controlfile to trace as ‘/u01/backup/control01.ora’;

6.Backup all your archive log files between the previous backup and the new backup as well.
7.Taken the password file & parameter file backup ( Optional)

Example:
steps:

2.Alter tablespace system begin backup;
To ensure the begin backup mode tablespace using below query
Select * from v$backup; (refer the Change#, Time column)

3. Host cp /u01/oracle/raja/system1.DBF /u01/backup/system01.dbf à using os command to copy the datafile.
4. Alter tablespace system end backup;
To ensure the begin backup mode tablespace using below query
Select * from v$backup;


Hot Backup internal operations:

Note: While I am trying to write “during hot backup what are the internal operations going on?” I have referred several notes, but below one is very clear and nice one. 
Reference: http://knol.google.com/k/franck-pachot/oracle-begin-backupend-backup/17uabcrki6uux/3#

During backup mode, for each datafile in the tablespace, here is what happens:

1- When BEGIN BACKUP is issued:

·The hot backup flag in the datafile headers is set, so that the copy is identified to be a hot backup copy. This is to manage the backup consistency issue when the copy will be used for a recovery.
·A checkpoint is done for the tablespace, so that in case of recovery, no redo generated before that point will be applied. Begin backup command completes only when checkpoint is done.

2- During backup mode:
·The datafile header is frozen so that whenever it is copied, it reflects the checkpoint SCN that was at the beginning of the backup. Then, when the copy will be restored, Oracle knows that it needs to start recovery at that SCN to apply the archived redo logs. This is to avoid the header inconsistency issue.
That means that any further checkpoints do not update the datafile header SCN (but they do update a 'backup' SCN)
·Each first modification to a block in buffer cache will write the full block into the redo thread (in addition to the default behavior that writes only the change vector).
This is to avoid the fractured block issue. There may be a fractured block in the copy, but it will be overwritten during the recovery with the full block image.


That means that everything goes as normal except for two operations:
- at checkpoint the datafile header SCN is not updated
- when updating a block, the first time it is updated since it came in the buffer cache, the whole before image of the block is recorded in redo
- direct path writes do not go through the buffer cache, but they always write full blocks and then full block is written to redo log (if not in nologging)

3- When END BACKUP is issued:
·A record that marks the end of backup is written to the redo thread so that if the copy is restored and recovered, it cannot be recovered earlier than that point. This is to avoid the backup consistency issue.
·The hot backup flag in the datafile headers is unset.
·The header SCN is written with the current one.







STEP:2


User Managed Backup Terminology

(Operating system command are used to make backups when database is closed or open in this terminology)

Whole database backup refer to a backup of all data file, control file and log file of the database. Whole database backup can be perform when database open or closed.

The backup takes when database is closed called consistent backup. (Because database file header are consistent with the control file and when restore completely the database can be opened without any recovery.)

The backup takes when database is opened and operational called inconsistent backup. (Because database file header are not consistent with the control file.)

Important:

1. If Database is in No Archive log mode, We can not perform user managed hot backup / inconsistent backup

2. If Database is in No Archive log mode and we don’t want to change mode then we can only perform clod backup/consistent backup

3. If Database is in No Archive log mode, we can perform only Restore and not possible to recover database to the point of failure. Means, restore to the point of the last backup

4. If Database is in Archive log mode , then we can recover to the point of failure

How to know Database file location information

For Data File > select * from V$database
For Control File > select * from v$controlfile
For Log File > select * from v$logfile

How to Know all data files and there respective table space

SQL>
SELECT T.NAME TABLESPACE,F.NAME DATAFILE
FROM V$TABLESPACE T, V$DATAFILE F
WHERE T.TS# = F.TS#
ORADER BY T.NAME;

How to Make a consistent whole Database Backup

· Shutdown the database.
· Backup all data file, control file and log file by using an operating system command.
· We can also include password file and parameter file.
· Restart the oracle database/Instance.

How to make a inconsistent whole database backup

Requirement for inconstant database backup:

· The database is set to ARCHIVELOG mode.
· You ensure that the online redo logs are archived, either by enabling the Oracle automatic archiving (ARCn) process.

Making a Backup of an Online teblespace or Data file

· Set the datafile or tablespace is backup mode by issuing following command:

SQL> ALTER TABLESPACE BEGIN BACKUP;

(Note: This prevent the sequence number in the datafile header from changing.)

· Use an operating system backup utility to copy all database in the tablespace to backup storage.
Copy c:\datafile_path e:\datafilepath

· After the datafile of the tablespace have been backed up, set them into mode by issuing the following command:

· SQL> ALTER TABLESPACE END BACKUP;

· Repeat these steps for all tablespaces.

· Archive the unarchive redo logs;

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

· Manual Control File Backups

Creating a binary image: 

ALTER DATABASE BACKUP CONTROLFILE TO 'control.bak';

Creating a taxt trace file: 

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

· Backing Up the Initilization Parameter File

CREATE PFILE FROM SPFILE;( For Default location)
CREATE PFILE ='C:\BACKUP\INIT.ORA' FROM SPFILE;

Mechanism of Open database backup

When a datafile is placed in backup mode, more redo log entries may be generated because the log writer writes block image of changes block of the datafile in backup mode to the redo log instead of just the row information

How to check Backup Status Information (When performing open database) 

select * from v$backup; (view to determine which file are in backup mode, when alter tablespace begin backup command is issued the status change to ACTIVE.)

How to verify backup

Use to ensure that a backup database or datafile is valid before a restore.

$dbv file='path of file location' start=1 logfile='enter path for log file generation'

Backup Issue with Logging and nologging Option

Tablespace, table, index may be use to set to NOLOGGING mode for Faster load of data when using direct load operation like SQL LOADER. (Because the redo logs do not contain the values that were inserted when the table was in NOLOGGING mode)
 
STEP:3

Cold Backup in Oracle (User Managed Backup)



Here are the main topics for this article:




A user-managed backup is made by performing a physical copy of data files using the OS commands. These copies are moved to a separate location using OS commands. The user maintains a record of the backups. For the recovery operation we have to move back (or to the new location of the database) the files and perform the recovery.  

The user-managed backups could be take at the following levels:
  • Data file level
  • Tablespace level
  • Database level 


  • the database should be in ARCHIVELOG mode
  • put the tablespace in "Begin Backup" mode  (example:  ALTER TABLESPACE users BEGINBACKUP;  )
  • copy the physical files associated with this tablespace on another location using OS commands
  • put the tablespace in "End Backup" mode  (example:  ALTER TABLESPACE users ENDBACKUP;  )
  • Archive the unachieved redo logs so that the redo required to recover the tablespace backups is archived                                    (  SQL> ALTER SYSTEM ARCHIVE LOG CURRENT; )
  • Take a backup of all archived redo log files generated between Begin Backup and End Backup using OS commands
NOTES: 
  • Many tablespaces could be backed up in parallel. However, online redo logs can grow large if multiple users are updating these tablespaces because the redo must contain a copy of each changed data block. Oracle doesn't recommend this.
  • When a tablespace is in backup mode, Oracle will stop updating its file headers, but will continue to write to the data files. When in backup mode, Oracle will write complete changed blocks to the redo log files. Normally only deltas (change vectors) are logged to the redo logs. This is done to enable reconstruction of a block if only half of it was backed up (split blocks). Because of this, one should notice increased log activity and archiving during on-line backups. To fix this problem, simply switch to RMAN backups.
  • If the tablespace is in READ ONLY mode, we don't need to put the tablespace in Backup Mode. 



    SELECT name FROM v$datafile;
    SELECT member FROM v$logfile;
    SELECT name FROM v$controlfile;

    To view which file correspond to which tablespace you can run:
 SELECT t.NAME "Tablespace", f.NAME "Datafile"
 FROM       V$TABLESPACE t,
                    V$DATAFILE f
 WHERE    t.TS# = f.TS#
 ORDER BY t.NAME; 


  • the database should be in ARCHIVELOG mode
  • put the datafile in OFFLINE mode  (example:  ALTER DATABASE DATAFILE'C:\oradata\file1.dbf' OFFLINE;  )
  • copy the physical file on another location using OS commands
  • put the datafile in ONLINE mode  (example:  ALTER DATABASE DATAFILE 'C:\oradata\file1.dbf'ONLINE;  )
  • backup the control file as the database has gone through structural changes 


This is done with SQL> RECOVER TABLESPACE command. It is possible to perform a tablespace recovery while the rest of the database is online. 

The prerequisites for a tablespace recovery are:
  • The tablespace must be OFFLINE (the database could be online)
  • Only COMPLETE recovery is possible
  • SYSTEM tablespace never can be recovered because is online all the time.


This is done with SQL> RECOVER DATAFILE command. It is possible to perform a data file  recovery while the rest of the database is online or offline. 

The prerequisites for a data file recovery are:
  • The data file must be OFFLINE (the database could be online)
  • SYSTEM data files never can be recovered because SYSTEM tablespace is online all the time.


If the database is down (shutdown IMMEDIATE, NORMAL) was used, we have only to copy the data files, redo log files and control files to a new location. This kind of backup is used for a database in NOARCHIVELOG which is not used for a 24x7 business. 



An Open Database Backup is a backup taken when the database is up and running. This is done by putting the tablespace in Backup mode and copying the data files and control files. All the latest archived log files must be copied as well. The V$BACKUP and V$DATAFILE_HEADER should be queried after the database backup to see if all the data files are in online mode.   



Backup the binary file:  
ALTER DATABASE BACKUP CONTROLFILE TO 'C:\backups\control1.bkp';

Generate the script to recreate the control file:
 ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS 'C:\backups\control1.txt';
or
 ALTER DATABASE BACKUP CONTROLFILE TO TRACE      ( the file will be created in USER_DUMP_DEST )



An Online Backup failure can occurs if:
  • An instance failure occurs
  • An OS failure occurs
  • The database is accidentally shut down.
The database could be stopped by using shutdown abort. See the picture below:

Cold Backup in Oracle

When the database is brought up a media recovery is needed and the database will be in mount state:

Cold Backup in Oracle

In mount state we can query the files to see what is happening (optional):

Cold Backup in Oracle

In Oracle 9i and + we can use RECOVER DATABASE to do an automatic recovery (the database will be consistent again and the tablespace will not be in Backup mode). After this recovery a new backup could be taken. 

Cold Backup in Oracle


To see the status of the files during the online user-managed backups the following select could be used:
       SELECT decode(b.status, 'ACTIVE', 'BACKUP_IS_RUNNING',b.status) "Backup_Status",
                     b.time  "Backup_Start_Time",
                     fh.status "File status",
                     fh.tablespace_name "Tablespace_Name",
                     fh.name "File_Name"
       FROM v$backup b,
                  v$datafile_header fh
       WHERE b.file#=fh.file#;

Posted by at 1 comment:

Tuesday, November 25, 2014

How to configure Oracle RMAN backup for the first time


Oracle 11g Logo
RMAN is a oracle utility to backup, restore & recovery of database.
The following Steps will be demonstrated the configuration of oracle RMAN backup (for first time configuration)
Lets assume the database is in NOARCHIVELOG mode, by default the database is in NOARCHIVELOG mode, we need to change it to ARCHIVELOG mode for RMAN backup configuration.
We can configure RMAN backup with catalog/repository database as well as control file. It is strongly recommended & very good practice to configure RMAN backup with catalog/repository database.
catalog/repository database: It’s central repository & it requires separate database for backup operation. All registered target databases information stored in catalog database.
Control file: It contains registered target database information at server level itself & RMAN utility directly connects to target database by command “RMAN target /”
Note: Create catalog/repository database with the help of DBCA.
Lets consider following Step by Step syntax to do so:
Step # 1: Connect to Target database(Target DB: The database on which Backup & Recovery to be performed) as sysdba.
[oracle@centos ~]$ sqlplus "/ as sysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 3 11:28:24 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
Step # 2: Ensure the database has been configured with ARCHIVELOG mode or not?
SQL> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG
Database is in NOARCHIVELOG mode.
Step # 3: If the database has been configured with ARCHIVELOG mode then skip the Step number 3 to 6, If not then Shutdown the database.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Step # 4: Startup the database in mount state.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 308981760 bytes
Fixed Size 2212896 bytes
Variable Size 163580896 bytes
Database Buffers 138412032 bytes
Redo Buffers 4775936 bytes
Database mounted.
Step # 5: Configure database in ARCHIVELOG mode.
SQL> alter database archivelog;
Database altered.
Step # 6: Alter database to open state.
SQL> alter database open;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
Step # 7: Ensure ARCHIVELOG destination.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2
Next log sequence to archive 4
Current log sequence 4
In case you wish to change default archive log destination then issue the following command.
SQL> alter system set log_archive_dest_1='location=/home/oracle/arch' scope=both;
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/arch
Oldest online log sequence 2
Next log sequence to archive 4
Current log sequence 4
Step # 8: Ensure the flash/fast recovery area location.
SQL> show parameter db_recovery_file_dest
Step # 9: Connect to RMAN prompt with target database.
[oracle@centos ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 3 11:46:22 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1363580714)
RMAN>
Step # 10: Configure RMAN with controlfile auto-backup feature that will be auto-backup controlfile in case of major changes done in database.
RMAN> configure controlfile autobackup on;
Step # 11: To enable backup optimization run the following command, by default backup optimization has been configured OFF.
RMAN> configure backup optimization on;
Step # 12: Configure retention policy for backup.
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
Step # 13: Connect to the recovery catalog database(RMAN Repository) & Create a tablespace to store RMAN catalog database objects.
[oracle@centos ~]$ sqlplus "/ as sysdba"
SQL> select global_name from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
CATALOGD
SQL> create tablespace catalogtbs datafile '/home/oracle/dbfile/catalogtbs1.dbf' size 100M autoextend on maxsize unlimited;
Tablespace created.
Step # 14: Create a RMAN user, assign RMAN tablespace to RMAN user as a default & grant recovery catalog owner,connect & resource privileges to RMAN user.
SQL> create user recoveryman identified by recoveryman;
User created.
SQL> alter user recoveryman default tablespace catalogtbs temporary tablespace temp;
User altered.
SQL> grant recovery_catalog_owner to recoveryman;
Grant succeeded.
SQL> grant connect,resource to recoveryman;
Grant succeeded.
Step # 15: Connect to RMAN on target and recovery catalog database.
[oracle@oracle ~]$ rman target / catalog recoveryman/recoveryman@catalogdb
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jan 4 14:30:28 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1363580714)
connected to recovery catalog database
Step # 16: create catalog by issuing the following command in RMAN prompt.
RMAN> create catalog;
recovery catalog created
Step # 17: After creating catalog, Ensure RMAN repository tables by logging into repository database as RMAN user. This is only for the first time.
[oracle@oracle ~]$ sqlplus "recoveryman/recoveryman@catalogdb"
SQL> show user;
USER is "RECOVERYMAN"
SQL> select table_name from user_tables;
Step # 18: Register database with recovery catalog.
RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
Step # 19: Check whether registration was successful.
RMAN> report schema;
Report of database schema for database with db_unique_name ORCL
List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    670      SYSTEM               YES     /home/oracle/app/oracle/oradata/orcl/system01.dbf
2    490      SYSAUX               NO      /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
3    30       UNDOTBS1             YES     /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
4    5        USERS                NO      /home/oracle/app/oracle/oradata/orcl/users01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace  Maxsize(MB) Tempfile Name
---- -------- ----------- ---------   -------------------------------
1    20       TEMP        32767       /home/oracle/app/oracle/oradata/orcl/temp01.dbf
OR
RMAN> LIST INCARNATION OF DATABASE;
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS Reset SCN    Reset Time
------- ------- -------- ---------------- ------------ ---    --------
89      102     ORCL     1363580714       PARENT       1      15-AUG-09
89      90      ORCL     1363580714       CURRENT      945184 02-JAN-14
Target database is registered with the RMAN.
Now you can backup your target(registered) database as per your convenience.
***********************************************************************
Posted by at 1 comment:

Monday, November 24, 2014

hotbackup script

#!/bin/bash

.  /u01/PROD/oraprod/db/tech_st/11.2.0/PROD_abr-ln-orc2.env
NOWDATE=`date +%d%b%y`
sqlplus '/ as sysdba' <
alter system switch logfile;

alter database begin backup;

exit;
eof

cd /u01/PROD/oraprod/db/

tar cvf - apps_st | gzip -c > /u01/PROD/oraprod/db/prod_data_top_hot_$NOWDATE.tgz

sqlplus '/ as sysdba' <
alter database end backup;

ALTER SYSTEM ARCHIVE LOG CURRENT;

ALTER DATABASE BACKUP CONTROLFILE TO TRACE as '/u01/PROD/oraprod/db/$NOWDATE.ctl';

exit;
eof
~

Posted by at 1 comment:

Friday, November 21, 2014

How to set DB Schema password to 'never expire'


As a Hyperion admin, I have time and again run into this issue with my hyperion schemas where the password sometimes expires and wreaks havoc on the application.

The key indicator for these error messages are the log files. They are quite descriptive .

Basic test to make sure that the schema is working correctly will be to login to the DB hosts and issue this command:

conn username/password;

To resolve this issue ,

connect to your DB as sysdba and run

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;


Restart all Services and ensure all the log files under diagnostics\logs\services directory startup properly.

This should resolve the issue.
Posted by at 3 comments:

Thursday, November 20, 2014

Hyperion Financial Management Error "Bad Gateway. Request could not be processed. Invalid response received by proxy or gateway server" When Browsing Content Within an Application

Posted by at 1 comment:

Tuesday, November 11, 2014

SSL-Enable Oracle Database

 SSL-Enable Oracle Database

Take these steps to SSL-enable Oracle database:
  1. Create a root CA and a certificate for the DB. Here is an example:
    Note:
    Self-signed certificates are not recommended for production use. For information about obtain production wallets, see Section 8.4.8.3, "Changing a Self-Signed Wallet to a Third-Party Wallet.".
    mkdir root
mkdir server
 
# Create root wallet, add self-signed certificate and export
orapki wallet create -wallet ./root -pwd password
orapki wallet add -wallet ./root -dn CN=root_test,C=US -keysize 2048 -self_signed -validity 3650 -pwd password
orapki wallet display -wallet ./root -pwd password
orapki wallet export -wallet ./root -dn CN=root_test,C=US -cert ./root/b64certificate.txt -pwd password
 
#Create server wallet, add self-signed certificate and export
orapki wallet create -wallet ./server -pwd password
orapki wallet add -wallet ./server -dn CN=server_test,C=US -keysize 2048 -pwd password
orapki wallet display -wallet ./server -pwd password
orapki wallet export -wallet ./server -dn CN=server_test,C=US -request ./server/creq.txt -pwd password
 
# Import trusted certificates
orapki cert create -wallet ./root -request ./server/creq.txt -cert ./server/cert.txt -validity 3650 -pwd password
orapki cert display -cert ./server/cert.txt -complete
orapki wallet add -wallet ./server -trusted_cert -cert ./root/b64certificate.txt -pwd password
orapki wallet add -wallet ./server -user_cert -cert ./server/cert.txt -pwd password
orapki wallet create -wallet ./server -auto_login -pwd password}}
  2. Update listener.ora, sqlnet.ora, and tnsnames.ora for the database.
    1. This example shows the default listener.ora:
      SID_LIST_LISTENER =
(SID_LIST =(SID_DESC =(SID_NAME = PLSExtProc)(ORACLE_HOME = /path_to_O_H)(PROGRAM = extproc)))
LISTENER =(DESCRIPTION_LIST =(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = mynode.mycorp.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCPS)(HOST = mynode.mycorp.com)(PORT = 2490))
))
 
WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/wallet_location)))
 
SSL_CLIENT_AUTHENTICATION=FALSE}}
      And here is an updated listener.ora file, illustrating a scenario with no client authentication:
      SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = dbname)
      (ORACLE_HOME = /path_to_O_H)
      (SID_NAME = sid)
    )
  )
 
SSL_CLIENT_AUTHENTICATION = FALSE
 
WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /wallet_path)
    )
  )
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = mynode.mycorp.com)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCPS)(HOST = mycorp.com)(PORT = 2490))
    )
  )
      Note that the SSL port has been added.
    2. Likewise, a modified sqlnet.ora file may look like this:
      NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
SQLNET.AUTHENTICATION_SERVICES=(BEQ,TCPS,NTS)
WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/directory)))
SSL_CLIENT_AUTHENTICATION=FALSE
    3. A modified tnsnames.ora file may look like this:
      OID =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = mynode.mycorp.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mynode.mycorp.com)
    )
  )
   
SSL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCPS)(HOST = mynode.mycorp.com)(PORT = 2490))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = mynode.mycorp.com)
    )
    (SECURITY=(SSL_SERVER_CERT_DN=\"CN=server_test,C=US\"))
  )
  3. Test the connection to the database using the new connect string. For example:
    $ tnsping ssl
$ sqlplus username/password@ssl
Posted by at 1 comment:
Subscribe to: Posts (Atom)