搜尋
首頁資料庫mysql教程Oracle 灾难恢复以及11g新特性恢复指导

实验: 数据库灾难恢复(数据文件、控制文件、参数文件、归档文件等丢失) 法一:利用冷备 法二:RMAN恢复及11g新特性(list/advise/repair failure,create spfile from memory) 1.配置catalog数据库 1)catalog目录库:创建大文件表空间、用户、授权 create

实验: 数据库灾难恢复(数据文件、控制文件、参数文件、归档文件等丢失)

法一:利用冷备

法二:RMAN恢复及11g新特性(list/advise/repair failure,create spfile from memory)

1.配置catalog数据库

1)catalog目录库:创建大文件表空间、用户、授权


create  bigfile tablespace rc_data datafile '/u01/app/oracle/oradata/ORCL/rc_data.dbf' size 20m;

create user rc_admin identified by oracle default tablespace rc_data;

grant connect,resource,recovery_catalog_owner to rc_admin;

2)创建catalog库
RMAN> rman catalog rc_admin/oracle@ORCL

RMAN> create catalog;

3)注册catalog库(在target库中)

RMAN> rman target / catalog rc_admin/oracle@ORCL

RMAN>register database;

4)配置target数据库

RMAN> rman target / catalog rc_admin/oracle@ORCL

RMAN>configure retention policy to recovery window of 7 days;
      --修改保留策略

RMAN>configure controlfile autobackup on;
      --打开控制文件自动备份

RMAN>configure device type disk parallelism  4;
      --开启并行备份,行度设置为4


2.备份target数据库

当所有文件丢失时,使用RMAN,应连接catalog库(catalog库中含有控制文件等)

[oracle@jibo admin]$ rman target / catalog rc_admin/oracle@ORCL

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Oct 16 10:19:55 2014

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

connected to target database: PROD (DBID=270879665)
connected to recovery catalog database

RMAN> show all;

RMAN configuration parameters for database with db_unique_name PROD are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_PROD.f'; # default

RMAN> backup database include current controlfile plus archivelog delete all input;
     --全库备份加上归档日志文件
   

3.模拟灾难:

1)删除所有数据文件:
SYS@PROD>select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD/datafile/o1_mf_system_b393xosc_.dbf
/u01/app/oracle/oradata/PROD/datafile/o1_mf_sysaux_b393xovt_.dbf
/u01/app/oracle/oradata/PROD/datafile/o1_mf_undotbs1_b393xq2d_.dbf
/u01/app/oracle/oradata/PROD/datafile/o1_mf_users_b393xqpm_.dbf
/u01/app/oracle/oradata/PROD/datafile/o1_mf_example_b393xp04_.dbf
/u01/app/oracle/oradata/PROD/datafile/tbs_users1.dbf
/u01/app/oracle/oradata/PROD/datafile/tbs_users2.dbf
/u01/app/oracle/oradata/PROD/datafile/pitr.dbf
/u01/app/oracle/oradata/PROD/datafile/pitr_ind.dbf
/u01/app/oracle/oradata/PROD/arch_tbs.dbf

10 rows selected.

