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'
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
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;
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;
QQ群⑧：
窥视卡
雷达卡
发表于 2015-11-9 12:43:21
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