1、修改参数文件
primary:
*.standby_archive_dest=’/opt/ora9/oradata/txzldb/stdarch/’
*.log_archive_dest_2=’service=txzldb.Standby optional reopen=60′ –配置tns
*.utl_file_dir=’/opt/ora9′
*.remote_archive_enable=TRUE
*.standby_file_management=AUTO
*.fal_server=’txzldb.Standby’
*.fal_client=’txzldb.Primary’
*.log_archive_dest_state_2=defer
standby:
*.standby_archive_dest=’/opt/ora9/oradata/txzldb/stdarch/’
*.log_archive_dest_2=’service=txzldb.Primary optional reopen=60′ –配置tns
*.utl_file_dir=’/opt/ora9′
*.remote_archive_enable=TRUE
*.standby_file_management=AUTO
*.fal_server=’txzldb.Primary’
*.fal_client=’txzldb.Standby’
*.log_archive_dest_state_2=defer
2、备份主库
backup database format=’/home/oracle/full_backup_%U_%s.bak’;
sql “alter system archive log current”;
–backup filesperset 10 archivelog all format=’/home/oracle/archivlog_%U_%s.bak’;（可选）
alter database create standby controlfile as ‘/home/oracle/control01.ctl’;
3、拷贝密码文件控制文件
scp /opt/oracle/product/9.2.0/dbs/orapwora9i
192.168.3.2:/opt/oracle/product/9.2.0/dbs/
scp control01.ctl
192.168.3.2:/opt/oracle/oradata/ora9i/control01.ctl
4、恢复备机
拷贝备份至备机与主机相同的目录
sqlplus “/ as sysdba”
startup nomount pfile=”
alter database mount standby database;
exit
rman target / nocatalog
restore database
–restore archivelog all;（可选）
–recover automatic standby database;（可选）
5、启动恢复状态
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
create spfile from pfile=”;
alter system set log_archive_dest_state_2=enable scope=both;
6、添加临时表空间
recover managed standby database cancel;
alter database open read only;
alter tablespace temp add tempfile ‘/opt/oracle/oradata/ora9i/temp01.dbf’
SIZE 412M AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED;
shutdown immediate;
startup nomount pfile=”
alter database mount standby database;
recover managed standby database disconnect from session;
Oracle监听设置密码
LSNRCTL> change_password
Old password:
New password:
Reenter new password:
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ecp-uc-db1)(PORT=1521)))
Password changed for LISTENER
The command completed successfully
LSNRCTL> set password
Password:
The command completed successfully
LSNRCTL> save_config
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ecp-uc-db1)(PORT=1521)))
Saved LISTENER configuration parameters.
Listener Parameter File /opt/oracle/product/10.2.0/db_1/network/admin/listener.ora
Old Parameter File /opt/oracle/product/10.2.0/db_1/network/admin/listener.bak
The command completed successfully
[oracle@ecp-uc-db1 admin]$ cat listener.ora
#—-ADDED BY TNSLSNR 10-JUN-2011 18:13:24—
PASSWORDS_LISTENER = 6D7AA003392C436A
#——————————————–
note：10g数据库上需要上添加（重启监听）
LOCAL_OS_AUTHENTICATION_LISTENER = OFF
1、添加LOCAL_OS_AUTHENTICATION_LISTENER = OFF之前
Security ON: Password or Local OS Authentication
2、添加LOCAL_OS_AUTHENTICATION_LISTENER = OFF之后
Security ON: Password
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ecp-uc-db1)(PORT=1521)))
TNS-01169: The listener has not recognized the password
LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ecp-uc-db1)(PORT=1521)))
TNS-01169: The listener has not recognized the password
LSNRCTL> set password 123456
The command completed successfully
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ecp-uc-db1)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.4.0 – Production
Start Date 10-JUN-2011 18:15:49
Uptime 0 days 0 hr. 1 min. 16 sec
Trace Level off
Security ON: Password
SNMP OFF
Listener Parameter File /opt/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /opt/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ECP-UC-DB1)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary…
Service “PLSExtProc” has 1 instance(s).
Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
Service “ecp” has 1 instance(s).
Instance “ecp”, status READY, has 1 handler(s) for this service…
Service “ecpXDB” has 1 instance(s).
Instance “ecp”, status READY, has 1 handler(s) for this service…
Service “ecp_XPT” has 1 instance(s).
Instance “ecp”, status READY, has 1 handler(s) for this service…
The command completed successfully
Oracle 调度程序作业–修改执行时间
--DBMS_SCHEDULER 运行信息
select job_name,state,enabled,to_char(last_start_date,'yyyy-mm-dd hh24:mi:ss'), schedule_name
from dba_scheduler_jobs;
--DBMS_SCHEDULER运行成功与否信息
SELECT log_id, job_name, status,TO_CHAR(ACTUAL_START_DATE,'yyyy-mm-dd HH24:MI:ss') start_date,
TO_CHAR (log_date, 'yyyy-mm-dd HH24:MI:ss') log_date
FROM dba_scheduler_job_run_details
WHERE job_name = 'GATHER_STATS_JOB'
order by 4 DESC;
--查询执行时间情况
select t1.window_name,t1.repeat_interval,t1.duration from dba_scheduler_windows t1,dba_scheduler_wingroup_members t2
where t1.window_name=t2.window_name and t2.window_group_name='MAINTENANCE_WINDOW_GROUP';
--修改执行时间
begin
dbms_scheduler.set_attribute('WEEKEND_WINDOW','REPEAT_INTERVAL','freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0');
dbms_scheduler.set_attribute('WEEKEND_WINDOW','DURATION','+002 00:00:00');
end;
ORA-01410: invalid ROWID 分析
1、跟踪错误表
alter session set max_dump_file_size=unlimited;
alter session set db_file_multiblock_read_count=1;
alter session set events ‘10200 trace name context forever, level 1’;
执行下面语句
SELECT COUNT(*) FROM QXTDEV1.TAB_SMS_MT_DOWN
报错
ORA-01410: invalid ROWID
alter session set events ‘10200 trace name context off’;
2、查看跟踪文件尾部
Consistent read started for block 0 : 00400652
env: (scn: 0x0abf.1ee04657 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 statement num=0 parent xid: xid: 0x0000.000.00000000 scn: 0x0000.00000000 0sch: scn: 0x0000.00000000)
Consistent read finished for block 0 : 400652
Consistent read finished for block 0 : 400652
通过这个文件看到文件读到400652的块的时候终止了，错误应该在这块上
3、分析400652块内容
–转换为10进制
SELECT to_number(‘400652′,’xxxxxxxx’) FROM dual;
–求文件号和块号
SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(4195922),
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(4195922)
FROM DUAL;
4、dump文件号为1，块号为1618的内容
alter system dump datafile 1 block 1618;
Block header dump: 0x00400652
Object id on Block? Y
seg/obj: 0xde csc: 0xabf.1e400c0c itc: 2 flg: O typ: 1 – DATA
fsl: 0 fnx: 0x0 ver: 0x01
得到object_id为de，转化为十进制
SELECT to_number(‘de’,’xx’) FROM dual;
查询all_objects表
SELECT * FROM All_Objects WHERE object_id=222;
发现这个数据库（绝对块号400652，相对：文件号1，块号1618）指向的表为dual
5、得出结论
我们的QXTDEV1.TAB_SMS_MT_DOWN表中的数据块指向了sys.dual表，从而出现了ORA-01410错误
undo损坏恢复–有事务
startup nomount;
create pfile=’/tmp/pfile’ from spfile;
修改pfile
*._allow_resetlogs_corruption=true
*._corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,
_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)
startup pfile=’/tmp/pfile’ mount;
–undo数据文件不用离线也可以打开的时候（2），不行采用下面的方法
alter database datafile 2 offline drop;
alter database open;
CREATE UNDO TABLESPACE UNDOTBS01
DATAFILE ‘/opt/oracle/oradata/xifenfei/UNDOTBS01.dbf’ SIZE 50M;
select segment_name,status from dba_rollback_segs;–查看是否有回滚段被创建，处于offline状态
shutdown immediate;
修改pfile：
*.undo_tablespace=’UNDOTBS01′
startup pfile=’/tmp/pfile’
–alter tablespace undotbs1 offline normal;（2）–使数据文件先离线，如果开始已经离线，这里不需要处理
select segment_name,status from dba_rollback_segs;
drop rollback segment “_SYSSMUx$”;
drop tablespace UNDOTBS02 including contents and datafiles;
select * from v$recover_file;–验证是否还有文件需要恢复
shutdown immediate
修改参数文件
#*._allow_resetlogs_corruption=true
#*._corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,
_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)
startup pfile=’/tmp/pfile’;
create spfile from pfile=’/tmp/pfile’;
undo损坏恢复–无事务
startup nomount;
create pfile=’/tmp/pfile’ from spfile;
修改pfile
*.undo_management=’MANUAL’
*.undo_tablespace=’SYSTEM’
#*.undo_tablespace=’UNDOTBS02′
#*.undo_management=’AUTO’
startup pfile=’/tmp/pfile’ mount;
alter database datafile 2 offline drop;
alter database open;
drop tablespace undotbs1 including contents;
select segment_name,status from dba_rollback_segs;
drop rollback segment “_SYSSMUx”$;
drop tablespace undotbs1 including contents;
CREATE UNDO TABLESPACE UNDOTBS02
DATAFILE ‘/opt/oracle/oradata/xifenfei/UNDOTBS02.dbf’ SIZE 50M
shutdown immediate;
修改pfile
#*.undo_management=’MANUAL’
#*.undo_tablespace=’SYSTEM’
*.undo_tablespace=’UNDOTBS02′
*.undo_management=’AUTO’
startup pfile=’/tmp/pfile’;
create spfile from pfile=’/tmp/pfile’;
使用xtrabackup 配置主从服务器
1、备份主机数据
innobackupex –user=root –password=xifenfei –defaults-file=/etc/my.cnf –stream /tmp/mysql 2>/tmp/mysql.log | gzip> /tmp/data/mysql.tar.gz
2、查看/tmp/mysql.log，记录下当前日志位置
innobackupex: MySQL binlog position: filename ‘mysql-bin.000018’, position 107
3、备份文件恢复
scp /tmp/data/mysql.tar.gz root@192.168.1.2:/tmp/
mkdir /tmp/mysql
cd /tmp/mysql
tar izxvf /tmp/mysql.tar.gz
–恢复数据库
innobackupex –apply-log –user=root –password=xifenfei /tmp/mysql
–复制到my.cnf中指定地方
innobackupex –copy-back –user=root –password=xifenfei /tmp/mysql
chmod -R mysql.mysql /var/lib/mysql
4、修改my.cnf文件
主服务器：
server-id=1
innodb_flush_log_at_trx_commit=1
sync-binlog=1
从服务器：
server-id=2
relay-log=/var/lib/mysql/replicate
relay-log-index=/var/lib/mysql/replicate.index
read-only
7、添加复制用户（主数据库上）
GRANT REPLICATION SLAVE ON *.*
TO ‘repl’@’192.168.1.2’ IDENTIFIED BY ‘xifenfei’;
8、配置从数据库
CHANGE MASTER TO
MASTER_HOST=’192.168.1.4′,
MASTER_USER=’repl’,
MASTER_PASSWORD=’xifenfei’,
MASTER_LOG_FILE=’mysql-bin.000018′,
MASTER_LOG_POS=107;
start slave;
9、查看主从是否正常
SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.4
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000022
Read_Master_Log_Pos: 1185
Relay_Log_File: replicate.000007
Relay_Log_Pos: 588
Relay_Master_Log_File: mysql-bin.000022
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1185
Relay_Log_Space: 1627
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: mysql-bin.000022
Position: 1185
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
SHOW PROCESSLIST\G
–从服务器进程
*************************** 2. row ***************************
Id: 22
User: system user
Host:
db: NULL
Command: Connect
Time: 1136
State: Waiting for master to send event
Info: NULL
*************************** 3. row ***************************
Id: 23
User: system user
Host:
db: NULL
Command: Connect
Time: 1676370
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
–主服务器进程
*************************** 1. row ***************************
Id: 14
User: repl
Host: 192.168.1.2:34594
db: NULL
Command: Binlog Dump
Time: 1207
State: Master has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
10、管理从服务器
start slave;
reset slave;
stop slave;
oracle分割函数
1、创建varry
CREATE OR REPLACE TYPE Varchar2Varray IS VARRAY(100) of VARCHAR2(40);
2、创建分割函数
CREATE OR REPLACE FUNCTION f_strsplit (STRING VARCHAR2, substring VARCHAR2)
RETURN varchar2varray
IS
len INTEGER := LENGTH (substring);
lastpos INTEGER := 1 - len;
pos INTEGER;
num INTEGER;
i INTEGER := 1;
ret varchar2varray := varchar2varray (NULL);
v_str VARCHAR2 (20);
/**自定义split函数,将指定的字符串按指定的标志符分割成字符数组*/
BEGIN
LOOP
pos := INSTR (STRING, substring, lastpos + len);
IF pos > 0
THEN --found
num := pos - (lastpos + len);
ELSE --not found
num := LENGTH (STRING) + 1 - (lastpos + len);
END IF;
IF i > ret.LAST
THEN
ret.EXTEND;
END IF;
v_str := SUBSTR (STRING, lastpos + len, num);
--DBMS_OUTPUT.put_line (v_str);
ret(i) := v_str;
EXIT WHEN pos = 0;
lastpos := pos;
i := i + 1;
END LOOP;
RETURN ret;
END;
3、调用
select * from tab_dailyreport_user where user_id in (
SELECT * FROM
( TABLE( select f_strsplit(user_invite,',')
from tab_dailyreport_user t
where t.user_id = 168148)));
说明：主要是改写开发提过来的一个sql语句的帮助
select * from tab_dailyreport_user where user_id in (
--'166445','164216','171128','184427','160113','160133','160134','160138','160144','160163','160587','166457','167914','168076','168192','168997','169842','169901','184407','184747','185089','185130','208134','208141','208143','208183','160142','166455','167838','168074','168194','168666','185088','185138','185145','208103','169844','171071','160107','166421','166874','168193','179871','179872','184096','184228','184232','184269','184774','184969','185111','207871','160167','161813','14880','134','164355','168149')
select substr(regexp_replace,3,length(regexp_replace)-4) from (
select regexp_replace((select user_invite
from tab_dailyreport_user t
where t.user_id = 168148),
'(,)',
chr(39)||','||chr(39)) regexp_replace
from dual) )
括号里面的语句查询出来的结果是注释部分，直接使用注释的部分在in中可以查询，如果使用里面的语句不能查询，他们的需求是想把外面的user_id在里面的语句中的，可是oracle会把里面的in查询出来的结果作为一个整体，从而出现number类型不能和varchar类型匹配的提示，采用方法是分割in里面查询出来的语句，然后类型转换为table进行查询。
该关注的sql语句
一般来说，调优的第一手资料，很可能就是典型业务期的一个statspack报告，那么如何根据statspack报告来判断是哪些SQL消耗了最多的系统资源?哪些SQL是最需要调整的呢?这里给出了一个大致的优化思路。当然，思路是死的，人是活的，优化也需要随需应变。
一般来说，需要关注下面四种Top SQL
消耗最多CPU的(逻辑IO过多)
导致过多物理I/O的
执行次数较频繁的
执行时间较长的
我们知道，一个语句的响应时间有个很著名的公式：
响应时间=服务时间+等待时间
其中服务时间就是CPU为执行该语句花费的时间。
服务时间=分析时间+递归时间+执行时间
分析时间是CPU用于分析语句的时间，递归时间是CPU用于语句的递归SQL的时间，剩下的则就是CPU用于执行语句的真正时间了。
那么，上面的这些时间信息从哪里来的?Oracle提供的系统统计信息中就有部分的时间统计信息：
服务时间=CPU used by this session
分析时间=parse time cpu
递归时间=recursive cpu usage
那么，执行时间就可以根据上面三个统计信息计算得出：
执行时间=CPU used by this session – parse time cpu – recursive cpu usage
如果执行时间在整个响应时间中占较大的比例，那么下一步就是找出那些造成了最多逻辑IO的SQL语句，可以从statspack报告的SQL ordered by Gets部分找到。
如果分析时间在整个响应时间中占较大的比例，那么下一步就是查找哪些SQL分析过多，这在statspack报告中在SQL ordered by Parse Calls中列出。
如果等待时间在整个响应时间中占较大的比例，并且主要是块读取相关的等待时，下一步就是找出哪些SQL造成了过多的物理读，可以查看statspack报告中的SQL ordered by Reads部分。
那么，根据上面列出的一个简单的原则，我们需要关注三个关于CPU时间的统计信息: CPU used by this session, parse time cpu和recursive cpu usage，以及top5等待事件中和IO相关的等待时间。如果是其他的一些等待事件出现在Top5中，那么可能需要根据不同的等待事件来分析原因了。然后优先调优时间消耗最多的相关SQL。
除了上面的SQL ordered by Gets(逻辑IO最多)，SQL ordered by Parse Calls(软解析过多)，SQL ordered by Reads(物理IO过多)，statspack还按照其他的一些方式列出了Top SQL，这些Top SQL在某些情况下都是需要给予特别关注的。比如：
SQL ordered by Executions 执行次数超过100的
SQL ordered by Sharable Memory 占用library cache超过1M的
SQL ordered by Version Count 子cursor超过20的
如果没有statspack，那么根据v$sysstat/v$sesstat中的统计信息，结合v$sql/v$sqlarea，一样可以得到相关的SQL。
v$sql对于每一个子cursor都有一行统计记录，而v$sqlarea则对同一个父cursor只有一行统计记录，也就是v$sqlarea是对v$sql按照父cursor进行group by后的一个结果。这两个视图中都有诸如buffer_gets,parse_calls,disk_reads,,executions,sharable_mem等列，和上面提到的statspack中列出Top SQL的条件对应。
insert引起的死锁
DEADLOCK DETECTED ( ORA-00060 )
[Transaction Deadlock]
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
———Blocker(s)——– ———Waiter(s)———
Resource Name process session holds waits process session holds waits
TX-000a0002-00005420 68 440 X 65 496 S
TX-0001001b-00002b19 65 496 X 68 440 S
session 440: DID 0001-0044-000A2346 session 496: DID 0001-0041-000CBA54
session 496: DID 0001-0041-000CBA54 session 440: DID 0001-0044-000A2346
Rows waited on:
Session 496: obj – rowid = 00014282 – AAAUKCAAMAAAEZrAAA
(dictionary objn – 82562, file – 12, block – 18027, slot – 0)
Session 440: no row
Information on the OTHER waiting sessions:
Session 496:
pid=65 serial=58006 audsid=1896040 user: 88/VAS
O/S info: user: web_meg, term: unknown, ospid: , machine: ezg-web1
program: JDBC Thin Client
application name: JDBC Thin Client, hash value=2546894660
Current SQL Statement:
insert into TAB_XN_CONTENT_TEMP (key,content) values (:1,:2)
End of information on OTHER waiting sessions.
Current SQL statement for this session:
insert into TAB_XN_CONTENT_TEMP (key,content) values (:1,:2)
我一看到这个错误，有点不明白，oracle 的insert操作竟然导致表被锁，然后查找些资料终于有了眉目：
当ORACLE执行insert等DML语句时，会首先自动在所要操作的表上申请一个TM锁，当TM锁获得后，再自动申请TX类型的锁。当两个或多个会话在表的同一条记录上执行DML语言时，第一个会话在记录上加锁，其它的会话处于等待状态，一直到第一个会话提交后TX锁释放，其它的会话才可以加锁。考虑是因为两个insert语句同时试图向一个表中插入PK或unique值相同的数据，而造成其中会话被阻塞，等待其它会话提交或回滚，因而造成死锁。这种情况，只要其中任何一个session提交，另外一个就会报出ORA-00001：违反唯一性约束条件，死锁终止；或者其中一个session回滚，另外一个即可正常执行。
通过对这段话的理解，应该是一个会话插入了一条记录未提交，然后另外一个会话继续插入主键或者唯一索引列相同的记录，导致死锁的发生。
环境模拟：
在会话1中执行下面语句
CREATE TABLE t1(ID NUMBER);
ALTER TABLE t1 ADD primary key (ID);
INSERT INTO t1 VALUES(1);
INSERT INTO t1 VALUES(2);
然后在会话2中执行下面语句
INSERT INTO t1 VALUES(1);
INSERT INTO t1 VALUES(2);
在会话3中查询死锁情况
通过上面模拟，重现了insert死锁现象，说明红色标注出来的文字的正确性