SYS@PROD>!rm /u01/app/oracle/oradata/PROD/datafile/*.dbf

SYS@PROD>!rm /u01/app/oracle/oradata/PROD/arch_tbs.dbf

SYS@PROD>desc v$controlfile;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 STATUS                                             VARCHAR2(7)
 NAME                                               VARCHAR2(513)
 IS_RECOVERY_DEST_FILE                              VARCHAR2(3)
 BLOCK_SIZE                                         NUMBER
 FILE_SIZE_BLKS                                     NUMBER


2)删除所有控制文件:
SYS@PROD>select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD/controlfile/o1_mf_b2255k12_.ctl
/u01/app/oracle/fast_recovery_area/PROD/controlfile/o1_mf_b2255kjo_.ctl

SYS@PROD>!rm /u01/app/oracle/oradata/PROD/controlfile/o1_mf_b2255k12_.ctl
SYS@PROD>!rm /u01/app/oracle/fast_recovery_area/PROD/controlfile/o1_mf_b2255kjo_.ctl


3)删除参数文件:
SYS@PROD>show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0
                                                 /dbhome_1/dbs/spfilePROD.ora
SYS@PROD>!rm /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilePROD.ora


4)删除归档文件:
desc v$archived_log --归档文件
SYS@PROD>select name from v$archived_log;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/fast_recovery_area/PROD/archivelog/2014_10_16/o1_mf_1_7_b3y4y1s8_.arc

/u01/app/oracle/fast_recovery_area/PROD/archivelog/2014_10_16/o1_mf_1_8_b3y56mno

...

8 rows selected.

SYS@PROD>!rm /u01/app/oracle/fast_recovery_area/PROD/archivelog/2014_10_16/*;

alter system checkpoint;
  --触发错误

5)退出重新进入:
SYS@PROD>stutdown immediate
SP2-0734: unknown command beginning "stutdown i..." - rest of line ignored.
SYS@PROD>shutdown immediate
ORA-01116: error in opening database file 2
ORA-01110: data file 2: '/u01/app/oracle/oradata/PROD/datafile/o1_mf_sysaux_b393xovt_.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SYS@PROD>shutdown abort
ORACLE instance shut down.
SYS@PROD>startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initPROD.ora'
  --报错

4.利用catalog恢复target数据库

1)在catalog库查看target数据库DBID

sqlplus rc_admin/oracle

RC_ADMIN@ORCL>select * from rc_database;

    DB_KEY  DBINC_KEY       DBID NAME     RESETLOGS_CHANGE# RESETLOGS
---------- ---------- ---------- -------- ----------------- ---------
       241        242 1385095721 ORCL                925702 03-SEP-14
         1         61  270879665 PROD               1107625 08-OCT-14


2)连接catalog数据库
[oracle@jibo ~]$ rman target / catalog rc_admin/oracle@ORCL

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Oct 16 14:08:59 2014

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

connected to target database (not started)
connected to recovery catalog database
    --可以连接,但数据库没有启动

3)设置dbid,并启动数据库到nomount状态
RMAN> set dbid=270879665;

executing command: SET DBID
database name is "PROD" and DBID is 270879665


RMAN> startup nomount;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initPROD.ora'

starting Oracle instance without parameter file for retrieval of spfile


Oracle instance started

Total System Global Area    1068937216 bytes

Fixed Size                     2260088 bytes
Variable Size                281019272 bytes
Database Buffers             780140544 bytes
Redo Buffers                   5517312 bytes

4)还原参数文件和数据文件
RMAN>restore spfile;

RMAN>restore controlfile;
或者:
RMAN>restore spfile from autobackup;
RMAN>restore controlfile from autobackup;
或者:
RMAN>restore spfile from '/u01/app/oracle/fast_recovery_area/PROD/autobackup/2014_10_16/o1_mf_s_861103939_b3yh28ts_.bkp';
RMAN>restore controlfile from '/u01/app/oracle/...';
  --如果Oracle无法找到自动备份文件,则需要手工制定该文件的具体位置

5)启动数据库到mount状态并使用11g数据库的新特性(list/advise/repair failure)
RMAN> startup mount;

database is already started
database mounted
released channel: ORA_DISK_1
released channel: ORA_DISK_2
released channel: ORA_DISK_3
released channel: ORA_DISK_4

新特性:
list failure;
   --如果list failure 没有输出结果,则需要我们手动恢复数据库;
advise failure;
   --如果advise failure 输出的最后,显示没有可用的自动修复选项,则同样需要我们手动恢复;
repair failure;
   --三条命令顺序不能改变

补充:
 如果list failure没有输出结果,
则可以尝试打开数据库,查看报错信息,然后进行相应的处理;
eg:
 alter database open;
 alter database open resetlogs;
 restore datafile 1;
 list failure;
 advise failure;
 repair failure;

指令:
RMAN>list failure;

RMAN>advise failure;

RMAN>repair failure;

具体执行过程:
RMAN> list failure;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
1702       CRITICAL OPEN      16-OCT-14     Control file needs media recovery
1522       CRITICAL OPEN      16-OCT-14     System datafile 1: '/u01/app/oracle/oradata/PROD/datafile/o1_mf_system_b393xosc_.dbf' is missing
709        CRITICAL OPEN      08-OCT-14     System datafile 1: '/u01/app/oracle/oradata/PROD/datafile/o1_mf_system_b393xosc_.dbf' needs media recovery
469        CRITICAL OPEN      08-OCT-14     System datafile 1: '/u01/app/oracle/oradata/PROD/datafile/o1_mf_system_b2251bs1_.dbf' is missing
242        HIGH     OPEN      29-SEP-14     One or more non-system datafiles are missing
974        HIGH     OPEN      10-OCT-14     Tablespace 11: 'PITR_IND' is offline
644        HIGH     OPEN      08-OCT-14     One or more non-system datafiles need media recovery
817        HIGH     OPEN      09-OCT-14     Name for datafile 7 is unknown in the control file
808        HIGH     OPEN      09-OCT-14     Name for datafile 6 is unknown in the control file

RMAN> advise failure;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
1702       CRITICAL OPEN      16-OCT-14     Control file needs media recovery
1522       CRITICAL OPEN      16-OCT-14     System datafile 1: '/u01/app/oracle/oradata/PROD/datafile/o1_mf_system_b393xosc_.dbf' is missing
709        CRITICAL OPEN      08-OCT-14     System datafile 1: '/u01/app/oracle/oradata/PROD/datafile/o1_mf_system_b393xosc_.dbf' needs media recovery
469        CRITICAL OPEN      08-OCT-14     System datafile 1: '/u01/app/oracle/oradata/PROD/datafile/o1_mf_system_b2251bs1_.dbf' is missing
242        HIGH     OPEN      29-SEP-14     One or more non-system datafiles are missing
974        HIGH     OPEN      10-OCT-14     Tablespace 11: 'PITR_IND' is offline
644        HIGH     OPEN      08-OCT-14     One or more non-system datafiles need media recovery
817        HIGH     OPEN      09-OCT-14     Name for datafile 7 is unknown in the control file
808        HIGH     OPEN      09-OCT-14     Name for datafile 6 is unknown in the control file

analyzing automatic repair options; this may take some time
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
analyzing automatic repair options complete

Not all specified failures can currently be repaired.
The following failures must be repaired before advise for others can be given.

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
1702       CRITICAL OPEN      16-OCT-14     Control file needs media recovery
1522       CRITICAL OPEN      16-OCT-14     System datafile 1: '/u01/app/oracle/oradata/PROD/datafile/o1_mf_system_b393xosc_.dbf' is missing
709        CRITICAL OPEN      08-OCT-14     System datafile 1: '/u01/app/oracle/oradata/PROD/datafile/o1_mf_system_b393xosc_.dbf' needs media recovery
469        CRITICAL OPEN      08-OCT-14     System datafile 1: '/u01/app/oracle/oradata/PROD/datafile/o1_mf_system_b2251bs1_.dbf' is missing
242        HIGH     OPEN      29-SEP-14     One or more non-system datafiles are missing
644        HIGH     OPEN      08-OCT-14     One or more non-system datafiles need media recovery
817        HIGH     OPEN      09-OCT-14     Name for datafile 7 is unknown in the control file
808        HIGH     OPEN      09-OCT-14     Name for datafile 6 is unknown in the control file

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If you have the correct version of the control file, then shutdown the database and replace the old control file
2. If file /u01/app/oracle/oradata/PROD/datafile/o1_mf_system_b393xosc_.dbf was unintentionally renamed or moved, restore it
3. If you restored the wrong version of data file /u01/app/oracle/oradata/PROD/datafile/o1_mf_system_b393xosc_.dbf, then replace it with the correct one
4. If file /u01/app/oracle/oradata/PROD/datafile/o1_mf_system_b2251bs1_.dbf was unintentionally renamed or moved, restore it
5. If file /u01/app/oracle/oradata/PROD/ind_tbs.dbs was unintentionally renamed or moved, restore it
6. If file /u01/app/oracle/oradata/PROD/datafile/o1_mf_sysaux_b2251bvo_.dbf was unintentionally renamed or moved, restore it
7. If file /u01/app/oracle/oradata/PROD/datafile/o1_mf_undotbs1_b2251bw5_.dbf was unintentionally renamed or moved, restore it
8. If file /u01/app/oracle/oradata/PROD/datafile/o1_mf_users_b2251byw_.dbf was unintentionally renamed or moved, restore it
9. If file /u01/app/oracle/oradata/PROD/datafile/o1_mf_example_b2257d0c_.dbf was unintentionally renamed or moved, restore it
10. If file /u01/app/oracle/oradata/PROD/datafile/tbs_move_01.dbf was unintentionally renamed or moved, restore it
11. If file /u01/app/oracle/oradata/PROD/datafile/o1_mf_sysaux_b393xovt_.dbf was unintentionally renamed or moved, restore it
12. If file /u01/app/oracle/oradata/PROD/datafile/o1_mf_undotbs1_b393xq2d_.dbf was unintentionally renamed or moved, restore it
13. If file /u01/app/oracle/oradata/PROD/datafile/o1_mf_users_b393xqpm_.dbf was unintentionally renamed or moved, restore it
14. If file /u01/app/oracle/oradata/PROD/datafile/o1_mf_example_b393xp04_.dbf was unintentionally renamed or moved, restore it
15. If file /u01/app/oracle/oradata/PROD/datafile/tbs_users1.dbf was unintentionally renamed or moved, restore it
16. If file /u01/app/oracle/oradata/PROD/datafile/tbs_users2.dbf was unintentionally renamed or moved, restore it
17. If file /u01/app/oracle/oradata/PROD/datafile/pitr.dbf was unintentionally renamed or moved, restore it
18. If file /u01/app/oracle/oradata/PROD/datafile/pitr_ind.dbf was unintentionally renamed or moved, restore it
19. If file /u01/app/oracle/oradata/PROD/arch_tbs.dbf was unintentionally renamed or moved, restore it
20. If you restored the wrong version of data file /u01/app/oracle/oradata/PROD/datafile/o1_mf_users_b393xqpm_.dbf, then replace it with the correct one
21. If you restored the wrong version of data file /u01/app/oracle/oradata/PROD/datafile/tbs_move_01.dbf, then replace it with the correct one
22. If you restored the wrong version of data file /u01/app/oracle/oradata/PROD/datafile/o1_mf_sysaux_b393xovt_.dbf, then replace it with the correct one
23. If you restored the wrong version of data file /u01/app/oracle/oradata/PROD/datafile/o1_mf_undotbs1_b393xq2d_.dbf, then replace it with the correct one
24. If you restored the wrong version of data file /u01/app/oracle/oradata/PROD/datafile/pitr_ind.dbf, then replace it with the correct one
25. If the file exists, rename data file 7 to the name of the real file using ALTER DATABASE RENAME FILE command.  If the file does not exist, create a new data file using ALTER DATABASE CREATE DATAFILE command.
26. If the file exists, rename data file 6 to the name of the real file using ALTER DATABASE RENAME FILE command.  If the file does not exist, create a new data file using ALTER DATABASE CREATE DATAFILE command.

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Restore and recover database 
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_2998735934.hm


RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_2998735934.hm

contents of repair script:
   # restore and recover database
   restore database;
   recover database;
   alter database open resetlogs;

Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script

Starting restore at 16-OCT-14
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/PROD/datafile/o1_mf_undotbs1_b393xq2d_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/PROD/datafile/o1_mf_users_b393xqpm_.dbf
channel ORA_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/PROD/arch_tbs.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/PROD/backupset/2014_10_16/o1_mf_nnndf_TAG20141016T105733_b3yf194z_.bkp
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00005 to /u01/app/oracle/oradata/PROD/datafile/o1_mf_example_b393xp04_.dbf
channel ORA_DISK_2: restoring datafile 00008 to /u01/app/oracle/oradata/PROD/datafile/pitr.dbf
channel ORA_DISK_2: restoring datafile 00009 to /u01/app/oracle/oradata/PROD/datafile/pitr_ind.dbf
channel ORA_DISK_2: reading from backup piece /u01/app/oracle/fast_recovery_area/PROD/backupset/2014_10_16/o1_mf_nnndf_TAG20141016T105733_b3yf12ln_.bkp
channel ORA_DISK_3: starting datafile backup set restore
channel ORA_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_DISK_3: restoring datafile 00002 to /u01/app/oracle/oradata/PROD/datafile/o1_mf_sysaux_b393xovt_.dbf
channel ORA_DISK_3: restoring datafile 00006 to /u01/app/oracle/oradata/PROD/datafile/tbs_users1.dbf
channel ORA_DISK_3: restoring datafile 00007 to /u01/app/oracle/oradata/PROD/datafile/tbs_users2.dbf
channel ORA_DISK_3: reading from backup piece /u01/app/oracle/fast_recovery_area/PROD/backupset/2014_10_16/o1_mf_nnndf_TAG20141016T105733_b3yf10lb_.bkp
channel ORA_DISK_4: starting datafile backup set restore
channel ORA_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_DISK_4: restoring datafile 00001 to /u01/app/oracle/oradata/PROD/datafile/o1_mf_system_b393xosc_.dbf
channel ORA_DISK_4: reading from backup piece /u01/app/oracle/fast_recovery_area/PROD/backupset/2014_10_16/o1_mf_nnndf_TAG20141016T105733_b3yf0zo6_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/PROD/backupset/2014_10_16/o1_mf_nnndf_TAG20141016T105733_b3yf194z_.bkp tag=TAG20141016T105733
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:04:30
channel ORA_DISK_2: piece handle=/u01/app/oracle/fast_recovery_area/PROD/backupset/2014_10_16/o1_mf_nnndf_TAG20141016T105733_b3yf12ln_.bkp tag=TAG20141016T105733
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:05:01
channel ORA_DISK_3: piece handle=/u01/app/oracle/fast_recovery_area/PROD/backupset/2014_10_16/o1_mf_nnndf_TAG20141016T105733_b3yf10lb_.bkp tag=TAG20141016T105733
channel ORA_DISK_3: restored backup piece 1
channel ORA_DISK_3: restore complete, elapsed time: 00:11:10
channel ORA_DISK_4: piece handle=/u01/app/oracle/fast_recovery_area/PROD/backupset/2014_10_16/o1_mf_nnndf_TAG20141016T105733_b3yf0zo6_.bkp tag=TAG20141016T105733
channel ORA_DISK_4: restored backup piece 1
channel ORA_DISK_4: restore complete, elapsed time: 00:16:35
Finished restore at 16-OCT-14

Starting recover at 16-OCT-14
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
datafile 6 not processed because file is read-only
datafile 7 not processed because file is read-only

starting media recovery

archived log for thread 1 with sequence 13 is already on disk as file /u01/app/oracle/fast_recovery_area/PROD/onlinelog/o1_mf_1_b395df1j_.log
archived log for thread 1 with sequence 14 is already on disk as file /u01/app/oracle/fast_recovery_area/PROD/onlinelog/o1_mf_2_b395dqql_.log
archived log file name=/u01/app/oracle/fast_recovery_area/PROD/onlinelog/o1_mf_1_b395df1j_.log thread=1 sequence=13
archived log file name=/u01/app/oracle/fast_recovery_area/PROD/onlinelog/o1_mf_2_b395dqql_.log thread=1 sequence=14
media recovery complete, elapsed time: 00:00:30
Finished recover at 16-OCT-14

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

RMAN>

5.查看恢复结果
1)登陆数据库,查看当前数据库状态,查看数据文件等重要文件的状态;

[oracle@jibo ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Oct 16 14:59:53 2014

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

SYS@PROD>select status from v$instance;

STATUS
------------
OPEN

  --数据库已经打开
或者:
SYS@PROD>select open_mode from v$database;

2)重新生成spfile;
数据库打开,查看spfile,没有spfile文件,生成spfile文件,再查看:

SYS@PROD>show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string

SYS@PROD>create spfile from memory;

File created.
 
SYS@PROD>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@PROD>startup;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2260088 bytes
Variable Size             285213576 bytes
Database Buffers          775946240 bytes
Redo Buffers                5517312 bytes
Database mounted.
Database opened.
SYS@PROD>show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0
                                                 /dbhome_1/dbs/spfilePROD.ora
SYS@PROD>


6.重新备份数据库

RMAN> backup database include current controlfile plus archivelog;

陳述
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn
什么是oracle asm什么是oracle asmApr 18, 2022 pm 04:16 PM

oracle asm指的是“自动存储管理”,是一种卷管理器,可自动管理磁盘组并提供有效的数据冗余功能;它是做为单独的Oracle实例实施和部署。asm的优势:1、配置简单、可最大化推动数据库合并的存储资源利用;2、支持BIGFILE文件等。

oracle怎么查询所有索引oracle怎么查询所有索引May 13, 2022 pm 05:23 PM

方法:1、利用“select*from user_indexes where table_name=表名”语句查询表中索引;2、利用“select*from all_indexes where table_name=表名”语句查询所有索引。

Oracle怎么查询端口号Oracle怎么查询端口号May 13, 2022 am 10:10 AM

在Oracle中,可利用lsnrctl命令查询端口号,该命令是Oracle的监听命令;在启动、关闭或重启oracle监听器之前可使用该命令检查oracle监听器的状态,语法为“lsnrctl status”,结果PORT后的内容就是端口号。

oracle全角怎么转半角oracle全角怎么转半角May 13, 2022 pm 03:21 PM

在oracle中,可以利用“TO_SINGLE_BYTE(String)”将全角转换为半角;“TO_SINGLE_BYTE”函数可以将参数中所有多字节字符都替换为等价的单字节字符,只有当数据库字符集同时包含多字节和单字节字符的时候有效。

oracle怎么删除sequenceoracle怎么删除sequenceMay 13, 2022 pm 03:35 PM

在oracle中,可以利用“drop sequence sequence名”来删除sequence;sequence是自动增加数字序列的意思,也就是序列号,序列号自动增加不能重置,因此需要利用drop sequence语句来删除序列。

oracle怎么查询数据类型oracle怎么查询数据类型May 13, 2022 pm 04:19 PM

在oracle中,可以利用“select ... From all_tab_columns where table_name=upper('表名') AND owner=upper('数据库登录用户名');”语句查询数据库表的数据类型。

oracle查询怎么不区分大小写oracle查询怎么不区分大小写May 10, 2022 pm 05:45 PM

方法:1、利用“LOWER(字段值)”将字段转为小写,或者利用“UPPER(字段值)”将字段转为大写;2、利用“REGEXP_LIKE(字符串,正则表达式,'i')”,当参数设置为“i”时,说明进行匹配不区分大小写。

Oracle怎么修改sessionOracle怎么修改sessionMay 13, 2022 pm 05:06 PM

方法:1、利用“alter system set sessions=修改后的数值 scope=spfile”语句修改session参数;2、修改参数之后利用“shutdown immediate – startup”语句重启服务器即可生效。

See all articles

熱AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智慧驅動的應用程序,用於創建逼真的裸體照片

AI Clothes Remover

AI Clothes Remover

用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool

Undress AI Tool

免費脫衣圖片

Clothoff.io

Clothoff.io

AI脫衣器

AI Hentai Generator

AI Hentai Generator

免費產生 AI 無盡。

熱門文章

R.E.P.O.能量晶體解釋及其做什麼(黃色晶體)
2 週前By尊渡假赌尊渡假赌尊渡假赌
倉庫:如何復興隊友
4 週前By尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island冒險:如何獲得巨型種子
4 週前By尊渡假赌尊渡假赌尊渡假赌

熱工具

DVWA

DVWA

Damn Vulnerable Web App (DVWA) 是一個PHP/MySQL的Web應用程序,非常容易受到攻擊。它的主要目標是成為安全專業人員在合法環境中測試自己的技能和工具的輔助工具,幫助Web開發人員更好地理解保護網路應用程式的過程,並幫助教師/學生在課堂環境中教授/學習Web應用程式安全性。 DVWA的目標是透過簡單直接的介面練習一些最常見的Web漏洞,難度各不相同。請注意,該軟體中

PhpStorm Mac 版本

PhpStorm Mac 版本

最新(2018.2.1 )專業的PHP整合開發工具

SublimeText3 Mac版

SublimeText3 Mac版

神級程式碼編輯軟體(SublimeText3)

MinGW - Minimalist GNU for Windows

MinGW - Minimalist GNU for Windows

這個專案正在遷移到osdn.net/projects/mingw的過程中,你可以繼續在那裡關注我們。 MinGW:GNU編譯器集合(GCC)的本機Windows移植版本,可自由分發的導入函式庫和用於建置本機Windows應用程式的頭檔;包括對MSVC執行時間的擴展,以支援C99功能。 MinGW的所有軟體都可以在64位元Windows平台上運作。

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

強大的PHP整合開發環境