Поиск файлов в режиме BEGIN BACKUP

select f.name
from v$datafile f, v$backup b
where b.file# = f.file#
and b.status = 'ACTIVE';

DB backup with BEGIN BACKUP

set serveroutput on
set trimspool on
set line 500
set head off
set feed off

spool begin_backup.sh

declare
cp_cmd constant varchar2(30) := 'cp';
cp_target constant varchar2(30) := '/u01/backup/';
DBname varchar2(30);
logmode varchar2(30);
begin
select name, log_mode into dbname, logmode from sys.v_$database;

if logmode <> 'ARCHIVELOG' then
raise_application_error(-20000, 'ERROR: Database must be in ARCHIVELOG mode!');
return;
end if;

dbms_output.put_line('spool backup.'||DBname||'.'||to_char(sysdate, 'ddMonyy')||'.log');

-- View all tablespaces except TEMP tablespace
for c1 in (select tablespace_name ts from sys.dba_tablespaces where contents<>'TEMPORARY' )
loop
dbms_output.put_line('alter tablespace '||c1.ts||' begin backup;');

-- View tablespace's data files
for c2 in (select file_name fil from sys.dba_data_files
where tablespace_name = c1.ts)
loop
dbms_output.put_line('!'||cp_cmd||' '||c2.fil||' '||cp_target);
end loop;

dbms_output.put_line('alter tablespace '||c1.ts||' end backup;');
end loop;

