Restore Ranger Admin Database

To restore Ranger admin databases, perform the following steps. First Stop Ranger Admin and KMS services if it's running and then follow below given steps.

MySQL

Perform the following steps to restore a database.

Delete an existing Database.
Create an empty new database on the database host.

Restore the database using below msyql command.

mysql -u root
drop database ranger;
create database ranger;
GRANT ALL PRIVILEGES ON ranger.* TO 'rangeradmin'@'localhost';
$ mysql -u [username] -p  existing_empty_db_name < dump_fileName.sql

Example

mysql -u rangeradmin -p rangeradmin < /root/backups/ranger/db/admin/ranger.sql

Select the Enter key. Type the database password at the password prompt.

POSTGRES

Perform the following steps to restore a database.

Delete an existing Database.
Create an empty new database in its place.

Execute the below command on postgres db host.

dropdb -U owner_username dbname; [Enter db owner password at the prompt]

Example

dropdb -U rangeradmin ranger;

su  -  postgres
psql 
create database ranger;
ALTER DATABASE ranger OWNER TO rangeradmin; 
\q
exit
psql -U rangeradmin ranger < /root/backups/ranger/db/admin/ranger.sql

[Enter db owner password at the prompt as rangeradmin]

Set path to oracle home if required :

export ORACLE_HOME=/opt/oracle/product/12.2.0
export PATH=${PATH}:${ORACLE_HOME}/bin
export ORACLE_SID=orcl12c

Restore ranger admin database.

rm -rf del_admin_tbl_cmd.sql
sqlplus -s rangeradmin/rangeradmin << EOF 
        			spool on
        			spool del_admin_tbl_cmd.sql
        			SELECT 'DROP TABLE "' || TABLE_NAME || '" CASCADE CONSTRAINTS;' FROM user_tables
        			union ALL
        			select 'drop '||object_type||' '|| object_name || ';' from user_objects 
        			where object_type in ('VIEW','PACKAGE','SEQUENCE', 'PROCEDURE', 'FUNCTION')
        			union ALL
        			SELECT 'drop '
        			||object_type
        			||' '
        			|| object_name
        			|| ' force;'
        			FROM user_objects
        			WHERE object_type IN ('TYPE');
        			spool off
        			@del_admin_tbl_cmd.sql
        			exit;   
                             EOF

Type the database password at the password prompt and then execute the below imp command.

imp rangeradmin/rangeradmin file=backups/ranger/db/admin/orcl12c.sql log=backups/ranger/db/admin/restore.log