Using RMAN to fix the issue based on time points, the strange thing is that, after setting nls_date_format, you cannot log on to RMAN?
Symptoms:
C:> set NLS_DATE_FORMAT = 'yyyy-MM-DD HH24: MI: ss'
C:> rman nocatalog target rman/rman @ test
Recovery Manager: Release 9.2.0.1.0-Production
RMAN-00571: ========================================================== ==============================
RMAN-00569: ==================== error message stack follows ==========================
RMAN-00571: ========================================================== ==============================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database: ORA-00604: error occurred at recursive SQL level 1
ORA-02248: invalid option for ALTER SESSION
You can log on with rman nocatalog targetrman/rman @ test before setting NLS_DATE_FORMAT.
In fact, the cause of the problem is that the nls_date_format is not correctly set, because it is affected by the setting in sqlplus,
So sometimes we directly C:> set NLS_DATE_FORMAT = 'yyyy-MM-DD HH24: MI: ss'
In fact, there is no error in win cmd, but this is incorrect!
The correct method is:
C:> set NLS_DATE_FORMAT = YYYY-MM-DD HH24: MI: SS -- note what is removed?
Then, C:> rman nocatalog targetrman/rman @ test
OK, success!
Appendix: rman until time recover
RMAN> run {
Allocate channel c1 type disk;
Alloctae channel c2 type disk;
Set until time = '2017-05-21: 15: 08: 01 ';
Restore database;
Recover database;
SQL 'alter database open resetlogs ';
Release channel c1;
Release channel c2;
}
Appendix: how to modify nls_date_format
Modify the default format in win cmd:
SQL> select sysdate from dual;
SYSDATE
----------
September 22-07
The method for setting nls_date_format is as follows:
1. Use alter session to modify
SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24: mi: ss'; -- Note: this may create a mindset.
SQL> select sysdate from dual;
SYSDATE
-------------------
15:51:26
2. Set nls_date_format iN OS
C:> SET NLS_DATE_FORMAT = yyyy-mm-dd hh24: mi: ss -- Note: yyyy-mm-dd hh24: mi: ss cannot be enclosed by quotation marks, but must be added to unix systems
SQL> select sysdate from dual;
SYSDATE
-------------------
15:51:26
3. Modify in RMAN
RMAN> run {SQL "alter session set nls_date_format = ''yyyy-mm-dd hh24: mi: s ''";}
Format description:
[Single] [single] yyyy-mm-dd hh24: mi: ss [single] [single]
SQL [double]... [Double]
-- [Single] represents single quotes, and [double] represents double quotes.
4. Modify in RMAN
Set until time = "TO_DATE ('2017-05-21 15:08:01 ', 'yyyy-mm-dd hh24: mi: ss ')";
5. Permanent Effect
The preceding two methods are only valid for the current session. If you do not want to set them every time, modify the environment variable and add the nls_date_format variable,
The value is yyyy-mm-dd hh24: mi: ss.