oracle 9.2.0.8 rman 备份恢复至中间库并升级至10.2.0.5.0 测试


Server/Database Name
Source database orcl
Source server   192.168.1.88            shost
Target database orcl
Target server  192.168.1.222            shost
TNS alias for source database    orcl
TNS alias for target database    orcl
Oracle version:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE        9.2.0.8.0        Production
TNS for Linux: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

用户环境变量:
[oracle@shost ~]$ id oracle
uid=500(oracle) gid=500(oinstall) groups=500(oinstall),501(DBA)
[oracle@shost ~]$ cat .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATHHOME/bin

export PATH
unset USERNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/9.2.0; export ORACLE_HOME
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=$ORACLE_HOME/binPATH; export PATH
ORACLE_OWNER=oracle; export ORACLE_OWNER
ORACLE_SID=orcl; export ORACLE_SID
LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JREORACLE_HOME/jlibORACLE_HOME/rdbms/jlib
CLASSPATH=$CLASSPATHORACLE_HOME/network/jlib; export CLASSPATH
LD_ASSUME_KERNEL=2.4.19; export LD_ASSUME_KERNEL
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
TMPDIR=$TMP; export TMPDIR





1.确认源端无长事务

select * from v$transaction;

2.确认scn

select dbms_flashback.get_system_change_number() from dual;
SQL> select dbms_flashback.get_system_change_number() from dual;

DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()
-----------------------------------------
                                   184000


alter system switch logfile;

3.源端及中间库创建备份目录

mkdir -p /backups/rman

[root@shost ~]# id oracle
uid=500(oracle) gid=500(oinstall) groups=500(oinstall),501(dba)
[root@shost ~]# chown -R oracleinstall /backups/
[root@shost ~]# chmod -R 775 /backups/

注意调整权限


3.备份数据库及归档
rman target /

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backups/rman/ORCL_%U';

vi backup.sh
export ORACLE_SID=orcl                                                                    
export ORACLE_HOME=/u01/app/oracle/product/9.2.0                                               
        rman target / nocatalog log=/backups/rman/test690/full_backup.log << EOF                        
        run                                                                                       
        {                                                                                          
        allocate channel ch1 device type disk maxpiecesize 32000m;                                          
                   
        allocate channel ch2 device type disk maxpiecesize 32000m;                                          
                   
        allocate channel ch3 device type disk maxpiecesize 32000m;                                          
                   
        allocate channel ch4 device type disk maxpiecesize 32000m;                                          
                   
        backup full database format '/backups/rman/full_%d_%T_%s_%p.bak';  
        backup archivelog all format '/backups/rman/arch_%d_%T_%s_%p.bak';                     
        backup current controlfile format '/backups/rman/control_%d_%T_%s_%p.bak' ;            
        release channel ch1;                                                                       
        release channel ch2;                                                                       
        release channel ch3;                                                                       
        release channel ch4;                                                                       
        }                                                                                          
        exit;                                                                                      
        EOF  


./backup.sh

RMAN> backup current controlfile format '/backups/rman/controlfile.bak';



4.拷贝备份集至中间库
cd /backups/rman/

scp -pr  * oracle@192.168.1.222:/backups/rman/
scp -pr controlfile.bak 192.168.1.222:/backups/rman/

5.Edit $TNS_ADMIN/tnsnames.ora

6.配置监听

7.源端执行创建静态参数文件
create pfile from spfile;
[oracle@shost dbs]$ sqlplus /nolog

SQL*Plus: Release 9.2.0.8.0 - Production on Thu Dec 1 12:32:14 2016

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> connect /as sysdba
Connected.
SQL> create pfile='/tmp/pfile.ora' from spfile;

File created.


并将参数文件拷贝至中间库$ORACLE_HOME/dbs目录下,根据目录是否相同决定是否调整参数文件
[oracle@shost tmp]$ echo $ORACLE_HOME/dbs
/u01/app/oracle/product/9.2.0/dbs
[oracle@shost tmp]$ mv pfile.ora initorcl.ora
[oracle@shost tmp]$ scp initorcl.ora 192.168.1.222:/u01/app/oracle/product/9.2.0/dbs/
oracle@192.168.1.222's password:
initorcl.ora                                                                          100% 1133     1.1KB/s   00:00  
[oracle@shost dbs]$ cat initorcl.ora
*.aq_tm_processes=1
*.background_dump_dest='/u01/app/oracle/admin/orcl/bdump'
*.compatible='9.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl','/u01/app/oracle/oradata/orcl/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/orcl/cdump'
*.db_block_size=8192
*.db_cache_size=25165824
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name='orcl'
*.java_pool_size=115343360
*.job_queue_processes=10
*.large_pool_size=8388608
*.log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/orcl/archive'
*.log_archive_format='%t_%s.dbf'
*.log_archive_start=true
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=115343360
*.sort_area_size=524288
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/orcl/udump'


