Мой блог

среда, 19 августа 2009 г.

Oracle ANALYZE INDEX


DECLARE
  vOwner   dba_indexes.owner% TYPE ;            /* Index Owner            */
  vIdxName dba_indexes.index_name% TYPE ;       /* Index Name             */
  vAnalyze VARCHAR2 ( 100 );                    /* String of Analyze Stmt */
  vCursor  NUMBER ;                             /* DBMS_SQL cursor        */
  vNumRows INTEGER ;                            /* DBMS_SQL return rows   */
  vHeight  index_stats.height% TYPE ;           /* Height of index tree   */
  vLfRows  index_stats.lf_rows% TYPE ;          /* Index Leaf Rows        */
  vDLfRows index_stats.del_lf_rows% TYPE ;      /* Deleted Leaf Rows      */
  vDLfPerc   NUMBER ;                           /* Del lf Percentage      */
  vMaxHeight NUMBER ;                           /* Max tree height        */
  vMaxDel    NUMBER ;                           /* Max del lf percentage  */
  CURSOR cGetIdx IS SELECT owner,index_name
     FROM dba_indexes
     --WHERE OWNER = 'OWS' and index_name in ('USAGE_APPR4')
     WHERE (OWNER, index_name) in ( select owner, segment_name from dba_segments where segment_type = 'INDEX' and owner = 'BSSADMIN')
     ;
BEGIN
  /* Define maximums. This section can be customized. */
  vMaxHeight := 3 ;
  vMaxDel    := 20 ;
  /* For every index, validate structure */
  OPEN cGetIdx;
  LOOP
     FETCH cGetIdx INTO vOwner,vIdxName;
     EXIT WHEN cGetIdx% NOTFOUND ;
     /* Open DBMS_SQL cursor */
     vCursor := DBMS_SQL.OPEN_CURSOR;
     begin
       /* Set up dynamic string to validate structure */
       vAnalyze := 'ANALYZE INDEX ' || vOwner || '.' || vIdxName || ' VALIDATE STRUCTURE' ;
       DBMS_SQL.PARSE(vCursor,vAnalyze,DBMS_SQL.V7);
       vNumRows := DBMS_SQL.EXECUTE(vCursor);
       /* Close DBMS_SQL cursor */
       DBMS_SQL.CLOSE_CURSOR(vCursor);
       /* Does index need rebuilding?  */
       /* If so, then generate command */
       SELECT height,lf_rows,del_lf_rows INTO vHeight,vLfRows,vDLfRows
          FROM INDEX_STATS;
       IF vDLfRows = 0 THEN          /* handle case where div by zero */
          vDLfPerc := 0 ;
       ELSE
          vDLfPerc := (vDLfRows / vLfRows) * 100 ;
       END IF ;
       IF   (vDLfPerc > vMaxDel) THEN
          DBMS_OUTPUT.PUT_LINE( 'ALTER INDEX ' || vOwner || '.' || vIdxName || ' REBUILD online compute statistics;  -- ' ||vDLfPerc);
       END IF ;
     exception
       when others then
         DBMS_OUTPUT.PUT_LINE( '-- ' || SQLERRM ());
     end ;
  END LOOP ;
  CLOSE cGetIdx;
END ;

суббота, 15 августа 2009 г.

Oracle Resize, Drop TEMP and UNDO

TEMP

CREATE TEMPORARY TABLESPACE MYTEMPFILE
TEMPFILE D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01_01.DBF'
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 2000M;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE MYTEMPFILE;

ALTER DATABASE TEMPFILE ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01.DBF' DROP INCLUDING DATAFILES;

ALTER TABLESPACE TEMP
ADD TEMPFILE ‘ D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01.DBF '
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 20000M;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

DROP TABLESPACE MYTEMPFILE INCLUDING CONTENTS AND DATAFILES;

UNDO

create undo tablespace undotbs2 datafile
'D:\ORACLE\ORA9I\ORADATA\ORA9I\UNDOTBS02.DBF' SIZE 200M REUSE AUTOEXTEND ON;

alter system set undo_tablespace = undotbs2;

drop tablespace undotbs1 including contents and datafiles;

суббота, 8 августа 2009 г.

EMCLI

Сводка недоступна. Нажмите эту ссылку, чтобы открыть запись.

четверг, 23 июля 2009 г.

DBMS_SQLTUNE (настройка SQL)

Поиск наилучшего плана – SQL Tuning Advisor

Минимально необходимые привилегии
для непривилегированного пользователя:

CONNECT / AS SYSDBA
GRANT ADVISOR                 TO < USERNAME >;
GRANT SELECTCATALOG_ROLE      TO < USERNAME >;
GRANT EXECUTE on DBMS_SQLTUNE TO < USERNAME >;

Дополнительно

Можно запускать помощник с помощью встроенного скрипта:
$ORACLE_HOME/rdbms/admin/sqltrpt.sql

Основные шаги

Убедитесь, что оригинальный запрос находится в памяти (shared pool)
или AWR репозитории

Создать задание для SQL Tuning Advisor одним из 3х способов

/* Анализ исходного текста запроса */
DECLARE
my_task_name varchar2(30);
my_sqltext   clob;
BEGIN
my_sqltext := q'!!';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK (
          sql_text => my_sqltext,
         user_name => '< SCHEMA_NAME >',
             scope => 'COMPREHENSIVE',
        time_limit => 600,
         task_name => '< TASK_NAME >',
       description => '< TASK_DESCRIPTION >');
END;
/

/* Анализ запроса по SQL_ID из Cursor Cache */
DECLARE
my_task_name varchar2(30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK (
            sql_id => '< SQL_ID >',
             scope => 'COMPREHENSIVE',
        time_limit => 600,
         task_name => '< TASK_NAME >',
       description => '< TASK_DESCRIPTION >');
END;
/

/* Анализ запроса по SQL_ID из AWR репозитория */
DECLARE
my_task_name varchar2(30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK (
        begin_snap => < BEGIN_SNAP_ID >,
          end_snap => < END_SNAP_ID >,
            sql_id => '< SQL_ID >',
             scope => 'COMPREHENSIVE',
        time_limit => 600,
         task_name => '< TASK_NAME >',
       description => '< TASK_DESCRIPTION >');
END;
/

Запустить задание на исполнение
exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => '< TASK_NAME >');

Просмотр рекомендаций

SET LONG 100000
SET LONGCHUNKSIZE 100000
SET LINESIZE 1000
SET PAGESIZE 1000
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('< TASK_NAME >') FROM DUAL;

Пример:

exec DBMS_SQLTUNE.DROP_TUNING_TASK('TSK_01');

DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := q'!SELECT * FROM DUAL!';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => my_sqltext,
                                               user_name => 'ANGORL',
                                                   scope => 'COMPREHENSIVE',
                                              time_limit => 600,
                                               task_name => 'TSK_01',
                                             description => 'test');
END;
/

exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'TSK_01' );

SELECT sofar, totalwork FROM V$ADVISOR_PROGRESS
WHERE task_id = (SELECT task_id FROM USER_ADVISOR_TASKS
                     WHERE task_name='TSK_01');

SET LONG 100000
SET LONGCHUNKSIZE 100000
SET LINESIZE 10000
SET PAGESIZE 10000
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TSK_01') FROM DUAL;

В рекоммендациях может быть предложено принять profile:

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.

Recommendation (estimated benefit: 92.44%)
------------------------------------------
- Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name =>'TSK_01', task_owner => 'ANGOR', replace => TRUE);

И выведены для сравнения два плана выполнения запроса:

Оригинального

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original With Adjusted Cost
------------------------------
Plan hash value: 5227986114

И с применённым profile

2- Using SQL Profile
--------------------
Plan hash value: 6245447849

1. Создайте задачу:

Задача создается для стейтманта из AWR:

DECLARE
my_task_name VARCHAR2(100);
BEGIN
my_task_name := DBMS_SQLTUNE.create_tuning_task (
                          begin_snap => 523,
                          end_snap    => 658,
                          sql_id      => '7h35uxf5uhmm1',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit => 60,
                          task_name   => '7h35uxf5uhmm1_AWR_tuning_task',
                          description => 'Tuning task for statement 7h35uxf5uhmm1 in AWR');
DBMS_OUTPUT.put_line('my_task_name: ' || my_task_name);
END;
/

Задача создается по sql_id курсора из кэша:

DECLARE
my_task_name VARCHAR2(100);
BEGIN
my_task_name := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => '7h35uxf5uhmm1',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit => 60,
                          task_name   => '7h35uxf5uhmm1_tuning_task',
                          description => 'Tuning task for statement 7h35uxf5uhmm1');
DBMS_OUTPUT.put_line('my_task_name: ' || my_task_name);
END;
/

Задача создается для операторов из STS:

DECLARE
my_task_name VARCHAR2(100);
BEGIN
my_task_name := DBMS_SQLTUNE.create_tuning_task (
                          sqlset_name => 'test_sql_tuning_set',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit => 60,
                          task_name   => 'sqlset_tuning_task',
                          description => 'Tuning task for an SQL tuning set.');
DBMS_OUTPUT.put_line('my_task_name: ' || my_task_name);
END;
/

Можно указать SQL-оператор вручную:

DECLARE
my_sql_text   VARCHAR2(500);
my_task_name VARCHAR2(100);
BEGIN
my_sql_text := 'SELECT e.*, d.* ' ||
           'FROM   emp e JOIN dept d ON e.deptno = d.deptno ' ||
           'WHERE NVL(empno, ''0'') = :empno';

my_task_name := DBMS_SQLTUNE.create_tuning_task (
                          sql_text    => my_sql_text,
                          bind_list   => sql_binds(anydata.ConvertNumber(100)),
                          user_name   => 'ANGOR',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit => 60,
                          task_name   => 'emp_dept_tuning_task',
                          description => 'Tuning task for an EMP to DEPT join query.');
DBMS_OUTPUT.put_line('my_task_name: ' || my_task_name);
END;
/

общее время на анализ ограничили до 60 секунд.

2. Выполните задачу:

BEGIN
     DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task' );
END;
/

