24 – 八月 – 2013 – 专业Oracle数据库恢复，或许是您恢复数据的最后机会@phone:17813235971

在ORACLE 12C之间的版本，如果一条sql中包含了N多视图,我们要查询这条sql访问了哪些基表，以及他们的关联条件是一件比较麻烦的时候，需要进入一个个视图然后一个个去分析，纯体力活，到了12C之后，ORACLE 提供了dbms_utility.expand_sql_text来获得某条sql所对应的全部基表，不用我们再一级一级的去看.

创建测试表
三个表分别来自数据库的v$datafile,v$tablespace,v$tempfile视图

CDB_PDB@CHF> create table datafile
  2  as
  3  select * from v$datafile;

表已创建。

CDB_PDB@CHF> create table tablespace
  2  as
  3  select * from v$tablespace;

表已创建。

CDB_PDB@CHF> create table tempfile
  2  as
  3  select * from v$tempfile;

表已创建。

创建视图
创建三个视图,datafile与tablespace，tempfile与tablespace，这两个视图然后做union all产生新视图

CDB_PDB@CHF> create view v_xifenfei1 as
  2  select d.name dname,t.name tname from datafile d,tablespace t where d.ts#=t.ts#;

视图已创建。

CDB_PDB@CHF> create view v_xifenfei2 as
  2  select d.name dname,t.name tname from tempfile d,tablespace t where d.ts#=t.ts#;

视图已创建。

CDB_PDB@CHF> create view v_xifenfei
  2  as
  3  select * from v_xifenfei1
  4  union all
  5  select * from v_xifenfei2;

视图已创建。

找出查询视图sql对应基表

CDB_PDB@CHF>  set linesize 32767 pagesize 0 serveroutput on
CDB_PDB@CHF>  declare
  2     original_sql clob :='select * from v_xifenfei';
  3     expanded_sql clob := empty_clob();
  4  begin
  5      dbms_utility.expand_sql_text(original_sql,expanded_sql);
  6      dbms_output.put_line(expanded_sql);
  7  end;
  8  /
SELECT "A1"."DNAME" "DNAME","A1"."TNAME" "TNAME" FROM  ( (SELECT "A4"."DNAME" "DNAME","A4"."TNAME" "TNAME" FROM  (SELECT
 "A6"."NAME" "DNAME","A5"."NAME" "TNAME" FROM CHF."DATAFILE" "A6",CHF."TABLESPACE" "A5" WHERE "A6"."TS#"="A5"."TS#") "A4
") UNION ALL  (SELECT "A3"."DNAME" "DNAME","A3"."TNAME" "TNAME" FROM  (SELECT "A8"."NAME" "DNAME","A7"."NAME" "TNAME" FR
OM CHF."TEMPFILE" "A8",CHF."TABLESPACE" "A7" WHERE "A8"."TS#"="A7"."TS#") "A3")) "A1"

PL/SQL 过程已成功完成。

格式化sql语句

/* Formatted on 2013/8/24 22:33:33 (QP5 v5.227.12220.39754) */
SELECT "A1"."DNAME" "DNAME", "A1"."TNAME" "TNAME"
  FROM ( (SELECT "A4"."DNAME" "DNAME", "A4"."TNAME" "TNAME"
            FROM (SELECT "A6"."NAME" "DNAME", "A5"."NAME" "TNAME"
                    FROM CHF."DATAFILE" "A6", CHF."TABLESPACE" "A5"
                   WHERE "A6"."TS#" = "A5"."TS#") "A4")
        UNION ALL
        (SELECT "A3"."DNAME" "DNAME", "A3"."TNAME" "TNAME"
           FROM (SELECT "A8"."NAME" "DNAME", "A7"."NAME" "TNAME"
                   FROM CHF."TEMPFILE" "A8", CHF."TABLESPACE" "A7"
                  WHERE "A8"."TS#" = "A7"."TS#") "A3")) "A1"

这里就非常清晰的看到是datafile与tablespace、tempfile与tablespace做union all的sql语句

环境描述
win 64中的ORACLE 12C(primary)与Linux 64中的ORACLE 12C(standby)搭建datagurad

primary force logging

