Oracle Создание тестовой базы

1. Создаем все каталоги

export ORACLE_SID=sapdbt1
export ORACLE_BASE=/app/oracle
export ORACLE_HOME=/app/oracle/product/10.2.0/db_1
export ORACLE_DATA=/app/oracle/oradata/$ORACLE_SID
export ORACLE_ADMIN=$ORACLE_BASE/admin/$ORACLE_SID

mkdir $ORACLE_ADMIN
mkdir $ORACLE_ADMIN/pfile
mkdir $ORACLE_ADMIN/bdump
mkdir $ORACLE_ADMIN/cdump
mkdir $ORACLE_ADMIN/udump
mkdir $ORACLE_ADMIN/create

2. Скопируем файл параметров

cp $ORACLE_HOME/dbs/initsapdb.ora $ORACLE_HOME/dbs/initsapdbt1.ora

в винде делаем так:

> cp $ORACLE_HOME\dbs\initsapdb.ora d:\app\oracle\admin\sapdbt1\pfile\initsapdbt1.ora

> notepad $ORACLE_HOME\dbs\initsapdbt1.ora > IFILE = d:\app\oracle\admin\sapdbt1\pfile\initsapdbt1.ora

3. Отредактируем файл initsapdbt1.ora

*.db_block_size=8192
*.compatible='10.2.0.3.0'
*.optimizer_features_enable='9.2.0'
*.optimizer_secure_view_merging=FALSE
*.timed_statistics=TRUE

*.db_name='sapdbt1'
*.db_domain='msk.oracle.com'


*.audit_sys_operations=TRUE
*.audit_trail='XML'
*.audit_file_dest='/app/oracle/admin/sapdbt1/adump'

*.background_dump_dest='/app/oracle/admin/sapdbt1/bdump'
*.user_dump_dest='/app/oracle/admin/sapdbt1/udump'
*.core_dump_dest='/app/oracle/admin/sapdbt1/cdump'
*.control_files='/app/oracle/oradata/sapdbt1/control1/control01.ctl','/app/oracle/oradata/sapdbt1/control2/control02.ctl'

*.db_recovery_file_dest='/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648

*.log_archive_dest_1='LOCATION=/app/oracle/archlogs/arch01/sapdbt1/ MANDATORY'
*.log_archive_dest_2='LOCATION=/app/oracle/archlogs/arch02/sapdbt1/ OPTIONAL'
*.log_archive_format='sapdb_%t_%s_%r.arc'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=sapdbt1XDB)'
*.nls_language='AMERICAN'
*.remote_login_passwordfile='EXCLUSIVE'
*.archive_lag_target=900
*.db_cache_advice='READY'

*.db_file_multiblock_read_count=16

*.sga_max_size=48318382080
*.db_cache_size=9663676416
*.db_keep_cache_size=8589934592
*.db_recycle_cache_size=12884901888
*.dbwr_io_slaves=4
*.shared_pool_size=1363148800
*.shared_pool_reserved_size=136314880
*.pga_aggregate_target=5368709120
*.large_pool_size=100M
*.java_pool_size=157286400
*.job_queue_processes=20
*.open_cursors=700
*.processes=1500
*.sessions=1655

*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.undo_retention=21600


4. Создаем файл паролей

orapwd file=$ORACLE_HOME/dbs/orapwsapdbt1 password=TEST entries=4

в винде еще создаем сервис

oradim -new -sid sapdbt1 -intpwd TEST


5. Выполняем горячее резервное копирование


6. Создаем сценарий управляющего файла

alter database backup controlfile to trace resetlogs;

он будет создан в каталоге

select value from v$parameter where name='user_dump_dest';

или

> show parameter user_dump_dest

переименуйте его в mk_sapdbt1.sql и поместите в
$ORACLE_BASE/admin/sapdbt1/create

отредактируйте его примерно так

STARTUP NOMOUNT pfile='/u01/app/oracle/oracle/product/10.2.0/db_1/dbs/initsapdbt1.ora';
CREATE CONTROLFILE SET DATABASE "SAPDBT1" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 732
LOGFILE
GROUP 1 '/app/oracle/oradata/sapdbt1/redoa/redo01.log' SIZE 50M,
GROUP 2 '/app/oracle/oradata/sapdbt1/redoa/redo02.log' SIZE 50M,
GROUP 3 '/app/oracle/oradata/sapdbt1/redoa/redo03.log' SIZE 50M
DATAFILE
'/app/oracle/oradata/sapdbt1/system/system01.dbf',
'/app/oracle/oradata/sapdbt1/undotbs/undotbs01.dbf',
'/app/oracle/oradata/sapdbt1/sysaux/sysaux01.dbf',
'/app/oracle/oradata/sapdbt1/users/users01.dbf'
CHARACTER SET CL8MSWIN1251
;
alter database recover automatic FROM '/app/oracle/archlogs/arch01/sapdbt1/' until cancel using backup controlfile;
alter database recover automatic cancel;
alter database open resetlogs;
ALTER TABLESPACE TEMP ADD TEMPFILE '/app/oracle/oradata/sapdbt1/temp/temp01.dbf'
SIZE 29360128 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;


