ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORA-01861: 文字与格式字符串不匹配

ORA-01861: 文字与格式字符串不匹配

原创 Linux操作系统 作者:xz43 时间:2013-07-11 14:19:08 0 删除 编辑
今天使用RMAN恢复一个指定的表空间到某个时间点,不管我怎么指定日期格式,最后都会报错。
执行

RMAN> sql 'alter session set NLS_DATE_FORMAT="yyyy-MM-dd hh24:mi:ss"';

或者退出RMAN执行

$ export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'

执行recover到最后都报错

RMAN> recover tablespace TEST_DB until time '2013-07-05 16:26:16' auxiliary destination '/app/auxiliary';


Starting recover at 2013-07-08 11:18:06

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=136 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=72 device type=DISK

RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-time


List of tablespaces expected to have UNDO segments

Tablespace SYSTEM

Tablespace UNDOTBS1


Creating automatic instance, with SID='gwAb'


initialization parameters used for automatic instance:

db_name=ORCL

db_unique_name=gwAb_tspitr_ORCL

compatible=11.2.0.0.0

db_block_size=8192

db_files=200

sga_target=280M

processes=50

db_create_file_dest=/app/auxiliary

log_archive_dest_1='location=/app/auxiliary'

#No auxiliary parameter file used



starting up automatic instance ORCL


Oracle instance started


Total System Global Area     292278272 bytes


Fixed Size                     2212736 bytes

Variable Size                100666496 bytes

Database Buffers             184549376 bytes

Redo Buffers                   4849664 bytes

Automatic instance created

Running TRANSPORT_SET_CHECK on recovery set tablespaces

TRANSPORT_SET_CHECK completed successfully


contents of Memory Script.:

{

# set requested point in time

set until  time "2013-07-05 16:26:16";

# restore the controlfile

restore clone controlfile;

# mount the controlfile

sql clone 'alter database mount clone database';

# archive current online log 

sql 'alter system archive log current';

# avoid unnecessary autobackups for structural changes during TSPITR

sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';

}

executing Memory Script


executing command: SET until clause


Starting restore at 2013-07-08 11:19:58

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=81 device type=DISK

allocated channel: ORA_AUX_DISK_2

channel ORA_AUX_DISK_2: SID=5 device type=DISK


channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /app/flash_recovery_area/ORCL/backupset/2013_07_05/o1_mf_ncnnf_TAG20130705T162423_8xf0qtxw_.bkp

channel ORA_AUX_DISK_1: piece handle=/app/flash_recovery_area/ORCL/backupset/2013_07_05/o1_mf_ncnnf_TAG20130705T162423_8xf0qtxw_.bkp tag=TAG20130705T162423

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/app/auxiliary/ORCL/controlfile/o1_mf_8xncyzk1_.ctl

Finished restore at 2013-07-08 11:20:00


sql statement: alter database mount clone database


sql statement: alter system archive log current


sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;


contents of Memory Script.:

{

# set requested point in time

set until  time "2013-07-05 16:26:16";

# set destinations for recovery set and auxiliary set datafiles

set newname for clone datafile  1 to new;

set newname for clone datafile  3 to new;

set newname for clone datafile  2 to new;

set newname for clone tempfile  1 to new;

set newname for clone tempfile  2 to new;

set newname for datafile  5 to 

 "/app/oradata/datafile/TEST_DB_1";

set newname for datafile  6 to 

 "/app/oradata/datafile/TEST_DB_2";

# switch all tempfiles

switch clone tempfile all;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile  1, 3, 2, 5, 6;

switch clone datafile all;

}

executing Memory Script


executing command: SET until clause


executing command: SET NEWNAME


executing command: SET NEWNAME


executing command: SET NEWNAME


executing command: SET NEWNAME


executing command: SET NEWNAME


executing command: SET NEWNAME


executing command: SET NEWNAME


renamed tempfile 1 to /app/auxiliary/ORCL/datafile/o1_mf_temp_%u_.tmp in control file

