Dream of Mirror Ruins

数据库平台迁移，常用工具优缺点对比： 方法 优点 缺点 RMAN 操作简单 不适用于不同字节序之间的平台迁移 数据泵 操作简单 数据量大时需要的停机时间很长 OGG 对源端目标端平台无要求 操作较复杂，维护成本高 XTTS 源端与目标端字节序可以不同，停机时间短 被移动的表空间不能包含 offline 的数据文件

Oracle Database Performance Optimization what oracle waiting for ??? select * from (select count(*), event, sql_id, program, inst_id from gv$active_session_history where sample_time > sysdate-5/1440 group by event, sql_id, program, inst_id order by 1 desc)where rownum <= 20; query execution plan select plan_table_output from table(dbms_xplan.display_cursor('&sql_id')); --已知SQL_Id的情况下explain plan for (SQL_TEXT); select * from table(dbms_xplan.display);set autotrace on explai...

Oracle Database High CPU Usage 数据库运行慢，登录操作系统发现 CPU 使用率比平常要高 查看当前会话运行的 SQL Text 和 PLAN SQL> select sql_id, count(1) from gv$session where type='USER' and status = 'ACTIVE' group by sql_id;SQL_ID COUNT(1)------------- ----------arz2wrgd05c5z 1SQL> select addr,spid from v$process where spid='3268';SQL> select sid,sql_id from v$session where paddr='0000000090CCF600';SQL> select sql_text from v$sql where sql_id='arz2wrgd05c5z';SQL>select sql_id from v$session wher...

RMAN Backup on Linux Create a backup directory. mkdir -p /rmanbackup/rmanlogmkdir -p /rmanbackup/dbbakmkdir -p /rmanbackup/scriptsmkdir -p /rmanbackup/weekmkdir -p /rmanbackup/daychown -R oracle:oinstall /rmanbackupchmod -R 755 /rmanbackup Create a full backup script: /rmanbackup/scripts/rmanbackup.sh #/bin/env bashexport ORACLE_BASE=/u01/app/oracleexport ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1export ORACLE_OWNER=oracleexport ORACLE_SID=orcl1export ORACLE_UNQNAME=orclexport PATH=$OR...

RMAN Database Duplication Creating a Password File for the Auxiliary Instance, When you create a standby database by using RMAN duplication, password files are always copied. Add Net Services for primary and standby databases in the tnsnames.ora file. Adding Static Service to Listener in the listener.ora file. Create an Initialization Parameter File for the Auxiliary Instance. Start the auxiliary instance in NOMOUNT mode. Duplication database. rman target sys/oracle@Matrix auxiliary sys/or...

RMAN Backup on Windows Full backup REM 需要修改的变量值开始set ORACLE_SID=xpxyyset BACKUP_PATH=E:\RMANset SYS_PASSWD=oracleREM 修改变量结束REM 在服务器上保留3个备份的脚本if exist %BACKUP_PATH%\*.BAK003 del /F /Q %BACKUP_PATH%\*.BAK003if exist %BACKUP_PATH%\*.BAK002 rename %BACKUP_PATH%\*.BAK002 *.BAK003if exist %BACKUP_PATH%\*.BAK001 rename %BACKUP_PATH%\*.BAK001 *.BAK002REM 在服务器上保留2个备份的脚本REM if exist %BACKUP_PATH%\*.BAK002 del /F /Q %BACKUP_PATH%\*.BAK002REM if exist %BACKUP_PATH%\*.BAK001 rename %BACKUP_PATH%\*...

RMAN Restore on Linux Restore Spfile ~]$ rman target / nocatalogRMAN> startup nomountRMAN> restore spfile from '/backup/spfile_01uuf10m_1_1.bak';RMAN> shutdown abort Restore Controlfile (Option) Modify the controlfile path: SQL> startup nomountSQL> alter system set control_files = '/home/oracle/orcl/control01.ctl','/home/oracle/orcl/control02.ctl' scope=spfile;SQL> shutdown abort Restore controlfile. ~]$ rman target / nocatalogRMAN> startup nomountRMAN> set dbid <...

CRS-1714 Unable to discover any voting files APPLIES TO Operating System - Oracle Linux Server 7.9 Oracle Database - Enterprise Edition - Version 11.2.0.4.0 Oracle Grid Infrastructure - Version 11.2.0.4.0 SYMPTOMS 操作系统重启后，crs 无法正常启动 [grid@rac1 ~]$ crsctl stat res -tCRS-4535: Cannot communicate with Cluster Ready ServicesCRS-4000: Command Status failed, or completed with errors. 查看 /u01/app/11.2.0/grid/log/rac1/alertrac1.log 日志，出现无法识别到 voting files 报错 [cssd(3348)]CRS-1714:Unable to discov...

CRS-4124 Oracle High Availability Services startup failed APPLIES TO Operating System - Oracle Linux Server 7.9 Oracle Database - Enterprise Edition - Version 11.2.0.3.0 - 11.2.0.4.0 Oracle Grid Infrastructure - Version 11.2.0.2 - 11.2.0.4.0 SYMPTOMS When installing the GI software and executing root.sh, an error occurs: CRS-4124: Oracle High Availability Services startup failed.CRS-4000: Command Start failed, or completed with errors.ohasd failed to start: Inappropriate ioctl for device...

Using RMAN to Delete Oracle Database Archive Logs Login as oracle, create the script del_arch.sh as below: ~]$ cat > /home/oracle/script/del_arch.sh << "END"#!/bin/env bashif [[ -z $1 ]]; then exit 1fi. ~/.bash_profileORACLE_SID=$1$ORACLE_HOME/bin/rman log=/tmp/del_arch_$1_`date +%Y%m%d`.log <<EOFconnect target /run { crosscheck archivelog all; delete noprompt expired archivelog all; delete noprompt archivelog all completed before 'sysdate - 1';}quitEOFfind /tmp/ -...