标签云
asm 恢复 asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 ORACLE恢复 Oracle 恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (100)
- 数据库 (1,598)
- DB2 (22)
- MySQL (70)
- Oracle (1,463)
- Data Guard (49)
- EXADATA (7)
- GoldenGate (21)
- ORA-xxxxx (158)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (13)
- ORACLE 21C (3)
- Oracle ASM (65)
- Oracle Bug (7)
- Oracle RAC (47)
- Oracle 安全 (6)
- Oracle 开发 (27)
- Oracle 监听 (27)
- Oracle备份恢复 (530)
- Oracle安装升级 (84)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (75)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (36)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (19)
-
最近发表
- PostgreSQL解析wal日志之—walminer
- Oracle 19c/21c最新patch信息-202404
- PostgreSQL恢复系列:pg_filedump批量处理
- PostgreSQL部分主要字典信息
- PostgreSQL恢复系列:pg_filedump恢复字典构造
- PostgreSQL 16 源码安装
- ORA-00742 ORA-00312 恢复
- 数据库open成功后报ORA-00353 ORA-00354错误引起的一系列问题(本质ntfs文件系统异常)
- ORA-600 ktsiseginfo1故障
- ORA-00600: internal error code, arguments: [16703], [1403], [4] 原因
- 最近遇到几起ORA-600 16703故障(tab$被清空),请引起重视
- ORA-600 2662快速恢复之Patch scn工具
- TNS-12518: TNS:listener could not hand off client connection
- ora.storage无法启动报ORA-12514故障处理
- 断电引起文件scn异常数据库恢复
- ORA-16188: LOG_ARCHIVE_CONFIG settings inconsistent with previously started instance
- .[hudsonL@cock.li].mkp勒索加密数据库完美恢复
- 模拟带库实现rman远程备份
- 又一例:ORA-600 kclchkblk_4和2662故障
- Oracle误删除数据文件恢复
月归档:二月 2012
In-Memory Database Cache 入门配置
一、Oracle数据库创建相关用户和权限
1.创建timesten用户
store information about cache grids
SQL> CREATE TABLESPACE cachetblsp DATAFILE 2 'E:\ORACLE\ORADATA\XFF\datfttuser.dbf' 3 SIZE 10M autoextend on next 10m maxsize 30g; 表空间已创建。 SQL> @E:\oracle\timesten\oraclescripts\initCacheGlobalSchema "cachetblsp" Please enter the tablespace where TIMESTEN user is to be created The value chosen for tablespace is cachetblsp ******* Creation of TIMESTEN schema and TT_CACHE_ADMIN_ROLE starts ******* 1. Creating TIMESTEN schema 2. Creating TIMESTEN.TT_GRIDID table 3. Creating TIMESTEN.TT_GRIDINFO table 4. Creating TT_CACHE_ADMIN_ROLE role 5. Granting privileges to TT_CACHE_ADMIN_ROLE ** Creation of TIMESTEN schema and TT_CACHE_ADMIN_ROLE done successfully ** PL/SQL 过程已成功完成。
2.创建测试用户
the Oracle tables to be cached in a TimesTen database
SQL> create user xff identified by xifenfei; 用户已创建。 SQL> grant create session,resource to xff; 授权成功。
3.创建cache管理用户
creates and maintains Oracle objects that store information used to manage cache grids and enforce predefined behaviors of particular cache group types.
SQL> CREATE USER cacheuser IDENTIFIED BY oracle 2 DEFAULT TABLESPACE cachetblsp QUOTA UNLIMITED ON cachetblsp; 用户已创建。 SQL> @grantCacheAdminPrivileges "cacheuser" Please enter the administrator user id The value chosen for administrator user id is cacheuser ***************** Initialization for cache admin begins ****************** 0. Granting the CREATE SESSION privilege to CACHEUSER 1. Granting the TT_CACHE_ADMIN_ROLE to CACHEUSER 2. Granting the DBMS_LOCK package privilege to CACHEUSER 3. Granting the RESOURCE privilege to CACHEUSER 4. Granting the CREATE PROCEDURE privilege to CACHEUSER 5. Granting the CREATE ANY TRIGGER privilege to CACHEUSER 6. Granting the DBMS_LOB package privilege to CACHEUSER 7. Granting the SELECT on SYS.ALL_OBJECTS privilege to CACHEUSER 8. Granting the SELECT on SYS.ALL_SYNONYMS privilege to CACHEUSER 9. Checking if the cache administrator user has permissions on the default tablespace Permission exists 11. Granting the CREATE ANY TYPE privilege to CACHEUSER ********* Initialization for cache admin user done successfully *********
二、TimesTen创建相关用户
1.cache管理用户
A cache manager user performs cache grid and cache group operations. The TimesTen cache manager user must have the same name as an Oracle user that can access the cached Oracle tables.
Command> CREATE USER cacheuser IDENTIFIED BY timesten; User created. Command> GRANT CREATE SESSION, CACHE_MANAGER, CREATE ANY TABLE TO cacheuser;
2.cache对应用户
You must create a TimesTen cache table user with the same name as an Oracle schema user for each schema user who owns or will own Oracle tables to be cached in the TimesTen database
Command> create user xff identified by timesten; User created.
三、配置DSN
Data Store Path + Name: E:\oracle\timesten\mytt_db\data Permanent Data Size: 64 Oracle Net Service Name: XFF Database Character Set: ZHS16GBK
四、在TT中设置cache管理用户名和密码
ttIsql "DSN=my_ttdb;UID=cacheuser;PWD=timesten;OraclePWD=oracle" Command> call ttCacheUidPwdSet('cacheuser','oracle'); Warning 5183: Function kollglid2 was not in the library. The function will not be called. Warning 5183: Function kollgsnp2 was not in the library. The function will not be called. Warning 5186: The OCI client library in use does not contain required routines to support caching LOBS from Oracle. --第一次调用相关函数因为没有编译出错,再次调用即可 Command> call ttCacheUidPwdSet('cacheuser','oracle');
五、创建测试表(Oracle db中)
SQL> CREATE TABLE readtab (keyval NUMBER NOT NULL PRIMARY KEY, str VARCHAR2(32)); 表已创建。 SQL> CREATE TABLE writetab (pk NUMBER NOT NULL PRIMARY KEY, attr VARCHAR2(40)); 表已创建。 SQL> INSERT INTO readtab VALUES (1, 'Hello'); 已创建 1 行。 SQL> INSERT INTO readtab VALUES (2, 'World'); 已创建 1 行。 SQL> INSERT INTO writetab VALUES (100, 'XIFENFEI'); 已创建 1 行。 SQL> INSERT INTO writetab VALUES (101, 'WWW.XIFENFEI.COM'); 已创建 1 行。 SQL> COMMIT; 提交完成。 SQL> GRANT SELECT ON readtab TO cacheuser; 授权成功。 SQL> GRANT SELECT ON writetab TO cacheuser; 授权成功。 SQL> GRANT INSERT ON writetab TO cacheuser; 授权成功。 SQL> GRANT UPDATE ON writetab TO cacheuser; 授权成功。 SQL> GRANT DELETE ON writetab TO cacheuser; 授权成功。
六、TimesTen相关配置
1.创建cache grid
Command> call ttGridCreate('myGrid'); Command> call ttGridNameSet('myGrid');
2.Start the cache agent
Command> call ttCacheStart;
3.创建cache group
Command> call ttCacheStart; Command> CREATE READONLY CACHE GROUP readcache > AUTOREFRESH INTERVAL 5 SECONDS > FROM XFF.readtab > (keyval NUMBER NOT NULL PRIMARY KEY, str VARCHAR2(32)); Command> CREATE DYNAMIC ASYNCHRONOUS WRITETHROUGH CACHE GROUP writecache > FROM XFF.writetab > (pk NUMBER NOT NULL PRIMARY KEY, attr VARCHAR2(40)); Command> cachegroups; Cache Group CACHEUSER.READCACHE: Cache Group Type: Read Only Autorefresh: Yes Autorefresh Mode: Incremental Autorefresh State: Paused Autorefresh Interval: 5 Seconds Autorefresh Status: ok Aging: No aging defined Root Table: XFF.READTAB Table Type: Read Only Cache Group CACHEUSER.WRITECACHE: Cache Group Type: Asynchronous Writethrough (Dynamic) Autorefresh: No Aging: LRU on Root Table: XFF.WRITETAB Table Type: Propagate 2 cache groups found.
4.Start the replication agent for the AWT cache group
Command> call ttRepStart;
5.Manually load the cache group
Command> LOAD CACHE GROUP readcache COMMIT EVERY 256 ROWS; 2 cache instances affected. Command> LOAD CACHE GROUP writecache COMMIT EVERY 256 ROWS; 2 cache instances affected.
七、TT授权
主要是为了直接在cacheuser中操作方便,无其他应意义
ttisql my_ttdb Command> GRANT SELECT ON xff.readtab TO cacheuser; Command> GRANT SELECT ON xff.writetab TO cacheuser; Command> GRANT UPDATE ON xff.writetab TO cacheuser; Command> GRANT DELETE ON xff.writetab TO cacheuser; Command> GRANT INSERT ON xff.writetab TO cacheuser;
八、相关测试
1.测试READTAB表
Command> SELECT * FROM XFF.READTAB; < 1, Hello > < 2, World > 2 rows found. SQL> INSERT INTO readtab VALUES (3, 'Welcome'); 已创建 1 行。 SQL> commit; 提交完成。 Command> SELECT * FROM XFF.READTAB; < 1, Hello > < 2, World > < 3, Welcome > 3 rows found. SQL> update readtab set str='www.xifenfei'; 已更新3行。 SQL> commit; 提交完成。 Command> SELECT * FROM XFF.READTAB; < 1, www.xifenfei > < 2, www.xifenfei > < 3, www.xifenfei > 3 rows found. SQL> delete from readtab where keyval=3; 已删除 1 行。 SQL> commit; 提交完成。 Command> SELECT * FROM XFF.READTAB; < 1, www.xifenfei > < 2, www.xifenfei > 2 rows found.
2.测试WRITETAB表
Command> SELECT * FROM XFF.writeTAB; < 100, XIFENFEI > < 101, WWW.XIFENFEI.COM > 2 rows found. Command> update xff.writetab set attr='www.xifenfei.com' where pk=100; 1 row updated. Command> commit; SQL> select * from writetab; PK ATTR ---------- ---------------------------------------- 100 www.xifenfei.com 101 WWW.XIFENFEI.COM Command> insert into xff.writetab values(102,'xifenfei'); 1 row inserted. Command> commit; SQL> select * from writetab; PK ATTR ---------- -------------------------------- 102 xifenfei 100 www.xifenfei.com 101 WWW.XIFENFEI.COM
通过sql查询rman备份信息
查看所有备份集
SELECT A.RECID "BACKUP SET", A.SET_STAMP, DECODE (B.INCREMENTAL_LEVEL, '', DECODE (BACKUP_TYPE, 'L', 'Archivelog', 'Full'), 1, 'Incr-1级', 0, 'Incr-0级', B.INCREMENTAL_LEVEL) "Type LV", B.CONTROLFILE_INCLUDED "包含CTL", DECODE (A.STATUS, 'A', 'AVAILABLE', 'D', 'DELETED', 'X', 'EXPIRED', 'ERROR') "STATUS", A.DEVICE_TYPE "Device Type", A.START_TIME "Start Time", A.COMPLETION_TIME "Completion Time", A.ELAPSED_SECONDS "Elapsed Seconds", --a.BYTES/1024/1024/1024 "大小(G)", --a.COMPRESSED, A.TAG "Tag", A.HANDLE "Path" FROM GV$BACKUP_PIECE A, GV$BACKUP_SET B WHERE A.SET_STAMP = B.SET_STAMP AND A.DELETED = 'NO' and a.set_count = b.set_count ORDER BY A.COMPLETION_TIME DESC;
查找某个备份集中包含数据文件
SELECT distinct c.file#,A.SET_STAMP, D.NAME, C.CHECKPOINT_CHANGE#, C.CHECKPOINT_TIME FROM V$BACKUP_DATAFILE C, V$BACKUP_PIECE A, V$DATAFILE D WHERE A.SET_STAMP = C.SET_STAMP AND D.FILE# = C.FILE# AND A.DELETED='NO' AND c.set_stamp=&set_stamp ORDER BY C.FILE#;
查询某个备份集中控制文件
SELECT DISTINCT A.SET_STAMP, D.NAME, C.CHECKPOINT_CHANGE#, C.CHECKPOINT_TIME FROM V$BACKUP_DATAFILE C, V$BACKUP_PIECE A, V$CONTROLFILE D WHERE A.SET_STAMP = C.SET_STAMP AND C.FILE# = 0 AND A.DELETED = 'NO' AND C.SET_STAMP = &SET_STAMP;
查看某个备份集中归档日志
SELECT DISTINCT B.SET_STAMP, B.THREAD#, B.SEQUENCE#, B.FIRST_TIME, B.FIRST_CHANGE#, B.NEXT_TIME, B.NEXT_CHANGE# FROM V$BACKUP_REDOLOG B, V$BACKUP_PIECE A WHERE A.SET_STAMP = B.SET_STAMP AND A.DELETED = 'NO' AND B.SET_STAMP = &SET_STAMP ORDER BY THREAD#, SEQUENCE#;
查看某个备份集SPFILE
SELECT DISTINCT A.SET_STAMP, B.COMPLETION_TIME, HANDLE FROM V$BACKUP_SPFILE B, V$BACKUP_PIECE A WHERE A.SET_STAMP = B.SET_STAMP AND A.DELETED = 'NO' AND B.SET_STAMP = &SET_STAMP;
rman配置信息
SELECT NAME,VALUE FROM V$RMAN_CONFIGURATION;
发表在 rman备份/恢复
评论关闭
TimesTen命令—ttrestore
ttrestore使用说明
E:\>ttrestore -h Usage: ttrestore [-h | -help | -?] ttrestore [-V | -version] ttrestore [-fname <filePrefix>] [-noconn] -dir <directory> {<DSN> | [-connstr] <connStr>} ttrestore -i [-noconn] {<DSN> | [-connstr] <connStr>} options: -h | -help | -? Prints this message and exits. -V | -version Prints the release number and exits. -fname <filePrefix> The file prefix for the backup files in the backup directory. Default is the base filename portion of the DataStore parameter of the data store to be restored. -dir <directory> The directory in which the backup files are stored. -noconn Do not test-connect after restoring the data store. -i Read from standard input for stream data. <DSN>, <connStr> The DSN or ODBC connection string of the data store to be restored.
查看库中当前情况
Command> tables; XIFENFEI.REP_TABLE XIFENFEI.T1 XIFENFEI.T2 XIFENFEI.T3 XIFENFEI.T4 XIFENFEI.V4 XIFENFEI.XFF 7 tables found. Command> select * from t2; < 3 > < 5 > < 3 > < 5 > < 3 > < 5 > < 3 > < 5 > < 3 > < 5 > < 3 > < 5 > 12 rows found.
删除数据文件和日志文件
E:\oracle\timesten\mytt_db>dir 驱动器 E 中的卷没有标签。 卷的序列号是 38D0-2A35 E:\oracle\timesten\mytt_db 的目录 2012/02/23 22:58 <DIR> . 2012/02/23 22:58 <DIR> .. 2012/02/11 19:06 <DIR> data 2012/02/23 22:57 <DIR> log 0 个文件 0 字节 4 个目录 9,478,365,184 可用字节 E:\>ttisql my_ttdb Copyright (c) 1996-2011, Oracle. All rights reserved. Type ? or "help" for help, type "exit" to quit ttIsql. connect "DSN=my_ttdb"; 821: No readable checkpoint files. OS error: '系统找不到指定的文件。'. Consi der connecting with Overwrite=1 to create new data store The command failed. Done.
还原数据库
E:\>ttrestore -dir E:\oracle\timesten\tt_back\full -fname xifenfei01_ my_ttdb Restore started ... Restore complete E:\oracle\timesten\mytt_db>dir 驱动器 E 中的卷没有标签。 卷的序列号是 38D0-2A35 E:\oracle\timesten\mytt_db 的目录 2012/02/23 23:01 <DIR> . 2012/02/23 23:01 <DIR> .. 2012/02/11 19:06 <DIR> data 2012/02/23 23:01 21,119,936 data.ds0 2012/02/23 23:01 21,119,936 data.ds1 2012/02/23 23:01 <DIR> log 2 个文件 42,239,872 字节 4 个目录 9,222,610,944 可用字节
测试还原结果
E:\>ttisql my_ttdb Copyright (c) 1996-2011, Oracle. All rights reserved. Type ? or "help" for help, type "exit" to quit ttIsql. connect "DSN=my_ttdb"; Connection successful: DSN=my_ttdb;UID=XIFENFEI;DataStore=E:\oracle\timesten\myt t_db\data;DatabaseCharacterSet=ZHS16GBK;ConnectionCharacterSet=ZHS16GBK;DRIVER=E :\oracle\timesten\bin\ttdv1122.dll;LogDir=E:\oracle\timesten\mytt_db\log;PermSiz e=64;TempSize=32;RACCallback=0;TypeMode=0;OracleNetServiceName=XFF; (Default setting AutoCommit=1) Command> tables; XIFENFEI.REP_TABLE XIFENFEI.T1 XIFENFEI.T2 XIFENFEI.T3 XIFENFEI.T4 XIFENFEI.V4 XIFENFEI.XFF 7 tables found. Command> select * FROM T2; < 3 > < 5 > < 3 > < 5 > < 3 > < 5 > < 3 > < 5 > < 3 > < 5 > < 3 > < 5 > 12 rows found.
补充说明
1)ttRestore 除了可以作为数据库还原操作外,还可以用来做数据库复制
ttBackup -dir /users/rob/tmp -fname restored "dsn=origDSN" ttRestore -dir /users/rob/tmp -fname restored "dsn=restoredDSN"
2)对于在Linux或者unix系统使用streamFull备份恢复方式
dd bs=64k if=/dev/rmt0 | ttRestore -i DSN=FastIns
3)该命令是用来数据库还原,那么对于备份之后到数据库异常这段时间的数据该如何处理,是否会丢失暂时还未知。
发表在 TimesTen
评论关闭