Миграция на ASM:

$export ORACLE_SID=SID
$sqlplus / as sysdba

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/app/oracle/oradata/SID/control1/control01.ctl
/app/oracle/oradata/SID/control2/control02.ctl


SQL> alter system set control_files='+DATA','+DATA' scope=spfile;
SQL> alter system set db_create_file_dest='+DATA' scope=spfile;

SQL> shutdown immediate;
SQL> startup nomount;
SQL> exit

$rman target /

RMAN> restore controlfile from '/app/oracle/oradata/SID/control1/control01.ctl';

RMAN> alter database mount;

RMAN> backup as copy database format '+DATA';

RMAN> switch database to copy;

RMAN> recover database;

RMAN> exit


$export ORACLE_SID=SID
$sqlplus / as sysdba

SQL> alter database open;

SQL> CREATE TEMPORARY TABLESPACE "TEMP1" TEMPFILE SIZE 61865984 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576;
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP1";

SQL> shutdown immediate;
SQL> startup;

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

SQL> shutdown immediate;
SQL> startup

SQL> DROP TABLESPACE TEMP1;

Или просто добавить временный файл в существующее табличное пространство:

SQL> ALTER TABLESPACE "TEMP" ADD TEMPFILE SIZE 500M AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Tablespace altered.
SQL>


SQL>SQL> alter system set db_create_online_log_dest_1='+REDO' scope=spfile;

SQL> shutdown immediate;
SQL> startup;

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/app/oracle/oradata/SID/redoa/redo06a.log
/app/oracle/oradata/SID/redoa/redo05a.log
/app/oracle/oradata/SID/redoa/redo04a.log
/app/oracle/oradata/SID/redoa/redo03a.log
/app/oracle/oradata/SID/redoa/redo02a.log
/app/oracle/oradata/SID/redoa/redo01a.log

6 rows selected.

SQL> alter database drop logfile group 1;
SQL> alter database add logfile group 1 size 52428800;

SQL> alter database drop logfile group 2;
SQL> alter database add logfile group 2 size 52428800;

SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for instance SID (thread 1) - cannot drop
ORA-00312: online log 3 thread 1: '/app/oracle/oradata/SID/redoa/redo03a.log'


SQL> alter database drop logfile group 4;
SQL> alter database add logfile group 4 size 52428800;

SQL> alter database drop logfile group 5;
SQL> alter database add logfile group 5 size 52428800;

SQL> alter database drop logfile group 6;
SQL> alter database add logfile group 6 size 52428800;

SQL> alter system switch logfile;
SQL> alter system checkpoint;

SQL> alter database drop logfile group 3;
SQL> alter database add logfile group 3 size 52428800;

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/SID/datafile/system.270.789045335
+DATA/SID/datafile/undotbs1.269.789045319
+DATA/SID/datafile/sysaux.268.789045295
+DATA/SID/datafile/users.271.789045349

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
+DATA/SID/tempfile/temp.275.789045723

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
+DATA/SID/controlfile/current.266.789045265
+DATA/SID/controlfile/current.267.789045265

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
+REDO/SID/onlinelog/group_1.274.789046953
+REDO/SID/onlinelog/group_2.276.789046981
+REDO/SID/onlinelog/group_4.277.789047043
+REDO/SID/onlinelog/group_5.278.789047061
+REDO/SID/onlinelog/group_3.279.789047111
+REDO/SID/onlinelog/group_6.280.789047303

6 rows selected.

SQL>

Установка патча DST17 на oracle 11.2.0.3

# cd /tmp

# ls -l

-rw-rw-r--  1 angor    angor    315821 Jul 26 10:37 p12949905_112030_Linux-x86-64.zip

# chown oradb:oinstall p12949905_112030_Linux-x86-64.zip

# exit

logout

ls -l

-rw-rw-r--  1 oradb    oinstall 315821 Jul 26 10:37 p12949905_112030_Linux-x86-64.zip

$unzip p12949905_112030_Linux-x86-64.zip

