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=$PATH HOME/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 oracle install /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)
|
|
京ICP备12024223-8号
不良信息举报电话：13718043309
转播
分享
淘帖