set linesize 200;
set long 100000;
startup pfile=‘D:\app\Administrator\product\11.2.0\dbhome_1\database\INITedw.ORA’;
https://www.cnblogs.com/orcl-2018/p/9211302.html
当数据库归档日志满了后,将无法正常登入数据库,需要删除一部分归档日志才能正常登入数据库。
一、首先删除归档日志目录下的物理文件,删除时至少保留最近几天的日志用于数据恢复。
1、archive log list
2、show parameter recovery
二、删除归档日志物理文件后,可以正常登入数据库,但是到这一步还没完全把归档日志删除干净,因为ORACLE的控制文件中仍然记录着这些归档日志的信息。我们使用rman完成这一步。
1、$rman target /
2、RMAN> crosscheck archivelog all;
3、RMAN> list expired archivelog all;
4、RMAN> delete expired archivelog all;
5、RMAN> delete archivelog until time “sysdate-7”;-----删除系统当前时间7天前的归档日志
6、RMAN> crosscheck archivelog all;------再次执行,检查归档日志是否已经被删除
rman有一个保留策略,可以定义多少天之前的日志算为过期;
configure retention policy to recovery window of 7 days;
在能用dba权限登录的情况下,可以用如下命令查询:
select log_mode from v$database; --检查当前归档日志是否开启
select * from v$flash_recovery_area_usage; --查看空间占用率
select * from v$recovery_file_dest; --查看归档日志的存放地址
在确认开启归档日志的情况下,可用rman 命令进行归档日志的清理。
win+r:cmd
进入默认数据库恢复管理器
rman target /
检查归档日志文件
crosscheck archivelog all;
清空归档文件
delete archivelog all;
清除全部文档
delete expired archivelog all;
清除过期文档
delete archivelog all completed before ‘sysdate-7’;–清除指定日期文档
保留7天归档日志
configure retention policy to recovery window of 7 days;
SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2;
闪回区大小的更改和闪回的启用
select * from v$flash_recovery_area_usage;
查看闪回(归档)占用情况
更改闪回去大小:
启动到mout状态:
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
查看闪回区大小:
SHOW PARAMETER DB_RECOVERY_FILE_DEST;
SQL> SHOW PARAMETER DB_RECOVERY_FILE_DEST;
NAME TYPE VALUE
db_recovery_file_dest string E:\app\Administrator\fast_reco
very_area
db_recovery_file_dest_size big integer 40G
修改闪回区大小:
alter system set db_recovery_file_dest_size = 40G scope=both;
alter system open;
查看是否在闪回模式:
select flashback_on from v d a t a b a s e ; S Q L > s e l e c t f l a s h b a c k o n f r o m v database; SQL> select flashback_on from v database;SQL>selectflashbackonfromvdatabase;
FLASHBACK_ON
NO
SQL>
show parameter flash;
NAME TYPE VALUE
db_flash_cache_file string
db_flash_cache_size big integer 0
db_flashback_retention_target integer 1440
select oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log;
启用闪回:
alter database flashback off [on]
alter database flashback on;
修改闪回时间:
show parameter db_flashback_retention_target;
alter system set db_flashback_retention_target=1440;
建测试表
create table TEST1 as
select rownum as id,
to_char(sysdate + rownum/24/3600, ‘yyyy-mm-dd hh24:mi:ss’) as inc_datetime,
trunc(dbms_random.value(0, 100)) as random_id,
dbms_random.string(‘x’, 20) random_string
from dual
connect by level <= 1000;
同步正常-主
同步正常-备
向测试表插入数据
insert into TEST1
(ID, INC_DATETIME,RANDOM_ID,RANDOM_STRING)
select rownum as id,
to_char(sysdate + rownum / 24 / 3600, ‘yyyy-mm-dd hh24:mi:ss’) as inc_datetime,
trunc(dbms_random.value(0, 100)) as random_id,
dbms_random.string(‘x’, 20) random_string
from dual
connect by level <= 2054;
commit;
commit;
commit;
SELECT COUNT(*) FROM TEST1;
alter system switch logfile;
delete from TEST1 where ID<‘100’;
commit;
commit;
commit;
commit;
SELECT COUNT(*) FROM TEST1;
startup spfile=‘SPFILEORCL.ORA’;
修改用户密码
ALTER USER 大写用户名 IDENTIFIED BY “新密码”;
ALTER USER SYS IDENTIFIED BY “his”;
ALTER USER sys IDENTIFIED BY “oracle”;
ALTER USER SCOTT IDENTIFIED BY “123456”;
show tables
select TABLE_NAME from user_tables;
查看当前登陆用户的所属表
select TABLE_NAME from user_tables where TABLE_NAME LIKE ‘%TEST%’;
或者
select table_name from all_tables where owner=‘SIMON’;
注意:此处 TABLE_NAME 为 user_tables的字段名称,请不要修改。用户名需要大写
oracle 查看当前用户名
show user
select user from dual;
oracle 查看所有用户名
select * from all_users;
select username from all_users;
查看数据文件路径
set linesize 110;
set long 100000;
select name from v$datafile;
查看redolog文件路径
SELECT MEMBER FROM v$logfile;
select bytes from v$log;
查看归档日志路径,归档日志默认使用闪回区域
show parameter USE_DB_RECOVERY_FILE_DEST;
show parameter DB_RECOVERY_FILE_DEST;
SQL> show parameter DB_RECOVERY_FILE_DEST
NAME TYPE VALUE
db_recovery_file_dest string /home/oracle/app/oracle/flash_recovery_area
db_recovery_file_dest_size big integer 3882M
/home/oracle/app/oracle/flash_recovery_area 一般就是 $ORACLE_BASE/flash_recovery_area
查看当前数据库是否使用spfile启动:
SYS@pri> show parameter spfile;
NAME TYPE VALUE
spfile string /u01/app/oracle/product/11.2.0/db_1/dbs/spfilepri.ora
如上,若能看到spfile的路径,则证明数据库是使用spfile启动的,若没有值,则说明是用pfile启动的。
C:\app\Administrator\oradata\orcl
create temporary tablespace SIMON_TEMP
tempfile ‘C:\app\Administrator\oradata\orcl\simon_temp.dbf’
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
create tablespace SIMON_DATA
logging
datafile ‘C:\app\Administrator\oradata\orcl\simon_data.dbf’
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
create user simon identified by “123456”
default tablespace SIMON_DATA
temporary tablespace SIMON_TEMP;
grant connect,resource,dba to simon;
commit;
commit;
commit;
commit;
commit;