[oracle@station15 ~]$ . oraenv
[root@station10 /]# vim /etc/oratab
[oracle@station38 ~]$ lsnrctl status 查看listener状态
[oracle@station15 ~]$ crs_stat -t 查看实例运行状态,grid环境
[oracle@station38 ~]$ emctl unsecure dbconsole 关闭EM的SSL
select * from v$dispatcher;
select * from v$process where addr in (select paddr from v$dispatcher );
select * from v$circuit;
select * from v$session where saddr in (select saddr from v$circuit);
select * from v$session where machine ='WORKGROUP2\STATION199';
SQL> show parameter name;
SQL> alter system set service_names='testdw';
SQL> alter user sys identified by oracle_4U account unlock;
select s.USERNAME,
s.SID,
s.SERIAL#,
s.SADDR,
s.PADDR,
s.TADDR,
s.STATUS,
s.TERMINAL,
s.BLOCKING_SESSION
from v_$session s
where s.USERNAME='HR' ;
----
select * from v$transaction;
----
select SID, SERIAL#, USERNAME
from V$SESSION where SID in
(select BLOCKING_SESSION from V$SESSION);
----
alter system kill session '200,428' immediate;
select * from v$transaction;
select * from dba_rollback_segs;
select * from dba_tablespaces;
select * from v$tablespace;
实验的语句:
select * from dba_rollback_segs;
create undo tablespace undotbs2 datafile size 256k autoextend off;
select * from dba_rollback_segs;
alter tablespace temp add tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf' size 10G
autoextend on maxsize 20G;
alter tablespace temp drop tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf' ;
drop tablespace undotbs2;
select * from dba_rollback_segs;
select * from dict
where table_name like '%UNDO%' or table_name like '%ROLL%';
select * from v$rollstat;
select * from v$undostat;
select * from dba_hist_undostat;
select * from dba_alert_history;
select s. username , s.SID ,s.SERIAL# ,s.SQL_ID , s.SQL_HASH_VALUE
from v_$session s
where s.TERMINAL='pts/1' ;
检查点相关指令:
select d.RESETLOGS_CHANGE#,
d.CHECKPOINT_CHANGE#,
d.CURRENT_SCN
from v_$database d;
select d.CHECKPOINT_CHANGE# ,d.NAME
from v_$datafile d;
select * from v$log;
alter system switch logfile;
alter system switch logfile;
alter system checkpoint;
alter database drop logfile group 1;
alter database add logfile group 1 size 50M;
select * from v$log;
select * from v$logfile;
alter database add logfile member '/u01/app/oracle/oradata/orcl/redo02c.log' to group 2 ;
alter database drop logfile member '/u01/app/oracle/oradata/orcl/redo02c.log';
alter system switch logfile;
alter database drop logfile member '/u01/app/oracle/oradata/orcl/redo02c.log';
select * from v$logfile;
select d.CHECKPOINT_CHANGE# ,d.NAME
from v_$datafile d;
select d.RESETLOGS_CHANGE#,
d.CHECKPOINT_CHANGE#,
d.CURRENT_SCN
from v_$database d;
alter tablespace users offline;
alter tablespace users online;
alter system checkpoint;
alter tablespace users read only;
alter tablespace users read write;
---------------------
alter tablespace users begin backup;
alter tablespace users end backup;
----------
alter database datafile '+DATA/orcl/datafile/users.259.816169553' offline;
alter database datafile '+DATA/orcl/datafile/users.259.816169553' online;
---------
alter database rename file '+DATA/orcl/datafile/users.259.816169553' to
'+data/orcl/DATAFILE/users.dbf';
alter database datafile '+DATA/orcl/datafile/users.dbf' online;
recover datafile '+DATA/orcl/datafile/users.dbf';
alter database datafile '+DATA/orcl/datafile/users.dbf' online;
alter tablespace sysaux offline;
select d.CHECKPOINT_CHANGE# ,d.NAME
from v_$datafile d;
alter tablespace sysaux online;
alter tablespace system offline;
[oracle@station8 ~]$ rman target /
RMAN> recover database;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/18/2018 17:46:53
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
start
ORA-00283: recovery session canceled due to errors
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '+FRA/station8/onlinelog/group_1.257.971108007'
ORA-17503: ksfdopn:2 Failed to open file +FRA/station8/onlinelog/group_1.257.971108007
ORA-15012: ASM file '+FRA/station8/onlinelog/group_1.257.971108007' does not exist
ORA-00312: online log 1 thread 1: '+DATA/station8/onlinelog/group_1.261.971108005'
ORA-17503: ksfdopn:2 Failed to open file +DATA/station8/online
RMAN> restore database ;
RMAN> recover database;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/18/2018 17:48:46
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '+FRA/station8/archivelog/2018_03_18/thread_1_seq_12.284.971111663'
ORA-00283: recovery session canceled due to errors
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '+FRA/station8/onlinelog/group_1.257.971108007'
ORA-17503: ksfdopn:2 Failed to open file +FRA/station8/onlinelog/group_1.257.971108007
ORA-15012: ASM file '+FRA/station8/onlinelog/group_1.257.971108007' does not exist
ORA-00312: online log 1 thread 1: '+DATA/station8/onlinelog/group_1.261.971108005'
ORA-17503: ksfdopn:2 Failed to open file +DATA/station8/online
SQL> conn / as sysdba
SQL> recover database;
SQL> recover database until cancel ;
SQL> alter database open resetlogs;
[oracle@station8 ~]$ rman target /
RMAN> list incarnation of database ;