1、空间管理(字典所在,定义信息,不放用户数据)
单个数据文件即可
设置自动扩展,设置bigfile
2、system如何备份
必须归档下才能在open下完成备份
a、热备 b、rman备
非归档下只能进行冷备
使用archive log list命令查看数据库是否为归档模式:
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
热备操作:
SQL> alter tablespace system begin backup;
Tablespace altered.
SQL> ho cp /u01/app/oracle/oradata/orcl/system01.dbf /u01/app/oracle/bak/system01.dbf
SQL> alter tablespace system end backup;
Tablespace altered.
RMAN备操作:
[oracle@oracle11g bak]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Mon Apr 24 11:48:48 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1445346880)
RMAN> backup tablespace system;
Starting backup at 24-APR-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: starting piece 1 at 24-APR-17
channel ORA_DISK_1: finished piece 1 at 24-APR-17
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2017_04_24/o1_mf_nnndf_TAG20170424T114901_dhtxfg5p_.bkp tag=TAG20170424T114901 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 24-APR-17
channel ORA_DISK_1: finished piece 1 at 24-APR-17
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2017_04_24/o1_mf_ncsnf_TAG20170424T114901_dhtxh8g7_.bkp tag=TAG20170424T114901 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-APR-17
RMAN>
3、system表出现问题,如何恢复
a、归档 有备份 日志完整 可以恢复
b、归档 有备份 日志不完整 可以恢复,不能保证数据不丢失
热备恢复:
SQL> startup
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2217952 bytes
Variable Size 624953376 bytes
Database Buffers 205520896 bytes
Redo Buffers 2412544 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'
SQL> ho cp /u01/app/oracle/oracle/bak/system01.dbf /u01/app/oracle/oradata/orcl/system01.dbf
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
RMAN备恢复:
SQL> startup;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2217952 bytes
Variable Size 624953376 bytes
Database Buffers 205520896 bytes
Redo Buffers 2412544 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracle11g ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Mon Apr 24 12:48:15 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1445346880, not open)
RMAN> list backup;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 588.09M DISK 00:00:36 19-APR-17
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20170419T115143
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL/backupset/2017_04_19/o1_mf_nnndf_TAG20170419T115143_dhfqphqg_.bkp
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1044250 19-APR-17 /u01/app/oracle/oradata/orcl/system01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 9.36M DISK 00:00:04 19-APR-17
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20170419T115143
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL/backupset/2017_04_19/o1_mf_ncsnf_TAG20170419T115143_dhfqr17l_.bkp
SPFILE Included: Modification time: 19-APR-17
SPFILE db_unique_name: ORCL
Control File Included: Ckp SCN: 1044266 Ckp time: 19-APR-17
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 589.84M DISK 00:00:41 20-APR-17
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20170420T142552
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL/backupset/2017_04_20/o1_mf_nnndf_TAG20170420T142552_dhjo3jv0_.bkp
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1089734 20-APR-17 /u01/app/oracle/oradata/orcl/system01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4 Full 9.36M DISK 00:00:01 20-APR-17
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20170420T142552
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL/backupset/2017_04_20/o1_mf_ncsnf_TAG20170420T142552_dhjo4z8l_.bkp
SPFILE Included: Modification time: 20-APR-17
SPFILE db_unique_name: ORCL
Control File Included: Ckp SCN: 1093814 Ckp time: 20-APR-17
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5 Full 593.09M DISK 00:00:49 24-APR-17
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20170424T114901
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL/backupset/2017_04_24/o1_mf_nnndf_TAG20170424T114901_dhtxfg5p_.bkp
List of Datafiles in backup set 5
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1268534 24-APR-17 /u01/app/oracle/oradata/orcl/system01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6 Full 9.36M DISK 00:00:03 24-APR-17
BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20170424T114901
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL/backupset/2017_04_24/o1_mf_ncsnf_TAG20170424T114901_dhtxh8g7_.bkp
SPFILE Included: Modification time: 24-APR-17
SPFILE db_unique_name: ORCL
Control File Included: Ckp SCN: 1268564 Ckp time: 24-APR-17
RMAN> restore tablespace system;
Starting restore at 24-APR-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
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 00001 to /u01/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2017_04_24/o1_mf_nnndf_TAG20170424T114901_dhtxfg5p_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2017_04_24/o1_mf_nnndf_TAG20170424T114901_dhtxfg5p_.bkp tag=TAG20170424T114901
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 24-APR-17
RMAN> recover tablespace system;
Starting recover at 24-APR-17
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 24-APR-17
RMAN> sql 'alter database open';
sql statement: alter database open
4、system表空间不可只读、不可脱机
SQL> alter tablespace system read only
2 ;
alter tablespace system read only
*
ERROR at line 1:
ORA-01643: system tablespace can not be made read-only
SQL> alter tablespace system offline;
alter tablespace system offline
*
ERROR at line 1:
ORA-01541: system tablespace cannot be brought offline; shut down if necessary
SQL>