中间库创建相关目录结构

mkdir -p /u01/app/oracle/admin/orcl/bdump
mkdir -p /u01/app/oracle/oradata/orcl/
mkdir -p /u01/app/oracle/admin/orcl/cdump
mkdir -p /u01/app/oracle/oradata/orcl/archive
mkdir -p /u01/app/oracle/admin/orcl/udump


8.中间库创建口令文件
$cd $ORACLE_HOME/dbs
$orapwd password=oracle file=orapworcl

9.启动中间库至nomount状态
export ORACLE_SID=orcl
echo $ORACLE_SID
sqlplus /nolog
connect / as sysdba
startup nomount pfile='/u01/app/oracle/product/9.2.0/dbs/initorcl.ora';

10.恢复控制文件
源端:

rman target /
list backup of controlfile;

中间库:

rman target /
RMAN> restore controlfile from '/backups/rman/controlfile.bak';

Starting restore at 01-DEC-16

using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=13 devtype=DISK
channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
replicating controlfile
input filename=/u01/app/oracle/oradata/orcl/control01.ctl
output filename=/u01/app/oracle/oradata/orcl/control02.ctl
output filename=/u01/app/oracle/oradata/orcl/control03.ctl
Finished restore at 01-DEC-16

RMAN>

sql 'alter database mount';

catalog backuppiece '/backups/rman/ORCL_01rmched_1_1'
catalog backuppiece '/backups/rman/ORCL_03rmchfu_1_1'
catalog backuppiece '/backups/rman/ORCL_02rmchef_1_1'


RUN
{
SET UNTIL SCN 183194;
RESTORE DATABASE;
RECOVER DATABASE;
}
RMAN>  RUN
{
SET UNTIL SCN 183194;
RESTORE DATABASE;
RECOVER DATABASE;
}2> 3> 4> 5> 6>

executing command: SET until clause

Starting restore at 01-DEC-16

using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00006 to /u01/app/oracle/oradata/orcl/indx01.dbf
restoring datafile 00009 to /u01/app/oracle/oradata/orcl/users01.dbf
restoring datafile 00010 to /u01/app/oracle/oradata/orcl/xdb01.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/backups/rman/full_ORCL_20161201_4_1.bak tag=TAG20161201T131512 params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00003 to /u01/app/oracle/oradata/orcl/cwmlite01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/orcl/drsys01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/backups/rman/full_ORCL_20161201_5_1.bak tag=TAG20161201T131512 params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /u01/app/oracle/oradata/orcl/undotbs01.dbf
restoring datafile 00007 to /u01/app/oracle/oradata/orcl/odm01.dbf
restoring datafile 00008 to /u01/app/oracle/oradata/orcl/tools01.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/backups/rman/full_ORCL_20161201_6_1.bak tag=TAG20161201T131512 params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/backups/rman/full_ORCL_20161201_7_1.bak tag=TAG20161201T131512 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 01-DEC-16

Starting recover at 01-DEC-16
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 14 is already on disk as file /u01/app/oracle/oradata/orcl/archive/1_14.dbf
archive log filename=/u01/app/oracle/oradata/orcl/archive/1_14.dbf thread=1 sequence=14
media recovery complete
Finished recover at 01-DEC-16

RMAN> sql'alter database open';

sql statement: alter database open
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 12/01/2016 13:37:47
RMAN-11003: failure during parse/execution of SQL statement: alter database open
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN> sql'alter database open resetlogs';

sql statement: alter database open resetlogs

RMAN>









[oracle@shost rman]$ rman target /

Recovery Manager: Release 9.2.0.8.0 - 64bit Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

connected to target database: ORCL (DBID=1454042070)

RMAN> list backup of database;