3. Получите отчет по настройке:

SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SET PAGESIZE 24
select DBMS_SQLTUNE.REPORT_TUNING_TASK ( 'my_sql_tuning_task' ) as recommendations from dual;

Мониторить выполнение задач по настройке SQL можно так:

SELECT task_name, status FROM dba_advisor_log WHERE owner = 'ANGOR';

Приостановить и продолжить выполнение задачи можно так:

EXEC DBMS_SQLTUNE.interrupt_tuning_task (task_name => 'my_sql_tuning_task');
EXEC DBMS_SQLTUNE.resume_tuning_task (task_name => 'my_sql_tuning_task');

Завершить выполнение задачи можно так:

EXEC DBMS_SQLTUNE.cancel_tuning_task (task_name => 'my_sql_tuning_task');


Сбросить задачу для ее повторного выполнения:

EXEC DBMS_SQLTUNE.reset_tuning_task (task_name => 'my_sql_tuning_task');

Простой пример:

DECLARE
    my_task_name VARCHAR2(30);
    my_sql_text CLOB;

BEGIN
    my_sql_text := 'select sysdate
                             from dual';

    my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
                                 sql_text                   => my_sql_text,
--                                 bind_list                  => sql_binds(anydata.ConvertNumber(100)),
                                 user_name             => 'ANGOR',
                                 scope                    => 'COMPREHENSIVE',
                                 time_limit                => 60,
                                 task_name             => 'my_sql_tuning_task',
                                 description             => 'task to tune sql'
                                 );
END;
/

BEGIN
     DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task' );
END;
/

select DBMS_SQLTUNE.REPORT_TUNING_TASK ( 'my_sql_tuning_task' ) as recommendations from dual;

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : my_sql_tuning_task
Tuning Task Owner : ANGOR
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at         : 06/08/2014 16:59:14
Completed at       : 06/08/2014 16:59:16

-------------------------------------------------------------------------------
Schema Name: ANGOR
SQL ID     : 7ahprz34d71wv
SQL Text   : select sysdate
                                          from dual

-------------------------------------------------------------------------------
There are no recommendations to improve the statement.

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

Использование инструмента SQLT (Doc ID 215187.1)

Закрепление существующего плана

Основные шаги

Если исходный запрос находится в памяти (shared pool)
и оптимизированный запрос находится в памяти (shared pool)
Перейдите в директорию sqlt/utl где установлен инструмент SQLT
Запустите скрипт coe_xfr_sql_profile.sql с ролью SYSDBA и предоставьте исходные данные:
SQL_ID исходного запроса [SQL_ID]
PLAN_HASH_VALUE оптимизированного запроса [OPT_PLAN_HASH_VALUE]

SQL> connect / as sysdba
SQL> @coe_xfr_sql_profile.sql SQL_ID OPT_PLAN_HASH_VALUE

Будет создан скрипт coe_xfr_sql_profile_< SQL_ID >_< OPT_PLAN_HASH_VALUE >.sql

Если запрос использует литералы то можно модифицировать параметр
force=>TRUE (в скрипте) для применения плана ко всем запросам с литералами

Запустить скрипт coe_xfr_sql_profile_< SQL_ID >_< OPT_PLAN_HASH_VALUE >.sql
из под пользователя с ролью SYSDBA для окончательной фиксации плана

SQL> connect / as sysdba
SQL> @coe_xfr_sql_profile_< SQL_ID >_< OPT_PLAN_HASH_VALUE >.sql

Проверить закрепление нового плана для исходного SQL_ID

select distinct p.name sql_profile_name, s.sql_id, p.category, p.status
from DBA_SQL_PROFILES p,
     DBA_HIST_SQLSTAT s
where p.name = s.sql_profile
and s.sql_id in ('&SQL_ID');

Проверить использование нового SQL Profile для исходного SQL_ID:

col sql_profile format a30
col sql_patch format a28
col sql_plan_baseline format a30
col last_load_time format a20
col last_active_time format a20
select INST_ID,
       SQL_ID,
       CHILD_NUMBER,
       PLAN_HASH_VALUE,
       SQL_PROFILE,
       SQL_PATCH,
       SQL_PLAN_BASELINE,
       LAST_LOAD_TIME,
       LAST_ACTIVE_TIME
from GV$SQL where SQL_ID in ('&SQL_ID');

Пример:

Создаем SQL Profile

Связываем sql_id с лучшим plan_hash_value

@coe_xfr_sql_profile.sql

SQL> @coe_xfr_sql_profile.sql try7b95kv87a3 4809245315

SQL> @coe_xfr_sql_profile_try7b95kv87a3_4809245315.sql

Смотрим план:

select SQL_ID, CHILD_NUMBER, PLAN_HASH_VALUE, SQL_PROFILE FROM V$SQL where sql_id='&SQL_ID';

select * from table(dbms_xplan.display_cursor('try7b95kv87a3',null,'ADVANCED ALLSTATS LAST'));

Убеждаемся, что он использует новый PLAN_HASH_VALUE.

Если нет, то делаем flush the HASH VALUE:

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like 'try7b95kv87a3';

ADDRESS HASH_VALUE
---------------- ----------
00000000A5D6BEE7 8756491057

SQL> exec DBMS_SHARED_POOL.PURGE ('00000000A5D6BEE7, 8756491057', 'C');

PL/SQL procedure successfully completed.

SQL>

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like 'try7b95kv87a3';

select name from dba_sql_profiles;

вторник, 21 июля 2009 г.

Параметры инициализации 12.2

Linux

SQL> SET PAGESIZE 10000
SET LINESIZE 10000
select name, ISSES_MODIFIABLE, ISSYS_MODIFIABLE, ISINSTANCE_MODIFIABLE, value
from v$parameter;

