Oracle 还原数据库

 

关闭防火墙 
sudo systemctl stop firewalld 

修改用户密码  
ALTER USER nc65 IDENTIFIED BY nc65;/home/erpbjt_full_20240719.zip
unzip erpbjt_full_20240719.zip


mkdir backup
mv /home/C**_erpbjt_full20240719.dmp /home/backup
mv /home/C**_erpbjt_full20240816.dmp /home/backup
chown -R oracle /home/backup/
chown -R oracle /home/oadb/

su - oracle
sqlplus / as sysdba

查询表空间及文件名
SELECT
    ts.tablespace_name,
    df.file_name
FROM
    dba_tablespaces ts
JOIN
    dba_data_files df ON ts.tablespace_name = df.tablespace_name

--查询用户关联了哪个表空间
SELECT username, default_tablespace, temporary_tablespace
FROM dba_users
WHERE username = 'YOUR_USERNAME';
        


查看表空间 文件位置
SELECT FILE_NAME, BYTES/1024/1024 AS FILE_SIZE_MB FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'UNDOTBS1'; 

修改表空间文件大小 
ALTER DATABASE DATAFILE '/data/u01/app/oracle/oradata/erpbjt/undotbs01.dbf' RESIZE 20480M;

修改temp表空间文件大小
ALTER DATABASE tempfile  '/data/u01/app/oracle/oradata/erpbjt/temp01.dbf' RESIZE 20480M;


请设置0racle数据库deferred_segment_creatlon参数为false
ALTER SYSTEM SET deferred_segment_Creation=FALSE;



show parameter processes;   
show parameter open_cursors;

alter system set processes=1000 scope=spfile;   

alter system set open_cursors=1000 scope=spfile;

ALTER SYSTEM SET open_cursors=1000 SCOPE=SPFILE;

#重启数据库:
　　shutdown immediate;
　　startup;
重启监听
lsnrctl stop
lsnrctl start
lsnrctl status


获取文件md5
certutil -hashfile 文件名 md5
md5sum 文件名

1、建表空间 oadb

CREATE TABLESPACE oadb DATAFILE '/home/oadb/oadb.dbf' SIZE 500M AUTOEXTEND ON NEXT 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K ;

CREATE TABLESPACE OASPACE DATAFILE '/home/oadb/oaspace01.dbf' size 4096M AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED;
alter tablespace OASPACE  add datafile  '/home/oadb/oaspace02.dbf'  size 4096M AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED;
alter tablespace OASPACE  add datafile  '/home/oadb/oaspace03.dbf'  size 4096M AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED;
alter tablespace OASPACE  add datafile  '/home/oadb/oaspace04.dbf'  size 4096M AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED;
alter tablespace OASPACE  add datafile  '/home/oadb/oaspace05.dbf'  size 4096M AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED;


alter tablespace NNC_DATA01 add datafile 'E:\app\Administrator\oradata\orcl\nnc_data0104.dbf'size 2000M autoextend on;

DROP USER Oauser2 CASCADE;
CASCADE 关键字的作用是在删除用户的同时，级联删除该用户所拥有的所有对象，包括表、视图、存储过程、索引等。

2、创建用户并关联表空间  
CREATE USER Oauser2 IDENTIFIED BY abc***** DEFAULT TABLESPACE OASPACE TEMPORARY TABLESPACE temp; 3、给用户授权 grant dba,connect to Oauser2; su - oracle sqlplus / as sysdba 创建—个操作目录 create directory dump_dir as '/home/backup'; 给操作目录授权 grant read,write on directory dump_dir to Oauser2; --无限使用表空间 grant unlimited tablespace to Oauser2; expdp Oauser2/abc***** directory=dump_dir dumpfile=newerpbjt_20240817.dmp logfile=newerpbjt_20240817.log full=y expdp Oauser2/abc***** directory=dump_dir dumpfile=oauser2_20250213.dmp logfile=oauser2_20250213.log full=y schemas=Oauser2 parallel=4 expdp Oauser2/abc***** directory=dump_dir dumpfile=oauser2_20250213.dmp logfile=oauser2_20250213.log full=y parallel=4 expdp Oauser2/abc***** directory=dump_dir dumpfile=oauser2_20250221.dmp logfile=oauser2_20250221.log full=y impdp Oauser2/abc***** directory=dump_dir dumpfile=CAISSA_erpbjt_full20240816.dmp logfile=CAISSA_erpbjt_full2024081601.log SCHEMAS=Oauser2 impdp NC65/NC65 directory=dump_dir dumpfile=full20241129.dmp logfile=full20241129.log SCHEMAS=nc65 imp NC65/NC65 fromuser=NC65 touser=NC65 file=E:\full\dump\full20241128\full20241129.dmp log=e:\NC.log; --导入 impdp 新用户/新用户密码 SCHEMAS=旧用户 remap_schema=旧用户:新用户 directory=dump_dir dumpfile=CAISSA_erpbjt_full20240816.dmp logfile=CAISSA_erpbjt_full2024081601.log ALTER USER Oauser2 IDENTIFIED BY abc***** DEFAULT TABLESPACE OASPACE; ALTER USER Oauser2 IDENTIFIED BY abc***** DEFAULT TABLESPACE oadb; DROP TABLESPACE OASPACE INCLUDING CONTENTS AND DATAFILES; -- 删除表空间及其数据文件 DROP TABLESPACE OASPACE INCLUDING CONTENTS AND oadb;