关于数据库catalog目录使用的一些问题

dbsuper

SQL> create tablespace rman datafile '/u01/app/oracle/oradata/calog/rman01.dbf' size 50m extent management local uniform size 128k;
Tablespace created

SQL> create user rman identified by rman default tablespace rman quota unlimited on rman;
User created

SQL> grant connect,resource,recovery_catalog_owner to rman;
Grant succeeded

SQL> select * from dba_sys_privs t where t.grantee='RECOVERY_CATALOG_OWNER';
GRANTEE                        PRIVILEGE                                ADMIN_OPTION
------------------------------ ---------------------------------------- ------------
RECOVERY_CATALOG_OWNER         CREATE SYNONYM                           NO
RECOVERY_CATALOG_OWNER         CREATE CLUSTER                           NO
RECOVERY_CATALOG_OWNER         ALTER SESSION                            NO
RECOVERY_CATALOG_OWNER         CREATE DATABASE LINK                     NO
RECOVERY_CATALOG_OWNER         CREATE PROCEDURE                         NO
RECOVERY_CATALOG_OWNER         CREATE SEQUENCE                          NO
RECOVERY_CATALOG_OWNER         CREATE TABLE                             NO
RECOVERY_CATALOG_OWNER         CREATE SESSION                           NO
RECOVERY_CATALOG_OWNER         CREATE TYPE                              NO
RECOVERY_CATALOG_OWNER         CREATE VIEW                              NO
RECOVERY_CATALOG_OWNER         CREATE TRIGGER                           NO
11 rows selected

[oracle@dba ~]$ export ORACLE_SID=calog
[oracle@dba ~]$ rman catalog rman/rman

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Feb 4 14:13:34 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to recovery catalog database

RMAN> create catalog;

recovery catalog created

[oracle@dba ~]$ rman catalog rman/rman target sys/oracle@lote

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Feb 4 14:16:35 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: LOTE (DBID=3306955865)
connected to recovery catalog database


RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

[oracle@dba ~]$  export ORACLE_SID=calog
[oracle@dba ~]$ sql /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 4 14:19:41 2015

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

SQL> conn rman/rman
Connected.

在实际的恢复过程中如果目标库处于nomount状态，则无法连接，意味着如果目标空控制文件丢失，则无法链接的情况，具体如下：

监听状态：
[oracle@dba ~]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 06-FEB-2015 09:17:18

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

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

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.4/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/dba/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.71)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.71)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                06-FEB-2015 09:17:19
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.4/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/dba/listener/alert/log.xml
Listening Endpoints Summary...
   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.71)(PORT=1521)))
Services Summary...
Service "lofe" has 1 instance(s).
   Instance "lote", status UNKNOWN, has 1 handler(s) for this service...
Service "rman" has 1 instance(s).
   Instance "calog", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

启动到nomount状态：

SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area  780824576 bytes
Fixed Size                    2257312 bytes
Variable Size                  511708768 bytes
Database Buffers          264241152 bytes
Redo Buffers                    2617344 bytes

[oracle@dba ~]$ rman catalog rman/rman target sys/oracle@lote

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Feb 6 09:19:51 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

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-12528: TNS:listener: all appropriate instances are blocking new connections                                                               -------------------------------------->无法链接

更改目标空的状态为mount:

SQL> alter database mount;

Database altered.

[oracle@dba ~]$ rman catalog rman/rman target sys/oracle@lote

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Feb 6 09:20:05 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: LOTE (DBID=3306955865, not open)
connected to recovery catalog database                                                                                 ------------------------------------------------------>成功链接，why？控制文件丢失怎么办呢？

RMAN> resync catalog;

new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete


是需要新建控制文件启动到mount才可以吗？

typ281

The blocked status is being set by the instance, indicating that the database is not able to accept connections. This can be caused by the instances not being mounted or mounted in a restricted state.

dbsuper