NAME                                        ISSES ISSYS_MOD ISINS VALUE
-------------------------------------------------------------------------------- ----- --------- ----- ---------------------------------------------------------------------------------------------------------------
DBFIPS_140                                   FALSE FALSE   FALSE FALSE
O7_DICTIONARY_ACCESSIBILITY                           FALSE FALSE   FALSE FALSE
active_instance_count                               FALSE FALSE   FALSE
allow_global_dblinks                               FALSE IMMEDIATE TRUE FALSE
allow_group_access_to_sga                           FALSE FALSE   FALSE FALSE
approx_for_aggregation                               TRUE IMMEDIATE TRUE FALSE
approx_for_count_distinct                           TRUE IMMEDIATE TRUE FALSE
approx_for_percentile                               TRUE IMMEDIATE TRUE none
aq_tm_processes                                FALSE IMMEDIATE TRUE 1
archive_lag_target                               FALSE IMMEDIATE TRUE 0
asm_diskgroups                                   FALSE IMMEDIATE TRUE
asm_diskstring                                   TRUE IMMEDIATE TRUE
asm_io_processes                               FALSE IMMEDIATE TRUE 20
asm_power_limit                                TRUE IMMEDIATE TRUE 1
asm_preferred_read_failure_groups                       FALSE IMMEDIATE TRUE
audit_file_dest                                FALSE DEFERRED TRUE /u01/app/oracle/admin/TESTDB_OMEGA/adump
audit_sys_operations                               FALSE FALSE   FALSE TRUE
audit_syslog_level                               FALSE FALSE   FALSE
audit_trail                                   FALSE FALSE   FALSE DB
autotask_max_active_pdbs                           FALSE IMMEDIATE TRUE 2
awr_pdb_autoflush_enabled                           FALSE IMMEDIATE TRUE FALSE
awr_snapshot_time_offset                           FALSE IMMEDIATE TRUE 0
background_core_dump                               FALSE IMMEDIATE TRUE partial
background_dump_dest                               FALSE IMMEDIATE TRUE /u01/app/oracle/product/12.2.0.1/rdbms/log
backup_tape_io_slaves                               FALSE DEFERRED TRUE FALSE
bitmap_merge_area_size                               FALSE FALSE   FALSE 1048576
blank_trimming                                   FALSE FALSE   FALSE FALSE
buffer_pool_keep                               FALSE FALSE   FALSE
buffer_pool_recycle                               FALSE FALSE   FALSE
cdb_cluster                                   FALSE FALSE   FALSE FALSE
cdb_cluster_name                               FALSE FALSE   FALSE TESTDB_OMEGA
cell_offload_compaction                            TRUE IMMEDIATE TRUE ADAPTIVE
cell_offload_decryption                            FALSE IMMEDIATE TRUE TRUE
cell_offload_parameters                            TRUE IMMEDIATE TRUE
cell_offload_plan_display                           TRUE IMMEDIATE TRUE AUTO
cell_offload_processing                            TRUE IMMEDIATE TRUE TRUE
cell_offloadgroup_name                               TRUE IMMEDIATE TRUE
circuits                                   FALSE IMMEDIATE TRUE
client_result_cache_lag                            FALSE FALSE   FALSE 3000
client_result_cache_size                           FALSE FALSE   FALSE 0
clonedb                                    FALSE FALSE   FALSE FALSE
clonedb_dir                                   FALSE FALSE   FALSE
cluster_database                               FALSE FALSE   FALSE TRUE
cluster_database_instances                           FALSE FALSE   FALSE 1
cluster_interconnects                               FALSE FALSE   FALSE
commit_logging                                   TRUE IMMEDIATE TRUE
commit_point_strength                               FALSE FALSE   FALSE 1
commit_wait                                   TRUE IMMEDIATE TRUE
commit_write                                   TRUE IMMEDIATE TRUE
common_user_prefix                               FALSE FALSE   FALSE C##
compatible                                   FALSE FALSE   FALSE 12.2.0
connection_brokers                               FALSE IMMEDIATE TRUE ((TYPE=DEDICATED)(BROKERS=1)), ((TYPE=EMON)(BROKERS=1))
containers_parallel_degree                           TRUE IMMEDIATE TRUE 65535
control_file_record_keep_time                           FALSE IMMEDIATE TRUE 7
control_files                                   FALSE FALSE   FALSE +DATA/TESTDB_OMEGA/CONTROLFILE/current.298.972512951, +DATA/TESTDB_OMEGA/CONTROLFILE/current.297.972512953
control_management_pack_access                           FALSE IMMEDIATE TRUE DIAGNOSTIC+TUNING
core_dump_dest                                   FALSE IMMEDIATE TRUE /u01/app/oracle/diag/rdbms/testdb_omega/TESTDB1/cdump
cpu_count                                   FALSE IMMEDIATE TRUE 2
create_bitmap_area_size                            FALSE FALSE   FALSE 8388608
create_stored_outlines                               TRUE IMMEDIATE TRUE
cursor_bind_capture_destination                        TRUE IMMEDIATE TRUE memory+disk
cursor_invalidation                               TRUE IMMEDIATE TRUE IMMEDIATE
cursor_sharing                                   TRUE IMMEDIATE TRUE EXACT
cursor_space_for_time                               FALSE FALSE   FALSE FALSE
data_guard_sync_latency                            FALSE IMMEDIATE TRUE 0
data_transfer_cache_size                           FALSE IMMEDIATE TRUE 0
db_16k_cache_size                               FALSE IMMEDIATE TRUE 0
db_2k_cache_size                               FALSE IMMEDIATE TRUE 0
db_32k_cache_size                               FALSE IMMEDIATE TRUE 0
db_4k_cache_size                               FALSE IMMEDIATE TRUE 0
db_8k_cache_size                               FALSE IMMEDIATE TRUE 0
db_big_table_cache_percent_target                       FALSE IMMEDIATE TRUE 0
db_block_buffers                               FALSE FALSE   FALSE 0
db_block_checking                               FALSE IMMEDIATE TRUE FALSE
db_block_checksum                               FALSE IMMEDIATE TRUE TYPICAL
db_block_size                                   FALSE FALSE   FALSE 8192
db_cache_advice                                FALSE IMMEDIATE TRUE ON
db_cache_size                                   FALSE IMMEDIATE TRUE 0
db_create_file_dest                               TRUE IMMEDIATE TRUE +DATA
db_create_online_log_dest_1                           TRUE IMMEDIATE TRUE
db_create_online_log_dest_2                           TRUE IMMEDIATE TRUE
db_create_online_log_dest_3                           TRUE IMMEDIATE TRUE
db_create_online_log_dest_4                           TRUE IMMEDIATE TRUE
db_create_online_log_dest_5                           TRUE IMMEDIATE TRUE
db_domain                                   FALSE FALSE   FALSE
db_file_multiblock_read_count                           TRUE IMMEDIATE TRUE 128
db_file_name_convert                               TRUE FALSE   FALSE
db_files                                   FALSE FALSE   FALSE 200
db_flash_cache_file                               FALSE IMMEDIATE TRUE
db_flash_cache_size                               FALSE IMMEDIATE TRUE 0
db_flashback_retention_target                           FALSE IMMEDIATE FALSE 1440
db_index_compression_inheritance                       TRUE IMMEDIATE TRUE NONE
db_keep_cache_size                               FALSE IMMEDIATE TRUE 0
db_lost_write_protect                               FALSE IMMEDIATE TRUE NONE
db_name                                    FALSE FALSE   FALSE TESTDB
db_performance_profile                               FALSE FALSE   FALSE
db_recovery_file_dest                               FALSE IMMEDIATE FALSE +DATA
db_recovery_file_dest_size                           FALSE IMMEDIATE FALSE 4294967296
db_recycle_cache_size                               FALSE IMMEDIATE TRUE 0
db_securefile                                   TRUE IMMEDIATE TRUE PREFERRED
db_ultra_safe                                   FALSE FALSE   FALSE OFF
db_unique_name                                   FALSE FALSE   FALSE TESTDB_OMEGA
db_unrecoverable_scn_tracking                           TRUE IMMEDIATE TRUE TRUE
db_writer_processes                               FALSE FALSE   FALSE 1
dbwr_io_slaves                                   FALSE FALSE   FALSE 0
ddl_lock_timeout                               TRUE IMMEDIATE TRUE 0
default_sharing                                TRUE IMMEDIATE TRUE metadata
deferred_segment_creation                           TRUE IMMEDIATE TRUE TRUE
dg_broker_config_file1                               FALSE IMMEDIATE TRUE /u01/app/oracle/product/12.2.0.1/dbs/dr1TESTDB_OMEGA.dat
dg_broker_config_file2                               FALSE IMMEDIATE TRUE /u01/app/oracle/product/12.2.0.1/dbs/dr2TESTDB_OMEGA.dat
dg_broker_start                                FALSE IMMEDIATE FALSE FALSE
diagnostic_dest                                FALSE IMMEDIATE TRUE /u01/app/oracle
disable_pdb_feature                               FALSE IMMEDIATE TRUE 0
disk_asynch_io                                   FALSE FALSE   FALSE TRUE
dispatchers                                   FALSE IMMEDIATE TRUE (PROTOCOL=TCP) (SERVICE=TESTDBXDB)
distributed_lock_timeout                           FALSE FALSE   FALSE 60
dml_locks                                   FALSE FALSE   FALSE 2076
dnfs_batch_size                                FALSE FALSE   FALSE 4096
dst_upgrade_insert_conv                            TRUE IMMEDIATE TRUE TRUE
enable_automatic_maintenance_pdb                       FALSE IMMEDIATE TRUE TRUE
enable_ddl_logging                               TRUE IMMEDIATE TRUE FALSE
enable_dnfs_dispatcher                               FALSE FALSE   FALSE FALSE
enable_goldengate_replication                           FALSE IMMEDIATE FALSE FALSE
enable_pluggable_database                           FALSE FALSE   FALSE FALSE
enabled_PDBs_on_standby                            FALSE IMMEDIATE TRUE *
encrypt_new_tablespaces                            FALSE IMMEDIATE TRUE CLOUD_ONLY
event                                       FALSE FALSE   FALSE
exafusion_enabled                               FALSE FALSE   FALSE 1
external_keystore_credential_location                       FALSE FALSE   FALSE
fal_client                                   FALSE IMMEDIATE TRUE
fal_server                                   FALSE IMMEDIATE TRUE
fast_start_io_target                               FALSE IMMEDIATE TRUE 0
fast_start_mttr_target                               FALSE IMMEDIATE TRUE 0
fast_start_parallel_rollback                           FALSE IMMEDIATE TRUE LOW
file_mapping                                   FALSE IMMEDIATE TRUE FALSE
fileio_network_adapters                            FALSE FALSE   FALSE
filesystemio_options                               FALSE FALSE   FALSE none
fixed_date                                   FALSE IMMEDIATE TRUE
gcs_server_processes                               FALSE FALSE   FALSE 1
global_names                                   TRUE IMMEDIATE TRUE FALSE
global_txn_processes                               FALSE IMMEDIATE TRUE 1
hash_area_size                                   TRUE FALSE   FALSE 131072
heat_map                                   TRUE IMMEDIATE TRUE OFF
hi_shared_memory_address                           FALSE FALSE   FALSE 0
hs_autoregister                                FALSE IMMEDIATE TRUE TRUE
ifile                                       FALSE FALSE   FALSE
inmemory_adg_enabled                               FALSE IMMEDIATE TRUE TRUE
inmemory_clause_default                            TRUE IMMEDIATE TRUE
inmemory_expressions_usage                           FALSE IMMEDIATE TRUE ENABLE
inmemory_force                                   FALSE IMMEDIATE TRUE DEFAULT
inmemory_max_populate_servers                           FALSE IMMEDIATE TRUE 0
inmemory_query                                   TRUE IMMEDIATE TRUE ENABLE
inmemory_size                                   FALSE IMMEDIATE TRUE 0
inmemory_trickle_repopulate_servers_percent                   FALSE IMMEDIATE TRUE 1
inmemory_virtual_columns                           FALSE IMMEDIATE TRUE MANUAL
instance_abort_delay_time                           FALSE IMMEDIATE TRUE 0
instance_groups                                FALSE FALSE   FALSE
instance_mode                                   FALSE FALSE   FALSE READ-WRITE
instance_name                                   FALSE FALSE   FALSE TESTDB1
instance_number                                FALSE FALSE   FALSE 1
instance_type                                   FALSE FALSE   FALSE RDBMS
instant_restore                                FALSE FALSE   FALSE FALSE
java_jit_enabled                               TRUE IMMEDIATE TRUE TRUE
java_max_sessionspace_size                           FALSE FALSE   FALSE 0
java_pool_size                                   FALSE IMMEDIATE TRUE 0
java_restrict                                   FALSE FALSE   FALSE none
java_soft_sessionspace_limit                           FALSE FALSE   FALSE 0
job_queue_processes                               FALSE IMMEDIATE TRUE 4000
large_pool_size                                FALSE IMMEDIATE TRUE 0
ldap_directory_access                               FALSE IMMEDIATE TRUE NONE
ldap_directory_sysauth                               FALSE FALSE   FALSE no
license_max_sessions                               FALSE IMMEDIATE TRUE 0
license_max_users                               FALSE IMMEDIATE TRUE 0
license_sessions_warning                           FALSE IMMEDIATE TRUE 0
listener_networks                               FALSE IMMEDIATE TRUE
local_listener                                   FALSE IMMEDIATE TRUE   (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.10.101)(PORT=1521))
lock_name_space                                FALSE FALSE   FALSE
lock_sga                                   FALSE FALSE   FALSE FALSE
log_archive_config                               FALSE IMMEDIATE TRUE
log_archive_dest                               FALSE IMMEDIATE TRUE
log_archive_dest_1                               TRUE IMMEDIATE TRUE
log_archive_dest_10                               TRUE IMMEDIATE TRUE
log_archive_dest_11                               TRUE IMMEDIATE TRUE
log_archive_dest_12                               TRUE IMMEDIATE TRUE
log_archive_dest_13                               TRUE IMMEDIATE TRUE
log_archive_dest_14                               TRUE IMMEDIATE TRUE
log_archive_dest_15                               TRUE IMMEDIATE TRUE
log_archive_dest_16                               TRUE IMMEDIATE TRUE
log_archive_dest_17                               TRUE IMMEDIATE TRUE
log_archive_dest_18                               TRUE IMMEDIATE TRUE
log_archive_dest_19                               TRUE IMMEDIATE TRUE
log_archive_dest_2                               TRUE IMMEDIATE TRUE
log_archive_dest_20                               TRUE IMMEDIATE TRUE
log_archive_dest_21                               TRUE IMMEDIATE TRUE
log_archive_dest_22                               TRUE IMMEDIATE TRUE
log_archive_dest_23                               TRUE IMMEDIATE TRUE
log_archive_dest_24                               TRUE IMMEDIATE TRUE
log_archive_dest_25                               TRUE IMMEDIATE TRUE
log_archive_dest_26                               TRUE IMMEDIATE TRUE
log_archive_dest_27                               TRUE IMMEDIATE TRUE
log_archive_dest_28                               TRUE IMMEDIATE TRUE
log_archive_dest_29                               TRUE IMMEDIATE TRUE
log_archive_dest_3                               TRUE IMMEDIATE TRUE
log_archive_dest_30                               TRUE IMMEDIATE TRUE
log_archive_dest_31                               TRUE IMMEDIATE TRUE
log_archive_dest_4                               TRUE IMMEDIATE TRUE
log_archive_dest_5                               TRUE IMMEDIATE TRUE
log_archive_dest_6                               TRUE IMMEDIATE TRUE
log_archive_dest_7                               TRUE IMMEDIATE TRUE
log_archive_dest_8                               TRUE IMMEDIATE TRUE
log_archive_dest_9                               TRUE IMMEDIATE TRUE
log_archive_dest_state_1                           TRUE IMMEDIATE TRUE enable
log_archive_dest_state_10                           TRUE IMMEDIATE TRUE enable
log_archive_dest_state_11                           TRUE IMMEDIATE TRUE enable
log_archive_dest_state_12                           TRUE IMMEDIATE TRUE enable
log_archive_dest_state_13                           TRUE IMMEDIATE TRUE enable
log_archive_dest_state_14                           TRUE IMMEDIATE TRUE enable
log_archive_dest_state_15                           TRUE IMMEDIATE TRUE enable
log_archive_dest_state_16                           TRUE IMMEDIATE TRUE enable
log_archive_dest_state_17                           TRUE IMMEDIATE TRUE enable
log_archive_dest_state_18                           TRUE IMMEDIATE TRUE enable
log_archive_dest_state_19                           TRUE IMMEDIATE TRUE enable
log_archive_dest_state_2                           TRUE IMMEDIATE TRUE enable
log_archive_dest_state_20                           TRUE IMMEDIATE TRUE enable
log_archive_dest_state_21                           TRUE IMMEDIATE TRUE enable
log_archive_dest_state_22                           TRUE IMMEDIATE TRUE enable
log_archive_dest_state_23                           TRUE IMMEDIATE TRUE enable
log_archive_dest_state_24                           TRUE IMMEDIATE TRUE enable
log_archive_dest_state_25                           TRUE IMMEDIATE TRUE enable
log_archive_dest_state_26                           TRUE IMMEDIATE TRUE enable
log_archive_dest_state_27                           TRUE IMMEDIATE TRUE enable
log_archive_dest_state_28                           TRUE IMMEDIATE TRUE enable
log_archive_dest_state_29                           TRUE IMMEDIATE TRUE enable
log_archive_dest_state_3                           TRUE IMMEDIATE TRUE enable
log_archive_dest_state_30                           TRUE IMMEDIATE TRUE enable
log_archive_dest_state_31                           TRUE IMMEDIATE TRUE enable
log_archive_dest_state_4                           TRUE IMMEDIATE TRUE enable
log_archive_dest_state_5                           TRUE IMMEDIATE TRUE enable
log_archive_dest_state_6                           TRUE IMMEDIATE TRUE enable
log_archive_dest_state_7                           TRUE IMMEDIATE TRUE enable
log_archive_dest_state_8                           TRUE IMMEDIATE TRUE enable
log_archive_dest_state_9                           TRUE IMMEDIATE TRUE enable
log_archive_duplex_dest                            FALSE IMMEDIATE TRUE
log_archive_format                               FALSE FALSE   FALSE %t_%s_%r.dbf
log_archive_max_processes                           FALSE IMMEDIATE TRUE 4
log_archive_min_succeed_dest                           TRUE IMMEDIATE TRUE 1
log_archive_start                               FALSE FALSE   FALSE FALSE
log_archive_trace                               FALSE IMMEDIATE TRUE 0
log_buffer                                   FALSE FALSE   FALSE 7806976
log_checkpoint_interval                            FALSE IMMEDIATE TRUE 0
log_checkpoint_timeout                               FALSE IMMEDIATE TRUE 1800
log_checkpoints_to_alert                           FALSE IMMEDIATE TRUE FALSE
log_file_name_convert                               FALSE FALSE   FALSE
long_module_action                               FALSE IMMEDIATE TRUE TRUE
max_datapump_jobs_per_pdb                           FALSE IMMEDIATE TRUE 100
max_dispatchers                                FALSE IMMEDIATE TRUE
max_dump_file_size                               TRUE IMMEDIATE TRUE unlimited
max_idle_time                                   FALSE IMMEDIATE TRUE 0
max_iops                                   FALSE IMMEDIATE TRUE 0
max_mbps                                   FALSE IMMEDIATE TRUE 0
max_pdbs                                   FALSE IMMEDIATE FALSE 4098
max_shared_servers                               FALSE IMMEDIATE TRUE
max_string_size                                FALSE IMMEDIATE FALSE STANDARD
memory_max_target                               FALSE FALSE   FALSE 0
memory_target                                   FALSE IMMEDIATE TRUE 0
nls_calendar                                   TRUE FALSE   FALSE
nls_comp                                   TRUE FALSE   FALSE BINARY
nls_currency                                   TRUE FALSE   FALSE
nls_date_format                                TRUE FALSE   FALSE
nls_date_language                               TRUE FALSE   FALSE
nls_dual_currency                               TRUE FALSE   FALSE
nls_iso_currency                               TRUE FALSE   FALSE
nls_language                                   TRUE FALSE   FALSE AMERICAN
nls_length_semantics                               TRUE IMMEDIATE TRUE BYTE
nls_nchar_conv_excp                               TRUE IMMEDIATE TRUE FALSE
nls_numeric_characters                               TRUE FALSE   FALSE
nls_sort                                   TRUE FALSE   FALSE
nls_territory                                   TRUE FALSE   FALSE AMERICA
nls_time_format                                TRUE FALSE   FALSE
nls_time_tz_format                               TRUE FALSE   FALSE
nls_timestamp_format                               TRUE FALSE   FALSE
nls_timestamp_tz_format                            TRUE FALSE   FALSE
noncdb_compatible                               FALSE FALSE   FALSE FALSE
object_cache_max_size_percent                           TRUE DEFERRED TRUE 10
object_cache_optimal_size                           TRUE DEFERRED TRUE 10240000
ofs_threads                                   FALSE IMMEDIATE TRUE 4
olap_page_pool_size                               TRUE DEFERRED TRUE 0
one_step_plugin_for_pdb_with_tde                       FALSE IMMEDIATE TRUE FALSE
open_cursors                                   FALSE IMMEDIATE TRUE 300
open_links                                   FALSE FALSE   FALSE 4
open_links_per_instance                            FALSE FALSE   FALSE 4
optimizer_adaptive_plans                           TRUE IMMEDIATE TRUE TRUE
optimizer_adaptive_reporting_only                       TRUE IMMEDIATE TRUE FALSE
optimizer_adaptive_statistics                           TRUE IMMEDIATE TRUE FALSE
optimizer_capture_sql_plan_baselines                       TRUE IMMEDIATE TRUE FALSE
optimizer_dynamic_sampling                           TRUE IMMEDIATE TRUE 2
optimizer_features_enable                           TRUE IMMEDIATE TRUE 12.2.0.1
optimizer_index_caching                            TRUE IMMEDIATE TRUE 0
optimizer_index_cost_adj                           TRUE IMMEDIATE TRUE 100
optimizer_inmemory_aware                           TRUE IMMEDIATE TRUE TRUE
optimizer_mode                                   TRUE IMMEDIATE TRUE ALL_ROWS
optimizer_secure_view_merging                           FALSE IMMEDIATE TRUE TRUE
optimizer_use_invisible_indexes                        TRUE IMMEDIATE TRUE FALSE
optimizer_use_pending_statistics                       TRUE IMMEDIATE TRUE FALSE
optimizer_use_sql_plan_baselines                       TRUE IMMEDIATE TRUE TRUE
os_authent_prefix                               FALSE FALSE   FALSE ops$
os_roles                                   FALSE FALSE   FALSE FALSE
outbound_dblink_protocols                           FALSE IMMEDIATE TRUE ALL
parallel_adaptive_multi_user                           FALSE IMMEDIATE TRUE FALSE
parallel_degree_limit                               TRUE IMMEDIATE TRUE CPU
parallel_degree_policy                               TRUE IMMEDIATE TRUE MANUAL
parallel_execution_message_size                        FALSE FALSE   FALSE 16384
parallel_force_local                               TRUE IMMEDIATE TRUE FALSE
parallel_instance_group                            TRUE IMMEDIATE TRUE
parallel_max_servers                               FALSE IMMEDIATE TRUE 80
parallel_min_percent                               TRUE FALSE   FALSE 0
parallel_min_servers                               FALSE IMMEDIATE TRUE 8
parallel_min_time_threshold                           TRUE IMMEDIATE TRUE AUTO
parallel_servers_target                            FALSE IMMEDIATE TRUE 32
parallel_threads_per_cpu                           FALSE IMMEDIATE TRUE 2
pdb_file_name_convert                               TRUE IMMEDIATE TRUE
pdb_lockdown                                   TRUE IMMEDIATE TRUE
pdb_os_credential                               FALSE FALSE   FALSE
permit_92_wrap_format                               FALSE FALSE   FALSE TRUE
pga_aggregate_limit                               FALSE IMMEDIATE TRUE 2147483648
pga_aggregate_target                               FALSE IMMEDIATE TRUE 598736896
plscope_settings                               TRUE IMMEDIATE TRUE IDENTIFIERS:NONE
plsql_ccflags                                   TRUE IMMEDIATE TRUE
plsql_code_type                                TRUE IMMEDIATE TRUE INTERPRETED
plsql_debug                                   TRUE IMMEDIATE TRUE FALSE
plsql_optimize_level                               TRUE IMMEDIATE TRUE 2
plsql_v2_compatibility                               TRUE IMMEDIATE TRUE FALSE
plsql_warnings                                   TRUE IMMEDIATE TRUE DISABLE:ALL
pre_page_sga                                   FALSE FALSE   FALSE TRUE
processes                                   FALSE FALSE   FALSE 300
processor_group_name                               FALSE FALSE   FALSE
query_rewrite_enabled                               TRUE IMMEDIATE TRUE TRUE
query_rewrite_integrity                            TRUE IMMEDIATE TRUE enforced
rdbms_server_dn                                FALSE FALSE   FALSE
read_only_open_delayed                               FALSE FALSE   FALSE FALSE
recovery_parallelism                               FALSE FALSE   FALSE 0
recyclebin                                   TRUE DEFERRED TRUE on
redo_transport_user                               FALSE IMMEDIATE TRUE
remote_dependencies_mode                           TRUE IMMEDIATE TRUE TIMESTAMP
remote_listener                                FALSE IMMEDIATE TRUE   scan12c:1521
remote_login_passwordfile                           FALSE FALSE   FALSE EXCLUSIVE
remote_os_authent                               FALSE FALSE   FALSE FALSE
remote_os_roles                                FALSE FALSE   FALSE FALSE
remote_recovery_file_dest                           FALSE IMMEDIATE FALSE
replication_dependency_tracking                        FALSE FALSE   FALSE TRUE
resource_limit                                   FALSE IMMEDIATE TRUE TRUE
resource_manage_goldengate                           FALSE IMMEDIATE TRUE FALSE
resource_manager_cpu_allocation                        FALSE IMMEDIATE TRUE 2
resource_manager_plan                               FALSE IMMEDIATE TRUE SCHEDULER[0x4AC5]:DEFAULT_MAINTENANCE_PLAN
result_cache_max_result                            FALSE IMMEDIATE TRUE 5
result_cache_max_size                               FALSE IMMEDIATE TRUE 8978432
result_cache_mode                               TRUE IMMEDIATE TRUE MANUAL
result_cache_remote_expiration                           TRUE IMMEDIATE TRUE 0
resumable_timeout                               TRUE IMMEDIATE TRUE 0
rollback_segments                               FALSE FALSE   FALSE
sec_case_sensitive_logon                           FALSE IMMEDIATE TRUE TRUE
sec_max_failed_login_attempts                           FALSE FALSE   FALSE 3
sec_protocol_error_further_action                       FALSE IMMEDIATE TRUE (DROP,3)
sec_protocol_error_trace_action                        FALSE IMMEDIATE TRUE TRACE
sec_return_server_release_banner                       FALSE FALSE   FALSE FALSE
serial_reuse                                   FALSE FALSE   FALSE disable
service_names                                   FALSE IMMEDIATE TRUE TESTDB_OMEGA
session_cached_cursors                               TRUE DEFERRED TRUE 50
session_max_open_files                               FALSE FALSE   FALSE 10
sessions                                   FALSE IMMEDIATE TRUE 472
sga_max_size                                   FALSE FALSE   FALSE 1795162112
sga_min_size                                   FALSE IMMEDIATE TRUE 0
sga_target                                   FALSE IMMEDIATE TRUE 1795162112
shadow_core_dump                               FALSE IMMEDIATE TRUE partial
shared_memory_address                               FALSE FALSE   FALSE 0
shared_pool_reserved_size                           FALSE FALSE   FALSE 21810380
shared_pool_size                               FALSE IMMEDIATE TRUE 0
shared_server_sessions                               FALSE IMMEDIATE TRUE
shared_servers                                   FALSE IMMEDIATE TRUE 1
shrd_dupl_table_refresh_rate                           FALSE IMMEDIATE TRUE 60
skip_unusable_indexes                               TRUE IMMEDIATE TRUE TRUE
smtp_out_server                                TRUE IMMEDIATE TRUE
sort_area_retained_size                            TRUE DEFERRED TRUE 0
sort_area_size                                   TRUE DEFERRED TRUE 65536
spatial_vector_acceleration                           TRUE IMMEDIATE TRUE FALSE
spfile                                       FALSE IMMEDIATE TRUE +DATA/TESTDB_OMEGA/PARAMETERFILE/spfile.305.972520367
sql92_security                                   FALSE FALSE   FALSE TRUE
sql_trace                                   TRUE IMMEDIATE TRUE FALSE
sqltune_category                               TRUE IMMEDIATE TRUE DEFAULT
standby_archive_dest                               FALSE IMMEDIATE TRUE ?#/dbs/arch
standby_db_preserve_states                           FALSE FALSE   FALSE NONE
standby_file_management                            FALSE IMMEDIATE TRUE MANUAL
star_transformation_enabled                           TRUE IMMEDIATE TRUE FALSE
statistics_level                               TRUE IMMEDIATE TRUE TYPICAL
streams_pool_size                               FALSE IMMEDIATE TRUE 0
tape_asynch_io                                   FALSE FALSE   FALSE TRUE
target_pdbs                                   FALSE FALSE   FALSE 0
temp_undo_enabled                               TRUE IMMEDIATE TRUE FALSE
thread                                       FALSE IMMEDIATE TRUE 1
threaded_execution                               FALSE FALSE   FALSE FALSE
timed_os_statistics                               TRUE IMMEDIATE TRUE 0
timed_statistics                               TRUE IMMEDIATE TRUE TRUE
trace_enabled                                   FALSE IMMEDIATE TRUE TRUE
tracefile_identifier                               TRUE FALSE   FALSE
transactions                                   FALSE FALSE   FALSE 519
transactions_per_rollback_segment                       FALSE FALSE   FALSE 5
undo_management                                FALSE FALSE   FALSE AUTO
undo_retention                                   FALSE IMMEDIATE TRUE 900
undo_tablespace                                FALSE IMMEDIATE TRUE UNDOTBS1
unified_audit_sga_queue_size                           FALSE FALSE   FALSE 1048576
uniform_log_timestamp_format                           FALSE IMMEDIATE TRUE TRUE
use_dedicated_broker                               FALSE IMMEDIATE TRUE FALSE
use_large_pages                                FALSE FALSE   FALSE TRUE
user_dump_dest                                   FALSE IMMEDIATE TRUE /u01/app/oracle/product/12.2.0.1/rdbms/log
utl_file_dir                                   FALSE FALSE   FALSE
workarea_size_policy                               TRUE IMMEDIATE TRUE AUTO
xml_db_events                                   TRUE IMMEDIATE TRUE enable

