pat98's always & forever

Applies to:

Symptoms

Cause

Solution

SOLUTION:
========

1. Check whether the database is in archive log mode and automatic archiving is enabled.

SQL> archive log list;

2. If Archive destination is USE_DB_RECOVERY_FILE_DEST, find the archive destination by :

SQL> show parameter db_recovery_file_dest;

Check what is the value set for db_recovery_file_dest_size

3. Find the space used in flash recovery area by :

SQL> SELECT * FROM V$RECOVERY_FILE_DEST;

4. If SPACE_USED is equal to SPACE_LIMIT, move the archive logs to different destination.

5. Archive all the log files

SQL> alter system archive log all;

NOTE:  See Note 315098.1 for pointers on backing up files from the flash recovery area to decrease space usage

6. If ORA-16020: less destinations available than specified by
LOG_ARCHIVE_MIN_SUCCEED_DEST

is received for step 5, then

For each destination give the correct archivelog path and issue:

SQL>alter system set LOG_ARCHIVE_DEST_.. = 'location=/archivelogpath reopen';

NOTE: 

If you have configurated Flash Recovery Area (default in 10gR2)--meaning that you have set the two initialization parameters:
-- DB_RECOVERY_FILE_DEST_SIZE
-- DB_RECOVERY_FILE_DEST:
you can not use the LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST parameters any more. You must always use the LOG_ARCHIVE_DEST_n parameters in case you have configured flash recovery area. LOG_ARCHIVE_DEST_10 is implicitly set to USE_DB_RECOVERY_FILE_DEST if you create a recovery area and do not set any other local archiving destinations.

If you try to use LOG_ARCHIVE_DEST with a Flash Recovery Area configured you will receive errors like

ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or
DB_RECOVERY_FILE_DEST

7. Just switch the logs to verify:

SQL> alter system switch logfile;


NOTE:
====

If you want to increase the flash recovery area:

STEPS:
=====

1. Note down the path of flash recovery area.

SQL> show parameter db_recovery_file_dest;

2. Disable the Flash Recovery Area

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='' SCOPE=BOTH SID='*';

3. Increase the Flash Recovery Area

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10g SCOPE=BOTH SID='*';

4. Enable the Flash Recovery Area

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/dir1' SCOPE=BOTH SID='*';

If the flash recovery area location is an Automatic Storage Management disk group named disk1, for example, then you can do the following:

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '+disk1' SCOPE=BOTH SID='*';

Oracle 8.1.6 -> 8.1.7 로 upgrade 하는 방법
==============================================================================
This is a short Guide to Migrate Oracle 8.1.x (x < 7) to 8.1.7 using Oracle Migration Scripts. We have successfully migrated several databases using this method.

Content

Special Migration Steps are needed (not documented here) for
Prepare to migrate
Migration Steps
Postmigration
Additional Checks after the Migration

Special Migration Steps are needed (not documented here) for

• Upgrading interMedia Text From a Previous 8.1 Release

• Upgrading JServer

• Upgrading Oracle Parallel Server

• Upgrading Snapshots

• Upgrading the Advanced Queuing Option

Prepare to migrate

Complete the following steps to begin the upgrade process:

• Before you migrate or upgrade to release 8.1, make sure the DB_DOMAIN initialization parameter in your initialization parameter file is set to one of the following:

• .WORLD
• A valid domain setting for your environment

• Make sure the _SYSTEM_TRIG_ENABLED initialization parameter is set to FALSE in the initialization parameter file. If this initialization parameter is not currently set, then explicitly set it to FALSE:

_SYSTEM_TRIG_ENABLED = FALSE

• Do NOT drop the OUTLN User

If you are upgrading from an 8.1 release, then you do not need to perform this check because the OUTLN user should have been created when you installed the previous 8.1 release. Do not drop the OUTLN user if you are upgrading from a previous 8.1 release.