Liu Maclean(刘相兵 发表于 2015-2-6 11:44
你的问题 只是nomount 情况下 sysdba无法登陆 ，估计与密码文件管理有关

SQL> startup nomount force
ORACLE instance started.

Total System Global Area  780824576 bytes
Fixed Size                    2257312 bytes
Variable Size                  511708768 bytes
Database Buffers          264241152 bytes
Redo Buffers                    2617344 bytes

Recovery Manager complete.
[oracle@dba ~]$  rman catalog rman/rman target sys/oracle@lofe

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Feb 6 16:30:43 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: LOTE (not mounted)
connected to recovery catalog database

RMAN>

[oracle@dba ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 06-FEB-2015 16:32:12

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.71)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                06-FEB-2015 16:20:42
Uptime                    0 days 0 hr. 11 min. 30 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.4/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/dba/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.71)(PORT=1521)))
Services Summary...
Service "calog" has 1 instance(s).
  Instance "calog", status READY, has 1 handler(s) for this service...
Service "calogXDB" has 1 instance(s).
  Instance "calog", status READY, has 1 handler(s) for this service...
Service "lofe" has 1 instance(s).
  Instance "lote", status UNKNOWN, has 1 handler(s) for this service...
Service "lote" has 1 instance(s).
  Instance "lote", status BLOCKED, has 1 handler(s) for this service...
Service "rman" has 2 instance(s).
  Instance "calog", status UNKNOWN, has 1 handler(s) for this service...
  Instance "calog", status READY, has 1 handler(s) for this service...
The command completed successfully

看来确实是ok的，只是不知道Service "lote" has 1 instance(s).
  Instance "lote", status BLOCKED, has 1 handler(s) for this service...
是因为什么？？
[oracle@dba ~]$  rman catalog rman/rman target sys/oracle@lote

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Feb 6 16:34:04 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

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-12154: TNS:could not resolve the connect identifier specified

在此表示感谢maclean的帮助指导。。。

dbsuper

Liu Maclean(刘相兵 发表于 2015-2-6 11:44
你的问题 只是nomount 情况下 sysdba无法登陆 ，估计与密码文件管理有关

[oracle@dba ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 06-FEB-2015 16:22:27

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.71)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                06-FEB-2015 16:20:42
Uptime                    0 days 0 hr. 1 min. 44 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.4/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/dba/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.71)(PORT=1521)))
Services Summary...
Service "lofe" has 1 instance(s).
  Instance "lote", status UNKNOWN, has 1 handler(s) for this service...
Service "lote" has 1 instance(s).
  Instance "lote", status BLOCKED, has 1 handler(s) for this service...          ------>有点不明白啊？！
Service "rman" has 1 instance(s).
  Instance "calog", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

修改了本地tnsname.ora已经ok了
lofe =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.71)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = lofe)
    )
  )

LOFE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.71)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = lofe)
    )
  )

[oracle@dba ~]$ sqlplus sys/oracle@lofe as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 6 16:20:19 2015

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

ERROR:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections


Enter user-name: sys@lofe as sysdba
Enter password:
ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name: sys@lofe as sysdba
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

dbsuper

dbsuper 发表于 2015-2-6 16:10
[root@dba ~]# su - oracle
[oracle@dba ~]$ sql /nolog

以上操作已经通过WINS远程进行验证...............

dbsuper

Liu Maclean(刘相兵 发表于 2015-2-6 11:44
你的问题 只是nomount 情况下 sysdba无法登陆 ，估计与密码文件管理有关

[root@dba ~]# su - oracle
[oracle@dba ~]$ sql /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 6 16:05:21 2015

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

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

Total System Global Area  780824576 bytes
Fixed Size                    2257312 bytes
Variable Size                  511708768 bytes
Database Buffers          264241152 bytes
Redo Buffers                    2617344 bytes

[root@dba ~]# su - oracle
[oracle@dba ~]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 06-FEB-2015 16:05:50

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

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

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.4/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/dba/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.71)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.71)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                06-FEB-2015 16:05:51
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.4/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/dba/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.71)(PORT=1521)))
Services Summary...
Service "lofe" has 1 instance(s).
  Instance "lote", status UNKNOWN, has 1 handler(s) for this service...
