Monday, June 6, 2011

New memory and dump parameters in Oracle 11g

If you want to migrate a database from Oracle 10g to Oracle 11g, in general you can dump all the database parameters with a CREATE PFILE sentence and start your new Oracle 11g instance with that pfile, but it would be better to substitute some deprecated parameters with the new Oracle 11g parameters; it would take just a couple of minutes and you won't get annoying messages at startup about deprecated parameters:

SQL> startup nomount pfile=initmydb.ora
ORA-32006: BACKGROUND_DUMP_DEST initialization parameter has been deprecated
ORA-32006: REMOTE_OS_AUTHENT initialization parameter has been deprecated
ORA-32006: USER_DUMP_DEST initialization parameter has been deprecated
ORACLE instance started.

Total System Global Area 267227136 bytes
Fixed Size 2158832 bytes
Variable Size 134221584 bytes
Database Buffers 125829120 bytes
Redo Buffers 5017600 bytes

First of all, the remote_os_authent parameter is deprecated in 11g so you can just drop it. And in Oracle 11g, you can substitute the parameters background_dump_dest, core_dump_dest and user_dump_dest with the new diagnostic_dest parameter, that by default is derived from the value of the $ORACLE_BASE environment variable. Finally, you can set a memory usage target for the whole instance (PGA and SGA) with the new memory_target parameter, and also a memory usage limit with the memory_max_target parameter.

In this example, all six parameters are set for this Oracle 10g instance:

SQL> select NAME, VALUE from v$parameter where name in ('pga_aggregate_target','sga_target',
'sga_max_size','user_dump_dest','background_dump_dest','core_dump_dest') order by NAME;

NAME VALUE
------------------------------ --------------------------------------------------
background_dump_dest /oracle_10g/app/oracle/admin/mydb/bdump
core_dump_dest /oracle_10g/app/oracle/admin/mydb/cdump
pga_aggregate_target 100663296
sga_max_size 268435456
sga_target 268435456
user_dump_dest /oracle_10g/app/oracle/admin/mydb/udump

6 rows selected.

In this 11g instance, just the diagnostic_dest and memory_target parameters where defined, and the other parameters where set by Oracle and derived from the former two parameters:

SQL> select NAME, VALUE from v$parameter where name in ('pga_aggregate_target','sga_target',
'sga_max_size','memory_target','memory_max_target','user_dump_dest','background_dump_dest',
'diagnostic_dest','core_dump_dest') order by NAME;

NAME VALUE
------------------------------ ------------------------------------------------------------
background_dump_dest /oracle_11g/product/diag/rdbms/mydb/mydb/trace
core_dump_dest /oracle_11g/product/diag/rdbms/mydb/mydb/cdump
diagnostic_dest /oracle_11g/product
memory_max_target 369098752
memory_target 369098752
pga_aggregate_target 0
sga_max_size 369098752
sga_target 0
user_dump_dest /oracle_11g/product/diag/rdbms/mydb/mydb/trace

9 rows selected.

More information:

11g Automatic Diagnostic Repository (ADR)
REMOTE_OS_AUTHENT
SGA_MAX_SIZE
DIAGNOSTIC_DEST

No comments:

Post a Comment