43.如何解决企业中数据的备份与恢复
1.用户进程错误网络故障解决方案
[oracle@yuanzj.com:/home/oracle]$ ps -ef|grep ora_|grep orcl10
oracle 4527 1 0 09:39 ? 00:00:00 ora_pmon_orcl10g
oracle 4530 1 0 09:39 ? 00:00:00 ora_psp0_orcl10g
oracle 4533 1 0 09:39 ? 00:00:00 ora_mman_orcl10g
oracle 4537 1 0 09:39 ? 00:00:00 ora_dbw0_orcl10g
oracle 4540 1 0 09:39 ? 00:00:00 ora_lgwr_orcl10g
oracle 4543 1 0 09:39 ? 00:00:01 ora_ckpt_orcl10g
oracle 4545 1 0 09:39 ? 00:00:00 ora_smon_orcl10g
oracle 4549 1 0 09:39 ? 00:00:00 ora_reco_orcl10g
oracle 4551 1 0 09:39 ? 00:00:00 ora_cjq0_orcl10g
oracle 4553 1 0 09:39 ? 00:00:00 ora_mmon_orcl10g
oracle 4555 1 0 09:39 ? 00:00:00 ora_mmnl_orcl10g
oracle 4557 1 0 09:39 ? 00:00:00 ora_d000_orcl10g
oracle 4559 1 0 09:39 ? 00:00:00 ora_s000_orcl10g
oracle 4570 1 0 09:39 ? 00:00:00 ora_qmnc_orcl10g
oracle 4701 1 0 09:40 ? 00:00:00 ora_q000_orcl10g
oracle 4703 1 0 09:40 ? 00:00:00 ora_q001_orcl10g
oracle 18924 1 0 11:10 ? 00:00:00 ora_j000_orcl10g
--这六个进程不能kill，否则数据库发生宕机
smon pmon ckpt lgwr dbw[0-9][a-z] reco
--配置监听的故障转移
[oracle@yuanzj.com:/u01/app/ora10g/product/10.2.0/db_1/network/admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/ora10g/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/ora10g/product/10.2.0/db_1)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = yuanzj.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
SID_LIST_LISTENER1 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl10g)
(SID_NAME = orcl10g)
(ORACLE_HOME = /u01/app/ora10g/product/10.2.0/db_1)
)
)
LISTENER1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = yuanzj.com)(PORT = 1525))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
)
[oracle@yuanzj.com:/u01/app/ora10g/product/10.2.0/db_1/network/admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/ora10g/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL10G =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = yuanzj.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = yuanzj.com)(PORT = 1525))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl10g)
)
(failover = on)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)2.用户错误和实例恢复
scott@ORCL10G 2023-03-05 11:58:05> drop table emp;
Table dropped.
Elapsed: 00:00:00.13
scott@ORCL10G 2023-03-05 11:58:31> show recyclebin
ORIGNAME_PLUS_SHOW_RECYC OBJECTNAME_PLUS_SHOW_RECYC OBJTYPE_PLUS_SHOW_RECYC DROPTIME_PLUS_SHOW_
-------------------------------- ------------------------------ ------------------------- -------------------
EMP BIN$9iAxDz3yR/XgUKjAKAJmMA==$0 TABLE 2023-03-05:11:58:31
scott@ORCL10G 2023-03-05 11:59:09> flashback table emp to before drop;
Flashback complete.
Elapsed: 00:00:00.00
scott@ORCL10G 2023-03-05 11:59:12> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
BONUS TABLE
SALGRADE TABLE
E SYNONYM
EMP TABLE
Elapsed: 00:00:00.02
scott@ORCL10G 2023-03-05 11:59:23> update emp set sal=sal+1;
14 rows updated.
Elapsed: 00:00:00.01
scott@ORCL10G 2023-03-05 12:00:46> commit;
Commit complete.
Elapsed: 00:00:00.00
scott@ORCL10G 2023-03-05 12:01:48> select sal,versions_starttime from emp versions between scn minvalue and maxvalue where empno = 7566;
SAL VERSIONS_STARTTIME
---------- ---------------------------------------------------------------------------
2976 05-MAR-23 12.00.49 PM
2975
Elapsed: 00:00:00.00
scott@ORCL10G 2023-03-05 12:02:11> alter table emp enable row movement;
Table altered.
Elapsed: 00:00:00.00
scott@ORCL10G 2023-03-05 12:02:37> flashback table emp to timestamp to_timestamp('05-MAR-23 12.00.49','dd-mon-yy hh24:mi:ss');
Flashback complete.
Elapsed: 00:00:00.05
scott@ORCL10G 2023-03-05 12:04:01> alter table emp disable row movement;
Table altered.
Elapsed: 00:00:00.00
scott@ORCL10G 2023-03-05 12:04:19> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
14 rows selected.
Elapsed: 00:00:00.00
前滚和回退
介质故障恢复
[oracle@yuanzj.com:/u01/app/ora10g/product/10.2.0/db_1/network/admin]$ cd /u01/app/ora10g/
admin/ flash_recovery_area/ oraInventory/ product/
[oracle@yuanzj.com:/u01/app/ora10g/product/10.2.0/db_1/network/admin]$ mkdir /u01/app/ora10g/flash_recovery_area/orcl10g
[oracle@yuanzj.com:/u01/app/ora10g/product/10.2.0/db_1/network/admin]$ sas
SQL*Plus: Release 10.2.0.5.0 - Production on Sun Mar 5 12:30:34 2023
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
sys@ORCL10G 2023-03-05 12:31:12> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/oradata/orcl10g/control01.ctl
/oradata/orcl10g/control02.ctl
/oradata/orcl10g/control03.ctl
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-05 12:31:17> show parameter control;
NAME_COL_PLUS_SHOW_PARAM TYPE VALUE_COL_PLUS_SHOW_PARAM
------------------------------ ----------- ---------------------------------------------
control_file_record_keep_time integer 7
control_files string /oradata/orcl10g/control01.ctl, /oradata/orcl
10g/control02.ctl, /oradata/orcl10g/control03
.ctl
sys@ORCL10G 2023-03-05 12:31:33> alter system set control_files='/oradata/orcl10g/control01.ctl','/u01/app/ora10g/flash_recovery_area/orcl10g/control02.ctl' scope=spfile
2 ;
System altered.
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-05 12:32:47> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ORCL10G 2023-03-05 12:32:57> ho cp /oradata/orcl10g/control02.ctl /u01/app/ora10g/flash_recovery_area/orcl10g/control02.ctl
sys@ORCL10G 2023-03-05 12:33:26>
sys@ORCL10G 2023-03-05 12:33:27>
sys@ORCL10G 2023-03-05 12:33:27> startup
ORACLE instance started.
Total System Global Area 1174405120 bytes
Fixed Size 2095800 bytes
Variable Size 301991240 bytes
Database Buffers 855638016 bytes
Redo Buffers 14680064 bytes
Database mounted.
Database opened.
sys@ORCL10G 2023-03-05 12:33:34> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/oradata/orcl10g/control01.ctl
/u01/app/ora10g/flash_recovery_area/orcl10g/control02.ctl
Elapsed: 00:00:00.013.备份可恢复性和归档模式
--redo多复用
sys@ORCL10G 2023-03-05 12:53:34> select member from v$logfile;
MEMBER
-------------------------------------------
/oradata/orcl10g/redo03.log
/oradata/orcl10g/redo02.log
/oradata/orcl10g/redo01.log
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-05 12:53:50> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 25 52428800 1 NO INACTIVE 873721 2023-03-05 09:38:13
2 1 26 52428800 1 NO INACTIVE 894184 2023-03-05 09:39:10
3 1 27 52428800 1 NO CURRENT 915257 2023-03-05 09:39:53
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-05 12:54:01> alter database add logfile member '/u01/app/ora10g/flash_recovery_area/orcl10g/redo01.log' to group 1;
Database altered.
Elapsed: 00:00:00.23
sys@ORCL10G 2023-03-05 12:55:09> alter database add logfile member '/u01/app/ora10g/flash_recovery_area/orcl10g/redo02.log' to group 2;
Database altered.
Elapsed: 00:00:00.23
sys@ORCL10G 2023-03-05 12:55:15> alter database add logfile member '/u01/app/ora10g/flash_recovery_area/orcl10g/redo03.log' to group 3;
Database altered.
Elapsed: 00:00:00.26
sys@ORCL10G 2023-03-05 12:55:23> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 25 52428800 2 NO INACTIVE 873721 2023-03-05 09:38:13
2 1 26 52428800 2 NO INACTIVE 894184 2023-03-05 09:39:10
3 1 27 52428800 2 NO CURRENT 915257 2023-03-05 09:39:53
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-05 12:55:28> select member from v$logfile;
MEMBER
-----------------------------------------------------------------
/oradata/orcl10g/redo03.log
/oradata/orcl10g/redo02.log
/oradata/orcl10g/redo01.log
/u01/app/ora10g/flash_recovery_area/orcl10g/redo01.log
/u01/app/ora10g/flash_recovery_area/orcl10g/redo02.log
/u01/app/ora10g/flash_recovery_area/orcl10g/redo03.log
6 rows selected.
Elapsed: 00:00:00.00
--如果redo日志是INACTIVE
sys@ORCL10G 2023-03-05 13:00:43> select l.group#,lf.member,l.status from v$log l,v$logfile lf where l.group#=lf.group# order by group#,member;
GROUP# MEMBER STATUS
---------- ------------------------------------------------------- ----------------
1 /oradata/orcl10g/redo01.log INACTIVE
1 /u01/app/ora10g/flash_recovery_area/orcl10g/redo01.log INACTIVE
2 /oradata/orcl10g/redo02.log INACTIVE
2 /u01/app/ora10g/flash_recovery_area/orcl10g/redo02.log INACTIVE
3 /oradata/orcl10g/redo03.log CURRENT
3 /u01/app/ora10g/flash_recovery_area/orcl10g/redo03.log CURRENT
6 rows selected.
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-05 13:00:56> ho rm /oradata/orcl10g/redo01.log
sys@ORCL10G 2023-03-05 13:01:32>
sys@ORCL10G 2023-03-05 13:01:32> startup force
ORACLE instance started.
Total System Global Area 1174405120 bytes
Fixed Size 2095800 bytes
Variable Size 301991240 bytes
Database Buffers 855638016 bytes
Redo Buffers 14680064 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/oradata/orcl10g/redo01.log'
ORA-00312: online log 1 thread 1: '/u01/app/ora10g/flash_recovery_area/orcl10g/redo01.log'
sys@ORCL10G 2023-03-05 13:02:23> alter database clear logfile group 1;
Database altered.
Elapsed: 00:00:00.39
sys@ORCL10G 2023-03-05 13:02:29> select status from v$instance;
STATUS
------------
MOUNTED
Elapsed: 00:00:00.01
sys@ORCL10G 2023-03-05 13:02:42> alter database open;
Database altered.
Elapsed: 00:00:00.38
sys@ORCL10G 2023-03-05 13:02:48> select status from v$instance;
STATUS
------------
OPEN
Elapsed: 00:00:00.00
--如果是current，且数据库redo没有数据
recover database until cacel；
alter database open resetlogs