417 rows selected.

SQL>

В кластеризованной среде , в которой применяется Oracle RAC , все экземпляры совместно используют тот же самый файл SPFILE

Пример такого файла для кластера:

TESTDB2.__data_transfer_cache_size=0
TESTDB1.__data_transfer_cache_size=0
TESTDB2.__db_cache_size=1191182336
TESTDB1.__db_cache_size=1207959552
TESTDB2.__inmemory_ext_roarea=0
TESTDB1.__inmemory_ext_roarea=0
TESTDB2.__inmemory_ext_rwarea=0
TESTDB1.__inmemory_ext_rwarea=0
TESTDB2.__java_pool_size=16777216
TESTDB1.__java_pool_size=16777216
TESTDB2.__large_pool_size=33554432
TESTDB1.__large_pool_size=33554432
TESTDB1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
TESTDB2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
TESTDB2.__pga_aggregate_target=603979776
TESTDB1.__pga_aggregate_target=603979776
TESTDB2.__sga_target=1795162112
TESTDB1.__sga_target=1795162112
TESTDB2.__shared_io_pool_size=83886080
TESTDB1.__shared_io_pool_size=83886080
TESTDB2.__shared_pool_size=452984832
TESTDB1.__shared_pool_size=436207616
TESTDB2.__streams_pool_size=0
TESTDB1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/TESTDB_OMEGA/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='12.2.0'
*.control_files='+DATA/TESTDB_OMEGA/CONTROLFILE/current.298.972512951','+DATA/TESTDB_OMEGA/CONTROLFILE/current.297.972512953'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_name='TESTDB'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=4294967296
*.db_unique_name='TESTDB_OMEGA'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=TESTDBXDB)'
family:dw_helper.instance_mode='read-only'
TESTDB1.instance_number=1
TESTDB2.instance_number=2
*.local_listener='-oraagent-dummy-'
*.log_archive_format='%t_%s_%r.dbf'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=571m
*.processes=300
*.remote_login_passwordfile='exclusive'
*.sga_target=1712m
TESTDB2.thread=2
TESTDB1.thread=1
*.undo_tablespace='UNDOTBS1'
TESTDB1.undo_tablespace='UNDOTBS1'
TESTDB2.undo_tablespace='UNDOTBS2'

