一、案例
1.1 创建 directory
create directory shuanghuo as '/dmp';
grant read,write on directory shuanghuo to public;
2.2 全库导出
expdp \'/ as sysdba \' directory=shuanghuo dumpfile=tccbdb.dmp logfile=tccbdb_exp.log full=y
3.3 按用户导入
impdp \'/ as sysdba \' directory=shuanghuo dumpfile=tccbdb.dmp logfile=tccbdb_imp.log schemas=CMS57,UCMS
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
已成功加载/卸载了主表 "SYS"."SYS_IMPORT_SCHEMA_01"
启动 "SYS"."SYS_IMPORT_SCHEMA_01": "/******** AS SYSDBA" directory=shuanghuo dumpfile=tccbdb.dmp logfile=tccbdb_imp.log schemas=CMS57,UCMS
处理对象类型 DATABASE_EXPORT/SCHEMA/USER
ORA-39384: 警告: 用户 UCMS 已被锁定且口令已过期。
ORA-39083: 对象类型 USER:"CMS57" 创建失败, 出现错误:
二、原因
1.1 密码版本
//所有这些数据库用户帐户均为10g密码版本（DBA_USERS.PASSWORD_VERSIONS列值为'10G'），但不是11g或12C版本。由于未在12.1.0.2 sqlnet.ora文件中设置SQLNET.ALLOWED_LOGON_VERSION_SERVER，因此其默认值为12。因此，它仅允许使用12C密码版本的用户。
三、解决
3.1 配置 sqlnet.ora
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8
SYS OPEN SYSTEM TEMP
SYSTEM OPEN SYSTEM TEMP
SYSMAN OPEN SYSAUX TEMP
DBSNMP OPEN SYSAUX TEMP
BOTEDW OPEN USERS TEMP
CMS57 OPEN USERS TEMP
PATROL OPEN PATROL_USER PATROL_TEMP
UCMS OPEN UCMS_DATA UCMS_TEMP
3.2 创建表空间
create tablespace UCMS_DATA datafile '/oradata/TCCBDB/UCMS_DATA01.dbf' size 1G autoextend on;
create temporary tablespace TEMP011 tempfile '/oradata/TCCBDB/TEMP011.dbf' size 1G autoextend on;
select username,account_status from dba_users where account_status='OPEN';
USERNAME ACCOUNT_STATUS
-------------------------------------------------- ----------------------------------------------------------------
SYS OPEN
SYSTEM OPEN
UCMS OPEN
CMS57 OPEN
//导出正常
#SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
#SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8
3.4 导出用户状态为锁住
drop user CMS57 cascade;
drop user UCMS cascade;
ORA-39384: Warning: User UCMS has been locked and the password expired.
ORA-39384: Warning: User CMS57 has been locked and the password expired.
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
SQL> select username,account_status from dba_users where account_status='OPEN';
USERNAME
--------------------------------------------------------------------------------
ACCOUNT_STATUS
----------------------------------------------------------------
SYS
OPEN
SYSTEM
OPEN
SQL> select username,account_status from dba_users where username in ('CMS57','UCMS');
USERNAME ACCOUNT_STATUS
-------------------- ----------------------------------------------------------------
UCMS EXPIRED & LOCKED
CMS57 EXPIRED & LOCKED
5.5 删除 sqlnet.ora
导出完毕后，删除sqlnet.ora 发现用户状态正常，重启库用户状态正常。但是没有测试过晚上00：00 后，用户是否过期
SEC_CASE_SENSITIVE_LOGON --密码大写 默认为true . 10g 默认不区分，SEC_CASE_SENSITIVE_LOGON 此参数得改成FALSE
