Un an sans parution d’article, c’est long! Mais suite à des évolutions de carrière, changement d’emploi du temps, etc… Il devenait parfois compliqué d’écrire un article sur des sujets techniques, n’ayant plus le temps et les ressources pour tester l’intégralité des commandes mentionnées, des corrections à effectuer. C’est donc, un article que j’ai commencé à écrire il y a maintenant un an et demi, et qui devrait vous permettre de vous sortir d’un mauvais pas en cas d’intervention sur une base de données sur laquelle vous ne possédez pas grand-chose! C’est parti!
Contexte
Il est parfois demandé à un DBA de devoir recréer une base de données. Mais il arrive, que la base de données ait été crée sur un environnement différent (AIX, Linux, Solaris ou encore Windows), la taille des blocs n’a pas été correctement définie, ou tout simplement, en tant qu’administrateur, l’on veut juste savoir comment la base de données a été crée. Nous allons détailler dans cette page, une méthode qui permet de générer le fichier SQL de création de la base de données sans passer par des logiciels tiers, tels que : TOAD.
Principe
Nous allons donc voir comment générer le script de création de la base. Toutefois, cette opération ne pourra pas être réalisée en une seule fois, il faudra pour cela utiliser également la commande de génération des fichiers de contrôles, car la suite des instructions seront incluses dans celui-ci.
Cas Concret
L’ensemble des commandes suivants doit être exécuté en tant qu’utilisateur avec les droits DBA. Les commandes généreront directement un script nommé crt_db.sql.
1 |
SQL> SELECT * FROM DUAL; |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 |
SET VERIFY OFF FEEDBACK OFF ECHO OFF PAGES 0 SET TERMOUT ON PROMPT Creating db build script... SET TERMOUT OFF; REM REM Create Holding Table REM CREATE TABLE db_temp (lineno NUMBER, text VARCHAR2(255)) / REM REM Procedure REM DECLARE -- -- Cursor Declarations -- CURSOR get_block IS SELECT value FROM v$parameter WHERE name='db_block_size'; -- CURSOR ts_cursor IS SELECT initial_extent, next_extent, min_extents, max_extents, pct_increase, min_extlen, extent_management, allocation_type FROM sys.dba_tablespaces WHERE tablespace_name = 'SYSTEM'; -- CURSOR df_cursor IS SELECT file_name, bytes, autoextensible, maxbytes, increment_by FROM sys.dba_data_files WHERE tablespace_name = 'SYSTEM' ORDER BY file_name; -- CURSOR grp_cursor IS SELECT group# FROM v$log; -- CURSOR mem_cursor (grp_num number) IS SELECT a.member, b.bytes FROM v$logfile a, v$log b WHERE a.group#=grp_num AND a.group#=b.group# ORDER BY member; -- -- Variable Declarations -- block_size NUMBER; lv_initial_extent sys.dba_tablespaces.initial_extent%TYPE; lv_next_extent sys.dba_tablespaces.next_extent%TYPE; lv_min_extents sys.dba_tablespaces.min_extents%TYPE; lv_max_extents sys.dba_tablespaces.max_extents%TYPE; lv_pct_increase sys.dba_tablespaces.pct_increase%TYPE; lv_file_name sys.dba_data_files.file_name%TYPE; lv_bytes sys.dba_data_files.bytes%TYPE; lv_max_extend sys.dba_data_files.maxbytes%TYPE; lv_ext_incr sys.dba_data_files.increment_by%TYPE; lv_autoext sys.dba_data_files.autoextensible%TYPE; lv_first_rec BOOLEAN; lv_min_extlen sys.dba_tablespaces.min_extlen%TYPE; lv_extent_man sys.dba_tablespaces.extent_management%TYPE; lv_allocation sys.dba_tablespaces.allocation_type%TYPE; sub_strg VARCHAR2(20); grp_member v$logfile.member%TYPE; bytes v$log.bytes%TYPE; db_name VARCHAR2(8); db_string VARCHAR2(255); db_lineno NUMBER := 0; thrd NUMBER; grp NUMBER; sz NUMBER; begin_count NUMBER; max_group NUMBER; -- -- Local use procedures -- PROCEDURE write_out(p_line INTEGER, p_string VARCHAR2) IS BEGIN INSERT INTO db_temp (lineno,text) VALUES (p_line,p_string); END; -- -- Start of actual code -- BEGIN SELECT MAX(group#) INTO max_group FROM v$log; db_lineno:=db_lineno+1; SELECT 'CREATE DATABASE '||name INTO db_string FROM v$database; write_out(db_lineno,db_string); db_lineno:=db_lineno+1; SELECT 'CONTROLFILE REUSE' INTO db_string FROM dual; write_out(db_lineno,db_string); db_lineno:=db_lineno+1; SELECT 'LOGFILE ' INTO db_string FROM dual; write_out(db_lineno,db_string); COMMIT; IF grp_cursor%ISOPEN THEN CLOSE grp_cursor; OPEN grp_cursor; ELSE OPEN grp_cursor; END IF; LOOP FETCH grp_cursor INTO grp; EXIT WHEN grp_cursor%NOTFOUND; db_lineno:=db_lineno+1; db_string:= ' GROUP '||grp||' ('; write_out(db_lineno,db_string); IF mem_cursor%ISOPEN THEN CLOSE mem_cursor; OPEN mem_cursor(grp); ELSE OPEN mem_cursor(grp); END IF; db_lineno:=db_lineno+1; begin_count:=db_lineno; LOOP FETCH mem_cursor INTO grp_member, bytes; EXIT when mem_cursor%NOTFOUND; IF begin_count=db_lineno THEN db_string:=chr(39)||grp_member||chr(39); write_out(db_lineno,db_string); db_lineno:=db_lineno+1; ELSE db_string:=' ,'||chr(39)||grp_member||chr(39); write_out(db_lineno,db_string); db_lineno:=db_lineno+1; END IF; END LOOP; db_lineno:=db_lineno+1; IF grp=max_group THEN db_string:=' ) SIZE '||bytes; write_out(db_lineno,db_string); ELSE db_string:=' ) SIZE '||bytes||','; write_out(db_lineno,db_string); END IF; END LOOP; OPEN get_block; FETCH get_block INTO block_size; CLOSE get_block; OPEN ts_cursor; FETCH ts_cursor INTO Lv_initial_extent, Lv_next_extent, Lv_min_extents, Lv_max_extents, Lv_pct_increase, Lv_min_extlen, Lv_extent_man, Lv_allocation; lv_first_rec := TRUE; CLOSE ts_cursor; OPEN df_cursor; LOOP FETCH df_cursor INTO lv_file_name, lv_bytes, lv_autoext, lv_max_extend, lv_ext_incr; EXIT WHEN df_cursor%NOTFOUND; IF (lv_first_rec) THEN lv_first_rec := FALSE; db_string := 'DATAFILE '; ELSE db_string := db_string ||chr(10)|| ','; END IF; db_string:=db_string||chr(39)||lv_file_name||chr(39)|| ' SIZE '||to_char(lv_bytes) || ' REUSE'; IF lv_autoext='YES' THEN IF lv_max_extend=0 THEN sub_strg:=' MAXSIZE UNLIMITED'; ELSE sub_strg:=' MAXSIZE '||TO_CHAR(lv_max_extend); END IF; IF lv_ext_incr != 0 THEN db_string:=db_string||chr(10)||' AUTOEXTEND ON NEXT '|| to_char(lv_ext_incr*block_size)||sub_strg; END IF; END IF; IF lv_min_extlen != 0 AND lv_extent_man!='LOCAL' THEN db_string:=db_string||chr(10)|| 'MINIMUM EXTENT '||TO_CHAR(lv_min_extlen); END IF; END LOOP; CLOSE df_cursor; db_lineno := db_lineno + 1; write_out(db_lineno, db_string); db_lineno := db_lineno + 1; IF lv_extent_man!='LOCAL' THEN db_string := (' DEFAULT STORAGE (INITIAL ' || TO_CHAR(lv_initial_extent) || ' NEXT ' || lv_next_extent); ELSE db_string:=('EXTENT MANAGEMENT '||lv_extent_man); IF lv_allocation='UNIFORM' THEN db_string:=db_string||' '||lv_extent_man||' SIZE ' ||lv_initial_extent; ELSE db_string:=db_string||' AUTOALLOCATE'; END IF; END IF; write_out(db_lineno, db_string); db_lineno := db_lineno + 1; IF lv_extent_man!='LOCAL' THEN db_string := (' MINEXTENTS ' || lv_min_extents || ' MAXEXTENTS ' || lv_max_extents); write_out(db_lineno, db_string); db_lineno := db_lineno + 1; db_string := (' PCTINCREASE ' || lv_pct_increase || ')'); write_out(db_lineno, db_string); END IF; COMMIT; SELECT DECODE(value,'TRUE','ARCHIVELOG','FALSE','NOARCHIVELOG') INTO db_string FROM v$parameter WHERE name='log_archive_start'; db_lineno:=db_lineno+1; write_out(db_lineno,db_string); SELECT ';' INTO db_string from dual; db_lineno:=db_lineno+1; write_out(db_lineno,db_string); CLOSE mem_cursor; CLOSE grp_cursor; COMMIT; END; / rem The next section could be converted to use rem UTLFILE so the entire anonymous PL/SQL section rem and this report section would become a stored rem procedure, but to keep it generic I will leave as rem is. COLUMN dbname NEW_VALUE db NOPRINT SELECT name dbname FROM v$database; SET HEADING OFF PAGES 0 VERIFY OFF RECSEP OFF SPOOL rep_out\&db\crt_db.sql COLUMN text FORMAT a80 WORD_WRAP SELECT text FROM db_temp ORDER BY lineno; SPOOL OFF SET FEEDBACK ON VERIFY ON TERMOUT ON DROP TABLE db_temp; PROMPT Press enter to continue SET VERIFY ON FEEDBACK ON PAGES 22 TERMOUT ON CLEAR COLUMNS |
Maintenant que nous avons généré une partie des instructions, nous devons également créer les instructions de création de la base de données et plus spécifiquement les options CREATE de l’instance.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
SET NEWPAGE 0 VERIFY OFF SET ECHO OFF FEEDBACK OFF TERMOUT OFF PAGES 300 LINES 80 HEADING OFF COLUMN name FORMAT a80 WORD_WRAPPED COLUMN dbname NEW_VALUE db NOPRINT SELECT name dbname FROM v$database; DEFINE OUTPUT = 'rep_out\&db\init.ora' DEFINE cr = chr(10) SPOOL &OUTPUT SELECT '# Init.ora file from v$parameter'||&&cr|| '# generated on:'||sysdate||&&cr|| '# script by MRA 11/7/95 REVEALNET'||&&cr|| '#' name FROM dual UNION SELECT name||' = '||value name FROM V$PARAMETER WHERE value IS NOT NULL; SPOOL OFF CLEAR COLUMNS SET NEWPAGE 0 VERIFY OFF SET TERMOUT ON PAGES 22 LINES 80 HEADING ON SET TERMOUT ON UNDEF OUTPUT |
Enfin, nous allons générer des instructions pour la gestion des control-files.
1 |
ALTER DATABASE BACKUP CONTROL FILE TO TRACE; |
L’ensemble des instructions que nous avons déroulé, nous a permis de générer un lot de commandes pour permettre la création d’une base de données. Bien sûr, ces méthodes ne sont pas exhaustives et nécessitent quelques connaissances poussées en Oracle, dans le but de permettre leurs utilisations; néanmoins, elles s’avèrent intéressantes pour donner certaines pistes dans le cadre d’un travail de retro-ingénierie.
Autre Méthode
Vous trouverez également ci-après, un autre script qui permet aussi la génération des instructions. Moins complet, mais intéressant pour comparer ce qui est fait avec les précédents.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 |
spool crdb.sql set pages 1000 set head off set termout off set feedback off set newpage none set serveroutput on select 'CREATE DATABASE '||name text from v$database; -- select 'CONTROLFILE REUSE' from dual; -- optional select 'LOGFILE' from dual; declare print_var varchar2(200); cursor c1 is select member from gv$logfile where inst_id = 1 order by group#; logfile gv$logfile.member%TYPE; cursor c2 is select bytes from gv$log where inst_id = 1 order by group#; bytes number; lsize varchar2(30); begin open c1; open c2; for record in ( select group#, count(*) members from gv$logfile where inst_id = 1 group by group#) loop dbms_output.put_line(print_var); fetch c2 into bytes; if mod(bytes,1024) = 0 then if mod(bytes,1024*1024) = 0 then lsize := to_char(bytes/(1024*1024))||'M'; else lsize := to_char(bytes/1024)||'K'; end if; else lsize := to_char(bytes); end if; lsize := lsize||','; if record.members > 1 then fetch c1 into logfile; print_var := 'GROUP '||record.group#||' ('; dbms_output.put_line(print_var); print_var := ''''||logfile||''''||','; for i in 2..record.members loop fetch c1 into logfile; dbms_output.put_line(print_var); print_var := ''''||logfile||''''||','; end loop; print_var := rtrim(print_var,','); dbms_output.put_line(print_var); print_var := ') SIZE '||lsize; else fetch c1 into logfile; print_var := 'GROUP '||record.group#||' '''|| logfile||''''||' SIZE '||lsize; end if; end loop; close c1; close c2; print_var := rtrim(print_var,','); dbms_output.put_line(print_var); end; / select 'MAXLOGFILES '||RECORDS_TOTAL from v$controlfile_record_section where type = 'REDO LOG'; select 'MAXLOGMEMBERS '||dimlm from sys.x$kccdi; select 'MAXDATAFILES '||RECORDS_TOTAL from v$controlfile_record_section where type = 'DATAFILE'; select 'MAXINSTANCES '||RECORDS_TOTAL from v$controlfile_record_section where type = 'DATABASE'; select 'MAXLOGHISTORY '||RECORDS_TOTAL from v$controlfile_record_section where type = 'LOG HISTORY'; select log_mode from v$database; select 'CHARACTER SET '||value from v$nls_parameters where parameter = 'NLS_CHARACTERSET'; select 'NATIONAL CHARACTER SET '||value from v$nls_parameters where parameter = 'NLS_NCHAR_CHARACTERSET'; select 'DATAFILE' from dual; declare cursor c1 is select * from dba_data_files where tablespace_name = 'SYSTEM' order by file_id; datafile dba_data_files%ROWTYPE; print_datafile dba_data_files.file_name%TYPE; begin open c1; fetch c1 into datafile; -- there is always 1 datafile print_datafile := ''''||datafile.file_name|| ''' SIZE '||ceil(datafile.bytes/(1024*1024))||' M,'; loop fetch c1 into datafile; if c1%NOTFOUND then -- strip the comma and print the last datafile print_datafile := rtrim(print_datafile,','); dbms_output.put_line(print_datafile); exit; else -- print the previous datafile and prepare the next dbms_output.put_line(print_datafile); print_datafile := ''''||datafile.file_name|| ''' SIZE '||ceil(datafile.bytes/(1024*1024))||' M,'; end if; end loop; end; / select ';' from dual; spool off |