Имена параметров, общих для всех экземпляров в кластере, будут начинаться с символов *.
Имена параметров, характерных для отдельного экземпляра, вроде INSTANCE_NUMBER и THREAD, должны быть предварены именем экземпляра
(идентификатором Oracle SID).

Специальные параметры кластера:

instance_number
instance_group
thread
cluster_database
cluster_database_instances
cluster_interconnects
remote_listener
local_listener
parallel_instance
max_commit_propagation_delay

Параметры имеющие одинаковое значение для всех инстансов:

active_instance_count
archive_lag_target
compatible
cluster_database
cluster_database_instances
cluster_interconnects
control_files
db_block_size
db_domain
db_files
db_name
db_recovery_file_dest
db_recovery_file_dest_size
db_unique_name
dml_locks                     -- when 0
instance_type                -- rdbms or asm
max_commit_propagation_delay
parallel_max_servers
remote_login_password_file
trace_enabled
undo_management

Параметры модифицируемые на уровне базы данных:

-- string
select 'alter system set '|| name ||' = '''||value||''' scope=spfile sid=''*'''||';' sql_cmd from SYS.V_$PARAMETER where ISINSTANCE_MODIFIABLE = 'FALSE' and type in (2,4) and value is not null and substr(name,1,1)<>'_'
union all
select 'alter system set "'|| name ||'" = '''||value||''' scope=spfile sid=''*'''||';' sql_cmd from SYS.V_$PARAMETER where ISINSTANCE_MODIFIABLE = 'FALSE' and type in (2,4) and value is not null and substr(name,1,1)='_'
union all
-- integer
select 'alter system set '|| name ||' = '||value||' scope=spfile sid=''*'''||';' sql_cmd from SYS.V_$PARAMETER where ISINSTANCE_MODIFIABLE = 'FALSE' and type in (3,6) and value is not null and substr(name,1,1)<>'_'
union all
select 'alter system set "'|| name ||'" = '||value||' scope=spfile sid=''*'''||';' sql_cmd from SYS.V_$PARAMETER where ISINSTANCE_MODIFIABLE = 'FALSE' and type in (3,6) and value is not null and substr(name,1,1)='_'
union all
-- boolean
select 'alter system set '|| name ||' = '||value||' scope=spfile sid=''*'''||';' sql_cmd from SYS.V_$PARAMETER where ISINSTANCE_MODIFIABLE = 'FALSE' and type in (1) and value is not null and substr(name,1,1)<>'_'
union all
select 'alter system set "'|| name ||'" = '||value||' scope=spfile sid=''*'''||';' sql_cmd from SYS.V_$PARAMETER where ISINSTANCE_MODIFIABLE = 'FALSE' and type in (1) and value is not null and substr(name,1,1)='_'
order by 1;

