Month: June 2019

VirtualBox Startup Errors: Result Code: E_FAIL (0x80004005) Interface: IMachine {b2547866-a0a1-4391-8b86-6952d82efaa0}

Oracle VirtualBox failed to starts up with errors.

Environment:

       Windows 10
       VirtualBox 5.16
The virtual machine 'racnode1' has terminated unexpectedly during 
startup with exit code 1 (0x1). More details may be available in 
'C:\Users\james huang\VirtualBox VMs\racnode1\Logs\VBoxHardening.log'.
Result Code:
 
E_FAIL (0x80004005)
Component:
 
MachineWrap
Interface:
 
IMachine {b2547866-a0a1-4391-8b86-6952d82efaa0}

WORKAROUND

First Way:

If you have closed the Virtual Machine abnormally and you have chosen to save the machine’s state, then maybe the VM’s state is corrupted. So:

1. Right click on the Virtual Machine and select Discard Saved State.
2. Then try to start the virtual machine. If the problem persists, continue to the next method below.

Second Way:

1) Uninstall old VirtualBox 5.16 on Windows 10.
2) Reboot Windows 10.
3) Install new VirtualBox 6.08, and then reboot Windows 10.
4) Restart VirtualBox 6.08, and each VM servers.
If  host-only adapter interface is missing, then go to “Oracle VM VirtualBox Manager” ->File ->”Host Network Manager” to crate one as below:

VirtualBox Host Network Manager
Host Network Manager

5) Run “ipconfig /all” to check “VirtualBox Host-Only Ethernet Adapter” is created in Windows 10.

"ipconfig /all" to check "VirtualBox Host-Only Ethernet Adapter
ipconfig /all VirtualBox Host-Only Ethernet Adapter

6) Finally download and install “Oracle VM VirtualBox Extension Pack “.

install "Oracle VM VirtualBox Extension Pack"
Oracle VM VirtualBox Extension Pack
Advertisement

ORA-16665: timeout waiting for the result from a database

$ oerr ora 16665
16665, 0000, "timeout waiting for the result from a database"
// *Cause: The Oracle Data Guard broker was forced to time out a network
// connection to a remote database because:
// - The network call to the remote database did not complete in
// a timely manner.
// - The remote database was unable to execute the command due to
// an instance failure.
// *Action: Check Data Guard broker log files for the details of the failure.
// If the network call did not complete in a timely manner, increase
// the CommunicationTimeout configuration property value and reissue
// the command.

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

PostgreSQL: Script to Kill Sessions

This is often used script to kill PostgreSQL session(s).

pg_terminate_backend() AND pg_cancel_backend()

1) kill all connections  of  a specified database name.

-- List all available databases

postgres=>\l

postgres=> SELECT pg_terminate_backend(pg_stat_activity.pid)
           FROM   pg_stat_activity
           WHERE  datname = 'DATABASE NAME'
              AND pid <> pg_backend_pid();

2) kill all connections  of a specified user name.

postgres=> SELECT pg_terminate_backend(pg_stat_activity.pid)
             FROM pg_stat_activity
            WHERE usename = 'testuser'
              AND pid <> pg_backend_pid();

3) kill all sessions of current database.

postgres=> SELECT pg_terminate_backend(pg_stat_activity.pid)
             FROM pg_stat_activity
            WHERE datname = current_database()
              AND pid <> pg_backend_pid();

4) kill a connection  of a specified pid.

postgres=> SELECT pg_terminate_backend(pid);

5) kill a running SQL of a session, but does not kill the session.

postgres=> SELECT pg_cancel_backend( pid );

It is useful when a running SQL is taking long time than expected.

Drop a Role with Dependent Objects in PostgreSQL

Try to drop a role/user in PostgreSQL, and get below errors:

postgres=>drop role testuser_read;
ERROR: role "testuser_read" cannot be dropped because some objects depend on it
DETAIL: privileges for table user1.tab1
privileges for table table user1.tab1
privileges for sequence user1.seq1
...
..
.

SOLUTION

1)Revoke objects privileges from this role/user which is to be dropped:

postgres=> select grantee,table_schema,table_name,privilege_type
            from information_schema.role_table_grants
           where grantee='testuser_read' 
           order by table_name,privilege_type;
grantee    | table_schema | table_name | privilege_type
-----------+--------------+------------+----------------
testuser_read |    user1     | tab1       | SELECT
testuser_read |    user1     | tab2       | SELECT
...
..
.

postgres=> select 'revoke '||privilege_type ||' on '||
           table_schema||'.'||table_name||' from '||grantee||' ;'
from information_schema.role_table_grants
where grantee='testuser_read' ;

-----------------------------------------------------------------------------
 revoke SELECT on user1.tab1 from testuser_read ;
 revoke SELECT on user1.tab2 from testuser_read ;
...
..
.

OR just ...

postgres=>REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA user1 
          FROM testuser_read; 

postgres=>REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA user1
          FROM testuser_read; 

postgres=>alter default privileges in schema testuser revoke select  
          on tables from testuser_read;

postgres=>alter default privileges in schema testuser revoke select ,  
          usage on sequences from testuser_read;

2) Revoke depended roles.

\du to find dependent roles

postgres=>\du
postgres=>revoke other_role from testuser_read;

3) Drop the role.

postgres=> drop role testuser_read;
ERROR:  role "testuser_read" cannot be dropped because some objects depend on it
DETAIL:  privileges for default privileges on new sequences belonging to role testuser in schema testuser
privileges for default privileges on new relations belonging to role tetsuser in schema testuser

postgres=> ALTER DEFAULT PRIVILEGES FOR ROLE testuser IN SCHEMA testuser REVOKE ALL ON TABLES FROM testuser_read;
ALTER DEFAULT PRIVILEGES
postgres=> ALTER DEFAULT PRIVILEGES FOR ROLE testuser IN SCHEMA testuser REVOKE ALL ON SEQUENCES FROM testuser_read;
ALTER DEFAULT PRIVILEGES
postgres=> drop role testuser_read;
DROP ROLE
postgres=>

Create Materialised View Log on the Remote Database

DB1 is a local database, on which the materialized views are to be created.
DB2 is a remote database, on which materialized view logs are created .

The high level steps are:

  • on local database DB1, create a database link between DB1 and DB2 database.
  • On the remote database DB2, create materialized logs on the tables of DB2.
  • On local database DB1, create materialized views with Fast Refresh option , etc.
  • On Local database DB1, run a complete refresh on your materialized views:          Exec dbms_snapshot.refresh(‘schema_name.mview_name’,’c’);
  • Then on local database DB1 again ,  refresh the materialized  view using the Fast Refresh option:
    Exec dbms_snapshot.refresh(‘schema_name.mview_name’,’f’);