周五吃完午饭,和Amazon DBA team 进行了技术交流, (Amazon上个月收购了我们公司AbeBooks, 下文详解)
我其实想问问 西雅图(Seatle)那边的DBA市场, 当时人多, 没有好意思问. 听说维多利亚这边一些华人去了微软,那边生活成本低,房子便宜,收入又好,去的人基本不打算回来了. 希望以后人员交流, 派我过去工作几个星期. 维多利亚(Victoria)距离西雅图比较近, 隔着欢德福卡海峡(Strait of Juan de Fuca),坐船一个小时.
内容如下,
*) suggest move away from RAC for OLTP database, remove one big central database, function split to many small databases, Amazon got 100+ databases.
- Cluster ware down, database down - one instance hang, database hang - painful global lock control - Lot’s of issues with RAC # Add database file make RAC database hang (happened in Amazon) # enq-US (undo segment management) cause slow Global cache/message transfer
*) Suggest Open source Linux over other Unix
*) Suggest cross platform and cross Oracle version standby database to help upgrade, will check the configuration certification to confirm it.
*) Suggest batch commit (SQL> COMMIT BATCH NOWAIT) for Inventory data loading row-by-row auto commit jobs
*) Oracle Active Data Guard Option enables real-time read-only access to a physical standby database to offload queries, sorting, reporting, web-based access,
*) Enable fast-start failover to fail over automatically when the primary database becomes unavailable, proven stable,
we’ll implement above 2 options after upgrade to 11.1.0.7 in 2009 spring.
今年很多DBA朋友从中国到旧金山参加了Oracle Open World, 馋的木匠口水长流. 终于领导建议并批准我明年前往, 暗自窃喜.
这里是预算, 去过的DBA同行, 看看够不够?
Oracle Openworld Conference 2009 a. Conference Name – Oracle Openworld Conference 2009 b. Conference Date –Oct 11-15, 2009 c. Number of Attendees – 1 x Dev - Charlie d. Conference Cost - $2600 US e. Flight Cost - $1000 – Estimated f. Hotel Cost - $1200 – Estimated g. Taxi Cost - $200 – Estimated h. Meals - $250 – Estimated
- a total of ##hours of Oracle expert consulting. - on-site support of our systems team for setting up and configuring Oracle 11g servers for high availability, including replication to an outside system and performance optimization for the type of data we are storing - other technologies in which the consultant should be proficient include: storage use strategies, partitioning, warm-standby and replication using DataGuard or other systems - provide basic Oracle 11g on-site training to developers and DBA.
Victoria IT Club hold free IT Seminars on 3rd Saturday 2:30pm every month at EBC church second floor meeting room. After each seminar we'll play basketball at Church Gym. www.ebcvictoria.ca
这里有一个示例, 自己在 SQL*Plus 或者SQL Developer里面跑一下吧, select DEFAULT_TABLESPACE, translate(wmsys.wm_concat(username),',','|') from dba_users group by DEFAULT_TABLESPACE;
3) FBI index, virtual column index and SHRINK clause
有个听众提个问题, 说在10.2以下版本, 有Function Based Index的表不能做空间回收-Shrink. Dan Morgan这位老大自己没测试过, 随口就说11g上,在一个表的虚拟列上的构建索引,这张表可以Shrink, 岂不是犯了和 老旦一样的错误. (老旦:Dan. 你们都知道是谁, 曾被老刘 Lewis 严肃的教育过, 以后有 另外一篇文章评论,关于PGA 和 Parallel execution)
第二天到办公室一测试, 发现11.1也不行.
以下是测试用例:
--drop table scott.y1; create table scott.y1(sal number, comm number);
drop index scott.yi_fbi1; create index scott.yi_fbi1 on scott.y1(sal + comm) --tablespace data_auto ;
alter table scott.y1 enable row movement;
alter TABLE scott.y1 shrink space compact; alter TABLE scott.y1 shrink space;
ERROR at line 1: ORA-10631: SHRINK clause should not be specified for this object
drop index scott.yi_fbi1;
alter table scott.y1 add (income AS (sal + comm));
drop index scott.yi_vi1; create index scott.yi_vi1 on scott.y1(income);
alter TABLE scott.y1 shrink space;
ERROR at line 1: ORA-10631: SHRINK clause should not be specified for this object
US Withholding for Canadian Independent Contractors
Using Form W-8BEN to Claim US-Canada Tax Treaty Benefits American companies generally withhold income taxes on income being paid to foreign nationals. You may qualify for reduced withholding if meet some rules. Basically, there are three steps to this process. First, you must clarify in which country you are a resident. Second, you must decide where your "fixed place of business" is located. Third, you must notify your clients of your tax status using Form W-8BEN. Withholding The tax treaty specifically allows for US companies to withhold income taxes on self-employed Canadian residents (Article XVII, paragraph 1). Withholding will be 10% on the first $5,000 of income, and 30% on income over that threshold. The client and independent contractor may agree on a lesser percentage of withholding if these amounts are considered "excessive" (Article XVII, paragraph 2). Normally, US companies are required to "withhold 30% of any payment of an amount subject to withholding made to a payee that is a foreign person" (Instructions for Form W-8BEN). Form W-8BEN is used to inform the US company that you are "a beneficial owner that is a foreign person entitled to a reduced rate of withholding." You qualify for a reduced rate of withholding if you meet the residency and fixed place of business rules Filling out Form W-8BEN Provide your name in Line 1 and check "individual" in Line 3. However, if you are working under a business name, provide your business name in Line 1 and check the appropriate type of entity in Line 3. See: http://taxes.about.com/od/taxplanning/qt/form_W8BEN.htm
Claiming Tax Treaty Benefits Exemption From Withholding If a tax treaty between the United States and your country provides an exemption from, or a reduced rate of, withholding for certain items of income, you should notify the payor of the income (the withholding agent) of your foreign status to claim the benefits of the treaty. Generally, you do this by filing Form W-8BEN, Certificate of Foreign Status of Beneficial Owner for United States Tax Withholding with the withholding agent.
Rules that Apply to Compensation for Personal Services Independent contractors. If you perform personal services as an independent contractor (rather than an employee) and you can claim an exemption from withholding on that personal service income because of a tax treaty, submit Form 8233 to each withholding agent from whom amounts will be received. See: http://www.irs.gov/businesses/small/international/article/0,,id=96438,00.html
Instructions for the Withholding Agent
Requirement To Withhold A withholding agent must withhold 30% of any payment of an amount subject to withholding made to a payee that is a foreign person unless it can associate the payment with documentation (for example, Form W-8 or Form W-9) … Responsibilities of the Withholding Agent If you are a withholding agent making a payment of U.S. source interest, dividends, rents, royalties, commissions, nonemployee compensation, other fixed or determinable annual or periodical gains, profits, or income, and certain other amounts (including broker and barter exchange transactions, and certain payments made by fishing boat operators), you are generally required to obtain from the payee either a Form W-9, Request for Taxpayer Identification Number and Certification, or a Form W-8. These forms are also used to establish a person's status for purposes of domestic information reporting (for example, on a Form 1099) and backup withholding. If you receive a Form W-9, you must generally make an information return on a Form 1099. If you receive a Form W-8, you are exempt from reporting on Form 1099, but you may have to file Form 1042-S and withhold under the rules applicable to payments made to foreign persons. See the Instructions for Form 1042-S for more information. Generally, you must withhold 30% from the gross amount paid to a foreign person unless you can reliably associate the payment with a Form W-8. You can reliably associate a payment with a Form W-8 if you hold a valid form, you can reliably determine how much of the payment relates to the form, and you have no actual knowledge or reason to know that any of the information or certifications on the form are unreliable or incorrect. Do not send Forms W-8 to the IRS. Instead, keep the forms in your records for as long as they may be relevant to the determination of your tax liability under section 1461. Use the information on Forms W-8 to prepare Forms 1042-S. See: http://www.irs.gov/instructions/iw8/ch01.html
We recommend going to AL32UTF8 as the ultimate solution for Oracle 11g-. AL32UTF8 is the database character set that supports the latest version (5.0 in Oracle 11.1) of the Unicode standard. It also provides support for the newly defined supplementary characters.
Here are some major points I briefed as a reference.
How to move to AL32UTF8 / UTF8 (Unicode) Database Character Set Note:119119.1
to check you database Character Set, select value from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET';
Usualy database will grow when going to AL32UTF8, use CSSCAN to generate the size expansion report.
The NLS_LENGTH_SEMANTICS initialization parameter determines whether a new column of character datatype uses byte or character semantics. The default value of the parameter is BYTE. The BYTE and CHAR qualifiers shown in the VARCHAR2 definitions should be avoided when possible because they lead to mixed-semantics databases. Instead, set NLS_LENGTH_SEMANTICS in the initialization parameter file and define column datatypes to use the default semantics based on the value of NLS_LENGTH_SEMANTICS.
columne_name VarChar2(300 char/byte)
Related function: lengthb(), substrb()
UniStr() over Chr() select Chr(163) from dual; select UniStr('\C2A3') from dual;
convert(string_column,'AL32UTF8','US7ASCII'), convert from US7ASCII to AL32UTF8.
To use WE8MSWIN1252 over WE8ISO8559P1, WE8MSWIN1252 supports European Code.
Reference
* US7ASCII: US 7-bit ASCII character set * WE8ISO8859P1: ISO 8859-1 West European 8-bit character set * WE8MSWIN1252: Microsoft Windows West European Code Page 1252 * UTF8: Unicode 3.0 Universal character set CESU-8 encoding form * AL32UTF8: Unicode 5.0 Universal character set UTF-8 encoding form
**Unicode character sets in the Oracle database, Note:260893.1
exp/imp
set NLS_LANG= export
set NLS_LANG= import into the new UTF8 db.
The conversion to UTF8 is done while inserting the data in the UTF8 database.
下面看看 System Architect 的定义, 还有我喜欢的职位 - Database Designer
System Architect Definition The system architect has the task of putting together the skeleton of a software project.
Depending on the specifications gathered by the requirements analyst, the system architect will choose to focus on ease of maintenance, application performance, compatibility with existing systems, or a combination of all three. Each decision that the system architect makes has to be carefully considered because a wrong move the beginning of a project can have damaging effects later in the software evelopment life cycle.
Database Designer Definition Most software projects boil down to information storage and retrieval. Deciding how and where this information is stored is the domain of the database designer. Working with a system architect and a requirements analyst, the database designer ensures that all necessary data has a place to be stored. At the same time, the speed at which the data can be stored and retrieved are taken in to account so that user's are not left waiting for unreasonable amounts of time.
Sometimes database designers take on database related activities such as arranging for backups, creating ad-hoc reports, and server tuning. However, these other tasks are often part of a Database Administrator's (DBA) job.
Posts