Archive:  p12949905_112030_Linux-x86-64.zip

   creating: 12949905/

  inflating: 12949905/README.txt

  inflating: 12949905/README.html

   creating: 12949905/files/

   creating: 12949905/files/oracore/

   creating: 12949905/files/oracore/zoneinfo/

  inflating: 12949905/files/oracore/zoneinfo/readme_17.txt

   creating: 12949905/files/oracore/zoneinfo/little/

  inflating: 12949905/files/oracore/zoneinfo/little/timezone_17.dat

  inflating: 12949905/files/oracore/zoneinfo/little/timezlrg_17.dat

  inflating: 12949905/files/oracore/zoneinfo/timezone_17.dat

   creating: 12949905/files/oracore/zoneinfo/big/

  inflating: 12949905/files/oracore/zoneinfo/big/timezone_17.dat

  inflating: 12949905/files/oracore/zoneinfo/big/timezlrg_17.dat

  inflating: 12949905/files/oracore/zoneinfo/timezlrg_17.dat

   creating: 12949905/etc/

   creating: 12949905/etc/config/

  inflating: 12949905/etc/config/inventory.xml

  inflating: 12949905/etc/config/actions.xml

  inflating: 12949905/etc/config/deploy.xml

   creating: 12949905/etc/xml/

  inflating: 12949905/etc/xml/GenericActions.xml

  inflating: 12949905/etc/xml/ShiphomeDirectoryStructure.xml

$cd 12949905

$ls -l

total 36

drwxr-xr-x 4 oradb oinstall  4096 Oct 14  2011 etc

drwxr-xr-x 3 oradb oinstall  4096 Oct 14  2011 files

-rw-r--r-- 1 oradb oinstall 16720 Oct 14  2011 README.html

-rw-r--r-- 1 oradb oinstall  5516 Oct 14  2011 README.txt

$

$/u01/app/oradb/product/11.2.0.3/dbhome_1/OPatch/opatch apply

Oracle Interim Patch Installer version 11.2.0.3.0

Copyright (c) 2012, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oradb/product/11.2.0.3/dbhome_1

Central Inventory : /u01/app/oraInventory

   from           : /u01/app/oradb/product/11.2.0.3/dbhome_1/oraInst.loc

OPatch version    : 11.2.0.3.0

OUI version       : 11.2.0.3.0

Log file location : /u01/app/oradb/product/11.2.0.3/dbhome_1/cfgtoollogs/opatch/12949905_Jul_26_2012_11_08_05/apply2012-07-26_11-08-05AM_1.log

Applying interim patch '12949905' to OH '/u01/app/oradb/product/11.2.0.3/dbhome_1'

Verifying environment and performing prerequisite checks...

All checks passed.

Provide your email address to be informed of security issues, install and

initiate Oracle Configuration Manager. Easier for you if you use your My

Oracle Support Email address/User Name.

Visit http://www.oracle.com/support/policies.html for details.

Email address/User Name:

You have not provided an email address for notification of security issues.

Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]:  Y

Backing up files...

Patching component oracle.oracore.rsf, 11.2.0.3.0...

Verifying the update...

Patch 12949905 successfully applied

Log file location: /u01/app/oradb/product/11.2.0.3/dbhome_1/cfgtoollogs/opatch/12949905_Jul_26_2012_11_08_05/apply2012-07-26_11-08-05AM_1.log

OPatch succeeded.

$

$export ORACLE_SID=rckt1

$sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 26 11:09:37 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME LIKE 'DST_%'

ORDER BY PROPERTY_NAME;

  2    3    4

PROPERTY_NAME                  VALUE

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

DST_PRIMARY_TT_VERSION         14

DST_SECONDARY_TT_VERSION       0

DST_UPGRADE_STATE              NONE

SQL> SELECT version FROM v$timezone_file;

   VERSION

----------

        14

SQL> alter session set "_with_subquery"=materialize;

Session altered.

SQL> alter session set "_simple_view_merging"=TRUE;

Session altered.

