|
Skillset 1
Section5:
11.1配置DNFS:
find this file in directory
(1)$ORACLE_HOME/dbs/oranfstab
(2)/etc/oranfstab
(3)/etc/mtab
cd $ORACLE_HOME/dbs
vi oranfstab
server: MyDataServer1
path: 132.34.35.12
export: /vol/oradata mount: /u02/oradata/prod1
cd $ORACLE_HOME/lib
mv libodm11.so libodm11.so_stub
ln -s libnfsodm11.so libodm11.so
SQL>create tablespace nfs_tb1 datafile '/nfs_df/nfs_tb101.dbf' size 10m;
警告日志出现如下错误:
Direct NFS: please check that oradism is setuid
解决办法:
1、先把 oradism 文件修改为 root 属主:
chown root /u01/app/oracle/product/11.1.0/db_1/bin/oradism
2、再把这个文件加上 setuid 的权限:
chmod u+s /u01/app/oracle/product/11.1.0/db_1/bin/oradism
11.2配置DNFS:
• 1、进入以下路径:
/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/
• 2、重新编译文件:
make -f ins_rdbms.mk dnfs_on
Section 6: Applying a Patch
export PATH=$ORACLE_HOME/OPatch
cd /home/oracle/scripts
unzip p8342329_111070_linux-x86.zip
cd 8342329
$ORACLE_HOME/OPatch/opatch query -is_online_patch `pwd`
--prod1/prod2 should be shutdown
$ORACLE_HOME/OPatch/opatch apply
$ORACLE_HOME/OPatch/opatch lsinventory
Section9:
- RMAN> backup tag 'Q107' database plus archivelog tag 'Q107' keep forever ;
复制代码
Skillset 2:
- rman target sys/oracle@PROD1 auxiliary sys/oracle@SBDB1 cmdfile=sbdb1.rcv
复制代码
- duplicate target database for standby
- from active database
- nofilenamecheck
- spfile
- set control_files='/u01/app/oracle/oradata/SBDB1/control01.ctl','/u01/app/oracle/oradata/SBDB1/control02.ctl','/u01/app/oracle/oradata/SBDB1/control03.ctl'
- set db_file_name_convert='/u01/app/oracle/oradata/PROD1','/u01/app/oracle/oradata/SBDB1'
- set log_file_name_convert='/u01/app/oracle/oradata/PROD1','/u01/app/oracle/oradata/SBDB1'
- set db_unique_name='SBDB1'
- set fal_server='PROD1'
- set fal_client='SBDB1'
- set log_archive_config='dg_config=(PROD1,SBDB1)'
- set log_archive_dest_1='location=use_db_recovery_file_dest'
- set log_archive_dest_2='service=PROD1 valid_for=(online_logfiles, primary_role) db_unique_name=PROD1'
- set audit_file_dest='/u01/app/oracle/admin/SBDB1/adump';
复制代码 备库监听器:
- # listener.ora Network Configuration File: /u01/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
- # Generated by Oracle configuration tools.
- SID_LIST_LISTENER =
- (SID_LIST =
- (SID_DESC =
- (GLOBAL_DBNAME = SBDB1)
- (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
- (SID_NAME = SBDB1)
- )
- (SID_DESC =
- (GLOBAL_DBNAME = SBDB1_DGMGRL)
- (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
- (SID_NAME = SBDB1)
- )
- )
- LISTENER =
- (DESCRIPTION_LIST =
- (DESCRIPTION =
- (ADDRESS = (PROTOCOL = TCP)(HOST = station37.example.com)(PORT = 1521))
- (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
- )
- )
复制代码
主库监听器:
- # listener.ora Network Configuration File: /u01/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
- # Generated by Oracle configuration tools.
- SID_LIST_LISTENER =
- (SID_LIST =
- (SID_DESC =
- (GLOBAL_DBNAME = PROD1)
- (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
- (SID_NAME = PROD1)
- )
- (SID_DESC =
- (GLOBAL_DBNAME = PROD1_DGMGRL)
- (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
- (SID_NAME = PROD1)
- )
- )
- LISTENER =
- (DESCRIPTION_LIST =
- (DESCRIPTION =
- (ADDRESS = (PROTOCOL = TCP)(HOST = station38.example.com)(PORT = 1521))
- )
- (DESCRIPTION =
- (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
- )
- )
复制代码
打开ADG:
- [oracle@station37 ~]$ sqlplus /nolog
- SQL*Plus: Release 10.1.0.5.0 - Production on Sat Aug 25 03:41:53 2018
- Copyright (c) 1982, 2005, Oracle. All rights reserved.
- SQL> conn / as sysdba
- Connected.
- SQL> select open_mode from v$database;
- OPEN_MODE
- ----------
- MOUNTED
- SQL> alter database open read only ;
- alter database open read only
- *
- ERROR at line 1:
- ORA-10456: cannot open standby database; media recovery session may be in
- progress
- SQL> alter database recover managed standby database cancel ;
- Database altered.
- SQL> alter database open read only ;
- Database altered.
- SQL> alter database recover managed standby database using current logfile disconnect from session ;
- Database altered.
- SQL>
复制代码
----create dataguard broker
----make static register in listener.ora, global name must be PROD1_DGMGRL and SBDB1_DGMGRL/ make tnsnames.ora connect PROD1_DGMGRL and SBDB1_DGMGRL
conn sys/oracle@prod1 as sysdba
alter system set dg_broker_start=true;
conn sys/oracle@sbdb1 as sysdba
alter system set dg_broker_start=true;
dgmgrl sys/oracle@prod1
CREATE CONFIGURATION 'dgconfig' AS PRIMARY DATABASE IS PROD1 CONNECT IDENTIFIER IS PROD1;
add database SBDB1 as connect identifier is sbdb1 MAINTAINED AS physical;
ENABLE CONFIGURATION;
Section 2: Testing the Standby Database
--convert database from physical standby to snapshot standby
convert database SBDB1 to snapshot standby;
Section 3: Restoring the Standby Database
--convert database from snapshot standby to phyical standby
convert database SBDB1 to physical standby;
--active dataguard
edit database SBDB1 set state='apply-off';
alter database open;
edit database SBDB1 set state='apply-on';
Skillset 3: Data and Data Warehouse Management
- CREATE MATERIALIZED VIEW sh.prod_mv AS SELECT SUM(prod_list_price - prod_min_price) M1, COUNT(prod_category) M2, prod_category FROM products GROUP BY prod_category;
复制代码- select * from dba_mviews m
- where m.OWNER='SH' and m.MVIEW_NAME='PROD_MV';
-
复制代码
[oracle@station38 admin]$ sqlplus /nolog
SQL*Plus: Release 11.1.0.7.0 - Production on Sat Aug 25 08:55:36 2018
Copyright (c) 1982, 2008, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> @/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/utlxmv.sql
Table created.
SQL>
- begin
- dbms_mview.explain_mview(mv => 'SH.PROD_MV');
- end;
- commit;
- select * from MV_CAPABILITIES_TABLE;
复制代码 | STATEMENT_ID | MVOWNER | MVNAME | CAPABILITY_NAME | POSSIBLE | RELATED_TEXT | RELATED_NUM | MSGNO | MSGTXT | SEQ | 1 | | SH | PROD_MV | PCT | N | | | | | 1 | 2 | | SH | PROD_MV | REFRESH_COMPLETE | Y | | | | | 1002 | 3 | | SH | PROD_MV | REFRESH_FAST | N | | | | | 2003 | 4 | | SH | PROD_MV | REWRITE | N | | | | | 3004 | 5 | | SH | PROD_MV | PCT_TABLE | N | PRODUCTS | 93 | 2068 | relation is not a partitioned table | 4005 | 6 | | SH | PROD_MV | REFRESH_FAST_AFTER_INSERT | N | SH.PRODUCTS | | 2162 | the detail table does not have a materialized view log | 5006 | 7 | | SH | PROD_MV | REFRESH_FAST_AFTER_ONETAB_DML | N | M1 | 7 | 2143 | SUM(expr) without COUNT(expr) | 6007 | 8 | | SH | PROD_MV | REFRESH_FAST_AFTER_ONETAB_DML | N | | | 2146 | see the reason why REFRESH_FAST_AFTER_INSERT is disabled | 6008 | 9 | | SH | PROD_MV | REFRESH_FAST_AFTER_ONETAB_DML | N | | | 2142 | COUNT(*) is not present in the select list | 6009 | 10 | | SH | PROD_MV | REFRESH_FAST_AFTER_ONETAB_DML | N | | | 2143 | SUM(expr) without COUNT(expr) | 6010 | 11 | | SH | PROD_MV | REFRESH_FAST_AFTER_ANY_DML | N | | | 2161 | see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled | 7011 | 12 | | SH | PROD_MV | REFRESH_FAST_PCT | N | | | 2157 | PCT is not possible on any of the detail tables in the materialized view | 8012 | 13 | | SH | PROD_MV | REWRITE_FULL_TEXT_MATCH | N | | | 2159 | query rewrite is disabled on the materialized view | 9013 | 14 | | SH | PROD_MV | REWRITE_PARTIAL_TEXT_MATCH | N | | | 2159 | query rewrite is disabled on the materialized view | 10014 | 15 | | SH | PROD_MV | REWRITE_GENERAL | N | | | 2159 | query rewrite is disabled on the materialized view | 11015 | 16 | | SH | PROD_MV | REWRITE_PCT | N | | | 2158 | general rewrite is not possible or PCT is not possible on any of the detail tables | 12016 | 17 | | SH | PROD_MV | PCT_TABLE_REWRITE | N | PRODUCTS | 93 | 2068 | relation is not a partitioned table | 13017 |
----
- SQL> set pagesize 10000
- SQL> set long 10000
- SQL> select dbms_metadata.get_ddl('MATERIALIZED_VIEW','PROD_MV','SH') from dual;
- DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','PROD_MV','SH')
- --------------------------------------------------------------------------------
- CREATE MATERIALIZED VIEW "SH"."PROD_MV" ("M1", "M2", "PROD_CATEGORY")
- ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOG
- GING
- STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
- PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
- TABLESPACE "USERS"
- BUILD IMMEDIATE
- USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
- STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
- PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
- TABLESPACE "USERS"
- REFRESH FORCE ON DEMAND
- USING DEFAULT LOCAL ROLLBACK SEGMENT
- USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
- AS SELECT SUM(prod_list_price - prod_min_price) M1, COUNT(prod_category) M2, p
- rod_category FROM products GROUP BY prod_category
复制代码 把以上的输出改成:
- CREATE MATERIALIZED VIEW SH.PROD_MV REFRESH Fast enable QUERY REWRITE AS SELECT SUM(prod_list_price - prod_min_price) M1, COUNT(prod_category) M2, prod_category FROM products GROUP BY prod_category
复制代码
tuning脚本3_1.sql:
- create directory dir3_1 as '/home/oracle/dir3_1';
- declare
- v_task varchar2(200);
- begin
- dbms_advisor.tune_mview(
- v_task,
- 'CREATE MATERIALIZED VIEW SH.PROD_MV REFRESH Fast enable QUERY REWRITE AS SELECT SUM(prod_list_price - prod_min_price) M1, COUNT(prod_category) M2, prod_category FROM sh.products GROUP BY prod_category');
- dbms_advisor.create_file (
- dbms_advisor.get_task_script(v_task),
- 'DIR3_1',
- 'mvtune_script.sql');
- end;
- /
复制代码- [oracle@station38 ~]$ sqlplus /nolog
- SQL*Plus: Release 11.1.0.7.0 - Production on Sat Aug 25 09:16:47 2018
- Copyright (c) 1982, 2008, Oracle. All rights reserved.
- SQL> conn / as sysdba
- Connected.
- SQL> @3_1.sql
- Directory created.
- PL/SQL procedure successfully completed.
- SQL>
复制代码
在目录对象里找到mvtune_script.sql, 去掉其中的alter段落,删除原有prod_mv,执行mvtune_script.sql:
- truncate table MV_CAPABILITIES_TABLE;
-
- begin
- dbms_mview.explain_mview(mv => 'SH.PROD_MV');
- end;
- commit;
- select * from MV_CAPABILITIES_TABLE;
复制代码 | STATEMENT_ID | MVOWNER | MVNAME | CAPABILITY_NAME | POSSIBLE | RELATED_TEXT | RELATED_NUM | MSGNO | MSGTXT | SEQ | 1 | | SH | PROD_MV | PCT | N | | | | | 1 | 2 | | SH | PROD_MV | REFRESH_COMPLETE | Y | | | | | 1002 | 3 | | SH | PROD_MV | REFRESH_FAST | Y | | | | | 2003 | 4 | | SH | PROD_MV | REWRITE | Y | | | | | 3004 | 5 | | SH | PROD_MV | PCT_TABLE | N | SH.PRODUCTS | 272 | 2068 | relation is not a partitioned table | 4005 | 6 | | SH | PROD_MV | REFRESH_FAST_AFTER_INSERT | Y | | | | | 5006 | 7 | | SH | PROD_MV | REFRESH_FAST_AFTER_ONETAB_DML | Y | | | | | 6007 | 8 | | SH | PROD_MV | REFRESH_FAST_AFTER_ANY_DML | Y | | | | | 7008 | 9 | | SH | PROD_MV | REFRESH_FAST_PCT | N | | | 2157 | PCT is not possible on any of the detail tables in the materialized view | 8009 | 10 | | SH | PROD_MV | REWRITE_FULL_TEXT_MATCH | Y | | | | | 9010 | 11 | | SH | PROD_MV | REWRITE_PARTIAL_TEXT_MATCH | Y | | | | | 10011 | 12 | | SH | PROD_MV | REWRITE_GENERAL | Y | | | | | 11012 | 13 | | SH | PROD_MV | REWRITE_PCT | N | | | 2158 | general rewrite is not possible or PCT is not possible on any of the detail tables | 12013 | 14 | | SH | PROD_MV | PCT_TABLE_REWRITE | N | SH.PRODUCTS | 272 | 2068 | relation is not a partitioned table | 13014 |
能否进行快速刷新:
- begin
- dbms_mview.refresh(list => 'SH.PROD_MV',method => 'F');
- end;
复制代码
能否进行查询重写:
- SQL> SELECT SUM(prod_list_price - prod_min_price) M1, COUNT(prod_category) M2, prod_category FROM products GROUP BY prod_category;
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 3752038752
- ----------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ----------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 5 | 265 | 3 (0)| 00:00:01 |
- | 1 | MAT_VIEW REWRITE ACCESS FULL| PROD_MV | 5 | 265 | 3 (0)| 00:00:01 |
- ----------------------------------------------------------------------------------------
- Note
- -----
- - dynamic sampling used for this statement
- Statistics
- ----------------------------------------------------------
- 1633 recursive calls
- 0 db block gets
- 323 consistent gets
- 1 physical reads
- 0 redo size
- 662 bytes sent via SQL*Net to client
- 420 bytes received via SQL*Net from client
- 2 SQL*Net roundtrips to/from client
- 8 sorts (memory)
- 0 sorts (disk)
- 5 rows processed
- SQL>
复制代码
XTTS准备考题时是这样:
- [oracle@station38 ~]$ expdp system/oracle1 directory=dir1 dumpfile=trans3_2.dmp TRANSPORT_TABLESPACES=trpdata
- Export: Release 11.1.0.7.0 - Production on Saturday, 25 August, 2018 9:47:25
- Copyright (c) 2003, 2007, Oracle. All rights reserved.
- UDE-28002: operation generated ORACLE error 28002
- ORA-28002: the password will expire within 7 days
- Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
- With the Partitioning, OLAP, Data Mining and Real Application Testing options
- Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** directory=dir1 dumpfile=trans3_2.dmp TRANSPORT_TABLESPACES=trpdata
- Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
- Processing object type TRANSPORTABLE_EXPORT/TABLE
- Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
- Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
- ******************************************************************************
- Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
- /home/oracle/scripts/trans3_2.dmp
- ******************************************************************************
- Datafiles required for transportable tablespace TRPDATA:
- /u01/app/oracle/oradata/EMREP/TRPDATA_6
- Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 09:47:39
- [oracle@station38 ~]$ sqlplus /nolog
- SQL*Plus: Release 11.1.0.7.0 - Production on Sat Aug 25 09:47:49 2018
- Copyright (c) 1982, 2008, Oracle. All rights reserved.
- SQL> conn / as sysdba
- Connected.
- SQL> desc v$transportable_platform
- Name Null? Type
- ----------------------------------------- -------- ----------------------------
- PLATFORM_ID NUMBER
- PLATFORM_NAME VARCHAR2(101)
- ENDIAN_FORMAT VARCHAR2(14)
- SQL> select PLATFORM_NAME from v$transportable_platform;
- PLATFORM_NAME
- --------------------------------------------------------------------------------
- Solaris[tm] OE (32-bit)
- Solaris[tm] OE (64-bit)
- Microsoft Windows IA (32-bit)
- Linux IA (32-bit)
- AIX-Based Systems (64-bit)
- HP-UX (64-bit)
- HP Tru64 UNIX
- HP-UX IA (64-bit)
- Linux IA (64-bit)
- HP Open VMS
- Microsoft Windows IA (64-bit)
- PLATFORM_NAME
- --------------------------------------------------------------------------------
- IBM zSeries Based Linux
- Linux x86 64-bit
- Apple Mac OS
- Microsoft Windows x86 64-bit
- Solaris Operating System (x86)
- IBM Power Based Linux
- HP IA Open VMS
- Solaris Operating System (x86-64)
- 19 rows selected.
- SQL> exit
- Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
- With the Partitioning, OLAP, Data Mining and Real Application Testing options
- [oracle@station38 ~]$ rman target /
- Recovery Manager: Release 11.1.0.7.0 - Production on Sat Aug 25 09:48:24 2018
- Copyright (c) 1982, 2007, Oracle. All rights reserved.
- connected to target database: EMREP (DBID=4076569368)
- RMAN> convert tablespace trpdata to platform 'Solaris[tm] OE (64-bit)' format '/home/oracle/TRPDATA_6';
- Starting conversion at source at 25-AUG-18
- using target database control file instead of recovery catalog
- allocated channel: ORA_DISK_1
- channel ORA_DISK_1: SID=1610 device type=DISK
- channel ORA_DISK_1: starting datafile conversion
- input datafile file number=00008 name=/u01/app/oracle/oradata/EMREP/TRPDATA_6
- converted datafile=/home/oracle/TRPDATA_6
- channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
- Finished conversion at source at 25-AUG-18
- RMAN>
复制代码
|
|