Service "rman" has 1 instance(s).
  Instance "calog", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Microsoft Windows [版本 6.1.7601]
版权所有 (c) 2009 Microsoft Corporation。保留所有权利。

C:\Users\monkey>sqlpus sys/oracle@lofe
'sqlpus' 不是内部或外部命令，也不是可运行的程序
或批处理文件。

C:\Users\monkey>sqlpuls sys/oracle@lofe
'sqlpuls' 不是内部或外部命令，也不是可运行的程序
或批处理文件。

C:\Users\monkey>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on 星期五 2月 6 16:06:28 2015

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

请输入用户名:  sys@lofe as sysdba
输入口令:

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

个人感觉不是密码文件的问题，求指导，还不是很清楚！

Liu Maclean(刘相兵

你的问题 只是nomount 情况下 sysdba无法登陆 ，估计与密码文件管理有关

dbsuper

Maclean Liu(刘相兵 发表于 2015-2-6 11:02
target

startup nomount;

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  780824576 bytes
Fixed Size                    2257312 bytes
Variable Size                  511708768 bytes
Database Buffers          264241152 bytes
Redo Buffers                    2617344 bytes

[oracle@dba ~]$ sqlplus sys/oracle@lote as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 6 11:08:08 2015

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

ERROR:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections


Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied


SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

SQL> alter database mount;

Database altered.

[oracle@dba ~]$ sqlplus sys/oracle@lote as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 6 11:08:34 2015

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

懂了，

已找到新的解决方案：

[oracle@dba ~]$ rman target / catalog rman/rman@calog

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Feb 6 10:41:07 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)
connected to recovery catalog database

RMAN> list backup;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 02/06/2015 10:41:14
RMAN-06403: could not obtain a fully authorized session
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory

RMAN> startup nomount

Oracle instance started

Total System Global Area     780824576 bytes

Fixed Size                     2257312 bytes
Variable Size                511708768 bytes
Database Buffers             264241152 bytes
Redo Buffers                   2617344 bytes

RMAN> list backup;


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time   
------- ---- -- ---------- ----------- ------------ -------------------
46      Full    9.36M      DISK        00:00:04     2015-02-04 13:10:27
          BP Key: 47   Status: AVAILABLE  Compressed: NO  Tag: TAG20150204T131023

dbsuper

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  780824576 bytes
Fixed Size                    2257312 bytes
Variable Size                  511708768 bytes
Database Buffers          264241152 bytes
Redo Buffers                    2617344 bytes

[oracle@dba ~]$ sqlplus sys/oracle@lote as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 6 11:08:08 2015

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

ERROR:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections


Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied


SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

SQL> alter database mount;

Database altered.

[oracle@dba ~]$ sqlplus sys/oracle@lote as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 6 11:08:34 2015

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

懂了，

已找到新的解决方案：

[oracle@dba ~]$ rman target / catalog rman/rman@calog

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Feb 6 10:41:07 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)
connected to recovery catalog database

RMAN> list backup;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 02/06/2015 10:41:14
RMAN-06403: could not obtain a fully authorized session
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory

RMAN> startup nomount

Oracle instance started

Total System Global Area     780824576 bytes

Fixed Size                     2257312 bytes
Variable Size                511708768 bytes
Database Buffers             264241152 bytes
Redo Buffers                   2617344 bytes

RMAN> list backup;


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time   
------- ---- -- ---------- ----------- ------------ -------------------
46      Full    9.36M      DISK        00:00:04     2015-02-04 13:10:27
         BP Key: 47   Status: AVAILABLE  Compressed: NO  Tag: TAG20150204T131023

Maclean Liu(刘相兵

target

startup nomount;

sqlplus sys/oracle@lote as sysdba

给出结果

itfit

职业挽尊