SQL> exec DBMS_DST.BEGIN_PREPARE(17);

PL/SQL procedure successfully completed.

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME LIKE 'DST_%'

ORDER BY PROPERTY_NAME;

  2    3    4

PROPERTY_NAME                  VALUE

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

DST_PRIMARY_TT_VERSION         14

DST_SECONDARY_TT_VERSION       17

DST_UPGRADE_STATE              PREPARE

SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;

Table truncated.

SQL> TRUNCATE TABLE sys.dst$affected_tables;

Table truncated.

SQL> TRUNCATE TABLE sys.dst$error_table;

Table truncated.

SQL> set serveroutput on

SQL> BEGIN

DBMS_DST.FIND_AFFECTED_TABLES

(affected_tables => 'sys.dst$affected_tables',

log_errors => TRUE,

log_errors_table => 'sys.dst$error_table');

END;
/

  2    3    4    5    6    7

PL/SQL procedure successfully completed.

SQL> SELECT * FROM sys.dst$affected_tables;

no rows selected

SQL> SELECT * FROM sys.dst$error_table;

no rows selected

SQL> SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1883';

no rows selected

SQL> EXEC DBMS_DST.END_PREPARE;

A prepare window has been successfully ended.

PL/SQL procedure successfully completed.

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME LIKE 'DST_%'

ORDER BY PROPERTY_NAME;

  2    3    4

PROPERTY_NAME                  VALUE

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

DST_PRIMARY_TT_VERSION         14

DST_SECONDARY_TT_VERSION       0

DST_UPGRADE_STATE              NONE

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup upgrade;

ORACLE instance started.

Total System Global Area 2137886720 bytes

Fixed Size                  2230072 bytes

Variable Size             503318728 bytes

Database Buffers         1627389952 bytes

Redo Buffers                4947968 bytes

Database mounted.

Database opened.

SQL> set serveroutput on

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME LIKE 'DST_%'

ORDER BY PROPERTY_NAME;

  2    3    4

PROPERTY_NAME                  VALUE

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

DST_PRIMARY_TT_VERSION         14

DST_SECONDARY_TT_VERSION       0

DST_UPGRADE_STATE              NONE

SQL> purge dba_recyclebin;

DBA Recyclebin purged.

SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;

Table truncated.

SQL> TRUNCATE TABLE sys.dst$affected_tables;

Table truncated.

SQL> TRUNCATE TABLE sys.dst$error_table;

Table truncated.

SQL> alter session set "_with_subquery"=materialize;

Session altered.

SQL> alter session set "_simple_view_merging"=TRUE;

Session altered.

SQL> EXEC DBMS_DST.BEGIN_UPGRADE(17);

An upgrade window has been successfully started.

PL/SQL procedure successfully completed.

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME LIKE 'DST_%'

ORDER BY PROPERTY_NAME;

  2    3    4

PROPERTY_NAME                  VALUE

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

DST_PRIMARY_TT_VERSION         17

DST_SECONDARY_TT_VERSION       14

DST_UPGRADE_STATE              UPGRADE

SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';

OWNER                          TABLE_NAME                     UPG

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

SYSMAN                         MGMT_PROV_ASSIGNMENT           YES

SYSMAN                         MGMT_PROV_BOOTSERVER           YES

SYSMAN                         MGMT_PROV_CLUSTER_NODES        YES

SYSMAN                         MGMT_PROV_DEFAULT_IMAGE        YES

SYSMAN                         MGMT_PROV_IP_RANGE             YES

SYSMAN                         MGMT_PROV_NET_CONFIG           YES

SYSMAN                         MGMT_PROV_OPERATION            YES

SYSMAN                         MGMT_PROV_RPM_REP              YES

SYSMAN                         MGMT_PROV_STAGING_DIRS         YES

SYSMAN                         MGMT_PROV_SUITE_INST_MEMBERS   YES

SYSMAN                         MGMT_CONFIG_ACTIVITIES         YES

OWNER                          TABLE_NAME                     UPG

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

