CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/BACKUP/rman_con/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 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 CHANNEL DEVICE TYPE DISK MAXPIECESIZE 100 M FORMAT '/BACKUP/rman/%d_%s_%p';
CONFIGURE MAXSETSIZE TO 2 G;
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/db_1/dbs/snapcf_orcl.f'; # default
RMAN> backup tablespace users;
Starting backup at 07-OCT-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=39 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=41 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
Finished backup at 07-OCT-15
Starting Control File and SPFILE Autobackup at 07-OCT-15
piece handle=/BACKUP/rman_con/c-1418332348-20151007-00 comment=NONE
Finished Control File and SPFILE Autobackup at 07-OCT-15
RMAN> list backup of tablespace "USERS";
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
464 Full 1.04G DISK 00:03:40 02-OCT-15
List of Datafiles in backup set 464
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
4 Full 3248206 02-OCT-15 /u01/app/oracle/oradata/orcl/users01.dbf
~중략
---- -- ---- ---------- --------- ----
5 Full 3500745 07-OCT-15 /u01/app/oracle/oradata/orcl/example01.dbf
RMAN> delete obsolete;
RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 7 days
using channel ORA_DISK_1
using channel ORA_DISK_2
no obsolete backups found
incremental할 경우 LV에 1이 찍힘
SYS@orcl>conn hr/hr
Connected.
HR@orcl>create table t1 (no) as
2 select level from dual connect by level <= 100000;
Table created.
HR@orcl>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lnx04 ~]$ rtc
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Oct 7 10:26:28 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1418332348)
connected to recovery catalog database
RMAN> backup incremental level 1 database;
Starting backup at 07-OCT-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=38 device type=DISK
no parent backup or copy of datafile 4 found
no parent backup or copy of datafile 2 found
no parent backup or copy of datafile 3 found
no parent backup or copy of datafile 1 found
no parent backup or copy of datafile 5 found
channel ORA_DISK_1: starting incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 07-OCT-15
channel ORA_DISK_2: starting incremental level 0 datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
Starting Control File and SPFILE Autobackup at 07-OCT-15
piece handle=/BACKUP/rman_con/c-1418332348-20151007-02 comment=NONE
Finished Control File and SPFILE Autobackup at 07-OCT-15
HR@orcl>update t1 set no = no+100 where no <= 5;
5 rows updated.
HR@orcl>commit;
Commit complete.
HR@orcl>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lnx04 ~]$ rtc
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Oct 7 10:30:31 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1418332348)
connected to recovery catalog database
RMAN> backup incremental level 1 database;
Finished backup at 07-OCT-15
Starting Control File and SPFILE Autobackup at 07-OCT-15
piece handle=/BACKUP/rman_con/c-1418332348-20151007-03 comment=NONE
Finished Control File and SPFILE Autobackup at 07-OCT-15
-rw-r----- 1 oracle oinstall 104857600 Oct 7 10:26 ORCL_32_1
-rw-r----- 1 oracle oinstall 104857600 Oct 7 10:28 ORCL_32_10
-rw-r----- 1 oracle oinstall 83853312 Oct 7 10:28 ORCL_32_11
-rw-r----- 1 oracle oinstall 104857600 Oct 7 10:27 ORCL_32_2
-rw-r----- 1 oracle oinstall 104857600 Oct 7 10:27 ORCL_32_3
-rw-r----- 1 oracle oinstall 104857600 Oct 7 10:27 ORCL_32_4
-rw-r----- 1 oracle oinstall 104857600 Oct 7 10:27 ORCL_32_5
-rw-r----- 1 oracle oinstall 104857600 Oct 7 10:28 ORCL_32_6
-rw-r----- 1 oracle oinstall 104857600 Oct 7 10:28 ORCL_32_7
-rw-r----- 1 oracle oinstall 104857600 Oct 7 10:28 ORCL_32_8
-rw-r----- 1 oracle oinstall 104857600 Oct 7 10:28 ORCL_32_9
-rw-r----- 1 oracle oinstall 104857600 Oct 7 10:26 ORCL_33_1
-rw-r----- 1 oracle oinstall 104857600 Oct 7 10:27 ORCL_33_2
-rw-r----- 1 oracle oinstall 104857600 Oct 7 10:27 ORCL_33_3
-rw-r----- 1 oracle oinstall 104857600 Oct 7 10:27 ORCL_33_4
-rw-r----- 1 oracle oinstall 104857600 Oct 7 10:27 ORCL_33_5
-rw-r----- 1 oracle oinstall 104857600 Oct 7 10:27 ORCL_33_6
-rw-r----- 1 oracle oinstall 104857600 Oct 7 10:27 ORCL_33_7
-rw-r----- 1 oracle oinstall 23969792 Oct 7 10:27 ORCL_33_8
-rw-r----- 1 oracle oinstall 6086656 Oct 7 10:28 ORCL_34_1
1차 증분 백업
-rw-r----- 1 oracle oinstall 761856 Oct 7 10:31 ORCL_36_1
-rw-r----- 1 oracle oinstall 622592 Oct 7 10:31 ORCL_37_1
2차 증분 백업
Fast incremental backup
HR@orcl>create table emp_16 as select * from employees;
Table created.
HR@orcl>insert into emp_16 select * from emp_16;
107 rows created.
HR@orcl>/
214 rows created.
HR@orcl>/
428 rows created.
HR@orcl>/
856 rows created.
HR@orcl>commit;
Commit complete.
HR@orcl>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lnx04 ~]$ rman target system/oracle@orcl catalog rman_admin/oracle@rcat
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Oct 7 10:35:08 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1418332348)
connected to recovery catalog database
RMAN> backup incremental level 1 tablespace users;
Finished backup at 07-OCT-15
Starting Control File and SPFILE Autobackup at 07-OCT-15
piece handle=/BACKUP/rman_con/c-1418332348-20151007-04 comment=NONE
Finished Control File and SPFILE Autobackup at 07-OCT-15
starting full resync of recovery catalog
full resync complete
SYS@orcl>select file#, incremental_level, completion_time, blocks, datafile_blocks
2 from v$backup_datafile
3 where incremental_level > 0;
FILE# INCREMENTAL_LEVEL COMPLETIO BLOCKS DATAFILE_BLOCKS
---------- ----------------- --------- ---------- ---------------
12 1 07-OCT-15 739 12800
1 1 07-OCT-15 6 170240
5 1 07-OCT-15 1 12800
12 1 07-OCT-15 65 12800
4 1 07-OCT-15 2 84480
2 1 07-OCT-15 44 74240
3 1 07-OCT-15 43 69760
4 1 07-OCT-15 27 84480
8 rows selected.
1 select file#,avg(datafile_blocks),avg(blocks_read),avg(blocks_read/datafile_blocks) * 100 "% read for backup"
2 from v$backup_datafile
3 where incremental_level > 0
4 and used_change_tracking = 'YES'
5 group by file#
6* order by file#
SYS@orcl>/
no rows selected
SYS@orcl>save inc_backup_%.sql
Created file inc_backup_%.sql
*****************************************************************************************
★ Block change tracking 설정!!!
Created file inc_backup_%.sql
SYS@orcl>alter database enable block change tracking
2 using file '/BACKUP/orcl_ctwr.f'
3 reuse;
Database altered.
alert log에
alter database enable block change tracking
using file '/BACKUP/orcl_ctwr.f'
reuse
Block change tracking file is current.
Starting background process CTWR
Wed Oct 07 10:41:20 2015
CTWR started with pid=32, OS id=366
Block change tracking service is active.
Completed: alter database enable block change tracking
using file '/BACKUP/orcl_ctwr.f'
reuse
HR@orcl>insert into emp_16 select * from emp_16;
1712 rows created.
HR@orcl>/
3424 rows created.
HR@orcl>/
6848 rows created.
HR@orcl>/
13696 rows created.
HR@orcl>commit;
Commit complete.
HR@orcl>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lnx04 ~]$ rtc
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Oct 7 10:42:19 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1418332348)
connected to recovery catalog database
RMAN> backup incremental level 1 tablespace users;
Starting backup at 07-OCT-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=49 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=48 device type=DISK
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 07-OCT-15
channel ORA_DISK_1: finished piece 1 at 07-OCT-15
piece handle=/BACKUP/rman/ORCL_41_1 tag=TAG20151007T104228 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-OCT-15
Starting Control File and SPFILE Autobackup at 07-OCT-15
piece handle=/BACKUP/rman_con/c-1418332348-20151007-05 comment=NONE
Finished Control File and SPFILE Autobackup at 07-OCT-15
[oracle@lnx04 ~]$ ss
SQL*Plus: Release 11.2.0.1.0 Production on Wed Oct 7 10:42:53 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@orcl>@inc_backup_1.sql
FILE# INCREMENTAL_LEVEL COMPLETIO BLOCKS DATAFILE_BLOCKS
---------- ----------------- --------- ---------- ---------------
12 1 07-OCT-15 739 12800
1 1 07-OCT-15 6 170240
5 1 07-OCT-15 1 12800
12 1 07-OCT-15 65 12800
4 1 07-OCT-15 2 84480
2 1 07-OCT-15 44 74240
3 1 07-OCT-15 43 69760
4 1 07-OCT-15 27 84480
4 1 07-OCT-15 320 84800
9 rows selected.
SYS@orcl>@inc_backup_%.sql
no rows selected
RMAN> backup incremental level 1 tablespace users;
Starting backup at 07-OCT-15
starting full resync of recovery catalog
full resync complete
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=39 device type=DISK
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 07-OCT-15
channel ORA_DISK_1: finished piece 1 at 07-OCT-15
piece handle=/BACKUP/rman/ORCL_43_1 tag=TAG20151007T104821 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-OCT-15
Starting Control File and SPFILE Autobackup at 07-OCT-15
piece handle=/BACKUP/rman_con/c-1418332348-20151007-06 comment=NONE
Finished Control File and SPFILE Autobackup at 07-OCT-15
SYS@orcl>@inc_backup_%.sql
FILE# AVG(DATAFILE_BLOCKS) AVG(BLOCKS_READ) % read for backup
---------- -------------------- ---------------- -----------------
4 85280 3 .003517824
RMAN> report schema;
Report of database schema for database with db_unique_name ORCL
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 1330 SYSTEM YES /u01/app/oracle/oradata/orcl/system01.dbf
2 580 SYSAUX NO /u01/app/oracle/oradata/orcl/sysaux01.dbf
3 545 UNDOTBS1 YES /u01/app/oracle/oradata/orcl/undotbs01.dbf
4 666 USERS NO /u01/app/oracle/oradata/orcl/users01.dbf
5 100 EXAMPLE NO /u01/app/oracle/oradata/orcl/example01.dbf
12 100 RMAN_TBS NO /u01/app/oracle/oradata/orcl/rman_tbs01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 38 TEMP 32767 /u01/app/oracle/oradata/orcl/temp01.dbf
RMAN> backup tag 'week_full_backup' datafile 4,5,12;
run {
allocate channel t1 type DISK;
allocate channel t2 type DISK;
allocate channel t3 type DISK;
allocate channel t4 type DISK;
allocate channel t5 type DISK;
backup as compressed backupset
section size 100m
format '/BACKUP/rman/%U_%T'
datafile 4;
release channel t1;
release channel t2;
release channel t3;
release channel t4;
release channel t5;
}
RMAN> configure channel device type disk clear;
old RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 300 M FORMAT '/BACKUP/rman/%d_%s_%p';
old RMAN configuration parameters are successfully deleted
starting full resync of recovery catalog
full resync complete
RMAN> show channel;
RMAN configuration parameters for database with db_unique_name ORCL are:
RMAN configuration has no stored or default parameters
RMAN> exit
Recovery Manager complete.
r[oracle@lnx04 ~]$ rtc
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Oct 7 11:38:10 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1418332348)
connected to recovery catalog database
run {
allocate channel t1 type DISK;
allocate channel t2 type DISK;
allocate channel t3 type DISK;
allocate channel t4 type DISK;
allocate channel t5 type DISK;
backup as compressed backupset
section size 100m
format '/BACKUP/rman/%U_%T'
datafile 3;e 3;
release channel t1;
release channel t2;
release channel t3;
release channel t4;
release channel t5;
}
allocated channel: t1
channel t1: SID=29 device type=DISK
allocated channel: t2
channel t2: SID=48 device type=DISK
allocated channel: t3
channel t3: SID=41 device type=DISK
allocated channel: t4
channel t4: SID=38 device type=DISK
allocated channel: t5
channel t5: SID=39 device type=DISK
Starting backup at 07-OCT-15
~중략
Starting Control File and SPFILE Autobackup at 07-OCT-15
piece handle=/BACKUP/rman_con/c-1418332348-20151007-08 comment=NONE
Finished Control File and SPFILE Autobackup at 07-OCT-15
released channel: t1
released channel: t2
released channel: t3
released channel: t4
released channel: t5
RMAN> alter database open;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 10/07/2015 12:12:16
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl/users01.dbf'
SYS@orcl>recover database;
Media recovery complete.
SYS@orcl>alter database open;
Database altered.
RMAN> crosscheck backupset;
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=41 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=42 device type=DISK
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/BACKUP/rman_con/c-1418332348-20151002-00 RECID=83 STAMP=892036563
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/BACKUP/rman_con/c-1418332348-20151002-01 RECID=105 STAMP=892045263
Crosschecked 95 objects
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/BACKUP/rman/ORCL_2_1 RECID=1 STAMP=892035686
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/BACKUP/rman/ORCL_2_2 RECID=2 STAMP=892035711
crosschecked backup piece: found to be 'EXPIRED'
Crosschecked 123 objects
SYS@orcl>select * from v$database_block_corruption;
no rows selected
RMAN> list copy;
specification does not match any datafile copy in the repository
specification does not match any control file copy in the repository
List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================
Name: /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2015_10_07/o1_mf_1_11_c18q97j6_.arc
begin backup 시 catalog datafilecopy로 등록 시
list copy로 볼 수 있다.
RMAN> run {
2> sql 'alter tablespace users begin backup';
3> host 'cp /u01/app/oracle/oradata/orcl/users01.dbf /BACKUP/user01.bak';
4> sql 'alter tablespace users end backup';
5> }
sql statement: alter tablespace users begin backup
host command complete
sql statement: alter tablespace users end backup
RMAN> catalog datafilecopy '/BACKUP/user01.bak';
cataloged datafile copy
datafile copy file name=/BACKUP/user01.bak RECID=2 STAMP=892471636
RMAN> list copy;
specification does not match any control file copy in the repository
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
1779 4 A 07-OCT-15 3511710 07-OCT-15
Name: /BACKUP/user01.bak
List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================
RMAN> change datafilecopy '/BACKUP/user01.bak' uncatalog;
uncataloged datafile copy
datafile copy file name=/BACKUP/user01.bak RECID=2 STAMP=892471636
Uncataloged 1 objects
RMAN> list copy;
specification does not match any datafile copy in the repository
specification does not match any control file copy in the repository
List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================
SYS@orcl>select * from v$tempseg_usage;
no rows selected
add temp drop temp
alter database default temporary tablespace
SYS@orcl>@hidden.sql
Enter value for param: _smm_max
old 4: and lower(ksppinm) like '%¶m%'
new 4: and lower(ksppinm) like '%_smm_max%'
KSPPINM KSPPSTVL
------------------------------ ------------------------------
_smm_max_size 102400
SYS@orcl>select 102400/1024 from dual;
102400/1024
-----------
100
SYS@orcl>show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 608M
SYS@orcl>alter system set pga_aggregate_target = 500m;
System altered.
SYS@orcl>show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 500M
SYS@orcl>@hidden.sql
Enter value for param: _smm_max
old 4: and lower(ksppinm) like '%¶m%'
new 4: and lower(ksppinm) like '%_smm_max%'
KSPPINM KSPPSTVL
------------------------------ ------------------------------
_smm_max_size 102400
SYS@orcl>@hidden.sql
Enter value for param: _smm_max
old 4: and lower(ksppinm) like '%¶m%'
new 4: and lower(ksppinm) like '%_smm_max%'
KSPPINM KSPPSTVL
------------------------------ ------------------------------
_smm_max_size 102400
SYS@orcl>alter user sh identified by sh account unlock;
User altered.
SYS@orcl>select name from v$tempfile union select name from v$tablespace;
NAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/temp01.dbf
EXAMPLE
RMAN_TBS
SYSAUX
SYSTEM
TEMP
UNDOTBS1
USERS
8 rows selected.
SYS@orcl>select * from v$tempseg_usage;
no rows selected
SYS@orcl>alter system flush shared_pool;
System altered.
SYS@orcl>alter system flush buffer_cache;
System altered.
SYS@orcl>alter system set pga_aggregate_target = 200m;
System altered.
SYS@orcl>select * from sh.sales order by 1,2,3,4,5,6,7 desc;
select * from sh.sales order by 1,2,3,4,5,6,7 desc
*
ERROR at line 1:
ORA-01116: error in opening database file 201
ORA-01110: data file 201: '/u01/app/oracle/oradata/orcl/temp01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SYS@orcl>startup force
ORACLE instance started.
Total System Global Area 1573527552 bytes
Fixed Size 1336764 bytes
Variable Size 872417860 bytes
Database Buffers 687865856 bytes
Redo Buffers 11907072 bytes
Database mounted.
Database opened.
Re-creating tempfile /u01/app/oracle/oradata/orcl/temp01.dbf
[oracle@lnx04 ~]$ cd /u01/app/oracle/oradata/orcl/
[oracle@lnx04 orcl]$ ls
control01.ctl example01.dbf sysaux01.dbf users01.dbf
control02.ctl redo03_b.log system01.dbf
control03.ctl redo03.log temp01.dbf
control04.ctl rman_tbs01.dbf undotbs01.dbf
[oracle@lnx04 orcl]$ rm -rf temp01.dbf
SYS@orcl>drop tablespace temp;
Tablespace dropped.
SYS@orcl>drop tablespace temp;
Tablespace dropped.
SYS@orcl>create temporary tablespace temp
2 tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' size 50m
3 autoextend on next 10m maxsize 1000m;
Tablespace created.
SYS@orcl>alter database default temporary tablespace temp;
Database altered.
SYS@orcl>drop tablespace temp2 including contents and datafiles;
Tablespace dropped.
SYS@orcl>alter system set memory_target=1504m;
System altered.
SYS@orcl>show parameter memory_t
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_target big integer 1504M
[oracle@lnx04 orcl]$ cd /u01/app/oracle/product/11.2.0/db_1/rdbms/lib/
[oracle@lnx04 lib]$ cat config.c
/* SS_DBA_GRP defines the UNIX group ID for sqldba adminstrative access. */
/* Refer to the Installation and User's Guide for further information. */
/* IMPORTANT: this file needs to be in sync with
rdbms/src/server/osds/config.c, specifically regarding the
number of elements in the ss_dba_grp array.
*/
#define SS_DBA_GRP "dba"
#define SS_OPER_GRP "oper"
#define SS_ASM_GRP ""
char *ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP, SS_ASM_GRP};
SYS@orcl>select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE
SYSTEM TRUE FALSE FALSE
[oracle@lnx04 dbs]$ rm -rf orapworcl
[oracle@lnx04 dbs]$ sqlplus sys/oracle@orcl as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Oct 7 16:39:51 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
ERROR:
ORA-01031: insufficient privileges
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
[oracle@lnx04 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Oct 7 16:39:58 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@orcl>select * from v$pwfile_users;
no rows selected
[oracle@lnx04 dbs]$ orapwd file=orapworcl password=oracle entries=5
[oracle@lnx04 dbs]$ ls
crere.sql hc_prodo.dat lkORCL orapwprodo snapcf_orcl.f
hc_DBUA0.dat init.ora lkPROD peshm_DBUA0_0 snapcf_prodo.f
hc_orcl.dat initorcl.ora lkPRODO peshm_orcl_0 spfileorcl.bak
hc_prod.dat initprod.ora orapworcl peshm_prod_0 spfileorcl.ora
hc_PROD.dat initPROD.ora orapwprod peshm_prodo_0 spfileprodo.ora
[oracle@lnx04 dbs]$ sqlplus sys/oracle@orcl as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Oct 7 16:42:18 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@orcl>select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE
SYS@orcl>grant sysdba to system;
Grant succeeded.
SYS@orcl>select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE
SYSTEM TRUE FALSE FALSE
시나리오 3번
server 1
- 수동 DB (ORCL)
- tablespace (3)
- impdp
- target DB
- CTWR enable
- configure
- backup
========================== network
server 2
- dbca db (PROD)
- catalog DB
========================== network
window에서 관리하도록...
저번 시험 impdp 해답 xxxx가 들어간 내용은 경로나 이름 변경될 수 있음
1. datapump를 위한 directory 생성하고 system 유저에게 read, write 권한 부여
예) SQL> create directory xxxxx as '/xxx/xxxxx/xxxx';
SQL> grant read, write on directory xxxxx to system;
2. impdp 실행(os에서). 넣을 datapump 파일의 example의 테이블스페이스를 impdp할 때 data02로 remap하기에 remap_tablespace가 example:data02가 되며 이는 문제에 따라 바뀌어야함
impdp system/oracle directory=dpdump dumpfile=xxx.xxx full=y remap_tablespace=example:data02
'스터디북' 카테고리의 다른 글
| [10/10] 정리 (0) | 2015.10.10 |
|---|---|
| [10/08] 정리 (0) | 2015.10.08 |
| [10/07] 정리 (0) | 2015.10.07 |
| [10/06] 정리 (0) | 2015.10.06 |
| [10/05] 정리 (0) | 2015.10.05 |
