How to connect SQLPlus without tnsnames.ora

Connect using one of service_name:

sqlplus username/password@//host[:port][/service_name]

where service_name can be one of simple name or domain name specified in service_names parameter:

show parameter service_names
ALTER SYSTEM SET service_names = 'ORCL', 'us.acme.com' COMMENT='Add new services' SCOPE=BOTH;

Procedure to write to alert.log

Link to original article Stored Proc to write to database alert logs author Charles Kim.

From the application or from database triggers, if I want to record anything to the alert log, I can simply invoke this stored procedure and write error messages to the alert log.

Most of the DBAs have implemented alert log scanners and will send alert notifications if new ORA- error messages pop up in the alert log file.
 

create or replace procedure write_msg_to_alert_log (
  p_message varchar2,
  p_oranum number default 20001
)
is
/*
  create procedure under system user
*/
begin
  sys.dbms_system.ksdddt;
  sys.dbms_system.ksdwrt(2, 'ORA-' || to_char(p_oranum) || ': ' || p_message);
end;
/
  
--grant execute on write_msg_to_alert_log to public;
grant execute on write_msg_to_alert_log to scott;

create public synonym write_msg_to_alert_log for system.write_msg_to_alert_log;

Recovery using the SWITCH DATABASE TO COPY command

Recover database from copy

rm users*.dbf
 
rman target /
RMAN> shutdown abort
 
# Recover database from copy
RMAN> startup mount;
RMAN> switch database to copy;
RMAN> recover database;
RMAN> alter database open;
 
SQL> select name,status from v$datafile;

Recover tablespace from copy

rm users*.dbf
 
rman target /
 
# Recover tablespace from copy
RMAN> sql 'alter tablespace users offline immediate';
RMAN> switch tablespace users to copy;
RMAN> recover tablespace users;
RMAN> sql 'alter tablespace users online';
 
SQL> select file_name from dba_data_files where tablespace_name='USERS';
NAME
----------------------------
/data/users01.dbf
/backup/users02.dbf
 
 
# Move datafile from /backup/$ORACLE_SID to /data/$ORACLE_SID directory
# while the Database is online
SQL> alter tablespace users offline;
 
# Move the file /db_backup/ptdb1/users02.dbf using operating system command
cp /backup/users02.dbf /data/users02.dbf
 
# Update the database data dictionary
SQL> alter database rename file '/backup/users02.dbf' to '/data/users02.dbf';
 
# Take tablespace USERS online:
SQL> alter tablespace users online;
 
# Delete file from old location using operating system command
rm /backup/users02.dbf

Check tablespace size usage

Rewritten version from Top DBA Shell Scripts for Monitoring the Database based on MAXBYTES of each file within tablespace:

SELECT F.TABLESPACE_NAME,
       TO_CHAR ((T.ALLOCATED_SPACE - F.FREE_SPACE),'999,999') "USED (MB)",
       TO_CHAR (T.TOTAL_SPACE, '999,999') "TOTAL (MB)",
       TO_CHAR (T.TOTAL_SPACE - (T.ALLOCATED_SPACE - F.FREE_SPACE), '999,999') "FREE (MB)",
       TO_CHAR ((ROUND (((T.TOTAL_SPACE - (T.ALLOCATED_SPACE - F.FREE_SPACE))/T.TOTAL_SPACE)*100)),'999')||' %' PER_FREE
FROM   (
       SELECT       TABLESPACE_NAME,
                    ROUND (SUM (BLOCKS*(SELECT VALUE/1024
                                        FROM V$PARAMETER
                                        WHERE NAME = 'db_block_size')/1024)
                           ) FREE_SPACE
       FROM DBA_FREE_SPACE
       GROUP BY TABLESPACE_NAME
       ) F,
       (
       SELECT TABLESPACE_NAME,
       ROUND(SUM (MAXBYTES/1048576)) TOTAL_SPACE, 
       ROUND (SUM (BYTES/1048576)) ALLOCATED_SPACE
       FROM DBA_DATA_FILES
       GROUP BY TABLESPACE_NAME
       ) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME
--AND ROUND (((T.TOTAL_SPACE - (T.ALLOCATED_SPACE - F.FREE_SPACE))/T.TOTAL_SPACE)*100) < 10;

Restore archivelog from backup by logseq/scn

See details in RMAN effective use

set archivelog destination to '/disk1/oracle/temp_restore';
#restore archivelog from scn=460779 until scn =506141
restore archivelog from logseq 1 until logseq 10;