выполните его

export ORACLE_SID=sapdbt1
cd $ORACLE_BASE/admin/$ORACLE_SID/create
sqlplus /nolog
connect sys/passw as sysdba;
@mk_sapdbt1.sql


Можно и руками восстановить (накатить базу) sapdbt1:

sql> set logsource /app/oracle/archlogs/arch01/sapdbt1;
sql> recover database using backup controlfile until cancel;

когда закончатся все архивные логи, можно попробовать скормить
и оперативный текущий редо-лог, предварительно остановив базу sapdb

просто введите путь:
/app/oracle/oradata/sapdb/redoa/redo01.log
в ответ на приглашение начать восстановление

или не закрывая переключите несколько раз журналы и скормите архивные файлы.

Откройте БД supdbt1

alter database open resetlogs;


С помощью RMAN восстанавливать так:

export ORACLE_SID=testdb
rman target /

CATALOG START WITH '/u09/tape_restore/testdb';
recover database;

alter database open resetlogs;

Примеры скриптов для пересоздания управляющего файла:

STARTUP NOMOUNT pfile='/u01/app/oracle/product/10.2.0.4/db_1/dbs/inittestdb.ora';
CREATE CONTROLFILE SET DATABASE "TESTDB" RESETLOGS NOARCHIVELOG
    MAXLOGFILES 40
    MAXLOGMEMBERS 3
    MAXDATAFILES 300
    MAXINSTANCES 8
    MAXLOGHISTORY 2920
LOGFILE
  GROUP 1 '/u09/app/oracle/oradata/testdb/redoa/redo01a.log'  SIZE 500M,
  GROUP 2 '/u09/app/oracle/oradata/testdb/redoa/redo02a.log'  SIZE 500M,
  GROUP 3 '/u09/app/oracle/oradata/testdb/redoa/redo03a.log'  SIZE 500M,
  GROUP 4 '/u09/app/oracle/oradata/testdb/redoa/redo04a.log'  SIZE 500M,
  GROUP 5 '/u09/app/oracle/oradata/testdb/redoa/redo05a.log'  SIZE 500M,
  GROUP 6 '/u09/app/oracle/oradata/testdb/redoa/redo06a.log'  SIZE 500M,
  GROUP 7 '/u09/app/oracle/oradata/testdb/redoa/redo07a.log'  SIZE 500M,
  GROUP 8 '/u09/app/oracle/oradata/testdb/redoa/redo08a.log'  SIZE 500M
DATAFILE
  '/u09/app/oracle/oradata/testdb/sysaux01.dbf',
  '/u09/app/oracle/oradata/testdb/system01.dbf',
  '/u09/app/oracle/oradata/testdb/temp01.dbf',
  '/u09/app/oracle/oradata/testdb/undotbs1_01.dbf',
  '/u09/app/oracle/oradata/testdb/undotbs2_02.dbf',
  '/u09/app/oracle/oradata/testdb/users01.dbf'
CHARACTER SET CL8MSWIN1251
;

или так:

STARTUP NOMOUNT pfile='/u01/app/oradb/product/11.2.0.3/dbhome_1/dbs/inittestdb.ora';
CREATE CONTROLFILE SET DATABASE "TESTDB" RESETLOGS NOARCHIVELOG
    MAXLOGFILES 40
    MAXLOGMEMBERS 3
    MAXDATAFILES 300
    MAXINSTANCES 8
    MAXLOGHISTORY 2920
LOGFILE
  GROUP 1 '+REDO/redo01a.log'  SIZE 500M BLOCKSIZE 512,
  GROUP 2 '+REDO/redo02a.log'  SIZE 500M BLOCKSIZE 512,
  GROUP 3 '+REDO/redo03a.log'  SIZE 500M BLOCKSIZE 512,
  GROUP 4 '+REDO/redo04a.log'  SIZE 500M BLOCKSIZE 512,
  GROUP 5 '+REDO/redo05a.log'  SIZE 500M BLOCKSIZE 512,
  GROUP 6 '+REDO/redo06a.log'  SIZE 500M BLOCKSIZE 512,
  GROUP 7 '+REDO/redo07a.log'  SIZE 500M BLOCKSIZE 512,
  GROUP 8 '+REDO/redo08a.log'  SIZE 500M BLOCKSIZE 512
DATAFILE
  '+TEST/testdb/datafile/system.584.824808053',         
  '+TEST/testdb/datafile/undotbs1.702.824808053',       
  '+TEST/testdb/datafile/sysaux.701.824808053',         
  '+TEST/testdb/datafile/users.589.824808213'          