-- Backup controlfile and switch logfiles
dbms_output.put_line('alter database backup controlfile to trace;');
dbms_output.put_line('alter database backup controlfile to '||''''||
cp_target||'control.'||dbname||'.'||
to_char(sysdate,'dd-mm-yyyy_HH24:MI')||''''||';');
dbms_output.put_line('alter system switch logfile;');
dbms_output.put_line('spool off');
end;
/

spool off

set head on
set feed on
set serveroutput off

@begin_backup.sh
exit

Проверка полей empty_clob()

SQL> create table clob_test (hist clob);

Table created

SQL>
SQL> insert into clob_test values ('123');

1 row inserted

SQL> insert into clob_test values( empty_clob() );

1 row inserted

SQL> insert into clob_test values( empty_clob() );

1 row inserted

SQL> commit;

Commit complete

SQL> select count(*) from clob_test where dbms_lob.compare( hist, empty_clob() ) = 0
2 ;

COUNT(*)
----------
2

SQL> select count(*) from clob_test where dbms_lob.compare( hist, empty_clob() ) != 0;

COUNT(*)
----------
1

Апргейд БД Oracle с 9i до 10g

Базы данных TEST
Версия БД Oracle 9.2.0.8
Версия целевой БД Oracle 10.2.0.4

Шаги выполнения апгрейда БД TEST
1. Установка софта Oracle 10.2.0.1
Время начала: 11:10
Время окончания: 11:30
Средняя скорость передачи данных с локального компьютера: 200 к/сек
2. Установка патча Oracle 10.2.0.4
Время начала: 11:30
Время окончания: 11:45
Средняя скорость передачи данных с локального компьютера: 200 к/сек
3. Подготовительный этап
 Запуск Pre-Upgrade Utility
Необходимо скопировать скрипт $ORACLE_HOME/rdbms/admin/utlu102i.sql в временную папку. (скопирован в директорию /u01/app/oracle/admin/TEST/scripts)

$ cp $ORACLE_HOME/rdbms/admin/utlu102i.sql /u01/app/oracle/admin/TEST/scripts
$ sqlplus “as / sysdba”
SQL> spool info.log
SQL> @utlu102i.sql
Oracle Database 10.2 Upgrade Information Utility 07-09-2008 13:50:55
.**********************************************************************
Database:
**********************************************************************
--> name: TEST
--> version: 9.2.0.8.0
--> compatible: 9.2.0
--> blocksize: 8192
.**********************************************************************
Logfiles: [make adjustments in the current environment]
**********************************************************************
--> The existing log files are adequate. No changes are required.
.**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 3873 MB
.... AUTOEXTEND additional space required: 79 MB
--> UNDO tablespace is adequate for the upgrade.
.... minimum required size: 2816 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 58 MB
.**********************************************************************
Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
WARNING: --> "streams_pool_size" is not currently defined and needs a value of
at least 50331648
.**********************************************************************
Renamed Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
--> "transaction_auditing"
--> "undo_suppress_errors"
--> "max_enabled_roles"
--> "parallel_automatic_tuning"
--> "enqueue_resources"
.**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views [upgrade] VALID
--> Oracle Packages and Types [upgrade] VALID
.**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Passwords exist in some database links.
.... Passwords will be encrypted during the upgrade.
.... Downgrade of database links with passwords is not supported.
WARNING: --> Deprecated CONNECT role granted to some user/roles.
.... CONNECT role after upgrade has only CREATE SESSION privilege.
WARNING: --> Database contains stale optimizer statistics.
.... Refer to the 10g Upgrade Guide for instructions to update
.... statistics prior to upgrading the database.
.... Component Schemas with stale statistics:
.... SYS
WARNING: --> Database contains INVALID objects prior to upgrade.
.... USER MIGRATION has 2 INVALID objects.
.... USER PERFSTAT has 3 INVALID objects.
.... USER SPOT has 1 INVALID objects.
.... USER SYS has 14 INVALID objects.
.... USER SYSTEM has 1 INVALID objects.
.**********************************************************************
SYSAUX Tablespace:
[Create tablespace in the Oracle Database 10.2 environment]
**********************************************************************
--> New "SYSAUX" tablespace
.... minimum required size for database upgrade: 500 MB
.
PL/SQL procedure successfully completed.

SQL> SPOOL OFF

-- Создание файла параметров для новой версии БД
SQL> create pfile from spfile;
File created.
 Изменены записи в файле .profile для отображения нового местоположения ORACLE_HOME
 Скопированы и настроены файлы listener.ora и tnsnames.ora в новом ORACLE_HOME
 Скопированы файл параметров и файл паролей в новый ORACLE_HOME со старого местоположения.
$ cp u01/app/oracle/product/9.2.0.8/dbs/orapwTEST /u01/app/oracle/product/10.2.0/dbs
$ cp /u01/app/oracle/product/9.2.0.8/dbs/initTEST.ora /u01/app/oracle/product/10.2.0/dbs
 Выполнены изменения в файле initTEST.ora :
- Добавлен параметр streams_pool_size = 50331648
- Изменено значение параметра compatible = 10.2.0
- Удалены параметры transaction_auditing, undo_suppress_errors, max_enabled_roles, parallel_automatic_tuning, enqueue_resources
- В значение параметра log_archive_format добавлены дополнительные символы указания формата %t и %r (log_archive_format= ${ORACLE_SID}%s%t%r.arch)

4. Запуск скрипта апгрейда БД
Время начала: 12:45
Время окончания: 13:52
-- Перезапуск БД в режиме UPGRADE
SQL>SHUTDOWN IMMEDIATE
SQL> Startup upgrade pfile=’initTEST.ora’
-- Создание дополнительного табличного пространства SYSAUX
SQL> CREATE TABLESPACE sysaux DATAFILE ' /u01/oradata/TEST/sysaux01.dbf' SIZE 500M REUSE EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ONLINE;
-- Скрипт апгрейда
SQL> SPOOL upgrade.log
SQL>@ /u01/app/oracle/product/10.2.0/rdbms/admin/catupgrd.sql
-- Проверка результата апгрейда
SQL>@ /u01/app/oracle/product/10.2.0/rdbms/admin/utlu102s.sql
SQL> SPOOL OFF
-- Перезапуск БД для возвращения в обычный режим
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
-- Перекомпиляция кода подпрограмм PLSQL и JAVA
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

5. Результат апгрейда БД TEST
Component Status Version HH:MM:SS
Oracle Database Server VALID 10.2.0.4.0 00:54:48
Общее время выполнения скрипта апгрейда: 00:57:30