• Check free Space in SYSTEM and Rollback Segment Tablepace

Upgrading to a new release requires more space in your SYSTEM tablespace and in the tablespaces where you store rollback segments. If you have enough space on your system, then consider adding more space to these tablespaces. In general, you need at least 20 MB of free space in your SYSTEM tablespace to upgrade. Check the free space with the following SQL statement

clear buffer
clear columns
clear breaks
set linesize 500
set pagesize 5000
column a1 heading 'Tablespace' format a15
column a2 heading 'Data File' format a45
column a3 heading 'Total|Space [MB]' format 99999.99
column a4 heading 'Free|Space [MB]' format 99999.99
column a5 heading 'Free|%' format 9999.99
break on a1 on report
compute sum of a3 on a1
compute sum of a4 on a1
compute sum of a3 on report
compute sum of a4 on report
SELECT a.tablespace_name a1,
       a.file_name a2,
       a.avail a3,
       NVL(b.free,0) a4,
       NVL(ROUND(((free/avail)*100),2),0) a5
  FROM (SELECT tablespace_name,
               SUBSTR(file_name,1,45) file_name,
               file_id,
               ROUND(SUM(bytes/(1024*1024)),3) avail
          FROM sys.dba_data_files
      GROUP BY tablespace_name,
               SUBSTR(file_name,1,45),
               file_id) a,
       (SELECT tablespace_name,
               file_id,
               ROUND(SUM(bytes/(1024*1024)),3) free
          FROM sys.dba_free_space
      GROUP BY tablespace_name, file_id) b
WHERE a.file_id = b.file_id (+)
ORDER BY 1, 2
/

• Save your 8.1.6 Database

Determine the files that you will back up by issuing the following SQL statements:

SPOOL v8files.log;
SELECT member FROM v$logfile;
SELECT name FROM v$datafile;
SELECT value FROM v$parameter WHERE name = 'control_files';
SPOOL OFF;

Migration Steps

• Shutdown your 8.1.x Database and Listener
  (Use shutdown immediate not shutdown abort)

SVRMGR> SHUTDOWN IMMEDIATE
LSNRCTL> STOP <Listener-Name>

• Adjust the initialization parameter file for use with the new release.

db_domain = .WORLD
optimizer_mode = choose
job_queue_processes = 0
aq_tm_processes = 0

• Switch to the Oracle 8.1.7 Environment

Make sure that the following environment variables point to the new release 8.1 directories:

• ORACLE_HOME

• PATH

• ORA_NLS

• LD_LIBRARY_PATH

• Start the Migration (this is the Main Task)

cd $ORACLE_HOME/rdbms/admin

As user SYS you can now perform the migration. If an ordinary user tries to connect, he will get an error messge:

svrmgrl
connect internal
STARTUP RESTRICT
SPOOL revoke_restricted_session;
SELECT 'REVOKE restricted session FROM '
        || username || ';' FROM dba_users
        WHERE username NOT IN ('SYS','SYSTEM');
SPOOL OFF;
@revoke_restricted_session.log;

ALTER SYSTEM ENABLE RESTRICTED SESSION;

SPOOL catoutu.log
SET ECHO ON
@u0801060.sql     # Script for 8.1.6 -> 8.1.7SET ECHO OFF
SPOOL OFF

ALTER SYSTEM DISABLE RESTRICTED SESSION;
SHUTDOWN IMMEDIATE

The script you run creates and alters certain dictionary tables. It also runs the catalog.sql and catproc.sql scripts that come with the release to which you are upgrading, which create the system catalog views and all the necessary packages for using PL/SQL.

Important

Executing this clean shutdown flushes all caches, clears buffers, and performs other DBMS housekeeping activities. These measures are an important final step to ensure the integrity and consistency of the newly upgraded Oracle8i database

If you retain the old Oracle software, then never start the upgraded database with the old software. Only start the database with the executables in the new release 8.1 installation directory.