C:\Users\XIFENFEI>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on 星期六 8月 24 16:59:53 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


连接到:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

CDB_CDB$ROOT@SYS> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE    12.1.0.1.0      Production
TNS for 64-bit Windows: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production

CDB_CDB$ROOT@SYS> alter database force logging ;

数据库已更改。

primary rman backup

backup filesperset = 5 as compressed backupset database format 'd:/backup/rman/full_%U.rman';

primary 生成standby controlfile

CDB_CDB$ROOT@SYS> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'c:/control01.ctl';

数据库已更改。

CDB_CDB$ROOT@SYS> create pfile='e:/pfile.txt' from spfile;

文件已创建。

standby 参数文件

DB_CREATE_FILE_DEST='+DATA'
db_create_online_log_dest_1='+DATA'
db_unique_name='cdb_dg'
service_names='cdb'
log_archive_dest_1='LOCATION=/u02/app/oracle/archivelog/ valid_for=(all_logfiles,all_roles)'
log_archive_dest_2='service=primary lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=cdb'
log_archive_config='dg_config=(cdb,cdb_dg)'
standby_file_management=auto
db_file_name_convert='E:\APP\XIFENFEI\ORADATA\','+DATA\CDB_DG\DATAFILE\'
log_file_name_convert='E:\APP\XIFENFEI\ORADATA\','+DATA\CDB_DG\LOGFILE\'
fal_server=primary

primary 修改参数

CDB_CDB$ROOT@SYS> alter system set log_archive_config='dg_config=(cdb,cdb_dg)';

系统已更改。

CDB_CDB$ROOT@SYS> alter system set log_archive_dest_2='
 2 service=standby lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=cdb_dg';

系统已更改。

CDB_CDB$ROOT@SYS> alter system set log_archive_dest_1=
  2 'LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles)';

系统已更改。

CDB_CDB$ROOT@SYS> alter system set fal_server=standby;

系统已更改。

CDB_CDB$ROOT@SYS> alter system set standby_file_management=auto;

系统已更改。

CDB_CDB$ROOT@SYS> alter system set db_file_name_convert='+DATA\CDB_DG\DATAFILE\','E:\APP\XIFENFEI\ORADATA\' scope=spfile;

系统已更改。

CDB_CDB$ROOT@SYS> alter system set log_file_name_convert='+DATA\CDB_DG\LOGFILE\','E:\APP\XIFENFEI\ORADATA\' scope=spfile;

系统已更改。

primary and standby tns

STANDBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.30.32 )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdb)
    )
  )

PRIMARY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.30.1 )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdb)
    )
  )

standby restore controlfile

ASMCMD> cp /tmp/CONTROL01.CTL control01.ctl
copying /tmp/CONTROL01.CTL -> +data/cdb/control01.ctl

standby password file

[oracle@xifenfei dbs]$ pwd
/u01/app/oracle/product/12.1/db_1/dbs
[oracle@xifenfei dbs]$ cp /tmp/rman/PWDcdb.ora  orapwcdb

standby mount

SYS% cdb> create spfile from pfile='initcdb.ora';

File created.

SYS% cdb> startup mount;
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size                  2290264 bytes
Variable Size             314576296 bytes
Database Buffers          197132288 bytes
Redo Buffers                7938048 bytes
Database mounted.

SYS% cdb> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE    12.1.0.1.0      Production
TNS for Linux: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production

SYS% cdb> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
E:\APP\XIFENFEI\ORADATA\CDB\SYSTEM01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\PDBSEED\SYSTEM01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\SYSAUX01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\PDBSEED\SYSAUX01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\UNDOTBS01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\USERS01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\PDB\SYSTEM01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\PDB\SYSAUX01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\PDB\PDB_USERS01.DBF

9 rows selected.

standby rman restore

--清理控制文件中的备份集垃圾
DELETE noprompt OBSOLETE;
crosscheck backup;
delete noprompt expired backup;

--注册新备份集
catalog start with '/tmp/rman/';

--还原数据文件
run
{
set newname for database to '+data';
restore database;
switch datafile all;
}

standby clear redo

SYS% cdb> select group# from v$log;

    GROUP#
----------
         4
         6
         5

SYS% cdb> alter database clear logfile group 4;