SYSMAN                         AQ$_MGMT_LOADER_QTABLE_L       YES

SYSMAN                         AQ$_MGMT_LOADER_QTABLE_S       YES

SYSMAN                         AQ$_MGMT_NOTIFY_QTABLE_L       YES

SYSMAN                         AQ$_MGMT_NOTIFY_QTABLE_S       YES

15 rows selected.

SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';

OWNER                          TABLE_NAME                     UPG

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

SYSMAN                         MGMT_PROV_ASSIGNMENT           YES

SYSMAN                         MGMT_PROV_BOOTSERVER           YES

SYSMAN                         MGMT_PROV_CLUSTER_NODES        YES

SYSMAN                         MGMT_PROV_DEFAULT_IMAGE        YES

SYSMAN                         MGMT_PROV_IP_RANGE             YES

SYSMAN                         MGMT_PROV_NET_CONFIG           YES

SYSMAN                         MGMT_PROV_OPERATION            YES

SYSMAN                         MGMT_PROV_RPM_REP              YES

SYSMAN                         MGMT_PROV_STAGING_DIRS         YES

SYSMAN                         MGMT_PROV_SUITE_INST_MEMBERS   YES

SYSMAN                         MGMT_CONFIG_ACTIVITIES         YES

OWNER                          TABLE_NAME                     UPG

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

SYSMAN                         AQ$_MGMT_LOADER_QTABLE_L       YES

SYSMAN                         AQ$_MGMT_LOADER_QTABLE_S       YES

SYSMAN                         AQ$_MGMT_NOTIFY_QTABLE_L       YES

SYSMAN                         AQ$_MGMT_NOTIFY_QTABLE_S       YES

15 rows selected.

SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';

OWNER                          TABLE_NAME                     UPG

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

SYSMAN                         MGMT_PROV_ASSIGNMENT           YES

SYSMAN                         MGMT_PROV_BOOTSERVER           YES

SYSMAN                         MGMT_PROV_CLUSTER_NODES        YES

SYSMAN                         MGMT_PROV_DEFAULT_IMAGE        YES

SYSMAN                         MGMT_PROV_IP_RANGE             YES

SYSMAN                         MGMT_PROV_NET_CONFIG           YES

SYSMAN                         MGMT_PROV_OPERATION            YES

SYSMAN                         MGMT_PROV_RPM_REP              YES

SYSMAN                         MGMT_PROV_STAGING_DIRS         YES

SYSMAN                         MGMT_PROV_SUITE_INST_MEMBERS   YES

SYSMAN                         MGMT_CONFIG_ACTIVITIES         YES

OWNER                          TABLE_NAME                     UPG

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

SYSMAN                         AQ$_MGMT_LOADER_QTABLE_L       YES

SYSMAN                         AQ$_MGMT_LOADER_QTABLE_S       YES

SYSMAN                         AQ$_MGMT_NOTIFY_QTABLE_L       YES

SYSMAN                         AQ$_MGMT_NOTIFY_QTABLE_S       YES

15 rows selected.

SQL> desc sysman.MGMT_PROV_NET_CONFIG;

 Name                                      Null?    Type

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

 NET_CONFIG_GUID                           NOT NULL RAW(16)

 LAST_MODIFIED_TIME                                 TIMESTAMP(6) WITH TIME ZONE

 NET_CONFIG_NAME                                    VARCHAR2(255)

 NET_DOMAIN_NAME                                    VARCHAR2(255)

 NET_SUBNET_MASK                                    VARCHAR2(255)

 NET_GATEWAY_ADDRS                                  VARCHAR2(512)

 NET_DNS_ADDRS                                      VARCHAR2(512)

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 2137886720 bytes

Fixed Size                  2230072 bytes

Variable Size             503318728 bytes

Database Buffers         1627389952 bytes

Redo Buffers                4947968 bytes

Database mounted.

Database opened.

SQL> alter session set "_with_subquery"=materialize;

Session altered.

SQL> alter session set "_simple_view_merging"=TRUE;

