AMAZON LINK for 11g New Features for DBA

OCP Oracle Database 11g: New Features for Administrators Exam Guide (Exam 1Z0-050): OCP Oracle Database 11g: New Features for Administrators Exam Guide (Exam 1Z0-XXX) (Osborne ORACLE Press Series)

Oracle 11g or 11gR2 Tablespace Encryption

Oracle 11g (11.2.0.1) Configure Tablespace Encryption using Safenet Hardware Security Module (LUNA SA 4.4.0 HSM)

Note: Step 1 to 4 need repeating on all the nodes in the cluster

Step 5 and 6 don't need repeat for RAC

Example : DATABASE SERVER Hostname is dbserver01
: HSM Device name is hsm01


1. Install HSM Client software on database Server dbserver01:


2. Register Oracle Database server (client) with HSM.

login as root on dbserver01:

su - root
password: *******

[root@dbserver01 bin]# cd /usr/lunasa/bin

-- Fetch server parameter from hsm01 to database server.

[root@dbserver01 bin]# /ctp admin@hsm01.domain.local:server.pem .

--- Verify Cerfiticate
[root@dbserver01 bin]# openssl x509 -in server.pem -noout -text

--- Register Hsm01 as authorised HSM Server.

[root@dbserver01 bin]# ./vtl addServer -n hsm01 -c server.pem

New server hsm01 successfully added to server list.

-- Verify Server added

[root@dbserver01 bin]# ./vtl listServers
Server: hsm01


-- Create HSM Client certificate from the Database Server

[root@dbserver01 bin]# ./vtl createCert -n dbserver01
Private Key created and written to: /usr/lunasa/cert/client/dbserver01Key.pem
Certificate created and written to: /usr/lunasa/cert/client/dbserver01.pem


--- upload client certificate to HSM

[root@dbserver01 bin]# ./ctp /usr/lunasa/cert/client/dbserver01.pem admin@hsm01.domain.local:
admin@hsm01.domain.local's password:
dbserver01.pem 100% 1172 1.1KB/s 00:00


---- Login to HSM Device via SSH or Local console

ssh admin@hsm01.domain.local
password: ***********

----- Register dbserver01 to HSM as client

[hsm01] lunash:>client reg -c dbserver01 -h dbserver01

'client register' successful.

Command Result : 0 (Success)


--- Assing HSM partition to dbserver01(client)

[hsm01] lunash:>client a -p Oracle -c dbserver01

'client assignPartition' successful.

Command Result : 0 (Success)


3. Verify that Oracle Database server can talk to HSM:


[root@gridcluster03 bin]# ./vtl verify

The following Luna SA Slots/Partitions were found:

Slot Serial # Label
==== ======== =====
1 951962001 Oracle

---- You have Successfully Added Database server to HSM as client and Now Oracle Server is ready to use HSM as wallet to Store Tablespace and Column Master keys.



4. Configure Oracle to use HSM.

--- copy the HSM Libarary to Oracle required location:

[root@dbserver01 bin]# mkdir -p /opt/oracle/extapi/64/hsm/safenet/4.4.0/

[root@dbserver01 bin]#cp /usr/lunasa/lib/libshim.so /opt/oracle/extapi/64/hsm/safenet/4.4.0/libshim.so

[root@dbserver01 bin]#chown -R oracle:dba /opt/oracle


--- Login as oracle and update sqlnet.ora

[root@dbserver01 bin]# su - oracle

[oracle@dbserver01 ]$ vi $ORACLE_HOME/network/admin/sqlnet.ora


Add the following line
------------------------------
ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = HSM))


5. Enable Tablespace Encryption in database by createing the Master key in HSM wallet.


[oracle@dbserver01 ]$ sqlpus "/ as sysdba"


----- Create wallet in HSM Use this command once only to create the master key. Every time you bounce database you need to open the wallet.

alter system set encryption key identified by "hsmpartition_password";



---- Create tablespace:

CREATE TABLESPACE TDE_TEST01 DATAFILE '+DATA' SIZE 10m ENCRYPTION using 'AES256' DEFAULT STORAGE (ENCRYPT);




6. Every Time you open start database open the wallet by following command.

alter system set encryption wallet open identified by "hsmpartition_password";


Note: Step 1 to 4 need repeating on all the nodes in the cluster

Flashback Database

SQL> alter system set db_recovery_file_dest='+BACKUPDG' scope=both sid='*';

SQL> alter system set db_recovery_file_dest_size=5000M scope=both sid='*';

