Minimalistic Oracle

Thursday, December 4, 2014

How to work around ORA-00845: MEMORY_TARGET not supported on this system during startup

On Linux platforms, you need to adjust the size of the shared memory file system (/dev/shm) to match the size of your memory_target or memory_max_target. If not you will see the following error:

SQL> startup mount

ORA-00845: MEMORY_TARGET not supported on this system

To adjust the size, open the file /etc/fstab in an editor:

vi /etc/fstab

At the line where you see the tmpfs file system specified, add the size after the keyword "defaults". In the example below, I would like to use a memory_target of 10g, so I add "10g". Do not use a capital letter G, but simply a "g":

tmpfs                   /dev/shm                tmpfs   defaults,size=10g        0 0

Then, remount the file system:

mount –o remount /dev/shm

Monday, December 1, 2014

How to find and change the default temporary tablespace and default tablespace for a database instance

set lines 200
col description format a50
col property_value format a30
col property_name format a30
SELECT * 
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE '%TABLESPACE%';

To change the default temporary tablespace, use

alter database default temporary tablespace tmp;

To change the default tablespace, use

alter database default tablespace user_data;

How to setup Oracle Net Encryption for non-JDBC or OCI (thick) JDBC client

Since June 2013, SQL Net Encryption is a part of the normal Oracle EE license, as opposed to the Advanced Security Option.

Add the following to your server’s sqlnet.ora file:

sqlnet.encryption_server=required
sqlnet.encryption_types_server=AES128

Add the following to your client’s sqlnet.ora file:

SQLNET.ENCRYPTION_CLIENT = accepted

Note:
A list of encryption algorithms can be stated on each side, and the first one common to both client and server in the list will be chosen.

Verify that SQL Net Encryption works

In client’s sqlnet.ora:

ADR_BASE = C:\app\k90387
TRACE_LEVEL_CLIENT = ADMIN

This above will create an ADR directory structure on the client, in my case trace files are written to

 c:\app\vegard\oradiag_vegard\diag\clients\user_vegard\host_3450395457_76\trace

Create a session against the database:

SQL> connect vegard/vegard@oras
Connected.
SQL> exit

Open your windows explorer and look in the folder

c:\app\vegard\oradiag_vegard\diag\clients\user_vegard\host_3450395457_76\trace

You'll now see two files: one .trc file and one .trm file
Open the .trc file in a text editor, and search for the following:

2014-11-28 14:11:13.374358 :  -> PARAMETER TABLE HAS THE FOLLOWING CONTENTS <-
2014-11-28 14:11:13.374375 :   SQLNET.ENCRYPTION_CLIENT = accepted
2014-11-28 14:11:13.374392 :   ADR_BASE = C:\app\vegard
.
.
.
2014-11-28 13:48:28.033764 : na_tns:  authentication is not active
2014-11-28 13:48:28.033781 : na_tns:  encryption is active, using AES128
2014-11-28 13:48:28.033797 : na_tns:  crypto-checksumming is not active

Note that neither authentication nor crypto-checksumming is active, but encryption is. As expected.

Sources:

Orafaq

Oracle Documentation

Thursday, November 20, 2014

How to flash the database back to a guaranteed restore point

After a week of testing their new release, my customer wanted their database reset to the state it was previously in.
Earlier this week, I created a restore point of type GUARANTEED FLASHBACK.

Here is how I restored a database using flashback database technology. It was, as expected, very quick. Only a few seconds to flash back 14 GB of changes.

Connect to Recovery Manager:

oracle@myserver:[TESTDB01]# rman target / catalog uid/pwd@rmancat

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Nov 20 16:06:44 2014

connected to target database: TESTDB01 (DBID=411134280, not open)
connected to recovery catalog database

RMAN> list restore point all;

SCN              RSP Time  Type          Time         Name
---------------- --------- ----------   ---------     ----
153050263689                GUARANTEED  17-NOV-14     AKSEPT_TEST_START

Start the flashback operation:

RMAN> FLASHBACK DATABASE TO RESTORE POINT AKSEPT_TEST_START;
Starting flashback at 20-NOV-14
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=585 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 6.3.0.0
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=683 device type=DISK

starting media recovery

archived log for thread 1 with sequence 4651 is already on disk as file /u04/fast_recovery_area/TESTDB01/archivelog/2014_11_17/o1_mf_1_4651_b6m91zog_.arc
media recovery complete, elapsed time: 00:00:01
Finished flashback at 20-NOV-14 

RMAN> ALTER DATABASE OPEN RESETLOGS;

database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
RMAN> exit

-- all done --

From alert.log:

Completed: ALTER DATABASE   MOUNT
Thu Nov 20 16:10:37 2014
alter database recover datafile list clear
Completed: alter database recover datafile list clear
RMAN flashback database to before scn 153050263690 in incarnation 2
Flashback Restore Start
Thu Nov 20 16:12:11 2014
Flashback Restore Complete
Flashback Media Recovery Start
started logmerger process
Parallel Media Recovery started with 8 slaves
Thu Nov 20 16:12:22 2014
Flashback Media Recovery Log /u04/fast_recovery_area/TESTDB01/archivelog/2014_11_17/o1_mf_1_4651_b6m91zog_.arc
Thu Nov 20 16:12:22 2014
Incomplete Recovery applied until change 153050263690 time 11/17/2014 07:53:33
Flashback Media Recovery Complete
Completed: RMAN flashback database to before scn 153050263690 in incarnation 2
Thu Nov 20 16:13:29 2014
alter database open resetlogs
RESETLOGS after incomplete recovery UNTIL CHANGE 153050263690
Archived Log entry 4827 added for thread 1 sequence 4826 ID 0x18811648 dest 1:
Archived Log entry 4828 added for thread 1 sequence 4823 ID 0x18811648 dest 1:
Archived Log entry 4829 added for thread 1 sequence 4822 ID 0x18811648 dest 1:
Archived Log entry 4830 added for thread 1 sequence 4827 ID 0x18811648 dest 1:
Archived Log entry 4831 added for thread 1 sequence 4824 ID 0x18811648 dest 1:
Archived Log entry 4832 added for thread 1 sequence 4825 ID 0x18811648 dest 1:
Clearing online redo logfile 1 /u03/oradata/TESTDB01/redo01.log
Clearing online log 1 of thread 1 sequence number 4826
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /u03/oradata/TESTDB01/redo02.log
Clearing online log 2 of thread 1 sequence number 4823
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 /u03/oradata/TESTDB01/redo03.log
Clearing online log 3 of thread 1 sequence number 4822
Thu Nov 20 16:13:41 2014
Clearing online redo logfile 3 complete
Clearing online redo logfile 4 /u03/oradata/TESTDB01/redo04.log
Clearing online log 4 of thread 1 sequence number 4827
Clearing online redo logfile 4 complete
Clearing online redo logfile 5 /u03/oradata/TESTDB01/redo05.log
Clearing online log 5 of thread 1 sequence number 4824
Clearing online redo logfile 5 complete
Clearing online redo logfile 6 /u03/oradata/TESTDB01/redo06.log
Clearing online log 6 of thread 1 sequence number 4825
Thu Nov 20 16:13:52 2014
Clearing online redo logfile 6 complete
Resetting resetlogs activation ID 411113032 (0x18811648)
Online log /u03/oradata/TESTDB01/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u03/oradata/TESTDB01/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u03/oradata/TESTDB01/redo03.log: Thread 1 Group 3 was previously cleared
Online log /u03/oradata/TESTDB01/redo04.log: Thread 1 Group 4 was previously cleared
Online log /u03/oradata/TESTDB01/redo05.log: Thread 1 Group 5 was previously cleared
Online log /u03/oradata/TESTDB01/redo06.log: Thread 1 Group 6 was previously cleared
Thu Nov 20 16:13:52 2014
Setting recovery target incarnation to 3

Wednesday, November 19, 2014

How to increase the SMB space budget

When the message below shows up in the database alert.log, you should increase the SMB space budget.

Message:

SPM: SMB space usage (13523353600) exceeds 10.000000% of SYSAUX size (15728640000).

Command:

exec dbms_spm.configure('Space_budget_percent', 30);

Monday, November 3, 2014

How to create a tarball - short description

To create a tarball, for example of an Oracle Home, do the following:

# become root
su - 
# cd to the directory in which you keep your files you'd like to tar up:
cd /u01/oracle/product/11204
# create the tarball, place it in the parent directory:
tar cvf ../oracle11204.tar *

You could also add compression at the same time:

tar -cvzf ../oracle11204.tar.tgz .

To create a tarball of the directory /tmp/dbscr and any file(s) in it:

cd /tmp
tar cvf mytarball.tar dbscr
dbscr/
dbscr/file1.txt
dbscr/file2.rsp
dbscr/file3.ora

Saturday, November 1, 2014

Step by step: how to switchover from primary to standby with Oracle 10g

Based on the guide found in the official Oracle 10g documents found here

This is a classic Oracle 10g two-node Physical standby Data Guard setup:

Primary database name: proddb01
Standby database name: stby01

STEP 1: Verify that it is possible to fail over.
On primary database (proddb01):

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE

A value of TO STANDBY or SESSIONS ACTIVE indicates that the primary database is ready to be switched to the standby role.
In my case, the output was SESSIONS ACTIVE. In this case, check if this is because of a) active SQL sessions or b) active user sessions.
The section "Problems switching over to a standby database, section A.4.2 and A.4.3" gives good examples on how to clean up your primary before attempting to switch over.

