 |
|
|
|
|
|
|
|
| |
| |
| |
Technique |
|
Domaines |
|
Divers | |
Contact | |
Parfois il est nécessaire de lister les paramètres de l’instance et éventuellement recréer le fichier init.ora, par exemple lors de switch init.ora/spfile. Le script générera initdb.ora, en mettant les commentaires trouvés dans v$parameter sur la meme ligne que le paramètre lui meme (spfile ne préserve pas les commentaires seuls sur une ligne).
set heading off set feedback OFF SET lines 200 set pages 0 set newpage 1 set termout off SELECT spool initdb.ora Prompt ################################################################# prompt # select '# initdb.ora - creation ' || to_char(sysdate,'MM/DD/YY HH:MI PM') from dual; prompt # prompt # Fichier pondu par l'extraction des parametres non nuls dans la select '# base ' || name || ', creation: ' || to_char(created,'MM/DD/YY HH:MI PM') from v$database; prompt # Prompt ################################################################# prompt # Valeurs modifiées Prompt ################################################################# prompt select name || ' = ' || value || ' # ' || description || chr(10) from v$parameter where isdefault = 'FALSE' AND value IS NOT NULL order by num; prompt Prompt ################################################################# prompt # Valeurs par defaut Prompt ################################################################# prompt select name || ' = ' || value || ' # ' || description || chr(10) from v$parameter where isdefault = 'TRUE' AND value IS NOT NULL order by num; spool off set termout on edit initdb.ora
Le fichier en sortie peut avoir cet aspect :
################################################################# # # initdb.ora - creation 11/07/02 07:34 AM # # Fichier pondu par l'extraction des parametres non nuls dans la # base LIB9, creation: 05/20/02 09:33 AM # ################################################################# # Valeurs modifiées ################################################################# processes = 150 # user processes timed_statistics = TRUE # maintain internal timing statistics shared_pool_size = 16777216 # size in bytes of shared pool large_pool_size = 8388608 # size in bytes of the large allocation pool java_pool_size = 33554432 # size in bytes of the Java pool control_files = i:\db\lib9\ctrl\control01.ctl, m:\db\lib9\ctrl\control02.ctl, z:\db\lib9\ctrl\control03.ctl # control file names list db_block_size = 8192 # Size of database block in bytes db_cache_size = 402653184 # Size of DEFAULT buffer pool for standard block size buffers compatible = 9.2.0.0.0 # Database will be completely compatible with this software versio log_archive_start = TRUE # start archival process on SGA initialization log_archive_dest = l:\archive_lib9 # archival destination text string log_archive_format = LIB9%S%T.ARC # archival destination format db_file_multiblock_read_count = 16 # db block to be read each IO fast_start_mttr_target = 300 # MTTR target of forward crash recovery in seconds undo_management = AUTO # instance runs in SMU mode if TRUE, else in RBU mode undo_tablespace = UNDOTBS1 # use/switch undo tablespace undo_retention = 10800 # undo retention in seconds remote_login_passwordfile = EXCLUSIVE # password file usage parameter instance_name = lib9 # instance name supported by the instance dispatchers = (PROTOCOL=TCP) (SERVICE=lib9XDB) # specifications of dispatchers utl_file_dir = d:\oracle\logs, l:\source # utl_file accessible directories list job_queue_processes = 8 # number of job queue slave processes parallel_min_servers = 0 # minimum parallel query servers per instance parallel_max_servers = 16 # maximum parallel query servers per instance hash_join_enabled = TRUE # enable/disable hash join background_dump_dest = M:\admin\lib9\bdump # Detached process dump directory user_dump_dest = M:\admin\lib9\udump # User process dump directory core_dump_dest = M:\admin\lib9\cdump # Core dump directory open_links = 4 # max # open links per session sort_area_size = 524288 # size of in-memory sort work area db_name = lib9 # database name specified in CREATE DATABASE open_cursors = 300 # max # cursors per session star_transformation_enabled = FALSE # enable the use of star transformation parallel_threads_per_cpu = 2 # number of parallel execution threads per CPU parallel_automatic_tuning = TRUE # enable intelligent defaults for parallel execution parameters query_rewrite_enabled = TRUE # allow rewrite of queries using materialized views if enabled pga_aggregate_target = 25165824 # Target size for the aggregate PGA memory consumed by the instanc aq_tm_processes = 1 # number of AQ Time Managers to start
En exemple, une séance de modification permanente des paramètres concernant le ARCHIVELOG :
Note : L’agent intelligent sait commander une commande de type ‘alter database archivelog’, mais ne regarde pas si les process d’archivage sont démarrés. Bête, non ? Il va falloir toucher (nous utilisons un spfile, comme Oracle recommande ;-))
PROD@K200-SQL> alter system set log_archive_start=true;
alter system set log_archive_start=true
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
*
C’est clair, non ? Il va falloir recréer le init.ora, démarrer l’instance avec après modification et le transformer en spfile . . . à chaud !
PROD@K200-SQL> alter system archive log all; System altered. PROD@K200-SQL> create pfile='I:\admin\prod\pfile\init.ora' FROM spfile='I:\oracl e\database\spfilePROD.ora'; File created. PROD@K200-SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down.
Démarrer avec le nouveau init.ora
PROD@K200-SQL> startup open pfile=I:\admin\prod\pfile\init.ora ORACLE instance started. Total System Global Area 135338868 bytes Fixed Size 453492 bytes Variable Size 109051904 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytes Database mounted. Database opened.
Et créer le spfile
PROD@K200-SQL> create spfile= 'i:\oracle\database\spfileprod.ora' from pfile='I: \admin\prod\pfile\init.ora' ; File created. PROD@K200-SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down.
Et démarer avec spfile . . .
PROD@K200-SQL> startup open ORACLE instance started. Total System Global Area 135338868 bytes Fixed Size 453492 bytes Variable Size 109051904 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytes Database mounted. Database opened. PROD@K200-SQL>
| Copyright © 1998-2002 Radu Caulea, TAFORA | MAJ 06/11/2006 |  |
| Commentaires et suggestions radu[CHEZ]tafora.fr | ||