关于数据库catalog目录使用的一些问题
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
$ export ORACLE_SID=calog
$ 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
$ 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
$ export ORACLE_SID=calog
$ 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状态，则无法连接，意味着如果目标空控制文件丢失，则无法链接的情况，具体如下：
监听状态：
$ 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
$ 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.
$ 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才可以吗？ 职业挽尊 target
startup nomount;
sqlplus sys/oracle@lote as sysdba
给出结果 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
$ 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.
$ 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>
懂了，
已找到新的解决方案：
$ 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(刘相兵 发表于 2015-2-6 11:02 static/image/common/back.gif
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
$ 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.
$ 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>
懂了，
已找到新的解决方案：
$ 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 你的问题 只是nomount 情况下 sysdba无法登陆 ，估计与密码文件管理有关 Liu Maclean(刘相兵 发表于 2015-2-6 11:44 static/image/common/back.gif
你的问题 只是nomount 情况下 sysdba无法登陆 ，估计与密码文件管理有关
# su - oracle
$ 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
# su - oracle
$ 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>
个人感觉不是密码文件的问题，求指导，还不是很清楚！ dbsuper 发表于 2015-2-6 16:10 static/image/common/back.gif
# su - oracle
$ sql /nolog
以上操作已经通过WINS远程进行验证............... Liu Maclean(刘相兵 发表于 2015-2-6 11:44 static/image/common/back.gif
你的问题 只是nomount 情况下 sysdba无法登陆 ，估计与密码文件管理有关
$ 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)
)
)
$ 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>
Liu Maclean(刘相兵 发表于 2015-2-6 11:44 static/image/common/back.gif
你的问题 只是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.
$ 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>
$ 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...
是因为什么？？
$ 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的帮助指导。。。
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.
页:
[1]