It seems like you can still successfully perform a switchover by appending the WITH SESSION SHUTDOWN clause to the ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY statement, and that is exactly what I did:

STEP 2: Instruct the primary database to take the role as the standby database.

On the primary (proddb01):

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

From primary (proddb01) alert log:

idle dispatcher 'D000' terminated, pid = (16, 1)
Tue Oct 28 19:13:39 2014
Switchover: Complete - Database shutdown required (proddb01)
Tue Oct 28 19:13:39 2014
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN

From the standby (stby01) alert log:

-- Connected User is Valid
RFS[40]: Assigned to RFS process 7078566
RFS[40]: Identified database type as 'physical standby'
RFS[40]: Successfully opened standby log 7: '/u02/oradata/stby01/stb_redo7.log
Tue Oct 28 19:10:57 2014
Media Recovery Log /u04/oradata/stby01/archive/log1_75389_681750063.arc
Media Recovery Waiting for thread 1 sequence 75390
Tue Oct 28 19:13:31 2014
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[41]: Assigned to RFS process 34669144
RFS[41]: Identified database type as 'physical standby'
RFS[41]: Archived Log: '/u04/oradata/stby01/archive/log1_75390_681750063.arc'
Tue Oct 28 19:13:32 2014
Media Recovery Log /u04/oradata/stby01/archive/log1_75390_681750063.arc
Identified End-Of-Redo for thread 1 sequence 75390
Tue Oct 28 19:13:32 2014
Media Recovery End-Of-Redo indicator encountered
Tue Oct 28 19:13:32 2014
Media Recovery Applied until change 142570810888
Tue Oct 28 19:13:32 2014
MRP0: Media Recovery Complete: End-Of-REDO (stby01)
Resetting standby activation ID 1529144623 (0x5b24e52f)
Tue Oct 28 19:13:35 2014
MRP0: Background Media Recovery process shutdown (stby01)

Step 3: shutdown the database and start it in mounted mode.
On primary (proddb01):

shutdown abort
startup mount

From the primary (proddb01) alert log:

Tue Oct 28 19:15:37 2014
Successful mount of redo thread 1, with mount id 1709512659
Tue Oct 28 19:15:37 2014
Physical Standby Database mounted.
Starting control autobackup
Control autobackup written to DISK device
        handle '/u01/oracle/product/ora10g/dbs/c-1529124399-20141028-12'
Completed: ALTER DATABASE MOUNT

Step 4: verify the current status.

On the standby (stby01):

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
TO PRIMARY

A value of TO PRIMARY or SESSIONS ACTIVE indicates that the standby database is ready to be switched to the primary role. If neither of these values is returned, verify that Redo Apply is active and that redo transport is configured and working properly. Continue to query this column until the value returned is either TO PRIMARY or SESSIONS ACTIVE.

Step 5: Instruct the standby database to take on the role as a primary database.
On the standby (stby01):

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

From the standby (stby01) alert log:

-- Connected User is Valid
RFS[41]: Assigned to RFS process 34669144
RFS[41]: Identified database type as 'physical standby'
RFS[41]: Archived Log: '/u04/oradata/stby01/archive/log1_75390_681750063.arc'
Tue Oct 28 19:13:32 2014
Media Recovery Log /u04/oradata/stby01/archive/log1_75390_681750063.arc
Identified End-Of-Redo for thread 1 sequence 75390
Tue Oct 28 19:13:32 2014
Media Recovery End-Of-Redo indicator encountered
Tue Oct 28 19:13:32 2014
Media Recovery Applied until change 142570810888
Tue Oct 28 19:13:32 2014
MRP0: Media Recovery Complete: End-Of-REDO (stby01)
Resetting standby activation ID 1529144623 (0x5b24e52f)
Tue Oct 28 19:13:35 2014
MRP0: Background Media Recovery process shutdown (stby01)
Tue Oct 28 19:18:43 2014
ALTER DATABASE SWITCHOVER TO PRIMARY (stby01)
Tue Oct 28 19:18:43 2014
If media recovery active, switchover will wait 900 seconds
SwitchOver after complete recovery through change 142570810888
Online log /u02/oradata/stby01/redo1.log: Thread 1 Group 4 was previously cleared
Online log /u02/oradata/stby01/redo2.log: Thread 1 Group 5 was previously cleared
Online log /u02/oradata/stby01/redo3.log: Thread 1 Group 6 was previously cleared
Standby became primary SCN: 142570810886
Converting standby mount to primary mount.
Tue Oct 28 19:18:43 2014
Switchover: Complete - Database mounted as primary (stby01)
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
Tue Oct 28 19:18:43 2014
ARC0: STARTING ARCH PROCESSES
ARC2: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
ARC2 started with pid=15, OS id=8847906
Tue Oct 28 19:19:41 2014
ALTER DATABASE OPEN