Session altered.

SQL> set serveroutput on

SQL> VAR numfail number

SQL> BEGIN

  2  DBMS_DST.UPGRADE_DATABASE(:numfail,

  3  parallel => TRUE,

  4  log_errors => TRUE,

  5  log_errors_table => 'SYS.DST$ERROR_TABLE',

  6  log_triggers_table => 'SYS.DST$TRIGGER_TABLE',

  7  error_on_overlap_time => FALSE,

  8  error_on_nonexisting_time => FALSE);

  9  DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);

 10  END;

 11  /

Table list: "SYSMAN"."AQ$_MGMT_LOADER_QTABLE_L"

Number of failures: 0

Table list: "SYSMAN"."AQ$_MGMT_LOADER_QTABLE_S"

Number of failures: 0

Table list: "SYSMAN"."MGMT_CONFIG_ACTIVITIES"

Number of failures: 0

Table list: "SYSMAN"."AQ$_MGMT_NOTIFY_QTABLE_L"

Number of failures: 0

Table list: "SYSMAN"."AQ$_MGMT_NOTIFY_QTABLE_S"

Number of failures: 0

Table list: "SYSMAN"."MGMT_PROV_SUITE_INST_MEMBERS"

Number of failures: 0

Table list: "SYSMAN"."MGMT_PROV_CLUSTER_NODES"

Number of failures: 0

Table list: "SYSMAN"."MGMT_PROV_IP_RANGE"

Number of failures: 0

Table list: "SYSMAN"."MGMT_PROV_NET_CONFIG"

Number of failures: 0

Table list: "SYSMAN"."MGMT_PROV_STAGING_DIRS"

Number of failures: 0

Table list: "SYSMAN"."MGMT_PROV_RPM_REP"

Number of failures: 0

Table list: "SYSMAN"."MGMT_PROV_DEFAULT_IMAGE"

Number of failures: 0

Table list: "SYSMAN"."MGMT_PROV_BOOTSERVER"

Number of failures: 0

Table list: "SYSMAN"."MGMT_PROV_ASSIGNMENT"

Number of failures: 0

Table list: "SYSMAN"."MGMT_PROV_OPERATION"

Number of failures: 0

Failures:0

PL/SQL procedure successfully completed.

SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';

no rows selected

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME LIKE 'DST_%'

ORDER BY PROPERTY_NAME;

  2    3    4

PROPERTY_NAME                  VALUE

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

DST_PRIMARY_TT_VERSION         17

DST_SECONDARY_TT_VERSION       14

DST_UPGRADE_STATE              UPGRADE

SQL> SELECT * FROM v$timezone_file;

FILENAME                VERSION

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

timezlrg_17.dat              17

SQL> VAR fail number

SQL> BEGIN

DBMS_DST.END_UPGRADE(:fail);

DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);

END;

/

  2    3    4    5   
An upgrade window has been successfully ended.

Failures:0

PL/SQL procedure successfully completed.

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME LIKE 'DST_%'

ORDER BY PROPERTY_NAME;

  2    3    4

PROPERTY_NAME                  VALUE

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

DST_PRIMARY_TT_VERSION         17

DST_SECONDARY_TT_VERSION       0

DST_UPGRADE_STATE              NONE

SQL> SELECT * FROM v$timezone_file;

FILENAME                VERSION

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

timezlrg_17.dat              17

SQL> select TZ_VERSION from registry$database;

TZ_VERSION

----------

        14

SQL> select version FROM v$timezone_file;

   VERSION

----------

        17

SQL> update registry$database set TZ_VERSION = (select version FROM v$timezone_file);

1 row updated.

SQL> select TZ_VERSION from registry$database;

TZ_VERSION

----------

        17

SQL> commit;

Commit complete.

SQL>

Oracle Upgrade 10.2.0.4 -> 11.2.0.3

Pre_upgrade

export ORACLE_SID=SID
export ORACLE_HOME=/u01/app/oradb/product/10.2.0.4/db_1