using target database controlfile instead of recovery catalog

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4       Full    20M        DISK        00:00:04     01-DEC-16      
        BP Key: 4   Status: AVAILABLE   Tag: TAG20161201T131512
        Piece Name: /backups/rman/full_ORCL_20161201_4_1.bak
  List of Datafiles in backup set 4
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  6       Full 183190     01-DEC-16 /u01/app/oracle/oradata/orcl/indx01.dbf
  9       Full 183190     01-DEC-16 /u01/app/oracle/oradata/orcl/users01.dbf
  10      Full 183190     01-DEC-16 /u01/app/oracle/oradata/orcl/xdb01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5       Full    132M       DISK        00:00:08     01-DEC-16      
        BP Key: 5   Status: AVAILABLE   Tag: TAG20161201T131512
        Piece Name: /backups/rman/full_ORCL_20161201_5_1.bak
  List of Datafiles in backup set 5
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  3       Full 183191     01-DEC-16 /u01/app/oracle/oradata/orcl/cwmlite01.dbf
  4       Full 183191     01-DEC-16 /u01/app/oracle/oradata/orcl/drsys01.dbf
  5       Full 183191     01-DEC-16 /u01/app/oracle/oradata/orcl/example01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6       Full    323M       DISK        00:00:22     01-DEC-16      
        BP Key: 6   Status: AVAILABLE   Tag: TAG20161201T131512
        Piece Name: /backups/rman/full_ORCL_20161201_6_1.bak
  List of Datafiles in backup set 6
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  2       Full 183193     01-DEC-16 /u01/app/oracle/oradata/orcl/undotbs01.dbf
  7       Full 183193     01-DEC-16 /u01/app/oracle/oradata/orcl/odm01.dbf
  8       Full 183193     01-DEC-16 /u01/app/oracle/oradata/orcl/tools01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7       Full    311M       DISK        00:00:27     01-DEC-16      
        BP Key: 7   Status: AVAILABLE   Tag: TAG20161201T131512
        Piece Name: /backups/rman/full_ORCL_20161201_7_1.bak
  List of Datafiles in backup set 7
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 183194     01-DEC-16 /u01/app/oracle/oradata/orcl/system01.dbf

RMAN> list backup of database summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Tag
------- -- -- - ----------- --------------- ------- ------- ---
4       B  F  A DISK        01-DEC-16       1       1       TAG20161201T131512
5       B  F  A DISK        01-DEC-16       1       1       TAG20161201T131512
6       B  F  A DISK        01-DEC-16       1       1       TAG20161201T131512
7       B  F  A DISK        01-DEC-16       1       1       TAG20161201T131512

RMAN> list backup of archivelog;

RMAN> list backup of archivelog all;


List of Backup Sets
===================

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
8       11M        DISK        00:00:02     01-DEC-16      
        BP Key: 8   Status: AVAILABLE   Tag: TAG20161201T131547
        Piece Name: /backups/rman/arch_ORCL_20161201_8_1.bak

  List of Archived Logs in backup set 8
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    11      170711     26-OCT-16 173893     01-DEC-16

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
9       31K        DISK        00:00:02     01-DEC-16      
        BP Key: 9   Status: AVAILABLE   Tag: TAG20161201T131547
        Piece Name: /backups/rman/arch_ORCL_20161201_10_1.bak

  List of Archived Logs in backup set 9
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    13      175008     01-DEC-16 175091     01-DEC-16

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
10      414K       DISK        00:00:02     01-DEC-16      
        BP Key: 10   Status: AVAILABLE   Tag: TAG20161201T131547
        Piece Name: /backups/rman/arch_ORCL_20161201_9_1.bak

  List of Archived Logs in backup set 10
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    12      173893     01-DEC-16 175008     01-DEC-16

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
11      2M         DISK        00:00:02     01-DEC-16      
        BP Key: 11   Status: AVAILABLE   Tag: TAG20161201T131547
        Piece Name: /backups/rman/arch_ORCL_20161201_11_1.bak

  List of Archived Logs in backup set 11
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    14      175091     01-DEC-16 183270     01-DEC-16



[oracle@shost rman]$ sqlplus /nolog

SQL*Plus: Release 9.2.0.8.0 - Production on Thu Dec 1 13:39:13 2016

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> connect /as sysdba
Connected.
SQL> !lsnrctl start

LSNRCTL for Linux: Version 9.2.0.8.0 - Production on 01-DEC-2016 13:39:25

Copyright (c) 1991, 2006, Oracle Corporation.  All rights reserved.

Starting /u01/app/oracle/product/9.2.0/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 9.2.0.8.0 - Production
System parameter file is /u01/app/oracle/product/9.2.0/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/9.2.0/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=shost)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=shost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 9.2.0.8.0 - Production
Start Date                01-DEC-2016 13:39:25
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  OFF
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/9.2.0/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/9.2.0/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=shost)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Services Summary...
Service "LSExtProc" has 1 instance(s).
  Instance "LSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