renamed tempfile 2 to /app/auxiliary/ORCL/datafile/o1_mf_test_tmp_%u_.tmp in control file


Starting restore at 2013-07-08 11:20:06

using channel ORA_AUX_DISK_1

using channel ORA_AUX_DISK_2


skipping datafile 6; already restored to file /app/oradata/datafile/TEST_DB_2

skipping datafile 5; already restored to file /app/oradata/datafile/TEST_DB_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00003 to /app/auxiliary/ORCL/datafile/o1_mf_undotbs1_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00002 to /app/auxiliary/ORCL/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece /app/flash_recovery_area/ORCL/backupset/2013_07_05/o1_mf_nnndf_TAG20130705T162423_8xf0orhx_.bkp

channel ORA_AUX_DISK_2: starting datafile backup set restore

channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_2: restoring datafile 00001 to /app/auxiliary/ORCL/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_2: reading from backup piece /app/flash_recovery_area/ORCL/backupset/2013_07_05/o1_mf_nnndf_TAG20130705T162423_8xf0orog_.bkp

channel ORA_AUX_DISK_1: piece handle=/app/flash_recovery_area/ORCL/backupset/2013_07_05/o1_mf_nnndf_TAG20130705T162423_8xf0orhx_.bkp tag=TAG20130705T162423

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:15

channel ORA_AUX_DISK_2: piece handle=/app/flash_recovery_area/ORCL/backupset/2013_07_05/o1_mf_nnndf_TAG20130705T162423_8xf0orog_.bkp tag=TAG20130705T162423

channel ORA_AUX_DISK_2: restored backup piece 1

channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:02:15

Finished restore at 2013-07-08 11:22:22


datafile 1 switched to datafile copy

input datafile copy RECID=20 STAMP=820236142 file name=/app/auxiliary/ORCL/datafile/o1_mf_system_8xncz7z7_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=21 STAMP=820236142 file name=/app/auxiliary/ORCL/datafile/o1_mf_undotbs1_8xncz85z_.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=22 STAMP=820236142 file name=/app/auxiliary/ORCL/datafile/o1_mf_sysaux_8xncz80d_.dbf


contents of Memory Script.:

{

# set requested point in time

set until  time "2013-07-05 16:26:16";

# online the datafiles restored or switched

sql clone "alter database datafile  1 online";

sql clone "alter database datafile  3 online";

sql clone "alter database datafile  2 online";

sql clone "alter database datafile  5 online";

sql clone "alter database datafile  6 online";

# recover and open resetlogs

recover clone database tablespace  "TEST_DB", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;

alter clone database open resetlogs;

}

executing Memory Script


executing command: SET until clause


sql statement: alter database datafile  1 online


sql statement: alter database datafile  3 online


sql statement: alter database datafile  2 online


sql statement: alter database datafile  5 online


sql statement: alter database datafile  6 online


Starting recover at 2013-07-08 11:22:23

using channel ORA_AUX_DISK_1

using channel ORA_AUX_DISK_2


starting media recovery

media recovery failed


Removing automatic instance

shutting down automatic instance 

database dismounted

Oracle instance shut down

Automatic instance removed

auxiliary instance file /app/auxiliary/ORCL/datafile/o1_mf_sysaux_8xncz80d_.dbf deleted

auxiliary instance file /app/auxiliary/ORCL/datafile/o1_mf_undotbs1_8xncz85z_.dbf deleted

auxiliary instance file /app/auxiliary/ORCL/datafile/o1_mf_system_8xncz7z7_.dbf deleted

auxiliary instance file /app/auxiliary/ORCL/controlfile/o1_mf_8xncyzk1_.ctl deleted

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 07/08/2013 11:22:31

RMAN-03015: error occurred in stored script. Memory Script

RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed

 start until time '7月  05 2013 16:26:16' using backup controlfile

ORA-01861: 文字与格式字符串不匹配


RMAN> 

哪位高手遇到过该问题,或者有解决办法,希望能指点一下。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9399028/viewspace-766045/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-11-16

  • 博文量
    420
  • 访问量
    1741423