Erro abaixo ao executar o impdp
|
1 2 3 4 |
ORA-39002: invalid operation ORA-31694: master table "SYSTEM"."SYS_IMPORT_SCHEMA_02" failed to load/unload ORA-02354: error in exporting/importing data ORA-39773: parse of metadata stream failed |
Causa Analisando o .dmp identifiquei que o mesmo estava corrompido. Provavelmente ocorreu falha ao gerar o binário Solução realizado um novo expdp e impdp com sucesso!
|
1 2 3 4 5 6 7 8 9 |
SYS@BDORA> column PARAMETER format a35; SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER IN ('NLS_NCHAR_CHARACTERSET','NLS_CHARACTERSET'); PARAMETER VALUE ----------------------------------- ---------------------------------------------------------------- NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_CHARACTERSET WE8MSWIN1252 |
ERRO: Creating PERFSTAT usercreate user perfstat*ERROR at line 1:ORA-01920: user name ‘PERFSTAT’ conflicts with another user or role name
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SQL> @?/rdbms/admin/spcreate Choose the PERFSTAT user's password [password] Choose the Default tablespace for the PERFSTAT user [PERFSTAT]<--I had to create it Choose the Temporary tablespace for the PERFSTAT user [TEMP] Using tablespace TEMP as PERFSTAT temporary tablespace. ... Creating PERFSTAT user create user perfstat * ERROR at line 1: ORA-01920: user name 'PERFSTAT' conflicts with another user or role name |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 |
SQL> @?/rdbms/admin/spdrop.sql ..... .... ... .. drop view STATS$X_$KSPPSV * ERROR at line 1: ORA-00942: table or view does not exist drop public synonym STATS$X$KSPPSV * ERROR at line 1: ORA-01432: public synonym to be dropped does not exist drop view STATS$X_$KSPPI * ERROR at line 1: ORA-00942: table or view does not exist drop public synonym STATS$X$KSPPI * ERROR at line 1: ORA-01432: public synonym to be dropped does not exist drop view STATS$X_$KSXPPING * ERROR at line 1: ORA-00942: table or view does not exist drop public synonym STATS$X$KSXPPING * ERROR at line 1: ORA-01432: public synonym to be dropped does not exist drop view STATS$V_$FILESTATXS * ERROR at line 1: ORA-00942: table or view does not exist drop public synonym STATS$V$FILESTATXS * ERROR at line 1: ORA-01432: public synonym to be dropped does not exist drop view STATS$V_$SQLXS * ERROR at line 1: ORA-00942: table or view does not exist drop public synonym STATS$V$SQLXS * ERROR at line 1: ORA-01432: public synonym to be dropped does not exist drop view STATS$V_$TEMPSTATXS * ERROR at line 1: ORA-00942: table or view does not exist drop public synonym STATS$V$TEMPSTATXS * ERROR at line 1: ORA-01432: public synonym to be dropped does not exist drop view STATS$V_$SQLSTATS_SUMMARY * ERROR at line 1: ORA-00942: table or view does not exist drop public synonym STATS$V$SQLSTATS_SUMMARY * ERROR at line 1: ORA-01432: public synonym to be dropped does not exist User dropped. |
Dropando usuário ORCL
|
1 |
SYS@ORCL > drop user ORCL cascade; |
Erro ORA-01940
|
1 2 |
ERROR at line 1: ORA-01940: cannot drop a user that is currently connected |
Solução: Parando
|
1 2 3 4 |
SYS@ORCL > Shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. |
Iniciar em modo restrict
|
1 2 3 4 5 6 7 8 9 10 |
SYS@ORCL > startup restrict; ORACLE instance started. Total System Global Area 1.0689E+10 bytes Fixed Size 2262656 bytes Variable Size 1979713920 bytes Database Buffers 8690597888 bytes Redo Buffers 16900096 bytes Database mounted. Database opened. |
Dropando usuário ORCL
|
1 2 3 |
SYS@NBS > drop user ORCL cascade; User dropped. |
Parando
|
1 2 3 4 |
SYS@ORCL > shut immediate Database closed. Database dismounted. ORACLE instance shut down. |
Iniciando
|
1 2 3 4 5 6 7 8 9 10 |
21:50:44 SYS@ORCL > startup ORACLE instance started. Total System Global Area 1.0689E+10 bytes Fixed Size 2262656 bytes Variable Size 1979713920 bytes Database Buffers 8690597888 bytes Redo Buffers 16900096 bytes Database mounted. Database opened. |
Em outro post mostrarei como matar processo do usuário.
Ao realizar a análise de uma rotina de backup Data Pump me deparei com seguinte erro no log abaixo:
|
1 2 3 4 |
EXP-00056: ORACLE error 28001 encountered ORA-28001: the password has expired EXP-00030: Unexpected End-Of-File encountered while reading input EXP-00000: Export terminated unsuccessfully |
Verificando contas com senha expirada.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
SYSTEM@ORCL> select username, account_status from dba_users where lock_date is not null; USERNAME ACCOUNT_STATUS ------------------------------ -------------------------------- FLOWS_FILES EXPIRED & LOCKED MDSYS EXPIRED & LOCKED ORDDATA EXPIRED & LOCKED ORDSYS EXPIRED & LOCKED CTXSYS EXPIRED & LOCKED ANONYMOUS EXPIRED & LOCKED EXFSYS EXPIRED & LOCKED OUTLN EXPIRED & LOCKED DIP EXPIRED & LOCKED APEX_PUBLIC_USER EXPIRED & LOCKED WMSYS EXPIRED & LOCKED XDB EXPIRED & LOCKED ORACLE_OCM EXPIRED & LOCKED APPQOSSYS EXPIRED & LOCKED ORDPLUGINS EXPIRED & LOCKED XS$NULL EXPIRED & LOCKED APEX_030200 EXPIRED & LOCKED SI_INFORMTN_SCHEMA EXPIRED & LOCKED SYSTEM EXPIRED & LOCKED 18 rows selected. |
Analisando profile do usuário SYSTEM
|
1 2 3 4 5 |
SYSTEM@ORCL > select username, profile from dba_users where username='SYSTEM'; USERNAME PROFILE ------------------------------ ------------------------------ SYSTEM DEFAULT |
Analisando DBA_PROFILES
|
1 2 3 4 5 |
SYSTEM@ORCL> select RESOURCE_NAME, LIMIT from dba_profiles where RESOURCE_NAME='PASSWORD_LIFE_TIME'; RESOURCE_NAME LIMIT -------------------------------- ---------------------------------------- PASSWORD_LIFE_TIME 180 |
Está definido 180 dias por default. Alterando Profile para ilimitado.
|
1 2 3 |
SYSTEM@ORCL> ALTER PROFILE default LIMIT PASSWORD_LIFE_TIME UNLIMITED; Profile altered. |
Verificando novamente DBA_PROFILES.
|
1 2 3 4 5 |
SYSTEM@ORCL> select RESOURCE_NAME, LIMIT from dba_profiles where RESOURCE_NAME='PASSWORD_LIFE_TIME'; RESOURCE_NAME LIMIT -------------------------------- ---------------------------------------- PASSWORD_LIFE_TIME UNLIMITED |
Alterando a senha.
|
1 2 |
SYSTEM@ORCL> ALTER USER system IDENTIFIED BY password; User altered. |
Desbloqueando…