alter system set DBFIPS_140 = FALSE scope=spfile sid='*';
alter system set O7_DICTIONARY_ACCESSIBILITY = FALSE scope=spfile sid='*';
alter system set allow_group_access_to_sga = FALSE scope=spfile sid='*';
alter system set audit_sys_operations = TRUE scope=spfile sid='*';
alter system set audit_trail = 'DB' scope=spfile sid='*';
alter system set bitmap_merge_area_size = 1048576 scope=spfile sid='*';
alter system set blank_trimming = FALSE scope=spfile sid='*';
alter system set cdb_cluster = FALSE scope=spfile sid='*';
alter system set cdb_cluster_name = 'TESTDB_OMEGA' scope=spfile sid='*';
alter system set client_result_cache_lag = 3000 scope=spfile sid='*';
alter system set client_result_cache_size = 0 scope=spfile sid='*';
alter system set clonedb = FALSE scope=spfile sid='*';
alter system set cluster_database = TRUE scope=spfile sid='*';
alter system set cluster_database_instances = 1 scope=spfile sid='*';
alter system set commit_point_strength = 1 scope=spfile sid='*';
alter system set common_user_prefix = 'C##' scope=spfile sid='*';
alter system set compatible = '12.2.0' scope=spfile sid='*';
alter system set control_files = '+DATA/TESTDB_OMEGA/CONTROLFILE/current.298.972512951, +DATA/TESTDB_OMEGA/CONTROLFILE/current.297.972512953' scope=spfile sid='*';
alter system set create_bitmap_area_size = 8388608 scope=spfile sid='*';
alter system set cursor_space_for_time = FALSE scope=spfile sid='*';
alter system set db_block_buffers = 0 scope=spfile sid='*';
alter system set db_block_size = 8192 scope=spfile sid='*';
alter system set db_files = 200 scope=spfile sid='*';
alter system set db_flashback_retention_target = 1440 scope=spfile sid='*';
alter system set db_name = 'TESTDB' scope=spfile sid='*';
alter system set db_recovery_file_dest = '+DATA' scope=spfile sid='*';
alter system set db_recovery_file_dest_size = 4294967296 scope=spfile sid='*';
alter system set db_ultra_safe = 'OFF' scope=spfile sid='*';
alter system set db_unique_name = 'TESTDB_OMEGA' scope=spfile sid='*';
alter system set db_writer_processes = 1 scope=spfile sid='*';
alter system set dbwr_io_slaves = 0 scope=spfile sid='*';
alter system set dg_broker_start = FALSE scope=spfile sid='*';
alter system set disk_asynch_io = TRUE scope=spfile sid='*';
alter system set distributed_lock_timeout = 60 scope=spfile sid='*';
alter system set dml_locks = 2076 scope=spfile sid='*';
alter system set dnfs_batch_size = 4096 scope=spfile sid='*';
alter system set enable_dnfs_dispatcher = FALSE scope=spfile sid='*';
alter system set enable_goldengate_replication = FALSE scope=spfile sid='*';
alter system set enable_pluggable_database = FALSE scope=spfile sid='*';
alter system set exafusion_enabled = 1 scope=spfile sid='*';
alter system set filesystemio_options = 'none' scope=spfile sid='*';
alter system set gcs_server_processes = 1 scope=spfile sid='*';
alter system set hash_area_size = 131072 scope=spfile sid='*';
alter system set hi_shared_memory_address = 0 scope=spfile sid='*';
alter system set instance_mode = 'READ-WRITE' scope=spfile sid='*';
alter system set instance_name = 'TESTDB1' scope=spfile sid='*';
alter system set instance_number = 1 scope=spfile sid='*';
alter system set instance_type = 'RDBMS' scope=spfile sid='*';
alter system set instant_restore = FALSE scope=spfile sid='*';
alter system set java_max_sessionspace_size = 0 scope=spfile sid='*';
alter system set java_restrict = 'none' scope=spfile sid='*';
alter system set java_soft_sessionspace_limit = 0 scope=spfile sid='*';
alter system set ldap_directory_sysauth = 'no' scope=spfile sid='*';
alter system set lock_sga = FALSE scope=spfile sid='*';
alter system set log_archive_format = '%t_%s_%r.dbf' scope=spfile sid='*';
alter system set log_archive_start = FALSE scope=spfile sid='*';
alter system set log_buffer = 7806976 scope=spfile sid='*';
alter system set max_pdbs = 4098 scope=spfile sid='*';
alter system set max_string_size = 'STANDARD' scope=spfile sid='*';
alter system set memory_max_target = 0 scope=spfile sid='*';
alter system set nls_comp = 'BINARY' scope=spfile sid='*';
alter system set nls_language = 'AMERICAN' scope=spfile sid='*';
alter system set nls_territory = 'AMERICA' scope=spfile sid='*';
alter system set noncdb_compatible = FALSE scope=spfile sid='*';
alter system set open_links = 4 scope=spfile sid='*';
alter system set open_links_per_instance = 4 scope=spfile sid='*';
alter system set os_authent_prefix = 'ops$' scope=spfile sid='*';
alter system set os_roles = FALSE scope=spfile sid='*';
alter system set parallel_execution_message_size = 16384 scope=spfile sid='*';
alter system set parallel_min_percent = 0 scope=spfile sid='*';
alter system set permit_92_wrap_format = TRUE scope=spfile sid='*';
alter system set pre_page_sga = TRUE scope=spfile sid='*';
alter system set processes = 300 scope=spfile sid='*';
alter system set read_only_open_delayed = FALSE scope=spfile sid='*';
alter system set recovery_parallelism = 0 scope=spfile sid='*';
alter system set remote_login_passwordfile = 'EXCLUSIVE' scope=spfile sid='*';
alter system set remote_os_authent = FALSE scope=spfile sid='*';
alter system set remote_os_roles = FALSE scope=spfile sid='*';
alter system set replication_dependency_tracking = TRUE scope=spfile sid='*';
alter system set sec_max_failed_login_attempts = 3 scope=spfile sid='*';
alter system set sec_return_server_release_banner = FALSE scope=spfile sid='*';
alter system set serial_reuse = 'disable' scope=spfile sid='*';
alter system set session_max_open_files = 10 scope=spfile sid='*';
alter system set sga_max_size = 1795162112 scope=spfile sid='*';
alter system set shared_memory_address = 0 scope=spfile sid='*';
alter system set shared_pool_reserved_size = 21810380 scope=spfile sid='*';
alter system set sql92_security = TRUE scope=spfile sid='*';
alter system set standby_db_preserve_states = 'NONE' scope=spfile sid='*';
alter system set tape_asynch_io = TRUE scope=spfile sid='*';
alter system set target_pdbs = 0 scope=spfile sid='*';
alter system set threaded_execution = FALSE scope=spfile sid='*';
alter system set transactions = 519 scope=spfile sid='*';
alter system set transactions_per_rollback_segment = 5 scope=spfile sid='*';
alter system set undo_management = 'AUTO' scope=spfile sid='*';
alter system set unified_audit_sga_queue_size = 1048576 scope=spfile sid='*';
alter system set use_large_pages = 'TRUE' scope=spfile sid='*';

96 rows selected.

SQL>

Параметры имеющие уникальные значения для всех инстансов:

instance_number
instance_name
thread
undo_tablespace/rollback_segments

Параметры модифицируемые на уровне экземпляра:

-- string
select 'alter system set '|| name ||' = '''||value||''' scope=spfile sid=''TESTDB1'''||';' sql_cmd from SYS.V_$PARAMETER where ISINSTANCE_MODIFIABLE = 'TRUE' and type in (2,4) and value is not null and substr(name,1,1)<>'_'
union all
select 'alter system set "'|| name ||'" = '''||value||''' scope=spfile sid=''TESTDB1'''||';' sql_cmd from SYS.V_$PARAMETER where ISINSTANCE_MODIFIABLE = 'TRUE' and type in (2,4) and value is not null and substr(name,1,1)='_'
union all
-- integer
select 'alter system set '|| name ||' = '||value||' scope=spfile sid=''TESTDB1'''||';' sql_cmd from SYS.V_$PARAMETER where ISINSTANCE_MODIFIABLE = 'TRUE' and type in (3,6) and value is not null and substr(name,1,1)<>'_'
union all
select 'alter system set "'|| name ||'" = '||value||' scope=spfile sid=''TESTDB1'''||';' sql_cmd from SYS.V_$PARAMETER where ISINSTANCE_MODIFIABLE = 'TRUE' and type in (3,6) and value is not null and substr(name,1,1)='_'
union all
-- boolean
select 'alter system set '|| name ||' = '||value||' scope=spfile sid=''TESTDB1'''||';' sql_cmd from SYS.V_$PARAMETER where ISINSTANCE_MODIFIABLE = 'TRUE' and type in (1) and value is not null and substr(name,1,1)<>'_'
union all
select 'alter system set "'|| name ||'" = '||value||' scope=spfile sid=''TESTDB1'''||';' sql_cmd from SYS.V_$PARAMETER where ISINSTANCE_MODIFIABLE = 'TRUE' and type in (1) and value is not null and substr(name,1,1)='_'
order by 1;