SQL> alter system set db_flashback_retention_target= 1440 scope=both sid='*';

--- Verify the parameter setup .

COLUMN NAME FORMAT A40
COLUMN VALUE FORMAT A50

SELECT INST_ID,NAME,VALUE FROM gv$PARAMETER WHERE NAME IN ('db_recovery_file_dest','db_recovery_file_dest_size','db_flashback_retention_target')
/

INST_ID NAME VALUE
---------- ---------------------------------------- --------------------------------------------------
1 db_recovery_file_dest +BACKUPDG
1 db_recovery_file_dest_size 5218762752
1 db_flashback_retention_target 1440
2 db_recovery_file_dest +BACKUPDG
2 db_recovery_file_dest_size 5218762752
2 db_flashback_retention_target 1440

6 rows selected.

-- Shutdown database and startup mount;

SQL> shutdown immediate;
SQL> startup mount;
Database mounted.

--- Ensure database is in archive log mode.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 71
Next log sequence to archive 0
Current log sequence 72

---- Turn the Flashback on;

SQL> alter database flashback on;

-- Verify Flashback in Turned on .

SQL> SELECT FLASHBACK_ON FROM V$DATABASE;

FLASHBACK_ON
------------------
YES

SQL> alter database open;

SQL> CREATE RESTORE POINT BEFORE_PATCH;

Restore point created.

---- Flashback Related Views :

SELECT OLDEST_FLASHBACK_SCN,OLDEST_FLASHBACK_TIME ,FLASHBACK_SIZE FROM v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FL FLASHBACK_SIZE
-------------------- --------- --------------
859379 10-DEC-09 159416320


SQL> SELECT SCN,NAME ,TIME FROM V$RESTORE_POINT;

SCN NAME TIME
---------- ---------------------------------------- ---------------------------------------------------------------------------
976812 BEFORE_PATCH 11-DEC-09 03.54.59.000000000 PM


COLUMN NAME FORMAT A50
1* SELECT NAME,LOG#,BYTES,FIRST_TIME FROM V$FLASHBACK_DATABASE_LOGFILE
SQL> /

NAME LOG# BYTES FIRST_TIM
-------------------------------------------------- ---------- ---------- ---------
+BACKUPDG/armynet/flashback/log_1.367.705266421 1 15941632 10-DEC-09
+BACKUPDG/armynet/flashback/log_2.365.705266477 2 15941632 10-DEC-09
+BACKUPDG/armynet/flashback/log_3.361.705276043 3 15941632 10-DEC-09
+BACKUPDG/armynet/flashback/log_4.360.705286825 4 15941632 11-DEC-09
+BACKUPDG/armynet/flashback/log_5.358.705286845 5 15941632 11-DEC-09
+BACKUPDG/armynet/flashback/log_6.357.705301241 6 15941632 11-DEC-09
+BACKUPDG/armynet/flashback/log_7.356.705304845 7 15941632 11-DEC-09
+BACKUPDG/armynet/flashback/log_8.354.705315645 8 15941632 11-DEC-09
+BACKUPDG/armynet/flashback/log_9.349.705322797 9 15941632 11-DEC-09
+BACKUPDG/armynet/flashback/log_10.348.705330031 10 15941632 11-DEC-09

10 rows selected.



=======================

---- Flasback to scn:

FLASHBACK [STANDBY] DATABASE TO [BEFORE] SCN ;


---- Flasback to timestamp

FLASHBACK [STANDBY] DATABASE TO [BEFORE] TIMESTAMP ;


---- Flasback to restore_point
FLASHBACK [STANDBY] DATABASE TO [BEFORE] RESTORE POINT ;



Example of Flasback using Restore point (Standby database open read/write and convert back to standby)



CREATE RESTORE POINT before_open;

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

ALTER DATABASE OPEN;


--- DATABASE OPEN TO READ/WRITE OPERATION.


----TO CONVERT BACK TO STANDBY;


SHUTDOWN IMMEDIATE;

STARTUP MOUNT;
ALTER DATABASE FALSHBACK TO RESTORE POINT BEFORE_OPEN;

ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

ORA-28362: master key not found

ORA-28362: master key not found
Cause:
The required master key required could not be located. This may be casued by the use of an invalid or incorrect wallet.
Action: Check wallet location parameters to see if they specify the correct wallet. Also, verify that an SSO wallet is not being used when an encrypted wallet is intended.