SQL> alter system register;

System altered.

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
[oracle@shost rman]$ tnsping orcl

TNS Ping Utility for Linux: Version 9.2.0.8.0 - Production on 01-DEC-2016 13:39:37

Copyright (c) 1997, 2006, Oracle Corporation.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = shost)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))
OK (0 msec)
[oracle@shost rman]$ sqlplus system/oracle

SQL*Plus: Release 9.2.0.8.0 - Production on Thu Dec 1 13:39:42 2016

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL>


create spfile from pfile='/u01/app/oracle/product/9.2.0/dbs/initorcl.ora';
shutdown immediate;
startup











$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$



[oracle@shost dbs]$ cd $ORACLE_HOME/dbs
[oracle@shost dbs]$ pwd
/u01/app/oracle/product/9.2.0/dbs
[oracle@shost dbs]$ su - ora10
Password:
[ora10@shost ~]$ echo $ORACLE_HOME
/u02/app/oracle/product/10.2.0/db_1
[ora10@shost ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Dec 1 13:45:51 2016

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> connect /as sysdba
Connected to an idle instance.
SQL> startup pfile='/u01/app/oracle/product/9.2.0/dbs/initorcl.ora';
ORA-25138: HASH_JOIN_ENABLED initialization parameter has been made obsolete
ORA-32006: LOG_ARCHIVE_START initialization parameter has been deprecated
ORACLE instance started.

Total System Global Area  276824064 bytes
Fixed Size                    2095640 bytes
Variable Size                  243271144 bytes
Database Buffers           25165824 bytes
Redo Buffers                    6291456 bytes
ORA-00221: error on write to control file
ORA-00206: error in writing (block 1, # blocks 1) of control file
ORA-00202: control file: '/u01/app/oracle/oradata/orcl/control01.ctl'
ORA-27091: unable to queue I/O
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 3


SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[ora10@shost ~]$ exit
logout
[oracle@shost dbs]$ exit
logout
[root@shost rman]# chmod -R 775 /u01/
[root@shost rman]# su - ora10
[ora10@shost ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Dec 1 13:47:52 2016

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> connect /as sysdba
Connected.
SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup upgrade pfile='/u01/app/oracle/product/9.2.0/dbs/initorcl.ora';
SQL>  startup upgrade pfile='/u01/app/oracle/product/9.2.0/dbs/initorcl.ora';
ORA-25138: HASH_JOIN_ENABLED initialization parameter has been made obsolete
ORA-32006: LOG_ARCHIVE_START initialization parameter has been deprecated
ORACLE instance started.

Total System Global Area  276824064 bytes
Fixed Size                    2095640 bytes
Variable Size                  243271144 bytes
Database Buffers           25165824 bytes
Redo Buffers                    6291456 bytes
Database mounted.
Database opened.
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql
DOC>######################################################################
DOC>######################################################################
DOC>        The following statement will cause an "ORA-01722: invalid number"
DOC>        error if the user running this script is not SYS.  Disconnect
DOC>        and reconnect with AS SYSDBA.
DOC>######################################################################
DOC>######################################################################
DOC>#

no rows selected

DOC>######################################################################
DOC>######################################################################
DOC>        The following statement will cause an "ORA-01722: invalid number"
DOC>        error if the database server version is not correct for this script.
DOC>        Shutdown ABORT and use a different script or a different server.
DOC>######################################################################
DOC>######################################################################
DOC>#

no rows selected

DOC>#######################################################################
DOC>#######################################################################
DOC>   The following statement will cause an "ORA-01722: invalid number"
DOC>   error if the database has not been opened for UPGRADE.
DOC>
DOC>   Perform a "SHUTDOWN ABORT"  and
DOC>   restart using UPGRADE.
DOC>#######################################################################
DOC>#######################################################################
DOC>#

no rows selected

DOC>#######################################################################
DOC>#######################################################################
DOC>        The following statements will cause an "ORA-01722: invalid number"
DOC>        error if the SYSAUX tablespace does not exist or is not
DOC>        ONLINE for READ WRITE, PERMANENT, EXTENT MANAGEMENT LOCAL, and
DOC>        SEGMENT SPACE MANAGEMENT AUTO.
DOC>
DOC>        The SYSAUX tablespace is used in 10.1 to consolidate data from
DOC>        a number of tablespaces that were separate in prior releases.
DOC>        Consult the Oracle Database Upgrade Guide for sizing estimates.
DOC>
DOC>        Create the SYSAUX tablespace, for example,
DOC>
DOC>         create tablespace SYSAUX datafile 'sysaux01.dbf'
DOC>             size 70M reuse
DOC>             extent management local
DOC>             segment space management auto
DOC>             online;
DOC>
DOC>        Then rerun the catupgrd.sql script.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SELECT TO_NUMBER('No SYSAUX tablespace') FROM dual
                 *
ERROR at line 1:
ORA-01722: invalid number


Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[ora10@shost ~]$
[ora10@shost ~]$
[ora10@shost ~]$
[ora10@shost ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Dec 1 13:52:32 2016

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> connect /as sysdba
Connected.
SQL> select open_mode from v$database;

OPEN_MODE
----------
READ WRITE

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[ora10@shost ~]$ exit
logout
[root@shost rman]# su - oracle
[oracle@shost ~]$ sqlplus /nolog

SQL*Plus: Release 9.2.0.8.0 - Production on Thu Dec 1 13:55:14 2016

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> connect /as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area  320306616 bytes
Fixed Size                     740792 bytes
Variable Size                  285212672 bytes
Database Buffers           33554432 bytes
Redo Buffers                     798720 bytes
Database mounted.
Database opened.
SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/cwmlite01.dbf
/u01/app/oracle/oradata/orcl/drsys01.dbf
/u01/app/oracle/oradata/orcl/example01.dbf
/u01/app/oracle/oradata/orcl/indx01.dbf
/u01/app/oracle/oradata/orcl/odm01.dbf
/u01/app/oracle/oradata/orcl/tools01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/xdb01.dbf

10 rows selected.

SQL> create tablespace SYSAUX datafile '/u01/app/oracle/oradata/orcl/sysaux01.dbf'
size 70M reuse extent management local segment space management auto online;  2  

Tablespace created.

SQL> shutdown immediate;  
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
[oracle@shost ~]$ su - ora10
Password:
[ora10@shost ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Dec 1 13:57:28 2016

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> connect /as sysdba
Connected to an idle instance.
SQL> startup upgrade pfile='/u01/app/oracle/product/9.2.0/dbs/initorcl.ora';
ORA-25138: HASH_JOIN_ENABLED initialization parameter has been made obsolete
ORA-32006: LOG_ARCHIVE_START initialization parameter has been deprecated
ORACLE instance started.

Total System Global Area  276824064 bytes
Fixed Size                    2095640 bytes
Variable Size                  243271144 bytes
Database Buffers           25165824 bytes
Redo Buffers                    6291456 bytes
Database mounted.
ORA-03113: end-of-file on communication channel



[oracle@shost ~]$ su - oracle
Password:
su: incorrect password
[oracle@shost ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/9.2.0
[oracle@shost ~]$ cp /u01/app/oracle/product/9.2.0/initorcl.ora /u02/app/oracle/product/10.2.0/db_1/dbs/
cp: cannot stat `/u01/app/oracle/product/9.2.0/initorcl.ora': No such file or directory
[oracle@shost ~]$ cp /u01/app/oracle/product/9.2.0/dbs/initorcl.ora /u02/app/oracle/product/10.2.0/db_1/dbs/
cp: cannot create regular file `/u02/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora': Permission denied
[oracle@shost ~]$ su
Password:
[root@shost oracle]# cp /u01/app/oracle/product/9.2.0/dbs/initorcl.ora /u02/app/oracle/product/10.2.0/db_1/dbs/
[root@shost oracle]# cd /u02/app/oracle/product/10.2.0/db_1/dbs/
[root@shost dbs]# ls -l | grep init
-rw-r--r--  1 ora10 oinstall 12920 May  3  2001 initdw.ora
-rw-r-----  1 ora10 oinstall  8385 Sep 11  1998 init.ora
-rwxr-xr-x  1 root  root      1133 Dec  1 14:08 initorcl.ora
[root@shost dbs]# chown ora10install initorcl.ora
[root@shost dbs]# exit
exit
[oracle@shost ~]$ pwd
/home/oracle
[oracle@shost ~]$ exit
logout
[root@shost rman]# su - ora10
[ora10@shost ~]$ cd $ORACLE_HOME/dbs
[ora10@shost dbs]$ vi initorcl.ora
[ora10@shost dbs]$ cat initorcl.ora
*.aq_tm_processes=1
*.background_dump_dest='/u01/app/oracle/admin/orcl/bdump'
*.compatible='9.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl','/u01/app/oracle/oradata/orcl/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/orcl/cdump'
*.db_block_size=8192
*.db_cache_size=25165824
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.fast_start_mttr_target=300
#*.hash_join_enabled=TRUE
*.instance_name='orcl'
*.java_pool_size=115343360
*.job_queue_processes=10
*.large_pool_size=8388608
*.log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/orcl/archive'
*.log_archive_format='%t_%s.dbf'
#*.log_archive_start=true
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=115343360
*.sort_area_size=524288
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/orcl/udump'
[ora10@shost dbs]$

[ora10@shost dbs]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Dec 1 14:11:16 2016

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> connect /as sysdba
Connected to an idle instance.
SQL> startup upgrade pfile='/u02/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora';
ORACLE instance started.

Total System Global Area  276824064 bytes
Fixed Size                    2095640 bytes
Variable Size                  243271144 bytes
Database Buffers           25165824 bytes
Redo Buffers                    6291456 bytes
Database mounted.
ORA-03113: end-of-file on communication channel


SQL>
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=17, OS id=19668
Thu Dec 01 14:11:41 EST 2016
Repairing half complete open of thread 1
Thu Dec 01 14:11:41 EST 2016
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_lgwr_19646.trc:
ORA-01114: IO error writing block to file 11 (block # 1)
ORA-01110: data file 11: '/u01/app/oracle/oradata/orcl/sysaux01.dbf'
ORA-27091: unable to queue I/O
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 3
LGWR: terminating instance due to error 1114
Instance terminated by LGWR, pid = 19646



[oracle@shost bdump]$ exit
logout
[root@shost ~]# chmod -R 775 /u01/
[root@shost ~]#



[ora10@shost dbs]$ cd /u01/app/oracle
[ora10@shost oracle]$ ls
admin  doc  jre  oradata  oraInventory  product
[ora10@shost oracle]$ cd oradata
[ora10@shost oradata]$ ls -l
total 4
drwxrwxr-x  3 oracle oinstall 4096 Dec  1 13:56 orcl
[ora10@shost oradata]$ cd orcl
[ora10@shost orcl]$ ls -l
total 1429676
drwxrwxr-x  2 oracle oinstall      4096 Dec  1 13:27 archive
-rwxrwxr-x  1 oracle oinstall   4022272 Dec  1 14:11 control01.ctl
-rwxrwxr-x  1 oracle oinstall   4022272 Dec  1 14:11 control02.ctl
-rwxrwxr-x  1 oracle oinstall   4022272 Dec  1 14:11 control03.ctl
-rwxrwxr-x  1 oracle oinstall  20979712 Dec  1 14:11 cwmlite01.dbf
-rwxrwxr-x  1 oracle oinstall  20979712 Dec  1 14:11 drsys01.dbf
-rwxrwxr-x  1 oracle oinstall 144842752 Dec  1 14:11 example01.dbf
-rwxrwxr-x  1 oracle oinstall  26222592 Dec  1 14:11 indx01.dbf
-rwxrwxr-x  1 oracle oinstall  20979712 Dec  1 14:11 odm01.dbf
-rwxrwxr-x  1 oracle oinstall 104858112 Dec  1 14:11 redo01.log
-rwxrwxr-x  1 oracle oinstall 104858112 Dec  1 14:11 redo02.log
-rwxrwxr-x  1 oracle oinstall 104858112 Dec  1 14:11 redo03.log
-rwxrwxr-x  1 oracle oinstall  73408512 Dec  1 13:56 sysaux01.dbf
-rwxrwxr-x  1 oracle oinstall 408952832 Dec  1 14:11 system01.dbf
-rwxrwxr-x  1 oracle oinstall  10493952 Dec  1 14:11 tools01.dbf
-rwxrwxr-x  1 oracle oinstall 335552512 Dec  1 14:11 undotbs01.dbf
-rwxrwxr-x  1 oracle oinstall  26222592 Dec  1 14:11 users01.dbf
-rwxrwxr-x  1 oracle oinstall  47194112 Dec  1 14:11 xdb01.dbf
[ora10@shost orcl]$

[root@shost rman]# su - ora10
[ora10@shost ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Dec 1 14:16:03 2016

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> connect /as sysdba
Connected to an idle instance.
SQL> startup upgrade pfile='/u02/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora';
ORACLE instance started.

Total System Global Area  276824064 bytes
Fixed Size                    2095640 bytes
Variable Size                  243271144 bytes
Database Buffers           25165824 bytes
Redo Buffers                    6291456 bytes
Database mounted.
Database opened.
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql
Oracle Database 10.2 Upgrade Status Utility           12-01-2016 14:35:16
.
Component                                Status         Version  HH:MM:SS
Oracle Database Server                  INVALID      10.2.0.5.0  00:05:20
JServer JAVA Virtual Machine              VALID      10.2.0.5.0  00:02:18
Oracle XDK                                VALID      10.2.0.5.0  00:00:47
Oracle Database Java Packages             VALID      10.2.0.5.0  00:00:17
Oracle Text                               VALID      10.2.0.5.0  00:00:39
Oracle XML Database                       VALID      10.2.0.5.0  00:00:55
Oracle Workspace Manager                  VALID      10.2.0.5.0  00:00:38
Oracle Data Mining                        VALID      10.2.0.5.0  00:00:18
OLAP Analytic Workspace                 INVALID      10.2.0.5.0  00:00:03
OLAP Catalog                            INVALID      10.2.0.5.0  00:00:51
Oracle OLAP API                           VALID      10.2.0.5.0  00:00:19
Oracle interMedia                         VALID      10.2.0.5.0  00:02:51
Spatial                                   VALID      10.2.0.5.0  00:02:07
Oracle Ultra Search                   NO SCRIPT       9.2.0.8.0  00:00:00
.
Total Upgrade Time: 00:18:19

PL/SQL procedure successfully completed.

DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC>   The above PL/SQL lists the SERVER components in the upgraded
DOC>   database, along with their current version and status.
DOC>
DOC>   Please review the status and version columns and look for
DOC>   any errors in the spool log file.  If there are errors in the spool
DOC>   file, or any components are not VALID or not the current version,
DOC>   consult the Oracle Database Upgrade Guide for troubleshooting
DOC>   recommendations.
DOC>
DOC>   Next shutdown immediate, restart for normal operation, and then
DOC>   run utlrp.sql to recompile any invalid application objects.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL> select count(*) from dba_objects where status='INVALID';

  COUNT(*)
----------
      1260

1 row selected.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup  pfile='/u02/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora';
ORACLE instance started.

Total System Global Area  276824064 bytes
Fixed Size                  2095640 bytes
Variable Size             243271144 bytes
Database Buffers           25165824 bytes
Redo Buffers                6291456 bytes
Database mounted.
Database opened.
SQL>

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql


alter tablespace temp add tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' size 1g;

shutdown immediate;
startup  pfile='/u02/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora';

  1* select comp_name,status from dba_registry
SQL> col comp_name for a40;
SQL> col status for a10;
SQL> /

COMP_NAME                                STATUS
---------------------------------------- ----------
Oracle Data Mining                       VALID
OLAP Catalog                             VALID
Oracle Ultra Search                      NO SCRIPT
Oracle XML Database                      VALID
Oracle Text                              VALID
Spatial                                  VALID
Oracle interMedia                        VALID
Oracle Workspace Manager                 VALID
Oracle Database Catalog Views            VALID
Oracle Database Packages and Types       INVALID
JServer JAVA Virtual Machine             VALID
Oracle XDK                               VALID
Oracle Database Java Packages            VALID
OLAP Analytic Workspace                  INVALID
Oracle OLAP API                          VALID

15 rows selected.

shutdown immediate;


startup  upgrade pfile='/u02/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora';
SQL> @$ORACLE_HOME/rdbms/admin/utlirp.sql
shutdown immediate;
startup   pfile='/u02/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora';
@$ORACLE_HOME/rdbms/admin/utlrp.sql

col comp_name for a40;
col status for a10;
select comp_name,status from dba_registry;

col comp_name for a40;
col status for a10;
select comp_name,status from dba_registry;

COMP_NAME                                 STATUS
---------------------------------------- ----------
Oracle Data Mining                         VALID
OLAP Catalog                                 VALID
Oracle Ultra Search                         NO SCRIPT
Oracle XML Database                         VALID
Oracle Text                                 VALID
Spatial                                  VALID
Oracle interMedia                         VALID
Oracle Workspace Manager                 VALID
Oracle Database Catalog Views                 VALID
Oracle Database Packages and Types         INVALID
JServer JAVA Virtual Machine                 VALID

COMP_NAME                                 STATUS
---------------------------------------- ----------
Oracle XDK                                 VALID
Oracle Database Java Packages                 VALID
OLAP Analytic Workspace                  INVALID
Oracle OLAP API                          VALID

15 rows selected.


select 'exec dbms_space_admin.tablespace_migrate_to_local('''||tablespace_name||''');' from dba_tablespaces where extent_management='DICTIONARY';

[ora10@shost ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Dec 1 14:59:54 2016

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> connect /as sysdba
Connected.
SQL> select directory_name from dba_directories;

DIRECTORY_NAME
------------------------------
MEDIA_DIR
LOG_FILE_DIR
DATA_FILE_DIR
DATA_PUMP_DIR
ORACLE_OCM_CONFIG_DIR

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[ora10@shost ~]$ expdp full=y directory=data_pump_dir log=exp_full.log
LRM-00101: unknown parameter name 'log'

[ora10@shost ~]$ expdp full=y directory=data_pump_dir logfile=exp_full.log

Export: Release 10.2.0.5.0 - 64bit Production on Thursday, 01 December, 2016 15:00:41

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Username: UDE-00001: user requested cancel of current operation


[ora10@shost ~]$ expdp system/oracle full=y directory=data_pump_dir logfile=exp_full.log

Export: Release 10.2.0.5.0 - 64bit Production on Thursday, 01 December, 2016 15:00:50

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39006: internal error
ORA-39213: Metadata processing is not available



EXPDP – ORA-39006,ORA-39213 Metadata processing is not available




While Taking full database export using “EXPDP” i was getting below mentioned error :

###############################################

bash-3.2$ expdp directory=datapump dumpfile=expdp_hrs92dmo.dmp logfile=expdp_hrs92dmo.log full=y

Export: Release 11.2.0.2.0 – Production on Thu Feb 27 10:48:09 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username : sys@hrs92dmo as sysdba
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORA-39006: internal error
ORA-39213: Metadata processing is not available

###############################################

I tried it many times but the error remains the same.

==> Problem Solution :

(1) Check all the oracle registry entry are in valid state. If not check the issue :

###########################################################

SQL> SELECT comp_id, version, status FROM dba_registry;

COMP_ID VERSION STATUS
—————————— —————————— ———–
EM 11.2.0.4.0 VALID
CATALOG 11.2.0.4.0 VALID
CATPROC 11.2.0.4.0 INVALID
###########################################################


(2) Check for Permission :

During dbms_metadata_util.load_stylesheets we read the directory $ORACLE_HOME/rdbms/xml/xsl and load the XSL files using DBMS_LOB package.
su - ora10
cd $ORACLE_HOME/rdbms/xml/
chmod -R 755 xsl/


If the files cannot be read (most of cases by missing rwx privileges for oracle user), then we raise the errors above.

Re-load the stylesheets using the dbms_metadata_util.load_stylesheets procedure after the permission issue is solved and then retry DataPump export ( expdp ).

###########################################################

SQL> execute dbms_metadata_util.load_stylesheets

PL/SQL procedure successfully completed.

###########################################################



(3) Repair Registry :

In case you have INVALID status in dba_registry. Check it and try to find a solution for it. In my case “CATPROC” was invalid. To resolve this i run “CATPROC.sql” file which is present in “$ORACLE_HOME/RDBMS/admin/” directory :

###########################################################

SQL> ?/rdbms/admin/catproc.sql
###########################################################

SQL> connect /as sysdba
Connected.
SQL> col comp_name for a40;
col status for a10;
select comp_name,status from dba_registry;SQL> SQL>

COMP_NAME                                 STATUS
---------------------------------------- ----------
Oracle Data Mining                         VALID
OLAP Catalog                                 VALID
Oracle Ultra Search                         NO SCRIPT
Oracle XML Database                         VALID
Oracle Text                                 VALID
Spatial                                  VALID
Oracle interMedia                         VALID
Oracle Workspace Manager                 VALID
Oracle Database Catalog Views                 VALID
Oracle Database Packages and Types         VALID
JServer JAVA Virtual Machine                 VALID

COMP_NAME                                 STATUS
---------------------------------------- ----------
Oracle XDK                                 VALID
Oracle Database Java Packages                 VALID
OLAP Analytic Workspace                  INVALID
Oracle OLAP API                          VALID

15 rows selected.


This solved my issue.




[ora10@shost ~]$ exp system/oracle full=y file=full.dmp log=exp_full.log

Export: Release 10.2.0.5.0 - Production on Thu Dec 1 15:01:33 2016

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)
标签: 暂无标签
dongxujian

写了 86 篇文章,拥有财富 384,被 13 人关注

转播转播 分享分享1 分享淘帖
回复

使用道具

您需要登录后才可以回帖 登录 | 加入社区

本版积分规则

意见
反馈