=================================
flashback database 활성화 설정
=================================
SYS@orcl>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl>startup mount exclusive;
ORACLE instance started.
Total System Global Area 1707446272 bytes
Fixed Size 1336988 bytes
Variable Size 1006635364 bytes
Database Buffers 687865856 bytes
Redo Buffers 11608064 bytes
Database mounted.
SYS@orcl>alter system set
2 db_flashback_retention_target=2880 scope=both;
System altered.
SYS@orcl>alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38713: Flashback Database logging is already turned on.
SYS@orcl>alter database open;
Database altered.
SYS@orcl>select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
======================================
USING RMAN
======================================
SYS@orcl>select current_scn from v$database;
CURRENT_SCN
-----------
1203414
SYS@orcl>select count(*) from hr.job_history;
COUNT(*)
----------
10
SYS@orcl>update hr.employees set department_id = 90 where job_id = 'IT_PROG';
5 rows updated.
1 update hr.employees
2 e set salary = least(e.salary,
3 (select(min_salary)/2*1.10
4 from hr.jobs j
5 where j.job_id = e.job_id))
6* where job_id not like 'AD_%'
SYS@orcl>/
103 rows updated.
SYS@orcl>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl>startup mount;
ORACLE instance started.
Total System Global Area 1707446272 bytes
Fixed Size 1336988 bytes
Variable Size 1006635364 bytes
Database Buffers 687865856 bytes
Redo Buffers 11608064 bytes
Database mounted.
RMAN> flashback database to scn = 1203414;
Starting flashback at 2015-10-14:11:25:23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=135 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished flashback at 2015-10-14:11:25:27
===========================================
Using SQLPLUS
===========================================
SYS@orcl>select current_scn from v$database;
CURRENT_SCN
-----------
1204254
SYS@orcl>update hr.employees set salary = 1;
107 rows updated.
SYS@orcl>select sum(salary) from hr.employees;
SUM(SALARY)
-----------
107
SYS@orcl>select current_scn from v$database;
CURRENT_SCN
-----------
1204305
SYS@orcl>alter database open resetlogs;
Database altered.
alert log
-----------------------------------------------------------
Wed Oct 14 11:39:05 2015
alter database open resetlogs
Archived Log entry 115 added for thread 1 sequence 1 ID 0x54b0d6b0 dest 1:
Archived Log entry 116 added for thread 1 sequence 2 ID 0x54b0d6b0 dest 1:
Archived Log entry 117 added for thread 1 sequence 3 ID 0x54b0d6b0 dest 1:
RESETLOGS after incomplete recovery UNTIL CHANGE 1206163
Resetting resetlogs activation ID 1420875440 (0x54b0d6b0)
Wed Oct 14 11:39:13 2015
Setting recovery target incarnation to 12
Wed Oct 14 11:39:13 2015
Assigning activation ID 1420856357 (0x54b08c25)
LGWR: STARTING ARCH PROCESSES
Wed Oct 14 11:39:13 2015
ARC0 started with pid=21, OS id=25142
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Thread 1 opened at log sequence 1
Current log# 1 seq# 1 mem# 0: /u01/app/oracle/oradata/orcl/redo01.log
Current log# 1 seq# 1 mem# 1: /u01/app/oracle/oradata/orcl/redo01_b.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed Oct 14 11:39:15 2015
ARC1 started with pid=22, OS id=25153
Wed Oct 14 11:39:15 2015
ARC2 started with pid=23, OS id=25157
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
Wed Oct 14 11:39:15 2015
ARC3 started with pid=24, OS id=25161
Wed Oct 14 11:39:15 2015
SMON: enabling cache recovery
Successfully onlined Undo Tablespace 2.
Dictionary check beginning <- 컨트롤 파일 정보와 딕셔너리 정보의 일치화
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Wed Oct 14 11:39:15 2015
QMNC started with pid=25, OS id=25165
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Wed Oct 14 11:39:15 2015
db_recovery_file_dest_size of 10240 MB is 1.48% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Wed Oct 14 11:39:16 2015
Starting background process CJQ0
Wed Oct 14 11:39:16 2015
Completed: alter database open resetlogs
Wed Oct 14 11:39:16 2015
CJQ0 started with pid=27, OS id=25195
SYS@orcl>select * from scott.emp where empno=7788;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 1987-04-19:00:00:00 3630 20
일반 시작시
------------------------------------
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
Using parameter settings in server-side spfile /u01/app/oracle/product/11.2.0/db_1/dbs/spfileorcl.ora
System parameters with non-default values:
processes = 150
memory_target = 1632M
control_files = "/u01/app/oracle/oradata/orcl/control01.ctl"
control_files = "/u01/app/oracle/oradata/orcl/control02.ctl"
db_block_size = 8192
compatible = "11.2.0.0.0"
log_archive_dest_1 = "location=/ARC"
log_archive_format = "ORCL_%r_%t_%s.arc"
log_archive_max_processes= 4
db_recovery_file_dest = "/u01/app/oracle/flash_recovery_area"
db_recovery_file_dest_size= 10G
db_flashback_retention_target= 2880
undo_tablespace = "UNDOTBS1"
remote_login_passwordfile= "EXCLUSIVE"
db_domain = ""
dispatchers = "(PROTOCOL=TCP) (SERVICE=orclXDB)"
audit_file_dest = "/u01/app/oracle/admin/orcl/adump"
audit_trail = "DB"
db_name = "orcl"
open_cursors = 300
diagnostic_dest = "/u01/app/oracle"
Wed Oct 14 11:50:19 2015
PMON started with pid=2, OS id=26693
Wed Oct 14 11:50:19 2015
VKTM started with pid=3, OS id=26697 at elevated priority
VKTM running at (10)millisec precision with DBRM quantum (100)ms
Wed Oct 14 11:50:19 2015
GEN0 started with pid=4, OS id=26703
Wed Oct 14 11:50:19 2015
DIAG started with pid=5, OS id=26707
Wed Oct 14 11:50:19 2015
DBRM started with pid=6, OS id=26711
Wed Oct 14 11:50:19 2015
PSP0 started with pid=7, OS id=26715
Wed Oct 14 11:50:19 2015
DIA0 started with pid=8, OS id=26719
Wed Oct 14 11:50:19 2015
MMAN started with pid=9, OS id=26723
Wed Oct 14 11:50:19 2015
DBW0 started with pid=10, OS id=26727
Wed Oct 14 11:50:19 2015
LGWR started with pid=11, OS id=26731
Wed Oct 14 11:50:19 2015
CKPT started with pid=12, OS id=26735
Wed Oct 14 11:50:19 2015
SMON started with pid=13, OS id=26739
Wed Oct 14 11:50:19 2015
RECO started with pid=14, OS id=26743
Wed Oct 14 11:50:19 2015
MMON started with pid=15, OS id=26747
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Wed Oct 14 11:50:19 2015
MMNL started with pid=16, OS id=26751
starting up 1 shared server(s) ...
ORACLE_BASE from environment = /u01/app/oracle
Wed Oct 14 11:50:19 2015
ALTER DATABASE MOUNT
Successful mount of redo thread 1, with mount id 1420865532
Allocated 8388608 bytes in shared pool for flashback generation buffer
Starting background process RVWR
Wed Oct 14 11:50:24 2015
RVWR started with pid=20, OS id=26866
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
Wed Oct 14 11:50:24 2015
ALTER DATABASE OPEN
LGWR: STARTING ARCH PROCESSES
Wed Oct 14 11:50:24 2015
ARC0 started with pid=21, OS id=26873
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Wed Oct 14 11:50:25 2015
ARC1 started with pid=22, OS id=26878
Thread 1 opened at log sequence 3
Current log# 3 seq# 3 mem# 0: /u01/app/oracle/oradata/orcl/redo03.log
Current log# 3 seq# 3 mem# 1: /u01/app/oracle/oradata/orcl/redo03_b.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed Oct 14 11:50:25 2015
ARC2 started with pid=23, OS id=26882
SMON: enabling cache recovery
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
Wed Oct 14 11:50:25 2015
ARC3 started with pid=24, OS id=26886
Successfully onlined Undo Tablespace 2.
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Wed Oct 14 11:50:25 2015
QMNC started with pid=25, OS id=26890
Completed: ALTER DATABASE OPEN
Wed Oct 14 11:50:26 2015
db_recovery_file_dest_size of 10240 MB is 1.48% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Starting background process CJQ0
Wed Oct 14 11:50:26 2015
CJQ0 started with pid=26, OS id=26918
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
------------------------------------------------------
'스터디북' 카테고리의 다른 글
| [10/14] 실습 - 4 block corruption recover (0) | 2015.10.14 |
|---|---|
| [10/14] 실습 - 3 FBDA (0) | 2015.10.14 |
| [10/14] 실습 - 1 Flashback Transaction BACKOUT table (0) | 2015.10.14 |
| [10/14] 정리 (0) | 2015.10.14 |
| [10/13] admin 기본 연습 (0) | 2015.10.13 |