Error Indicate that It doesn't found the current Master key. Effectively Command will create the New master key as the specified location(Either FILE or HSM. Any objects(TAblespace) created with OLD Master key will be unusable.

ORA-28386: "cannot perform a PKI certificate-based Master Key Rekey"
// *Cause: A PKI certificate-based Master Key Rekey was attempted when
// Tablespace Encryption or HSM was in use.
// *Action: Perform a symmetric Master Key Rekey or disable Tablespace
// Encryption for all tablespaces.

11gR2 (11.2.0.1) database don't support REKEY if MASTERKEY is stored in HSM(Hardware Secure module".

libdb.so.2: cannot open shared object file

====== GRID START ERROR

/u01/app/oracle/products/oms10g/Apache/Apache/bin/apachectl start: execing httpd
/u01/app/oracle/products/oms10g/Apache/Apache/bin/httpd: error while loading shared libraries: libdb.so.2: cannot open shared object file: No such file or di
rectory


Fix:

ln -s /usr/lib/libgdbm.so.2.0.0 /usr/lib/libdb.so.2

ORA-27154: post/wait create failed / +ASM1 instance startup failed

+ASM1 instance startup failed :

SQL> startup nomount;
ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semget failed with status: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpsemsper


Cause of the problem
The error may mislead you. Though it indicates No space left on device but whenever I issue df -h on my OS there is enough space. The problem happened because of short of semaphores setting in the OS.


Solution:

add the following line in /etc/sysctl.conf
kernel.sem = 256 32768 100 228

starting opmn failed (OEM) Oracle enterprise manager

==================
Enterprise manageer installation , starting opmn failed

create a symbolic link "ln -s /usr/lib/libgdbm.so.2.0.0 /usr/lib/libdb.so.2"

Remember that change the permissions in the libdb.so.2 file

chmod 755 /usr/lib/libgdbm.so.2.0.0
chmod 755 /usr/lib/libdb.so.2

Reexecute the configuration assitant

libnnz11.so: cannot restore segment prot after reloc: Permission denied

Issue :

Installation failed with library error: Oracle11g
libnnz11.so: cannot restore segment prot after reloc: Permission denied


Workaround : -

Change SELinux from Enforcing to permisible , until oracle fix the bug. Once oracle fixed the bug(hopefully) , you will be able to set to Enforcing.

Setup ntpd for 11g RAC / Clusterware

Linux  :
------
Edit the followin file :/etc/sysconfig/ntpd

Add -x to the current option as follows:

# Drop root to id 'ntp:ntp' by default.
OPTIONS="-x -u ntp:ntp -p /var/run/ntpd.pid"

# Set to 'yes' to sync hw clock after successful ntpdate
SYNC_HWCLOCK=no

# Additional options for ntpdate
NTPDATE_OPTIONS=""

-------------------------
HP-UX :

Add export XNTPD_ARGS="-x" to /etc/rc.config.d/netdaemons


Restart:
/sbin/init.d/xntpd stop


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

Sun Solaris :
/sbin/init.d/xntpd start

Setup ISCSI Client (Linux) for 11g RAC

Follow the following Steps to setup iscsi-initiater on linux server as client of openfier.

[root@oracluster03 /]# cd /media/*/Server
[root@oracluster03 Server]# rpm -i iscsi-init*
warning: iscsi-initiator-utils-6.2.0.868-0.18.el5.x86_64.rpm: Header V3 DSA sign ature: NOKEY, key ID 1e5e0159


[root@oracluster03 ~]# service iscsid start
Turning off network shutdown. Starting iSCSI daemon: [ OK ]
[ OK ]
[root@oracluster03 ~]# chkconfig iscsid on
[root@oracluster03 ~]# chkconfig iscsi on
[root@oracluster03 ~]# service iscsid status
iscsid (pid 22651 22650) is running...

[root@oracluster03 ~]#
[root@oracluster03 ~]#

--- cut here start -------


iscsiadm -m discovery -t sendtargets -p $STORAGE|awk '{print "iscsiadm -m node -T "$2"  -p $STORAGE -l"}'

----- cut here end ----------

Test your command and make sure you can see the expected storage.

# export STORAGE=192.168.1.200
# iscsiadm -m discovery -t sendtargets -p $STORAGE


To manually log in to an iSCSI target, use the following command
iscsiadm -m node -T proper_target_name -p target_IP -l

As you need to do this each disk, it can be achieved by using simple AWK command to generate for all disks.

iscsiadm -m discovery -t sendtargets -p $STORAGE|awk '{print "iscsiadm -m node -T "$2"  -p $STORAGE -l"}'

