A workaround for RMAN-04014: startup failed: ORA-27069: attempt to do I/O beyond the range of the file after a clone from active database

My clone from active database failed at the very end of the procedure with

RMAN-04014: startup failed: ORA-27069: attempt to do I/O beyond the range of the file

This happened after media recovery was complete, and after the oracle instance was started, as can be seen from the log file below:

RMAN-08181: media recovery complete, elapsed time: 00:00:21
RMAN-03091: Finished recover at 06-FEB-19
RMAN-08031: released channel: c1
RMAN-08031: released channel: c2
RMAN-08031: released channel: aux1
RMAN-08031: released channel: aux2
RMAN-06196: Oracle instance started

Total System Global Area   22749904896 bytes

Fixed Size                     8632928 bytes
Variable Size               3758097824 bytes
Database Buffers           18924699648 bytes
Redo Buffers                  58474496 bytes

RMAN-08161: contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''TESTDB01'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
}
RMAN-08162: executing Memory Script

RMAN-06162: sql statement: alter system set  db_name =  ''TESTDB01'' comment= ''Reset to original value by RMAN'' scope=spfile

RMAN-06162: sql statement: alter system reset  db_unique_name scope=spfile
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 02/06/2019 04:39:51
RMAN-05501: aborting duplication of target database
RMAN-04014: startup failed: ORA-27069: attempt to do I/O beyond the range of the file
Additional information: 51
Additional information: 1
Additional information: 48

This is a bummer, espescially if you've cloned a very large database and have waited for a long time for it to finish.
There is very little information about this error, but a workaround to save the freshly cloned auxiliary database, is to recreate the controlfile from script

The workaround is outlined in one of my older posts, but basically it boils down to the following steps:

1. Go from the source database, generate a "create controlfile" script
2. Transfer it to the auxiliary server
3. Edit it to suit the auxiliary database data files
4. Remove the controlfile files from the auxiliary server
5. Startup the instance in nomount mode and recreate the controlfile
6. Open the database with the resetlog option

How to create a trigger that will add newly created tables to a role

A commonly asked for functionality in my day-to-day work is READ-ONLY access for users to various tables in the database. This is easy to solve by creating a role, and granting SELECT on the tables to that role.

Every time a new table is created, the role must be updated. This will for most of the time be impossible to administer, and should be automated by creating a trigger on the schema owning the tables/views.

Here is how:

create a new role:

create role my_ro_role;

Grant execute permissions on dbms_jobs to scott:

grant create job to scott;

Note that if you do not have the password for the schema that should own the trigger, you cannot use the SYS user to create it for them. You need SYSTEM or another DBA user to avoid the error

ORA-30510: system triggers cannot be defined on the schema of SYS user

Create the trigger. In this example, I am using the system user to accomplish this:

conn system/password
create or replace trigger scott.add_to_ro_role
after CREATE on schema
declare
 l_str varchar2(255);
 l_job number;