sqlplus "/ as sysdba"
SQL>shutdown immediate
SQL>STARTUP UPGRADE
SQL>SPOOL /export/home/oradb/upgrade_info.log
SQL>@/u01/app/oradb/product/11.2.0.3/dbhome_1/rdbms/admin/utlu112i.sql
SQL>SPOOL OFF

SQL>@/u01/app/oradb/product/10.2.0.4/db_1/rdbms/admin/utlrp.sql

SQL>shutdown immediate;
SQL>spool off
SQL>exit

//////////////////////////////////////////////////////////////////////////////////////////////////


cp /u01/app/oradb/product/10.2.0.4/db_1/dbs/iniSID.ora  /u01/app/oradb/product/11.2.0.3/dbhome_1/dbs/

Удаляем устаревшие параметры из : /u01/app/oradb/product/11.2.0.3/dbhome_1/dbs/initSID.ora

background_dump_dest
user_dump_dest      

Добавляем устаревшие параметры в : /u01/app/oradb/product/11.2.0.3/dbhome_1/dbs/initSID.ora

*.diagnostic_dest='/u01/app/oradb'


////////////////////////////////////////////////////////////////////////////////////////////////////

Upgrade

export ORACLE_BASE=/u01/app/oradb
export ORACLE_HOME=${ORACLE_BASE}/product/11.2.0.3/dbhome_1
export PATH=${ORACLE_HOME}/OPatch:${ORACLE_HOME}/bin:${PATH}

. envdb11203

$env


sqlplus "/ as sysdba"
STARTUP UPGRADE
SPOOL /export/home/oradb/upgrade_db.log
SPOOL /home/oradb/upgrade_db.log

@/u01/app/oradb/product/11.2.0.3/dbhome_1/rdbms/admin/catupgrd.sql

   Если завершается с ошибкой :
      SELECT TO_NUMBER('MUST_HAVE_RUN_PRE-UPGRADE_TOOL_FOR_TIMEZONE')
      *
      ERROR at line 1:
      ORA-01722: invalid number

   то возможно поможет рекомендация [ID 1255474.1]:

   update registry$database set TZ_VERSION = (select version FROM v$timezone_file);
   commit; 
  
   и повторяем запуск
   @/u01/app/oradb/product/11.2.0.3/dbhome_1/rdbms/admin/catupgrd.sql


shutdown immediate;
startup
SPOOL OFF
exit
EOF

Post Upgrade:

sqlplus "/ as sysdba"
SPOOL /tmp/invalid_test1.log
@/u01/app/oradb/product/11.2.0.3/dbhome_1/rdbms/admin/utlu112s.sql;
@/u01/app/oradb/product/11.2.0.3/dbhome_1/rdbms/admin/catuppst.sql;
@/u01/app/oradb/product/11.2.0.3/dbhome_1/rdbms/admin/utlrp.sql;
spool off
exit
EOF

    Инвалидные объекты X_$KGLLK и X_$KSLEI можно удалить [ID 878623.1]

    drop view X_$KGLLK;
    drop public synonym X$KGLLK;

    drop view X_$KSLEI;
    drop public synonym X$KSLEI;

    И повторяем рекомпиляцию
    @/u01/app/oradb/product/11.2.0.3/dbhome_1/rdbms/admin/utlrp.sql;


SQL> create spfile from pfile;

SQL> shutdown immediate;
SQL> startup;

SQL> alter system set compatible='11.2.0' scope=spfile;

SQL> shutdown immediate;
SQL> startup;

FLASHBACK DATABASE

$export ORACLE_SID=test

$sqlplus "/as sysdba"

SQL> select name from v$database;

NAME

---------

TEST

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 50G;

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '+RECO';

SQL> SELECT flashback_on, log_mode FROM v$database;

FLASHBACK_ON       LOG_MODE

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

NO                 ARCHIVELOG

SQL> alter database flashback on;

SQL> SELECT flashback_on, log_mode FROM v$database;