CHARACTER SET CL8MSWIN1251
;

Файл параметров:

$more inittestdb.ora
*.aq_tm_processes=0
*.archive_lag_target=900
*.audit_file_dest='/u01/app/oracle/admin/testdb/adump'
*.audit_sys_operations=TRUE
*.audit_trail='XML'
*.background_dump_dest='/u01/app/oracle/admin/testdb/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/u09/app/oracle/oradata/testdb/control/control01.ctl','/u09/app/oracle/oradata/testdb/control/control02.ctl','/u09/ap
p/oracle/oradata/testdb/control/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/testdb/cdump'
*.db_block_size=8192
*.db_cache_size=3221225472
*.db_domain='msk.vbrr.loc'
*.db_files=1024
*.db_name='testdb'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=21474836480
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testdbt0XDB)'
*.fast_start_mttr_target=300
*.fast_start_parallel_rollback='LOW'
*.java_pool_size=268435456
*.job_queue_processes=0
*.large_pool_size=200M
*.log_archive_dest_1='LOCATION=/u09/app/oracle/archlogs/arch01/testdb/ MANDATORY'
*.log_archive_format='testdb_%t_%s_%r.arc'
*.log_archive_max_processes=10
*.log_archive_min_succeed_dest=1
*.open_cursors=1000
*.optimizer_features_enable='9.2.0'
*.optimizer_secure_view_merging=FALSE
*.pga_aggregate_target=1g
*.processes=2500
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=5368709120
*.shared_pool_size=1073741824
*.smtp_out_server='solaris-mx.sun.com'
*.standby_file_management='AUTO'
testdb.streams_pool_size=335544320
*.undo_management='AUTO'
*.undo_retention=14400
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/testdb/udump'


или так:


*.archive_lag_target=900
*.audit_sys_operations=TRUE
*.audit_trail='DB'
*.compatible='11.2.0'
*.control_files='+TEST/testdb/controlfile/current.866.822734201','+TEST/testdb/controlfile/current.867.822734201'#Restore Controlfile
*.control_management_pack_access='DIAGNOSTIC+TUNING'
*.db_block_size=8192
*.db_cache_size=12884901888
*.db_create_file_dest='+TEST'
*.db_create_online_log_dest_1='+REDO'
*.db_domain='msk.vbrr.loc'
*.db_files=1024
*.db_name='testdb'#Reset to original value by RMAN
*.db_recovery_file_dest='+RECO'
*.db_recovery_file_dest_size=107374182400
*.diagnostic_dest='/u01/app/oradb'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testdbXDB)'
*.fast_start_mttr_target=300
*.fast_start_parallel_rollback='LOW'
*.java_pool_size=268435456
*.job_queue_processes=60
*.large_pool_size=268435456
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES, ALL_ROLES)'
*.log_archive_dest_state_3='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=4
*.log_archive_min_succeed_dest=1
*.open_cursors=1000
*.optimizer_secure_view_merging=FALSE
*.os_authent_prefix='OSP$'
*.pga_aggregate_target=6442450944
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_manager_plan=''
*.sec_case_sensitive_logon=FALSE
*.sga_max_size=16106127360
*.shared_pool_size=2147483648
*.smtp_out_server='afina-mx.vbrr.ru'
*.standby_file_management='AUTO'
*.undo_retention=14400
*.undo_tablespace='UNDOTBS1'


Добавление временных файлов:

ALTER TABLESPACE TEMP ADD TEMPFILE '/app/oracle/oradata/sapdbt1/temp/temp01.dbf'
SIZE 29360128 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

или так:

CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE SIZE 61865984 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP";
ALTER TABLESPACE "TEMP" ADD TEMPFILE SIZE 500M AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;


Установить DBID для тестовой базы можно так:

set ORACLE_SID=TESTDB
sqlplus sys/sys as sysdba
shutdown immediate;
startup open read only;

declare
  v_chgdbid   binary_integer;
  v_chgdbname binary_integer;
  v_skipped   binary_integer;
  old_name    varchar2(100);
  old_dbid    varchar2(100);

begin

  select name, dbid into old_name, old_dbid from v$database;
  dbms_backup_restore.nidbegin('TESTDB', old_name, 97255669, old_dbid, 0, 0, 10);
  dbms_backup_restore.nidprocesscf(v_chgdbid,v_chgdbname);
  for i in (select file#,name from v$datafile) loop
     dbms_backup_restore.nidprocessdf(i.file#,0, v_skipped,v_chgdbid,v_chgdbname);
  end loop;
  for i in (select file#,name from v$tempfile) loop
     dbms_backup_restore.nidprocessdf(i.file#,1, v_skipped,v_chgdbid,v_chgdbname);
  end loop;
  dbms_backup_restore.nidend;
end;

/

shutdown immediate;
startup mount;
alter database open resetlogs;
startup force;