alter system set allow_global_dblinks = FALSE scope=spfile sid='TESTDB1';
alter system set approx_for_aggregation = FALSE scope=spfile sid='TESTDB1';
alter system set approx_for_count_distinct = FALSE scope=spfile sid='TESTDB1';
alter system set approx_for_percentile = 'none' scope=spfile sid='TESTDB1';
alter system set aq_tm_processes = 1 scope=spfile sid='TESTDB1';
alter system set archive_lag_target = 0 scope=spfile sid='TESTDB1';
alter system set asm_io_processes = 20 scope=spfile sid='TESTDB1';
alter system set asm_power_limit = 1 scope=spfile sid='TESTDB1';
alter system set audit_file_dest = '/u01/app/oracle/admin/TESTDB_OMEGA/adump' scope=spfile sid='TESTDB1';
alter system set autotask_max_active_pdbs = 2 scope=spfile sid='TESTDB1';
alter system set awr_pdb_autoflush_enabled = FALSE scope=spfile sid='TESTDB1';
alter system set awr_snapshot_time_offset = 0 scope=spfile sid='TESTDB1';
alter system set background_core_dump = 'partial' scope=spfile sid='TESTDB1';
alter system set background_dump_dest = '/u01/app/oracle/product/12.2.0.1/rdbms/log' scope=spfile sid='TESTDB1';
alter system set backup_tape_io_slaves = FALSE scope=spfile sid='TESTDB1';
alter system set cell_offload_compaction = 'ADAPTIVE' scope=spfile sid='TESTDB1';
alter system set cell_offload_decryption = TRUE scope=spfile sid='TESTDB1';
alter system set cell_offload_plan_display = 'AUTO' scope=spfile sid='TESTDB1';
alter system set cell_offload_processing = TRUE scope=spfile sid='TESTDB1';
alter system set connection_brokers = '((TYPE=DEDICATED)(BROKERS=1)), ((TYPE=EMON)(BROKERS=1))' scope=spfile sid='TESTDB1';
alter system set containers_parallel_degree = 65535 scope=spfile sid='TESTDB1';
alter system set control_file_record_keep_time = 7 scope=spfile sid='TESTDB1';
alter system set control_management_pack_access = 'DIAGNOSTIC+TUNING' scope=spfile sid='TESTDB1';
alter system set core_dump_dest = '/u01/app/oracle/diag/rdbms/testdb_omega/TESTDB1/cdump' scope=spfile sid='TESTDB1';
alter system set cpu_count = 2 scope=spfile sid='TESTDB1';
alter system set cursor_bind_capture_destination = 'memory+disk' scope=spfile sid='TESTDB1';
alter system set cursor_invalidation = 'IMMEDIATE' scope=spfile sid='TESTDB1';
alter system set cursor_sharing = 'EXACT' scope=spfile sid='TESTDB1';
alter system set data_guard_sync_latency = 0 scope=spfile sid='TESTDB1';
alter system set data_transfer_cache_size = 0 scope=spfile sid='TESTDB1';
alter system set db_16k_cache_size = 0 scope=spfile sid='TESTDB1';
alter system set db_2k_cache_size = 0 scope=spfile sid='TESTDB1';
alter system set db_32k_cache_size = 0 scope=spfile sid='TESTDB1';
alter system set db_4k_cache_size = 0 scope=spfile sid='TESTDB1';
alter system set db_8k_cache_size = 0 scope=spfile sid='TESTDB1';
alter system set db_big_table_cache_percent_target = '0' scope=spfile sid='TESTDB1';
alter system set db_block_checking = 'FALSE' scope=spfile sid='TESTDB1';
alter system set db_block_checksum = 'TYPICAL' scope=spfile sid='TESTDB1';
alter system set db_cache_advice = 'ON' scope=spfile sid='TESTDB1';
alter system set db_cache_size = 0 scope=spfile sid='TESTDB1';
alter system set db_create_file_dest = '+DATA' scope=spfile sid='TESTDB1';
alter system set db_file_multiblock_read_count = 128 scope=spfile sid='TESTDB1';
alter system set db_flash_cache_size = 0 scope=spfile sid='TESTDB1';
alter system set db_index_compression_inheritance = 'NONE' scope=spfile sid='TESTDB1';
alter system set db_keep_cache_size = 0 scope=spfile sid='TESTDB1';
alter system set db_lost_write_protect = 'NONE' scope=spfile sid='TESTDB1';
alter system set db_recycle_cache_size = 0 scope=spfile sid='TESTDB1';
alter system set db_securefile = 'PREFERRED' scope=spfile sid='TESTDB1';
alter system set db_unrecoverable_scn_tracking = TRUE scope=spfile sid='TESTDB1';
alter system set ddl_lock_timeout = 0 scope=spfile sid='TESTDB1';
alter system set default_sharing = 'metadata' scope=spfile sid='TESTDB1';
alter system set deferred_segment_creation = TRUE scope=spfile sid='TESTDB1';
alter system set dg_broker_config_file1 = '/u01/app/oracle/product/12.2.0.1/dbs/dr1TESTDB_OMEGA.dat' scope=spfile sid='TESTDB1';
alter system set dg_broker_config_file2 = '/u01/app/oracle/product/12.2.0.1/dbs/dr2TESTDB_OMEGA.dat' scope=spfile sid='TESTDB1';
alter system set diagnostic_dest = '/u01/app/oracle' scope=spfile sid='TESTDB1';
alter system set disable_pdb_feature = 0 scope=spfile sid='TESTDB1';
alter system set dispatchers = '(PROTOCOL=TCP) (SERVICE=TESTDBXDB)' scope=spfile sid='TESTDB1';
alter system set dst_upgrade_insert_conv = TRUE scope=spfile sid='TESTDB1';
alter system set enable_automatic_maintenance_pdb = TRUE scope=spfile sid='TESTDB1';
alter system set enable_ddl_logging = FALSE scope=spfile sid='TESTDB1';
alter system set enabled_PDBs_on_standby = '*' scope=spfile sid='TESTDB1';
alter system set encrypt_new_tablespaces = 'CLOUD_ONLY' scope=spfile sid='TESTDB1';
alter system set fast_start_io_target = 0 scope=spfile sid='TESTDB1';
alter system set fast_start_mttr_target = 0 scope=spfile sid='TESTDB1';
alter system set fast_start_parallel_rollback = 'LOW' scope=spfile sid='TESTDB1';
alter system set file_mapping = FALSE scope=spfile sid='TESTDB1';
alter system set global_names = FALSE scope=spfile sid='TESTDB1';
alter system set global_txn_processes = 1 scope=spfile sid='TESTDB1';
alter system set heat_map = 'OFF' scope=spfile sid='TESTDB1';
alter system set hs_autoregister = TRUE scope=spfile sid='TESTDB1';
alter system set inmemory_adg_enabled = TRUE scope=spfile sid='TESTDB1';
alter system set inmemory_expressions_usage = 'ENABLE' scope=spfile sid='TESTDB1';
alter system set inmemory_force = 'DEFAULT' scope=spfile sid='TESTDB1';
alter system set inmemory_max_populate_servers = 0 scope=spfile sid='TESTDB1';
alter system set inmemory_query = 'ENABLE' scope=spfile sid='TESTDB1';
alter system set inmemory_size = 0 scope=spfile sid='TESTDB1';
alter system set inmemory_trickle_repopulate_servers_percent = 1 scope=spfile sid='TESTDB1';
alter system set inmemory_virtual_columns = 'MANUAL' scope=spfile sid='TESTDB1';
alter system set instance_abort_delay_time = 0 scope=spfile sid='TESTDB1';
alter system set java_jit_enabled = TRUE scope=spfile sid='TESTDB1';
alter system set java_pool_size = 0 scope=spfile sid='TESTDB1';
alter system set job_queue_processes = 4000 scope=spfile sid='TESTDB1';
alter system set large_pool_size = 0 scope=spfile sid='TESTDB1';
alter system set ldap_directory_access = 'NONE' scope=spfile sid='TESTDB1';
alter system set license_max_sessions = 0 scope=spfile sid='TESTDB1';
alter system set license_max_users = 0 scope=spfile sid='TESTDB1';
alter system set license_sessions_warning = 0 scope=spfile sid='TESTDB1';
alter system set local_listener = ' (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.10.101)(PORT=1521))' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_1 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_10 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_11 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_12 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_13 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_14 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_15 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_16 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_17 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_18 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_19 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_2 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_20 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_21 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_22 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_23 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_24 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_25 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_26 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_27 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_28 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_29 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_3 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_30 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_31 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_4 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_5 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_6 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_7 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_8 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_9 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_max_processes = 4 scope=spfile sid='TESTDB1';
alter system set log_archive_min_succeed_dest = 1 scope=spfile sid='TESTDB1';
alter system set log_archive_trace = 0 scope=spfile sid='TESTDB1';
alter system set log_checkpoint_interval = 0 scope=spfile sid='TESTDB1';
alter system set log_checkpoint_timeout = 1800 scope=spfile sid='TESTDB1';
alter system set log_checkpoints_to_alert = FALSE scope=spfile sid='TESTDB1';
alter system set long_module_action = TRUE scope=spfile sid='TESTDB1';
alter system set max_datapump_jobs_per_pdb = 100 scope=spfile sid='TESTDB1';
alter system set max_dump_file_size = 'unlimited' scope=spfile sid='TESTDB1';
alter system set max_idle_time = 0 scope=spfile sid='TESTDB1';
alter system set max_iops = 0 scope=spfile sid='TESTDB1';
alter system set max_mbps = 0 scope=spfile sid='TESTDB1';
alter system set memory_target = 0 scope=spfile sid='TESTDB1';
alter system set nls_length_semantics = 'BYTE' scope=spfile sid='TESTDB1';
alter system set nls_nchar_conv_excp = 'FALSE' scope=spfile sid='TESTDB1';
alter system set object_cache_max_size_percent = 10 scope=spfile sid='TESTDB1';
alter system set object_cache_optimal_size = 10240000 scope=spfile sid='TESTDB1';
alter system set ofs_threads = 4 scope=spfile sid='TESTDB1';
alter system set olap_page_pool_size = 0 scope=spfile sid='TESTDB1';
alter system set one_step_plugin_for_pdb_with_tde = FALSE scope=spfile sid='TESTDB1';
alter system set open_cursors = 300 scope=spfile sid='TESTDB1';
alter system set optimizer_adaptive_plans = TRUE scope=spfile sid='TESTDB1';
alter system set optimizer_adaptive_reporting_only = FALSE scope=spfile sid='TESTDB1';
alter system set optimizer_adaptive_statistics = FALSE scope=spfile sid='TESTDB1';
alter system set optimizer_capture_sql_plan_baselines = FALSE scope=spfile sid='TESTDB1';
alter system set optimizer_dynamic_sampling = 2 scope=spfile sid='TESTDB1';
alter system set optimizer_features_enable = '12.2.0.1' scope=spfile sid='TESTDB1';
alter system set optimizer_index_caching = 0 scope=spfile sid='TESTDB1';
alter system set optimizer_index_cost_adj = 100 scope=spfile sid='TESTDB1';
alter system set optimizer_inmemory_aware = TRUE scope=spfile sid='TESTDB1';
alter system set optimizer_mode = 'ALL_ROWS' scope=spfile sid='TESTDB1';
alter system set optimizer_secure_view_merging = TRUE scope=spfile sid='TESTDB1';
alter system set optimizer_use_invisible_indexes = FALSE scope=spfile sid='TESTDB1';
alter system set optimizer_use_pending_statistics = FALSE scope=spfile sid='TESTDB1';
alter system set optimizer_use_sql_plan_baselines = TRUE scope=spfile sid='TESTDB1';
alter system set outbound_dblink_protocols = 'ALL' scope=spfile sid='TESTDB1';
alter system set parallel_adaptive_multi_user = FALSE scope=spfile sid='TESTDB1';
alter system set parallel_degree_limit = 'CPU' scope=spfile sid='TESTDB1';
alter system set parallel_degree_policy = 'MANUAL' scope=spfile sid='TESTDB1';
alter system set parallel_force_local = FALSE scope=spfile sid='TESTDB1';
alter system set parallel_max_servers = 80 scope=spfile sid='TESTDB1';
alter system set parallel_min_servers = 8 scope=spfile sid='TESTDB1';
alter system set parallel_min_time_threshold = 'AUTO' scope=spfile sid='TESTDB1';
alter system set parallel_servers_target = 32 scope=spfile sid='TESTDB1';
alter system set parallel_threads_per_cpu = 2 scope=spfile sid='TESTDB1';
alter system set pga_aggregate_limit = 2147483648 scope=spfile sid='TESTDB1';
alter system set pga_aggregate_target = 598736896 scope=spfile sid='TESTDB1';
alter system set plscope_settings = 'IDENTIFIERS:NONE' scope=spfile sid='TESTDB1';
alter system set plsql_code_type = 'INTERPRETED' scope=spfile sid='TESTDB1';
alter system set plsql_debug = FALSE scope=spfile sid='TESTDB1';
alter system set plsql_optimize_level = 2 scope=spfile sid='TESTDB1';
alter system set plsql_v2_compatibility = FALSE scope=spfile sid='TESTDB1';
alter system set plsql_warnings = 'DISABLE:ALL' scope=spfile sid='TESTDB1';
alter system set query_rewrite_enabled = 'TRUE' scope=spfile sid='TESTDB1';
alter system set query_rewrite_integrity = 'enforced' scope=spfile sid='TESTDB1';
alter system set recyclebin = 'on' scope=spfile sid='TESTDB1';
alter system set remote_dependencies_mode = 'TIMESTAMP' scope=spfile sid='TESTDB1';
alter system set remote_listener = ' scan12c:1521' scope=spfile sid='TESTDB1';
alter system set resource_limit = TRUE scope=spfile sid='TESTDB1';
alter system set resource_manage_goldengate = FALSE scope=spfile sid='TESTDB1';
alter system set resource_manager_cpu_allocation = 2 scope=spfile sid='TESTDB1';
alter system set resource_manager_plan = 'SCHEDULER[0x4AC5]:DEFAULT_MAINTENANCE_PLAN' scope=spfile sid='TESTDB1';
alter system set result_cache_max_result = 5 scope=spfile sid='TESTDB1';
alter system set result_cache_max_size = 8978432 scope=spfile sid='TESTDB1';
alter system set result_cache_mode = 'MANUAL' scope=spfile sid='TESTDB1';
alter system set result_cache_remote_expiration = 0 scope=spfile sid='TESTDB1';
alter system set resumable_timeout = 0 scope=spfile sid='TESTDB1';
alter system set sec_case_sensitive_logon = TRUE scope=spfile sid='TESTDB1';
alter system set sec_protocol_error_further_action = '(DROP,3)' scope=spfile sid='TESTDB1';
alter system set sec_protocol_error_trace_action = 'TRACE' scope=spfile sid='TESTDB1';
alter system set service_names = 'TESTDB_OMEGA' scope=spfile sid='TESTDB1';
alter system set session_cached_cursors = 50 scope=spfile sid='TESTDB1';
alter system set sessions = 472 scope=spfile sid='TESTDB1';
alter system set sga_min_size = 0 scope=spfile sid='TESTDB1';
alter system set sga_target = 1795162112 scope=spfile sid='TESTDB1';
alter system set shadow_core_dump = 'partial' scope=spfile sid='TESTDB1';
alter system set shared_pool_size = 0 scope=spfile sid='TESTDB1';
alter system set shared_servers = 1 scope=spfile sid='TESTDB1';
alter system set shrd_dupl_table_refresh_rate = 60 scope=spfile sid='TESTDB1';
alter system set skip_unusable_indexes = TRUE scope=spfile sid='TESTDB1';
alter system set sort_area_retained_size = 0 scope=spfile sid='TESTDB1';
alter system set sort_area_size = 65536 scope=spfile sid='TESTDB1';
alter system set spatial_vector_acceleration = FALSE scope=spfile sid='TESTDB1';
alter system set spfile = '+DATA/TESTDB_OMEGA/PARAMETERFILE/spfile.305.972520367' scope=spfile sid='TESTDB1';
alter system set sql_trace = FALSE scope=spfile sid='TESTDB1';
alter system set sqltune_category = 'DEFAULT' scope=spfile sid='TESTDB1';
alter system set standby_archive_dest = '?#/dbs/arch' scope=spfile sid='TESTDB1';
alter system set standby_file_management = 'MANUAL' scope=spfile sid='TESTDB1';
alter system set star_transformation_enabled = 'FALSE' scope=spfile sid='TESTDB1';
alter system set statistics_level = 'TYPICAL' scope=spfile sid='TESTDB1';
alter system set streams_pool_size = 0 scope=spfile sid='TESTDB1';
alter system set temp_undo_enabled = FALSE scope=spfile sid='TESTDB1';
alter system set thread = 1 scope=spfile sid='TESTDB1';
alter system set timed_os_statistics = 0 scope=spfile sid='TESTDB1';
alter system set timed_statistics = TRUE scope=spfile sid='TESTDB1';
alter system set trace_enabled = TRUE scope=spfile sid='TESTDB1';
alter system set undo_retention = 900 scope=spfile sid='TESTDB1';
alter system set undo_tablespace = 'UNDOTBS1' scope=spfile sid='TESTDB1';
alter system set uniform_log_timestamp_format = TRUE scope=spfile sid='TESTDB1';
alter system set use_dedicated_broker = FALSE scope=spfile sid='TESTDB1';
alter system set user_dump_dest = '/u01/app/oracle/product/12.2.0.1/rdbms/log' scope=spfile sid='TESTDB1';
alter system set workarea_size_policy = 'AUTO' scope=spfile sid='TESTDB1';
alter system set xml_db_events = 'enable' scope=spfile sid='TESTDB1';

