Time Format problems in RMAN

Source: Internet
Author: User

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.