The primary database (proddb01) is from now on considering itself to be the physical standby database.

From the old primary (proddb01) database alert log:

Tue Oct 28 19:15:37 2014
Physical Standby Database mounted.
Starting control autobackup
Control autobackup written to DISK device
        handle '/u01/oracle/product/ora10g/dbs/c-1529124399-20141028-12'
Completed: ALTER DATABASE   MOUNT
Tue Oct 28 19:26:10 2014
Using STANDBY_ARCHIVE_DEST parameter default value as /u01/oracle/admin/proddb01/archive/
Tue Oct 28 19:26:19 2014
Starting control autobackup
Control autobackup written to SBT_TAPE device
        comment 'API Version 2.0,MMS Version 5.3.3.0',
        media '1362'
        handle 'c-1529124399-20141028-13'

Step 6: Verify that the former standby database now has the primary role.
On stby01:

SQL> SELECT DB_UNIQUE_NAME,NAME,DATABASE_ROLE FROM V$DATABASE;

DB_UNIQUE_NAME                 NAME           DATABASE_ROLE
------------------------------ -------------- ----------------
stby01                          proddb01           PRIMARY

After switchover, Oracle complained about some temporary files:

ORA-01187: cannot read from file 201 because it failed verification tests
ORA-01110: data file 201: '/u02/oradata/stby01/temp01.dbf'



SQL> 
  1  select tf.file#,tf.ts#,tf.status,tf.enabled, tf.bytes/1024/1024,tf.name "file_name",ts.name "ts_name"
  2  from v$tempfile tf, v$tablespace ts
  3* where tf.ts# = ts.ts#

     FILE#        TS# STATUS  ENABLED    TF.BYTES/1024/1024 file_name                              ts_name
---------- ---------- ------- ---------- ------------------ -------------------------------------- --------------------
         1          3 ONLINE  READ WRITE              32767 /u02/oradata/stby01/temp01.dbf          TEMP
         2          8 ONLINE  READ WRITE                100 /u02/oradata/stby01/EDPROD_iastemp.dbf  EDPROD_IAS_TEMP
         3          3 ONLINE  READ WRITE               9900 /u02/oradata/stby01/temp02.dbf          TEMP

SQL> save temp_tabspc_overview.sql
Created file temp_tabspc_overview.sql

SQL> drop tablespace EDPROD_IAS_TEMP including contents and datafiles;

Tablespace dropped.

SQL> @temp_tabspc_overview.sql

     FILE#        TS# STATUS  ENABLED    TF.BYTES/1024/1024 file_name                      ts_name
---------- ---------- ------- ---------- ------------------ ------------------------------ --------------------
         1          3 ONLINE  READ WRITE              32767 /u02/oradata/stby01/temp01.dbf  TEMP
         3          3 ONLINE  READ WRITE               9900 /u02/oradata/stby01/temp02.dbf  TEMP


SQL> create temporary tablespace EDPROD_IAS_TEMP tempfile '/u02/oradata/stby01/EDPROD_iastemp.dbf' size 128M reuse autoextend on next 32M maxsize unlimited

Tablespace created.

SQL>  @temp_tabspc_overview.sql

     FILE#        TS# STATUS  ENABLED    TF.BYTES/1024/1024 file_name                              ts_name
---------- ---------- ------- ---------- ------------------ -------------------------------------- --------------------
         1          3 ONLINE  READ WRITE              32767 /u02/oradata/stby01/temp01.dbf          TEMP
         2          8 ONLINE  READ WRITE                128 /u02/oradata/stby01/EDPROD_iastemp.dbf  EDPROD_IAS_TEMP
         3          3 ONLINE  READ WRITE               9900 /u02/oradata/stby01/temp02.dbf          TEMP


SQL> alter database default temporary tablespace EDPROD_IAS_TEMP;

Database altered.

SQL>  drop tablespace TEMP including contents and datafiles;

Tablespace dropped.

SQL> create temporary tablespace TEMP tempfile '/u02/oradata/stby01/temp01.dbf' size 128M reuse autoextend on next 32M maxsize unlimited;

Tablespace created.

SQL> @temp_tabspc_overview.sql

     FILE#        TS# STATUS  ENABLED    TF.BYTES/1024/1024 file_name                              ts_name
---------- ---------- ------- ---------- ------------------ -------------------------------------- --------------------
         1          3 ONLINE  READ WRITE                128 /u02/oradata/stby01/temp01.dbf          TEMP
         2          8 ONLINE  READ WRITE                128 /u02/oradata/stby01/EDPROD_iastemp.dbf  EDPROD_IAS_TEMP