Database altered.

SYS% cdb> alter database clear logfile group 5;

Database altered.

SYS% cdb> alter database clear logfile group 6;

Database altered.

standby add standby redolog

SYS% cdb> ALTER DATABASE ADD STANDBY LOGFILE GROUP 10  size 50M;

Database altered.

SYS% cdb> ALTER DATABASE ADD STANDBY LOGFILE GROUP 11  size 50M;

Database altered.

SYS% cdb> ALTER DATABASE ADD STANDBY LOGFILE GROUP 12  size 50M;

Database altered.

SYS% cdb> ALTER DATABASE ADD STANDBY LOGFILE GROUP 13  size 50M;

Database altered.

primary add standby redolog

CDB_CDB$ROOT@SYS> ALTER DATABASE ADD STANDBY LOGFILE GROUP 10  'E:\APP\XIFENFEI\ORADATA\CDB\std_redo10.log' size 50M;

数据库已更改。

CDB_CDB$ROOT@SYS> ALTER DATABASE ADD STANDBY LOGFILE GROUP 11  'E:\APP\XIFENFEI\ORADATA\CDB\std_redo11.log' size 50M;

数据库已更改。

CDB_CDB$ROOT@SYS> ALTER DATABASE ADD STANDBY LOGFILE GROUP 12  'E:\APP\XIFENFEI\ORADATA\CDB\std_redo12.log' size 50M;

数据库已更改。

CDB_CDB$ROOT@SYS> ALTER DATABASE ADD STANDBY LOGFILE GROUP 13  'E:\APP\XIFENFEI\ORADATA\CDB\std_redo13.log' size 50M;

数据库已更改。

standby readonly

SYS% cdb> ALTER DATABASE OPEN READ ONLY;

Database altered.

standby start mrp

SYS% cdb> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

DATAGURAD 是否正常检查

--primary
CDB_CDB$ROOT@SYS> archive log list;
数据库日志模式            存档模式
自动存档             启用
存档终点            USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列     374
下一个存档日志序列   376
当前日志序列           376

--standby 
Mon Aug 12 13:56:51 2013
All non-current ORLs have been archived.
Mon Aug 12 13:56:53 2013
Media Recovery Log /u02/app/oracle/archivelog/1_370_820595806.dbf
Mon Aug 12 13:56:57 2013
Media Recovery Log /u02/app/oracle/archivelog/1_371_820595806.dbf
Mon Aug 12 13:57:02 2013
Media Recovery Log /u02/app/oracle/archivelog/1_372_820595806.dbf
Mon Aug 12 13:57:04 2013
Media Recovery Log /u02/app/oracle/archivelog/1_373_820595806.dbf
Mon Aug 12 13:57:05 2013
Media Recovery Log /u02/app/oracle/archivelog/1_374_820595806.dbf
Media Recovery Waiting for thread 1 sequence 375
Mon Aug 12 13:57:19 2013
Primary database is in MAXIMUM PERFORMANCE mode
RFS[2]: Assigned to RFS process (PID:26114)
RFS[2]: No standby redo logfiles created for thread 1 
RFS[2]: Opened log for thread 1 sequence 376 dbid 1937199326 branch 820595806
Mon Aug 12 13:57:19 2013
RFS[3]: Assigned to RFS process (PID:26118)
RFS[3]: Opened log for thread 1 sequence 375 dbid 1937199326 branch 820595806
Mon Aug 12 13:57:19 2013
Archived Log entry 16 added for thread 1 sequence 375 rlc 820595806 ID 0x7377d8de dest 2:
Mon Aug 12 13:57:22 2013
Media Recovery Log /u02/app/oracle/archivelog/1_375_820595806.dbf
Media Recovery Waiting for thread 1 sequence 376 (in transit)

专业Oracle数据库恢复技术支持

专业Oracle数据库恢复，或许是您恢复数据的最后机会@phone:17813235971

Daily Archives: 2013 年 08 月 24 日

ORACLE 12C dbms_utility.expand_sql_text 查看SQL视图基表

ORACLE 12C Windows-Linux 部署DATAGURAD

S	M	T	W	T	F	S
« Jul				Sep »
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30	31