<<OCM实验选讲>> 第六课 数据仓库实验

一 当有数据文件被误删除时如何恢复

SQL> select file_name from dba_data_files where file_id=1;

FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/system01.dbf

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !ls -l /home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/system01.dbf
-rw-r----- 1 oracle oinstall 314580992 Jan 21 21:42 /home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/system01.dbf

SQL> !rm -f /home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/system01.dbf

SQL> !ls -l /home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/system01.dbf
ls: /home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/system01.dbf: No such file or directory

SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size            1218992 bytes
Variable Size           92276304 bytes
Database Buffers      188743680 bytes
Redo Buffers            2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1:
'/home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/system01.dbf'


SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size            1218992 bytes
Variable Size           92276304 bytes
Database Buffers      188743680 bytes
Redo Buffers            2973696 bytes
Database mounted.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@odd admin]$ rman target sys/oracle@LEO1 catalog catadmin/catadmin@LEO2

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Jan 21 21:44:15 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: LEO1 (DBID=1715976824, not open)
connected to recovery catalog database

RMAN> restore datafile 1;

Starting restore at 21-JAN-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=154 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/system01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/full_leo1_02oud82212.rmn
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/backup/full_leo1_02oud82212.rmn tag=TAG20140118T185810
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
Finished restore at 21-JAN-14

RMAN> recover datafile 1;

Starting recover at 21-JAN-14
using channel ORA_DISK_1
using channel ORA_DISK_2

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 21-JAN-14

RMAN> alter database open;

database opened

RMAN> exit


Recovery Manager complete.
[oracle@odd admin]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jan 21 21:45:04 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select file_name,tablespace_name,status from dba_data_files where tablespace_name='SYSTEM';

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME            STATUS
------------------------------ ---------
/home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/system01.dbf
SYSTEM                   AVAILABLE


SQL>

--EOF--

二 物化视图

基表和物化视图不在同一个库上，基表和日志在LEO2上，物化视图在LEO1上，在LEO1上可手动刷新物化视图

[oracle@odd admin]$ sqlplus sys/oracle@LEO2 as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jan 21 22:14:51 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options

SQL> create user mv_leo2 identified by mv_leo2;

User created.

SQL> grant connect,resource to mv_leo2;

Grant succeeded.

SQL> conn mv_leo2/mv_leo2@LEO2
Connected.
SQL> create table t (a varchar2(10));   

Table created.

SQL> insert into t values ('leonarding');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;

A
----------
leonarding

SQL> create materialized view log on t with rowid;

Materialized view log created.

SQL> conn sys/oracle@LEO1 as sysdba
Connected.
SQL> show parameter global_names

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
global_names                 boolean     FALSE
SQL> create public database link dblink_to_leo2 connect to mv_leo2 identified by mv_leo2 using 'LEO2';

Database link created.

SQL> create user mv_leo1 identified by mv_leo1;

User created.

SQL> grant dba to mv_leo1;

Grant succeeded.

SQL> conn mv_leo1/mv_leo1@leo1
Connected.
SQL> create materialized view mv_t refresh fast with rowid as select * from t@dblink_to_leo2;

Materialized view created.

SQL> select * from mv_t;

A
----------
leonarding

SQL> conn mv_leo2/mv_leo2@LEO2
Connected.
SQL> insert into t values ('ls');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;

A
----------
leonarding
ls

SQL> conn mv_leo1/mv_leo1@LEO1
Connected.
SQL> select * from mv_t;

A
----------
leonarding

SQL> exec dbms_mview.refresh('mv_t','f');

PL/SQL procedure successfully completed.

SQL> select * from mv_t;

A
----------
leonarding
ls

SQL>

--EOF--

三 外部表

LEO1卸载数据，传输到LEO2加载数据

[oracle@odd admin]$ sqlplus sys/oracle@LEO1 as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jan 21 22:20:37 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create user ext identified by ext;

User created.

SQL> grant dba to ext;

Grant succeeded.

SQL> conn ext/ext@leo1
Connected.
SQL> create directory dir_dmp as '/home/oracle';

Directory created.

SQL> grant read,write on directory dir_dmp to public;

Grant succeeded.

SQL> create table t2                   
organization external
(type oracle_datapump                
default directory dir_dmp               
location ('t2_part1.dat','t2_part2.dat')
)
parallel 2                      
as               
select owner,object_id,object_name from dba_objects where owner='SYSTEM';  2    3    4    5    6    7    8    9  

Table created.

SQL> !ls -l /home/oracle/t2_part*
-rw-r----- 1 oracle oinstall 20480 Jan 21 22:21 /home/oracle/t2_part1.dat
-rw-r----- 1 oracle oinstall 20480 Jan 21 22:21 /home/oracle/t2_part2.dat

SQL> !scp ~/t2_part1.dat ~/t2_part2.dat even:~/
The authenticity of host 'even (192.168.10.104)' can't be established.
RSA key fingerprint is bf:0a:4e:9b:3b:42:7d:45:b3:c7:cb:62:e9:34:1a:7f.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'even' (RSA) to the list of known hosts.
oracle@even's password:
t2_part1.dat                                                                                                                                    100%   20KB  20.0KB/s   00:00    
t2_part2.dat                                                                                                                                    100%   20KB  20.0KB/s   00:00    

SQL> conn sys/oracle@LEO2 as sysdba
Connected.
SQL> create user ext identified by ext;

User created.

SQL> grant dba to ext;

Grant succeeded.

SQL> conn ext/ext@leo2
Connected.
SQL> create directory dir_dmp as '/home/oracle';

Directory created.

SQL> grant read,write on directory dir_dmp to public;

Grant succeeded.

SQL> create table t3 (owner varchar2(100),object_id varchar2(100),object_name varchar2(100))   
organization external                  
(type oracle_datapump               
default directory dir_dmp         
location ('t2_part1.dat','t2_part2.dat'));  2    3    4    5  

Table created.

SQL> select count(*) from t3;

  COUNT(*)
----------
       449

SQL>

--EOF--