Optimize UNDO Parameters

When working with UNDO (instead of Rollback) two things are important to consider.
1. The size of the UNDO tablespace
2. The Undo_retnetion parameter
A particular size for the UNDO tablespace can be selected and set the UNDO_RETENTION parameter to an best possible value according to the undo size and the relevant database activity. If you have limited diskspace, you don’t want to allocate more space than necessary to the Undo tablespace.

The following query will help to optimize the UNDO_RETENTION parameter

SELECT d.undo_size / (1024 * 1024) "ACTUAL UNDO SIZE (MEGS)",
SUBSTR (e.VALUE, 1, 25) "UNDO RETENTION (Secs)",
ROUND ((d.undo_size / (TO_NUMBER (f.VALUE) * g.undo_block_per_sec))
) "OPTIMAL UNDO RETENTION (Secs)"
FROM (SELECT SUM (a.BYTES) undo_size
FROM v$datafile a, v$tablespace b, dba_tablespaces c
WHERE c.CONTENTS = 'UNDO'
AND c.status = 'ONLINE'
AND b.NAME = c.tablespace_name
AND a.ts# = b.ts#) d,
v$parameter e,
v$parameter f,
(SELECT MAX (undoblks / ((end_time - begin_time) * 3600 * 24)
) undo_block_per_sec
FROM v$undostat) g
WHERE e.NAME = 'undo_retention' AND f.NAME = 'db_block_size';



If disk space it not an issue, then it would be ideal to choose the UNDO_RETENTION time that is best. (Flashback etc). Assign the suitable size to the UNDO tablespace according to the database activity:

SELECT d.undo_size / (1024 * 1024) "ACTUAL UNDO SIZE (MEGS)",
SUBSTR (e.VALUE, 1, 25) "UNDO RETENTION (Secs)",
(TO_NUMBER (e.VALUE) * TO_NUMBER (f.VALUE) * g.undo_block_per_sec
)
/ (1024 * 1024) "NEEDED UNDO SIZE (MEGS)"
FROM (SELECT SUM (a.BYTES) undo_size
FROM v$datafile a, v$tablespace b, dba_tablespaces c
WHERE c.CONTENTS = 'UNDO'
AND c.status = 'ONLINE'
AND b.NAME = c.tablespace_name
AND a.ts# = b.ts#) d,
v$parameter e,
v$parameter f,
(SELECT MAX (undoblks / ((end_time - begin_time) * 3600 * 24)
) undo_block_per_sec
FROM v$undostat) g
WHERE e.NAME = 'undo_retention' AND f.NAME = 'db_block_size';

Calculate Tablespace Sizes

SELECT df.tablespace_name "Tablespace"
,round((sum(nvl(fs.bytes,0))/ (df.bytes)) * 100)
"%Free"
,round(((df.bytes - sum(nvl(fs.bytes,0)))
/ (df.bytes) ) * 100) "%Used"
,round(sum(nvl(fs.bytes/1024/1024,0))) "Mb Free"
,round(df.bytes/1024/1024
- sum(nvl(fs.bytes/1024/1024,0))) "Mb Used"
FROM dba_free_space fs, dba_data_files df
WHERE fs.file_id(+) = df.file_id
GROUP BY df.tablespace_name, df.file_id, df.bytes,
df.autoextensible
ORDER BY df.file_id;

Calculate the database size

SELECT 'DB Size' "*****"
,round(sum(round(sum(nvl(fs.bytes/1024/1024,0)))) /
sum(round(sum(nvl(fs.bytes/1024/1024,0))) + round
(df.bytes/1024/1024 – sum(nvl(fs.bytes/1024/1024,0))))
* 100, 0) "%FREE"
,round(sum(round(df.bytes/1024/1024 –
sum(nvl(fs.bytes/1024/1024,0)))) / sum(round(sum
(nvl(fs.bytes/1024/1024,0))) + round(df.bytes/1024/
1024 – sum(nvl(fs.bytes/1024/1024,0)))) * 100, 0)
"%USED"
,sum(round(sum(nvl(fs.bytes/1024/1024,0)))) "FREE MB"
,sum(round(df.bytes/1024/1024
– sum(nvl(fs.bytes/1024/1024,0)))) "USED MB"
,sum(round(sum(nvl(fs.bytes/1024/1024,0))) + round
(df.bytes/1024/1024
– sum(nvl(fs.bytes/1024/1024,0)))) "Size"
FROM dba_free_space fs, dba_data_files df
WHERE fs.file_id(+) = df.file_id
GROUP BY df.tablespace_name, df.file_id, df.bytes,
df.autoextensible
ORDER BY df.file_id;

List all constraints

SELECT DECODE (t.constraint_type,
'P', 'Primary',
'R', 'Foreign',
'U', 'Alternate',
'Unknown'
) "Key",
t.table_name "Tab", t.constraint_name "Cons", c.column_name "Col",
c.POSITION "Pos"
FROM user_constraints t, user_cons_columns c
WHERE t.constraint_type IN ('P', 'R', 'U')
AND t.table_name = c.table_name
AND t.constraint_name = c.constraint_name
ORDER BY t.table_name, t.constraint_type, c.POSITION;

One of the issues using ASM is its invisibility to the operating system and moving a file within ASM can be tricky.
Recently I needed to move a control file from one diskgroup to another and he is the solution.

--Create the new diskgroup
ASM>CREATE DISKGROUP CMP_PRE_REDO
EXTERNAL REDUNDANCY
DISK '/dev/rdsk/c6t60060480000287750753474B50354345d0s7',
'/dev/rdsk/c6t60060480000287750753474B50354441d0s7',
'/dev/rdsk/c6t60060480000287750753474B50363136d0s7',
'/dev/rdsk/c6t60060480000287750753474B50363232d0s7',
'/dev/rdsk/c6t60060480000287750753474B50354645d0s7',
'/dev/rdsk/c6t60060480000287750753474B50354632d0s7',
'/dev/rdsk/c6t60060480000287750753474B50354536d0s7',
'/dev/rdsk/c6t60060480000287750753474B50363041d0s7';



SQL>ALTER DATABASE ADD LOGFILE MEMBER '+CMP_PRE_REDO' TO GROUP 1;
ALTER DATABASE ADD LOGFILE MEMBER '+CMP_PRE_REDO' TO GROUP 2;
ALTER DATABASE ADD LOGFILE MEMBER '+CMP_PRE_REDO' TO GROUP 3;
ALTER DATABASE ADD LOGFILE MEMBER '+CMP_PRE_REDO' TO GROUP 4;
ALTER DATABASE ADD LOGFILE MEMBER '+CMP_PRE_REDO' TO GROUP 5;
ALTER DATABASE ADD LOGFILE MEMBER '+CMP_PRE_REDO' TO GROUP 6;

SQL>alter system switch logfile

ALTER DATABASE DROP LOGFILE MEMBER '+CMP_PRE_FRA/wpscs02q/onlinelog/group_1.3601.643130119'


export ORACLE_SID=CMPBT01S
restore controlfile to '+CMP_PRE_REDO' from '+CMP_PRE_DATA/CMPBT01S/CONTROLFILE/Current.689.650299077';
alter system set control_files='+cmp_pre_redo/cmpbt01s/controlfile/current.289.650374053' scope=spfile;

+cmp_pre_redo/cmpbt01s/controlfile/current.289.650374053

How much space each user is consuming

SELECT u.NAME Owner,
t.NAME Tablespace,
SUM((seg.blocks * t.BLOCKSIZE)/1024)/1024 MB
FROM SYS.ts$ t,
SYS.user$ u,
SYS.seg$ seg
WHERE seg.user# = u.user#
AND t.ts# = seg.ts#
GROUP BY u.NAME, t.NAME

Resolving Disk Space Issues in Grid Control

After trying to apply templates to a series of databases, I was constantly receiving a message while removing all policies from the database configuration.

“Error: return number of status does not match 1!=0”

(I don’t understand why Oracle can’t provide meaningful messages)

I managed to track down the message, and it’s related to threshold settings within the agent! Ultimately the agent was complaining that I didn’t have enough disk space to complete the job.

I checked on the server and the disk space had grown to 97% full in our agent home, while the threshold for the agent collecting information stops at 96%, there is also a lower threshold that was set to 95%.

This means, if our disk space reaches 97% then grid control agent will stop collecting information, and if we clean up the space, the agent won’t kick in until we have at least 95%.

The solution is to either turn off the check (which is dangerous) or change the thresholds (recommended). Follow these steps:

1. Stop the agent
2. emctl stop agent
3. Check the value of the parameters in /sysman/config/emd.properties:

# The maximum amount (%) of disk space that can be used on the EMD filesystem
# before the following is disabled:
# - Collection of data (upload manager)
# - Logging and tracing

UploadMaxDiskUsedPct=96

# The amount (%) of disk space that can be used on the EMD filesystem
# before the following is re-enabled after being disabled previously:
# - Collection of data (upload manager)
# - Logging and tracing

UploadMaxDiskUsedPctFloor=95

4. Change the relevant percentages
5. Start the agent
6. emctl start agent

Recover From Missing Redo Log Files

The disk that is holding the most up-to-date redo log files experienced a fatal failure, and the database wouldn`t come up any more. It complains about missing redo logs. So, you copy the redo log files from a recent backup; but still the database won`t come up with error messages like ORA-00314: log 1 of thread 1, expected sequence# doesn`t match.

SQL> startup
ORACLE instance started.
Total System Global Area 4294967296 bytes
Fixed Size 1306568 bytes
Variable Size 2534150200 bytes
Database Buffers 1728053248 bytes
Redo Buffers 31457280 bytes
Database mounted.

ORA-00314: log 1 of thread 1, expected sequence# doesn't match
ORA-00312: online log 1 thread 1: '/opt/oracle/oradata_4/RedoLogFiles/log1.dbf'

How to bring up the database with no redo logs?

In production environments, usually archive logging will be turned on. If that's the case, consult the Oracle system administration documentation to restore the lost transactions.

In all other cases it is very unlikely to recover the lost transactions. So the easiest way to bring up the database is to rebuild the control file. The steps to rebuild the control file with new redo log files are as follows:

Start the database up.

SQL> startup <- let it complain about online redo log

Run the following command to dump the control file into a trace file.

SQL> alter database backup controlfile to trace;
Database altered.

Go to udump location and locate the file which contains the necessary SQL text to build the control file. The easiest way is to grep for text like `CREATE CONTROLFILE REUSE DATABASE`. Once you locate the trace file, change the extension from trc to sql and change NORESETLOGS to RESETLOGS. That is, a SQL like:

CREATE CONTROLFILE REUSE DATABASE "XYZ" NORESETLOGS NOARCHIVELOG

should be changed to

CREATE CONTROLFILE REUSE DATABASE "XYZ" RESETLOGS NOARCHIVELOG

Shut down the database

Run the script that you just modified -- it creates a new control file.

Open the database with

alter database open;

You may have to read the alert messages carefully and act accordingly to fix the things up, if the database won`t come up even after the above step.

Finally bring down the database and bring it up as you usually do.

How to Move Control file in Oracle ASM

1. First get the current control file names

SQL>select name from v$controlfile;

+CMP_PRE_DATA/cmpbt02q/control01.ctl.

2. Next we need to create a pfile
create pfile='/app/oracle/initCMPBT02Q.ora' from spfile;

3. shutdown the database but don't mount it
srvctl stop database -d CMPBT02Q
export ORACLE_SID=CMPBT02Q1
sqlplus / as sysdba
startup nomount
exit

4. Use RMAN to create a copy of the control file into the new diskgroup.

RMAN>rman target /restore controlfile to '+CMP_PRE_REDO' from '+CMP_PRE_DATA/cmpbt02q/control03.ctl';

5. Modify the initCMPBT02Q.ora file with the new control file, which can be obtained from the ASM +CMP_PRE_REDO/CMPBT02Q/CONTROLFILE location. Place this name in the pfile.

6. Shutdown the database immediate shutdown immediate
SQL>shutdown immediate;

7. Mount the database with the modified pfile.
SQL>startup pfile='/app/oracle/initCMPBT02Q.ora'

8. Create the spfile
SQL>create spfile='+CMP_PRE_DATA/CMPBT02Q/spfileCMPBT02Q.ora' from pfile='/app/oracle/initCMPBT02Q.ora';

9. Check you have the new control file location.
SQL>select name from v$controlfile
SQL> set wrap off
SQL> select name from v$controlfile;

NAME-------------------------------------------------------
+CMP_PRE_DATA/cmpbt02q/control01.ctl
+CMP_PRE_REDO/cmpbt02q/current.290.650448573
+CMP_PRE_DATA/cmpbt02q/control03.ctl

10. Start database services

HTC Diamond Setup For Telstra WAP

Instructions:
Please follow the set up instructions below.
1. On the handset, tap on the Start button.
2. Tap on Settings.
3. Tap on Connections tab.
4. Tap on Connections icon.
5. Tap on Add a new modem connection under My Work Network.
6. Enter Telstra Internet in the Enter a name for the connection field.
7. Select Cellular Line (GPRS, 3G) in the drop down list under Select a modem.
8. Tap on Next.
9. Enter telstra.wap in the Access point name field.
10. Tap on Next.
11. Enter a username in the User name field or otherwise leave it blank.
12. Enter a password in the Password field or otherwise leave it blank.
13. Tap on Advanced.
14. Select Use server-assigned IP address.
15. Tap on OK.
16. Tap on Finish.
17. Tap on Set up my proxy server under My Work Network.
18. Make sure that This network connects to the Internet and This network uses a proxy server to connect to the Internet are checked.
19. Tap on Advanced.
20. Tap on HTTP.
21. Enter 10.1.1.181 in the Server field.
22. Enter 80 in the Port field.
23. Tap on OK three times.

The handset is now configured for using WAP.