Now use the output to test your login to each disk.

iscsiadm -m node -T iqn.2006-01.com.openfiler:tsn.ocrdisk3  -p $STORAGE -l
iscsiadm -m node -T iqn.2006-01.com.openfiler:tsn.ocrdisk2  -p $STORAGE -l

iscsiadm -m node -T iqn.2006-01.com.openfiler:tsn.ocrdisk1  -p $STORAGE -l

As We setting this up for Oracle RAC, so ideally we need automatic login on startup ( default behaviour ), As a safe side we setup the disk to auto login on startup by using "--op update -n node.startup -v automatic" option.

# iscsiadm -m discovery -t sendtargets -p $STORAGE|awk '{print "iscsiadm -m node -T "$2"  -p $STORAGE --op update -n node.startup -v automatic"}'  

iscsiadm -m node -T iqn.2006-01.com.openfiler:tsn.ocrdisk3  -p $STORAGE --op update -n node.startup -v automatic
iscsiadm -m node -T iqn.2006-01.com.openfiler:tsn.ocrdisk2  -p $STORAGE --op update -n node.startup -v automatic
iscsiadm -m node -T iqn.2006-01.com.openfiler:tsn.ocrdisk1  -p $STORAGE --op update -n node.startup -v automatic

# iscsiadm -m node -T iqn.2006-01.com.openfiler:tsn.ocrdisk3  -p $STORAGE --op update -n node.startup -v automatic
# iscsiadm -m node -T iqn.2006-01.com.openfiler:tsn.ocrdisk2  -p $STORAGE --op update -n node.startup -v automatic
# iscsiadm -m node -T iqn.2006-01.com.openfiler:tsn.ocrdisk1  -p $STORAGE --op update -n node.startup -v automatic


Now create UDEV rules so all the disk have uniform name on startup.

Create openscsi Rules file

[root@oracluster03 ~]# vi /etc/udev/rules.d/55-openiscsi.rules
----------------------File start here ---------------------
KERNEL=="sd*", BUS=="scsi", PROGRAM="/etc/udev/scripts/iscsidev.sh %b",SYMLINK+="iscsi/%c/part%n"

-------------------------------------------------------------------------------------------
[root@oracluster03 ~]# mkdir -p /etc/udev/scripts

[root@oracluster03 ~]# vi /etc/udev/scripts/iscsidev.sh

-------------------------------file start here --------------------


#!/bin/sh

# FILE: /etc/udev/scripts/iscsidev.sh

BUS=${1}
HOST=${BUS%%:*}

[ -e /sys/class/iscsi_host ] || exit 1

file="/sys/class/iscsi_host/host${HOST}/device/session*/iscsi_session*/targetname"

target_name=$(cat ${file})

# This is not an open-scsi drive
if [ -z "${target_name}" ]; then
exit 1
fi

echo "${target_name##*.}"

---------file ends here -------------------------

[root@oracluster03 ~]# chmod 755 /etc/udev/scripts/iscsidev.sh

[root@oracluster03 ~]# service iscsi stop

Logging out of session [sid: 1, target: iqn.2006-01.com.openfiler:cluster03.gridcontrol01, portal: 192.168.2.5,3260]
Logout of [sid: 1, target: iqn.2006-01.com.openfiler:cluster03.gridcontrol01, portal: 192.168.2.5,3260]: successful
Stopping iSCSI daemon: [ OK ]

[root@oracluster03 ~]# service iscsi start
iscsid dead but pid file exists
Turning off network shutdown. Starting iSCSI daemon: [ OK ]
[ OK ]
Setting up iSCSI targets: Logging in to [iface: default, target: iqn.2006-01.com.openfiler:cluster03.gridcontrol01, portal: 192.168.2.5,3260]
Login to [iface: default, target: iqn.2006-01.com.openfiler:cluster03.gridcontrol01, portal: 192.168.2.5,3260]: successful

[ OK ]

Verify that, Disk are linked for logical link with local name.

[root@oracluster03 ~]# ls -lrt /dev/iscsi/*/


####Only on First node ##########


fdisk /dev/iscsi/disk01/part

fdisk /dev/iscsi/disk02/part


######### on all other nodes ########
partprobe


fdisk -l

Oracle Database Security

Oracle provides the following way of managing database security.