FLASHBACK_ON       LOG_MODE

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

YES                ARCHIVELOG

SQL> SELECT name, scn, time, database_incarnation#, guarantee_flashback_database, storage_size FROM gv$restore_point;

SQL> show parameter flashback

NAME                                 TYPE        VALUE

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

db_flashback_retention_target        integer     1440

SQL> alter system set db_flashback_retention_target=1440 scope=both sid='*';

SQL> show parameter flashback

NAME                                 TYPE        VALUE

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

db_flashback_retention_target        integer     1440

SQL> SELECT name, scn, time, database_incarnation#, guarantee_flashback_database, storage_size FROM gv$restore_point;

no rows selected

Создание негарантированной RESTORE POINT.

SQL> create restore point rp1;

Restore point created.

SQL> SELECT name, scn, time, database_incarnation#, guarantee_flashback_database, storage_size FROM gv$restore_point;

NAME   SCN           TIME                                                              DATABASE_INCARNATION#  GUA            STORAGE_SIZE

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

RP1       14633448  01-AUG-12 10.28.32.000000000 AM  1                                                        NO               0

Удаление негарантированной RESTORE POINT:

SQL> drop restore point rp1;

Restore point dropped.

Создание тесеовой таблицы:

SQL> create table test (name varchar2(35));

Table created.

SQL> insert into test values ('aaa');

1 row created.

SQL> insert into test values ('bbb');

1 row created.

SQL> insert into test values ('ccc');

1 row created.

SQL> select * from test;

NAME

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

aaa

bbb

ccc

Создание гарантированной RESTORE POINT:

SQL> create restore point rp1 guarantee flashback database;

Restore point created.

SQL> select * from test;

NAME

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

aaa

bbb

ccc

SQL> insert into test values ('ddd');

1 row created.

SQL> insert into test values ('eee');

1 row created.

SQL> insert into test values ('fff');

1 row created.

SQL> select * from test;

NAME

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

aaa

bbb

ccc

ddd

eee

fff

6 rows selected.

SQL> commit;

Commit complete.

SQL> select * from test;

NAME

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

aaa

bbb

ccc

ddd

eee

fff

6 rows selected.

SQL> shutdown immediate;

SQL> startup mount;

SQL> flashback database to restore point rp1;

Flashback complete.

SQL> alter database open read only;

Database altered.

SQL> select * from test;

NAME

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

aaa

bbb

ccc

Отменим FLASHBACK

SQL> shutdown immediate;

SQL> startup mount;

SQL> recover database;

SQL> alter database open;

Database altered.

SQL> select * from test;

NAME

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

aaa

bbb

ccc

ddd

eee

fff

6 rows selected.

Вернемся к RESTORE POINT

SQL> shutdown immediate;

SQL> startup mount;

SQL> flashback database to restore point rp1;

Flashback complete.

Окончательно откроем базу

SQL> alter database open resetlogs;

Database altered.

SQL> select * from test;

NAME

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

aaa

bbb

ccc

SQL> exit

Удалаем точки восстанавления и отключаем режим FLASHBACK:

$sqlplus "/as sysdba"

SQL> SELECT name, scn, time, database_incarnation#, guarantee_flashback_database, storage_size FROM gv$restore_point;

NAME  SCN             TIME                                                               DATABASE_INCARNATION#   GUA  STORAGE_SIZE

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

RP1       14634255  01-AUG-12 10.37.23.000000000 AM   1                                                          YES      52428800

SQL> drop restore point rp1;

Restore point dropped.

SQL> SELECT name, scn, time, database_incarnation#, guarantee_flashback_database, storage_size FROM gv$restore_point;

no rows selected

SQL> SELECT flashback_on, log_mode FROM v$database;

FLASHBACK_ON       LOG_MODE

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

YES                ARCHIVELOG

SQL> alter database flashback off;

Database altered.

SQL> SELECT flashback_on, log_mode FROM v$database;

FLASHBACK_ON       LOG_MODE

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

NO                 ARCHIVELOG

SQL>