begin
 if ( ora_dict_obj_type = 'TABLE' ) then
  l_str := 'execute immediate "grant select on ' || ora_dict_obj_name || ' to my_ro_role";';
  dbms_job.submit( l_job, replace(l_str,'"','''') );
 end if;
end;
/

Every time a new table is created in the SCOTT schema, SELECT on the same table will be granted to the role.
The role can then be granted to individual users.

Thanks to Tron Malmø-Lund for the idea and the code to implement it! ;-)

How to solve SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled

Logged in as the user owning the objects, you are getting the following error when trying to use the autotrace feature:

SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

Solution: run the necessary scripts first:

conn / as sysdba
@$ORACLE_HOME/sqlplus/admin/plustrce.sql
grant plustrace to scott;

conn scott/password

@$ORACLE_HOME/rdbms/admin/utlxplan.sql
exit

After this is done, the errors should no longer appear.

last - show listing of last logged in users

A potentiall very useful tool when investigating what really happened, is the command "last".

Here is an abbrivated output:

root     pts/4        psl0ssh01.skead. Thu Jan 31 12:56   still logged in
oracle   pts/4        w7x64proddvh111. Thu Jan 31 12:50 - 12:52  (00:02)
root     pts/6        psl0ssh01.skead. Thu Jan 31 12:18 - 12:23  (00:05)
root     pts/5        psl0ssh01.skead. Thu Jan 31 11:08   still logged in
admin    pts/5        psa0addm2.skead. Wed Jan 30 20:21 - 20:23  (00:01)
admin    pts/5        psa0addm2.skead. Wed Jan 30 20:11 - 20:21  (00:09)

The shell variable HISTTIMEFORMAT and the "history" command

If you need to investigate the history of commands on your linux/Unix server, set the HISTTIMEFORMAT parameter first:

HISTTIMEFORMAT="%d/%m/%y %T " 

After that, you'll find a much more precis listing of your history:

  962  31/01/19 11:08:50 cd product/
  963  31/01/19 11:08:50 ls -al
  964  31/01/19 11:08:50 du -sh *
  965  31/01/19 11:08:50 cd ..

instead of

  962  cd product/
  963  ls -al
  964  du -sh *
  965  cd ..

The man page for history states

If the HISTTIMEFORMAT variable is set, the time stamp information associated with each history entry is written to the history file, marked with the history comment character.

How to solve "User "superuser" has an expired password" in postgreSQL

psql
psql (11.2)
Type "help" for help.

postgres=# alter role superuser valid until 'infinity';
ALTER ROLE

How to find the log file for a PostgreSQL database

Look in the file $PGDATA/postgresql.conf:

#------------------------------------------------------------------------------
# REPORTING AND LOGGING
#------------------------------------------------------------------------------

# - Where to Log -

log_destination = stderr # Valid values are combinations of
                                        # stderr, csvlog, syslog, and eventlog,
                                        # depending on platform.  csvlog
                                        # requires logging_collector to be on.

# This is used when logging to stderr:
logging_collector = on # Enable capturing of stderr and csvlog
                                        # into log files. Required to be on for
                                        # csvlogs.
                                        # (change requires restart)

# These are only used if logging_collector is on:
log_directory = log # directory where log files are written,
                                        # can be absolute or relative to PGDATA

log_filename = 'postgresql-%a.log' # log file name pattern,
                                        # can include strftime() escapes

log_truncate_on_rotation = on # If on, an existing log file with the
                                        # same name as the new log file will be
                                        # truncated rather than appended to.
                                        # But such truncation only occurs on
                                        # time-driven rotation, not on restarts
                                        # or size-driven rotation.  Default is
                                        # off, meaning append to existing files
                                        # in all cases.
log_rotation_age = 1d # Automatic rotation of logfiles will
                                        # happen after that time.  0 disables.

log_rotation_size = 0 # Automatic rotation of logfiles will
                                        # happen after that much log output.
                                        # 0 disables.

So in my case, the directory $PGDATA/log should contain the database server's log files.

The directory listing shows:

[postgres@myserver /pgdata/data/log]$ ls -latrh
total 5.6M
drwx------  2 postgres postgres 4.0K Dec 23 00:00 .
-rw-------  1 postgres postgres 867K Jan 24 23:59 postgresql-Thu.log
-rw-------  1 postgres postgres 869K Jan 25 23:59 postgresql-Fri.log
-rw-------  1 postgres postgres 873K Jan 26 23:59 postgresql-Sat.log
-rw-------  1 postgres postgres 873K Jan 27 23:59 postgresql-Sun.log
-rw-------  1 postgres postgres 873K Jan 28 23:59 postgresql-Mon.log
-rw-------  1 postgres postgres 873K Jan 29 23:59 postgresql-Tue.log
drwx------ 21 postgres postgres 4.0K Jan 30 10:47 ..
-rw-------  1 postgres postgres 407K Jan 30 11:01 postgresql-Wed.log

Since I am writing this post on a Wednesday, the log currently being written to is postgresql-Wed.log, and putting a tail on that will be somewhat like tailing the alert log of an oracle database.

Source: The online documentation