• Alerts
• System Privileges
• Object Privileges
• Users and Roles
• User and Tablespace Quotas
• Profiles and Resource Limits
• Password Management
• Connect Internal and Password Files
• O/S Authentication
• Auditing
• Event Triggers
• Fine Grained Access Control
• Oracle Label Security
• Database Vault
• Audit Vault
• Data Encryption
• Security Server

ORA-16069: Archive Log standby database activation identifier mismatch

I was Testing "Using a standby database for Read/Write for testing and Convert back to standby mode using Flasback"

Step followed :
Standby database :
1) alter database recover managed standby database finish ;
2) create restore point Before_DR_Test;
3) alter database commit to switchover to primary;
4) Startup force;
5) Create table , select data, etc
6) Complete any testing for read/write operation

Convert back to Standby :

7) startup mount force;
8) flashback database to restore point Before_DR_Test;
9) alter database convert to physical standby;
10) startup mount force;
11) alter database recover managed standby database disconnect;

All succeed and managed to convert the database in standby mode, but its failing to accept any new archive log generated at primary database. I am getting following error in standby database alert.log

RFS[10]: Activation ID mismatch: found 0x99d06db8 expected 0x99d0b54

ORA-16069: Archive Log standby database activation identifier mismatch

Configure SSH for RAC

update will be soon

Modify VIP Configuration

Update will be soon

Oracle DBA Questions

1. Database size is 500GB, what will be best possible backup solution/stratagy and why ?
2. CRS and ASM is 10.2.0.4 and DB is 10.1.0.4. Do you think there will any issues , If yes/No , why
3. At What stage you think RMAN Backup stratagy won't be enough? why

Regards
Krishan

10g OCM – GOAL Achieved

Hello ,

I like to share the joyful moment of my life with internet community. I am glad to inform, that I am now member of an elite group of professional by successful competition of 10g OCM Certification.

I wrote my exam in New Delhi in August 2008, today after long wait of more then 7 months, I received my result by email saying
“
Dear Krishan
Congratulations! on the successful completion of the Oracle DBA 10g Certified Master practicum

”
Now I am waiting for my OCM ID card and denim OCM shirt, which I am going to receive very soon by post.

Regards,

Krishan Jaglan

11g New Features for DBA's

Oracle latest database features

In this multipart series, learn how important new features such as Database Replay, Flashback Data Archive, and SecureFiles work via simple, actionable how-to's and sample code.
Change, although constantly present, is seldom risk-free. Even if the change is relatively minor (creating an index for example), your goal is probably to predict its precise impact as accurately as possible and then take appropriate action
Many new change assurance (or "Real Application Testing," as Oracle calls it) features in Oracle Database 11g bring that dream closer to reality. The Database Replay tool, for example, allows you to capture production database workload and replay it in a test (or even the same) database to assess the impact of change. Or consider SQL Performance Analyzer, which predicts the performance impact of changes to SQL before they are made. In my opinion, this Real Application Testing functionality alone justifies the upgrade.
Overall, Oracle Database 11g makes database infrastructure far more efficient, resilient, and manageable. For example, very compelling new features in the realm of partitioning ease the design and management of partitioned tables immensely.
In this series (as in the previous series focusing on Oracle Database 10g), you will learn how these new features work via simple, actionable how-to's and sample code.
by arup nanda

Oracle 10g OCM

Oracle 10g OCM (Oracle Certified Master)

Benefits for those who Earn the Distinguished OCM Credential
The OCM credential represents a proven ability to perform in the challenging role of a senior enterprise DBA. OCM benefits can help leverage this high level of credibility and turn it into career and business opportunities; OCMs can take advantage of valuable endorsement tools to become more marketable and differentiate themselves from others when competing for job opportunities and projects. OCMs continue to enjoy the OCP member benefits and also receive these additional benefits:
Distiguished industry-recognized credential, backed by Oracle’s brand equity.
OCM professional profile hosted on Oracle Technology Network (OTN) website. Profiles are viewable by over 2.5 million members within the Oracle community.
Exclusive use of the OCM logo for business cards, literature, etc.
Complimentary OCM Logo embroidered apparel items.


Candidate Information :
The Oracle Certified Master is the industry's most advanced certification
The Oracle Certified Master credential is for Oracle Certified Professionals with advanced training and at least 3 - 4 years of professional enterprise-level Oracle experience. To become an Oracle Certified Master a candidate must complete two required advanced DBA courses from Oracle University and pass an Oracle Certified Master exam.
Expert Advice about the OCM Exam from Senior OU InstructorsWe recommend minimum Oracle experience of 3 - 4 years; more typical of the qualified candidate would be 5 years or more.