Reset expired password and set as default, Password experied-2 schema01,schema02
SQL> select password from user$ where name='username';
SQL> alter user username identified by values 'PASS ID';
SQL> alter user username account unlock;
SQL> select username,password from dba_users where username='AMIT';
SQL> alter user amit identified by values '9DEC0D889E8E9A6B';
SQL> select username,account_status from dba_users where account_status like '%EXPIRED%'
or account_status like '%LOCKED%';
Verify archive log path.
SQL> archive log list;
SQL> show parameter recovery_file_dest;
How to set archive log path.
SQL> find the trace file-show parameter background_dump_dest or SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Diag Trace';
SQL> select value from v$diag_info where name='Diag Trace';
# find . -name "*alert*log*" |less
Clear archive log space.
SQL> show parameter db_recovery_file
Overall space usage in /recoveryarea mountpoint
$ df -h /recoveryarea/$ORACLE_SID/archivelog/
Space usage in database /recoveryarea directory
$ du -sh /recoveryarea/$ORACLE_SID/archivelog/
Above query to display the size=10gb
Need to increase from 10gb to 12gb below query
SQL> alter system set db_recovery_file_dest_size = 12g;
Clear archivelog and path change
RMAN> crosscheck archivelog all;
RMAN> delete expired archivelog all;
RMAN> delete noprompt force archivelog all;
RMAN> delete archivelog all completed before 'sysdate-0';
SQL> archive log list;
SQL> alter sysem set log_archive_dest='new locatin' scope=both;
Without increasing DB_RECOVERY_FILE_DEST_SIZE.
archivelog size
SQL> select group#, thread#, bytes from v$log;
---------------------------------------------------------------------------------------------------------------------------------------------
Check whether the database is in archive log mode and automatic archiving is enabled.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 29
Next log sequence to archive 31
Current log sequence 31
2. If archive destination is defined by USE_DB_RECOVERY_FILE_DEST, find the archive destination by:
SQL> show parameter db_recovery_file_dest;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string C:\oracle\product\10.2.0/flash_recovery_area
db_recovery_file_dest_size big integer 2G
Check what the value for db_recovery_file_dest_size. Find the space used in flash recovery area by using following SQL:
col ROUND(SPACE_LIMIT/1048576) heading “Space Allocated (MB)” format 999999
col round(space_used/1048576) heading “Space Used (MB)” format 99999
col name format a40
select name, round(space_limit/1048576) As space_limit,round(space_used/1048576) As space_used
from v$RECOVERY_FILE_DEST;
If SPACE_USED is equal to SPACE_LIMIT of db_recovery_file_dest, move the archive logs to different destination.
Archive all the log files
SQL> alter system archive log all;
Just switch the logs to verify
SQL> alter system switch logfile;
DB_RECOVERY_FILE_DEST_SIZE is to delete (archive log) files from DB_RECOVERY_FILE_DEST if you are sure you have backups and the archived logs are no longer necessary.
$rman target /
RMAN> delete archivelog until time 'SYSDATE-1';
or,
RMAN> delete archivelog all;
By increasing DB_RECOVERY_FILE_DEST_SIZE.See the path of flash recovery area.
SQL> show parameter db_recovery_file_dest;
Increase the Flash Recovery Area
SQL> ALTER SYSTEM SET db_recovery_file_dest_size='10G' SCOPE=BOTH;
Sytem Altered.
Permission to system users on oracle.
SQL> grant create any directory to username;
SQL> GRANT connect,resource TO username;
SQL> grant all privileges to userName;
SQL> GRANT CONNECT, RESOURCE, DBA TO books_admin;
SQL> GRANT CREATE SESSION GRANT ANY PRIVILEGE TO books_admin;
SQL> GRANT UNLIMITED TABLESPACE TO books_admin;
SQL> GRANT read, write ON DIRECTORY LIVEDIR TO LIVE_OWNER;
SQL> create directory TEST as '/somevalidfilepath';
SQL> select * from all_directories where directory_name = 'TEST';
SQL> select grantor, grantee, table_schema, table_name, privilege from all_tab_privs where table_name =
'TEST';
Check temp files.
SQL> select * from dba_temp_files;
IF Offline give this==alter database tempfile 'PATH.dbf' online;
Size check temp file.
select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;
SELECT * FROM dba_temp_free_space;
select bytes /1024/1024/1024 from v$tempfile;
ERROR Code: ora-12519 tns:no appropriate service handler found solution.
SOLUTION:
SQL> select * from v$resource_limit where resource_name = 'processes';
SQL> alter system set processes=500 scope=spfile;
SQL> shutdown immediate;
SQL> startup;
(OR)
SQL> select SID,serial#,status,server from v$session where status='INACTIVE';
SQL> alter system kill session '25,13217';
(OR)
SQL> show parameter processes;
SQL> select count(*) from v$process;
SQL> alter system set processes=500 scope=spfile;
SQL> shut immediate;
SQL> startup;
Obsolete or deprecated parameters ERROR (ORA-32004)
select p.name,p.value from v$parameter p, v$spparameter s where s.name=p.name and p.isdeprecated='TRUE' and s.isspecified='TRUE';
NAME VALUE
--------------------- ----------------------------------------------
background_dump_dest /u01/app/oracle/diag/rdbms/hfmtst/HFMTST/trace
user_dump_dest /u01/app/oracle/diag/rdbms/hfmtst/HFMTST/trace
They can be cleared with:
SQL> alter system reset background_dump_dest;
SQL> alter system reset user_dump_dest;
ORA-00257-archive error clear archivelog and increase the space.
SQL> show parameter db_recovery_file_dest;
SQL> ALTER SYSTEM SET db_recovery_file_dest_size='10G' SCOPE=BOTH;
ORA-03113: end-of-file on communication channel.
SQL> startup mount;
Switch to rman.
# rman target /
RMAN> crosscheck archivelog all;
RMAN> delete expired archivelog all;
ASM AND DB CHECK WHETHER ASM CONNECT TO DB OR NOT GIVE BELOW QUERY.
SQL> select instance_name,db_name,status,software_version as "version" from v$asm_client;
ORA-00845-MEMORY TARGET NOT SUPPORTED.
# mount -t tmpfs shmfs -o size=12g /dev/shm
Permanent mount fstab
# vi /etc/fstab
tmpfs /dev/shm tmpfs size=12g defaults 0 0
ERROR TNS-12545,12560,00515
[root@secdb ~]# cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost localhost.localdomain localhost
::1 localhost6.localdomain6 localhost6
[root@secdb ~]# hostname
secdb
[root@secdb ~]# vi /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 secdb localhost.localdomain localhost
::1 localhost6.localdomain6 localhost6
LSNRCTL> start
Starting /oracle/ora10gR2/product/10.2.0/db_2/bin/tnslsnr: please wait...
ORA-32001: write to SPFILE requested but no SPFILE is in use.
SQL> create spfile from pfile;
SQL> shut immediate;
SQL> startup;
User session kill process via SQL.
SQL> select sid,serial# from v$session where username = 'test';
SQL> ALTER SYSTEM KILL SESSION '<SID>, <SERIAL>'
Export and Import activity create the audit.
# sqlplus “/ as sysdba”
SQL> CREATE AUDIT POLICY audit_dp_all_policy ACTIONS COMPONENT=DATAPUMP ALL;
SQL> AUDIT POLICY audit_dp_all_policy BY system;
// Run the following data pump command to backup emp table of SYSTEM schema:
# expdp system/manager tables=emp directory=test_dir dumpfile=emp.dmp logfile=expdp_emp.log
SCHEMA RENAME
SQL> update user$ set name='TIGER' where name='SCOTT';
SQL>alter user TIGER account unlock;
SQL>alter user TIGER identified by PASSWORD;
SQL> select object_name, object_type from all_objects where owner='SCOTT';
Kill the existing data pump job.
SQL> select owner_name,job_name,operation,job_mode,state,attached_sessions from dba_datapump_jobs;
SQL> select object_name, object_type from all_objects where owner='SCOTT';
ORA-04063: view "SYS.KU$_RADM_FPTM_VIEW
SQL> alter type ku$_radm_fptm_t compile reuse settings;
SQL> alter view SYS.KU$_RADM_FPTM_VIEW compile;
Check the NLS_CHAR & NLS_LANG
SQL> select * from nls_database_parameters;
ORA-27300: OS system dependent oper:semctl failed
ORA-27301: OS failure message: invalied argument
ORA-27302: failure occurred at: sskgpwrm1
ORA-27157: OS post/wait facility removed
SOLUTION
# vi /etc/systemd/logind.conf
Change line
RemoveIPC=no
# systemctl daemon-reload
# systemctl restart systemd-logind
SQL> select password from user$ where name='username';
SQL> alter user username identified by values 'PASS ID';
SQL> alter user username account unlock;
SQL> select username,password from dba_users where username='AMIT';
SQL> alter user amit identified by values '9DEC0D889E8E9A6B';
SQL> select username,account_status from dba_users where account_status like '%EXPIRED%'
or account_status like '%LOCKED%';
Verify archive log path.
SQL> archive log list;
SQL> show parameter recovery_file_dest;
How to set archive log path.
SQL> find the trace file-show parameter background_dump_dest or SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Diag Trace';
SQL> select value from v$diag_info where name='Diag Trace';
# find . -name "*alert*log*" |less
Clear archive log space.
SQL> show parameter db_recovery_file
Overall space usage in /recoveryarea mountpoint
$ df -h /recoveryarea/$ORACLE_SID/archivelog/
Space usage in database /recoveryarea directory
$ du -sh /recoveryarea/$ORACLE_SID/archivelog/
Above query to display the size=10gb
Need to increase from 10gb to 12gb below query
SQL> alter system set db_recovery_file_dest_size = 12g;
Clear archivelog and path change
RMAN> crosscheck archivelog all;
RMAN> delete expired archivelog all;
RMAN> delete noprompt force archivelog all;
RMAN> delete archivelog all completed before 'sysdate-0';
SQL> archive log list;
SQL> alter sysem set log_archive_dest='new locatin' scope=both;
Without increasing DB_RECOVERY_FILE_DEST_SIZE.
archivelog size
SQL> select group#, thread#, bytes from v$log;
---------------------------------------------------------------------------------------------------------------------------------------------
Check whether the database is in archive log mode and automatic archiving is enabled.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 29
Next log sequence to archive 31
Current log sequence 31
2. If archive destination is defined by USE_DB_RECOVERY_FILE_DEST, find the archive destination by:
SQL> show parameter db_recovery_file_dest;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string C:\oracle\product\10.2.0/flash_recovery_area
db_recovery_file_dest_size big integer 2G
Check what the value for db_recovery_file_dest_size. Find the space used in flash recovery area by using following SQL:
col ROUND(SPACE_LIMIT/1048576) heading “Space Allocated (MB)” format 999999
col round(space_used/1048576) heading “Space Used (MB)” format 99999
col name format a40
select name, round(space_limit/1048576) As space_limit,round(space_used/1048576) As space_used
from v$RECOVERY_FILE_DEST;
If SPACE_USED is equal to SPACE_LIMIT of db_recovery_file_dest, move the archive logs to different destination.
Archive all the log files
SQL> alter system archive log all;
Just switch the logs to verify
SQL> alter system switch logfile;
DB_RECOVERY_FILE_DEST_SIZE is to delete (archive log) files from DB_RECOVERY_FILE_DEST if you are sure you have backups and the archived logs are no longer necessary.
$rman target /
RMAN> delete archivelog until time 'SYSDATE-1';
or,
RMAN> delete archivelog all;
By increasing DB_RECOVERY_FILE_DEST_SIZE.See the path of flash recovery area.
SQL> show parameter db_recovery_file_dest;
Increase the Flash Recovery Area
SQL> ALTER SYSTEM SET db_recovery_file_dest_size='10G' SCOPE=BOTH;
Sytem Altered.
Permission to system users on oracle.
SQL> grant create any directory to username;
SQL> GRANT connect,resource TO username;
SQL> grant all privileges to userName;
SQL> GRANT CONNECT, RESOURCE, DBA TO books_admin;
SQL> GRANT CREATE SESSION GRANT ANY PRIVILEGE TO books_admin;
SQL> GRANT UNLIMITED TABLESPACE TO books_admin;
SQL> GRANT read, write ON DIRECTORY LIVEDIR TO LIVE_OWNER;
SQL> create directory TEST as '/somevalidfilepath';
SQL> select * from all_directories where directory_name = 'TEST';
SQL> select grantor, grantee, table_schema, table_name, privilege from all_tab_privs where table_name =
'TEST';
Check temp files.
SQL> select * from dba_temp_files;
IF Offline give this==alter database tempfile 'PATH.dbf' online;
Size check temp file.
select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;
SELECT * FROM dba_temp_free_space;
select bytes /1024/1024/1024 from v$tempfile;
ERROR Code: ora-12519 tns:no appropriate service handler found solution.
SOLUTION:
SQL> select * from v$resource_limit where resource_name = 'processes';
SQL> alter system set processes=500 scope=spfile;
SQL> shutdown immediate;
SQL> startup;
(OR)
SQL> select SID,serial#,status,server from v$session where status='INACTIVE';
SQL> alter system kill session '25,13217';
(OR)
SQL> show parameter processes;
SQL> select count(*) from v$process;
SQL> alter system set processes=500 scope=spfile;
SQL> shut immediate;
SQL> startup;
Obsolete or deprecated parameters ERROR (ORA-32004)
select p.name,p.value from v$parameter p, v$spparameter s where s.name=p.name and p.isdeprecated='TRUE' and s.isspecified='TRUE';
NAME VALUE
--------------------- ----------------------------------------------
background_dump_dest /u01/app/oracle/diag/rdbms/hfmtst/HFMTST/trace
user_dump_dest /u01/app/oracle/diag/rdbms/hfmtst/HFMTST/trace
They can be cleared with:
SQL> alter system reset background_dump_dest;
SQL> alter system reset user_dump_dest;
ORA-00257-archive error clear archivelog and increase the space.
SQL> show parameter db_recovery_file_dest;
SQL> ALTER SYSTEM SET db_recovery_file_dest_size='10G' SCOPE=BOTH;
ORA-03113: end-of-file on communication channel.
SQL> startup mount;
Switch to rman.
# rman target /
RMAN> crosscheck archivelog all;
RMAN> delete expired archivelog all;
ASM AND DB CHECK WHETHER ASM CONNECT TO DB OR NOT GIVE BELOW QUERY.
SQL> select instance_name,db_name,status,software_version as "version" from v$asm_client;
ORA-00845-MEMORY TARGET NOT SUPPORTED.
# mount -t tmpfs shmfs -o size=12g /dev/shm
Permanent mount fstab
# vi /etc/fstab
tmpfs /dev/shm tmpfs size=12g defaults 0 0
ERROR TNS-12545,12560,00515
[root@secdb ~]# cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost localhost.localdomain localhost
::1 localhost6.localdomain6 localhost6
[root@secdb ~]# hostname
secdb
[root@secdb ~]# vi /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 secdb localhost.localdomain localhost
::1 localhost6.localdomain6 localhost6
LSNRCTL> start
Starting /oracle/ora10gR2/product/10.2.0/db_2/bin/tnslsnr: please wait...
ORA-32001: write to SPFILE requested but no SPFILE is in use.
SQL> create spfile from pfile;
SQL> shut immediate;
SQL> startup;
User session kill process via SQL.
SQL> select sid,serial# from v$session where username = 'test';
SQL> ALTER SYSTEM KILL SESSION '<SID>, <SERIAL>'
Export and Import activity create the audit.
# sqlplus “/ as sysdba”
SQL> CREATE AUDIT POLICY audit_dp_all_policy ACTIONS COMPONENT=DATAPUMP ALL;
SQL> AUDIT POLICY audit_dp_all_policy BY system;
// Run the following data pump command to backup emp table of SYSTEM schema:
# expdp system/manager tables=emp directory=test_dir dumpfile=emp.dmp logfile=expdp_emp.log
SCHEMA RENAME
SQL> update user$ set name='TIGER' where name='SCOTT';
SQL>alter user TIGER account unlock;
SQL>alter user TIGER identified by PASSWORD;
SQL> select object_name, object_type from all_objects where owner='SCOTT';
Kill the existing data pump job.
SQL> select owner_name,job_name,operation,job_mode,state,attached_sessions from dba_datapump_jobs;
SQL> select object_name, object_type from all_objects where owner='SCOTT';
ORA-04063: view "SYS.KU$_RADM_FPTM_VIEW
SQL> alter type ku$_radm_fptm_t compile reuse settings;
SQL> alter view SYS.KU$_RADM_FPTM_VIEW compile;
Check the NLS_CHAR & NLS_LANG
SQL> select * from nls_database_parameters;
ORA-27300: OS system dependent oper:semctl failed
ORA-27301: OS failure message: invalied argument
ORA-27302: failure occurred at: sskgpwrm1
ORA-27157: OS post/wait facility removed
SOLUTION
# vi /etc/systemd/logind.conf
Change line
RemoveIPC=no
# systemctl daemon-reload
# systemctl restart systemd-logind