222 rows selected.

SQL>

Мой блог

среда, 19 августа 2009 г.

Oracle ANALYZE INDEX

суббота, 15 августа 2009 г.

Oracle Resize, Drop TEMP and UNDO

суббота, 8 августа 2009 г.

EMCLI

четверг, 23 июля 2009 г.

DBMS_SQLTUNE (настройка SQL)

Поиск наилучшего плана – SQL Tuning Advisor

Закрепление существующего плана

Пример:

вторник, 21 июля 2009 г.

Параметры инициализации 12.2

Поиск по этому блогу

Архив блога

Ярлыки

Постоянные читатели

Обо мне

Мой блог

среда, 19 августа 2009 г.

Oracle ANALYZE INDEX

суббота, 15 августа 2009 г.

Oracle Resize, Drop TEMP and UNDO

суббота, 8 августа 2009 г.

EMCLI

четверг, 23 июля 2009 г.

DBMS_SQLTUNE (настройка SQL)

Поиск наилучшего плана – SQL Tuning Advisor

Закрепление существующего плана

Пример:

вторник, 21 июля 2009 г.

Параметры инициализации 12.2

Поиск по этому блогу

Архив блога

Ярлыки

Постоянные читатели

Обо мне

среда, 19 августа 2009 г.

суббота, 15 августа 2009 г.

суббота, 8 августа 2009 г.

четверг, 23 июля 2009 г.

вторник, 21 июля 2009 г.