Tuesday, April 4, 2017

Manually drop and recreate Oracle Enterprise Manager 11g Database Console repos

5:14 PM Posted by Dilli Maharjan No comments

Login as user sys and execute DBMS_AQADM.DROP_QUEUE_TABLE. This will drop the queue table that is located on the sysman schema.
sqlplus "/ as sysdba"

Shutdown the database and start database in restrict mode to remove dbms_jobs.

Remove dbms_jobs and set em user context to 5
EXEC sysman.emd_maintenance.remove_em_dbms_jobs;
EXEC sysman.setEMUserContext('',5);

Using following PL/SQL block remove all the SYNONYMS that belongs to SYSMAN
SELECT owner, synonym_name name
FROM dba_synonyms
WHERE table_owner = 'SYSMAN';
FOR r1 IN c1
IF r1.owner = 'PUBLIC' THEN
EXECUTE IMMEDIATE 'DROP SYNONYM '||r1.owner||'.'||r1.name;

Drop role mgmt_user and users mgmt_view, sysman with cascade option. Once users are dropped we can disable restricted session. This will lift an instance from restricted mode to normal.
DROP ROLE mgmt_user;
DROP user mgmt_view cascade;
DROP user sysman cascade;

Make sure SYSMAN is dropped successfully.
select username, account_status, created from dba_users where username in ('SYSMAN');

Creating em repos with the command below.
$ORACLE_HOME/bin/emca -config dbcontrol db -repos create 

In my case I got error message with insufficient privileges. Since password file is missing in my case this error has occured.

Create password file for the database.
orapwd file=orapworcl password=oracle_4U entries=5

With new password file, em repos will be created successfully.

Thursday, March 30, 2017

RMAN recovery on new machine

2:16 PM Posted by Dilli Maharjan , 1 comment

Database Server is not available due to critical hardware failure. It may be some power issue resulting Mainboard, CPU, Memory, Storage failed. Fortunately we have backup on our DVD with the name bck.tar.gz. Backup contains the controlfile and spfile autobackup, archive logs and all RMAN backup pieces for all datafiles.

Please reinstall Oracle Software on the host we are trying to recover. Follow the URL below for the Oracle Software only installation. 

Do not install any database yet. In my case I have one existing rcat database on the machine on which I am planning to restore.

Extract the backup file. Since backup is in archive format, first I need to extract it. I am using /tmp as the destination to extract the file.

I switch to the rcat instance and export ORACLE_SID=orcl. Since I do not have any orcl instance I cannot directly switch to the orcl instance.

Startup database without any parameter file. In my case I need to restore my spfile too. Startup nomount force command will start oracle instance with the dummy parameter file.
startup nomount force.

Restore spfile with the autobackup.  This will restore the spfile. Database should be shutdown immediate and startup. Shutdown completed successfully but failed to start the instance . I am getting error as Unable to create audit trail file. We need to search for the parameter where my audit_file_dest is pointing.
restore spfile from '/tmp/bck/s_939891274.265.939891275';
shutdown immediate
startup nomount

Open spfile and find the directory where audit_file_dest is pointing. In this case it is pointing to /u01/app/oracle/admin/orcl/adump. Create directory with mkdir -p command.

Shutdown the database and startup with nomount option. Move ahead to restore controlfile.

Before restoring the controlfile we need to set dbid for the database to be restored. 
set dbid=1464472300

Restore controlfile from the same autobackup with the command below.
restore controlfile from '/tmp/bck/s_939891274.265.939891275';

Now we have successfully restored the controlfile. We can alter database to the mount state.
alter database mount;

Use list backup command to list the backup information stored on the controlfile.
list backup;


Verify all the File Names and make sure the directory exists before restore beings. In my case, some of the datafiles are stored on the OS directory. Make sure the directory exists if not, create them.

Copy backupset files from /tmp/bck directory to ASM directory. Make sure that you follow the following format. Each piece of backup file is on the following format:

Backup Piece name: nnndf0_TAG20170329T085330_0.276.939891255

Naming Defination: 

nnndf0: Backupset of datafiles without spfile
s indicates that the backup set includes the spfile; 
n indicates that the backup set does not include the spfile.

TAG20170329T085330_0: backup was initiated on 2017-03-20 08:53:30

276: File ID

939891255: Incarnation 

Never copy the incarnation value inside the ASM disk, this does not work and will give the error as below.

Instead use the syntax as below:
cp /tmp/bck/nnndf0_TAG20170329T085330_0.276.939891255 +fra/orcl/backupset/2017_03_29/nnndf0_TAG20170329T085330_0.276
cp /tmp/bck/nnndf0_TAG20170329T085330_0.279.939891271 +fra/orcl/backupset/2017_03_29/nnndf0_TAG20170329T085330_0.279
cp /tmp/bck/nnndf0_TAG20170329T085330_0.280.939891241 +fra/orcl/backupset/2017_03_29/nnndf0_TAG20170329T085330_0.280
cp /tmp/bck/nnndf0_TAG20170329T085330_0.282.939891225 +fra/orcl/backupset/2017_03_29/nnndf0_TAG20170329T085330_0.282
cp /tmp/bck/nnndf0_TAG20170329T085330_0.301.939891211 +fra/orcl/backupset/2017_03_29/nnndf0_TAG20170329T085330_0.301
cp /tmp/bck/s_939891274.265.939891275 +fra/orcl/backupset/2017_03_29/s_939891274.265.939891275
cp /tmp/bck/thread_1_seq_139.264.939891817 +fra/orcl/archivelog/2017_03_29/thread_1_seq_139.264
cp /tmp/bck/thread_1_seq_140.312.939891821 +fra/orcl/archivelog/2017_03_29/thread_1_seq_140.312

Execute crosscheck backup to verify the backup really exists on the required location. Make sure all the backup piece have the status Available.

Now we can start restoring the backup with the command below:
restore database;

Once restore is completed execute recover database to recover database till the archivelog exists.

Open database with the resetlogs option. Since block change tracking is enabled and the file is missing on our environment we can simply disable the block change tracking and move ahead with the alter database open.

Database restore and recover completed successfully. We can now access database with the database users.

Sunday, March 12, 2017

Shell script to fetch valid and unexpired domain from URL list

8:26 AM Posted by Dilli Maharjan No comments
Lets support we have URLs in the file as below:
[root@core tmp]# cat > url_lists.txt

We need to list all the domain that are valid and not expired with sorted as below:

Use Following shell command to filter out the domains.
cat all_url.txt | grep ".com" | grep -v [0-9] | grep -v "-" | grep -v "@" | tr '[:upper:]' '[:lower:]' | \
sed -e 's/http[s]*:\/\///g' | awk -F "/" '{print $1}' | awk -F "." '{print $(NF-1)"."$NF}' | sort | uniq > all_url_filtered_v1.txt

Command Description
grep ".com":Search for .com only
grep -v [0-9]:Removes url with numbers
grep -v "-":Removes url with hyphen
grep -v "@":Removes url with @
tr '[:upper:]' '[:lower:]':Converts upper case into lower case
\:Line break for the command that make our command more readable
sed -e 's/http[s]*:\/\///g':Find and replace http:// and https:// with nothing
awk -F "/" '{print $1}':Split the URL on basic of / and print the first part only
awk -F "." '{print $(NF-1)"."$NF}':Split the URL on basic of . and fetch second last, last and add "." in between
sort:Sort the output
uniq:list all the unique output only
> all_url_filtered_v1.txt:Redirect all output to all_url_filtered_v1.txt

Install jwhois package that will fetch the Expiration Date of the domain.
yum install jwhois-4.0-19.el6.x86_64
whois oraclecloudadmin.com
whois oraclecloudadmin.com | grep "Expiration Date" | awk '{print $NF}' | awk -F "T" '{print $1}'

Create a script file that read every line from the output file and use dig command to find the valid domains on basic of ANSWER SECTION and use whois command to find the Expiration Date and compare with current date of the system.

cat test.sh  

# loop throught the end of file
while read line
        # dig to find out the valid command
        nsoutput=$(dig ${line} | grep -A1 "ANSWER SECTION" | grep -v "ANSWER SECTION")
        # If we have answer section with IP address then the domain is considered as VALID
        if [ "${nsoutput}" != "" ]; then
                # Fetch the expiriry date of the domain and compare with the date.
                ED=$(whois ${line} | grep "Expiration Date" | awk '{print $NF}' | awk -F "T" '{print $1}')
                if [[ "${ED}" > "$(date +%Y-%m-%d)" ]]
                        echo ${line} >> all_url_filtered_final.txt
 done < all_url_filtered_v1.txt

Wednesday, March 8, 2017

Oracle Enterprise Manager 13c Release 2 Cloud Control installation

9:58 PM Posted by Dilli Maharjan No comments

For Oracle Enterprise Manager 13c R2 installation Oracle init parameter compatible parameter should be set to or higher.
show parameter compatible;

Make sure _allow_insert_with_update_check parameter is set to be true.
alter system set "_allow_insert_with_update_check" = true;

Create Middleware Home directory, directory to store agent, BI conf, BI cluster and swlib directory.

Download Oracle Enterprise Manager Cloud Control 13c Release 2 from URL below:

Change mode of the file em13200p1_linux64.bin to executable and execute the file.
The warning message will be appeared if you do not have 10GB free space on the /tmp directory. Alternatively we can provide the new tmp location for the installation.

Below is the landing page once you have started the installation process.

Uncheck the option I wish to receive security updates via My Oracle Support.

Once you uncheck the option Click on Next to continue.

Critical message will be displayed. It is safe to ignore on the test or the lab environment. Click on Yes to continue.

Click Skip on the Software updates and Click on Next to continue.

Prerequisite checks will be performed. Make sure that the status of all the checks are Succeeded. If there is any failure, fix the issue before moving ahead. Click on Next.

Make sure you have selected Create a new Enterprise Manager system and then Simple.

Set proper Middleware Home Location, and Agent Base Directory.

Set Administrator password and the Database connection Details for the repository database.

Information window will be displayed regarding the database character set. Click on OK to continue.

Following error message will be displayed. Click on Yes to fix the issue by the installer itself.

Again new Warning message will be displayed. Click on OK to fix the issue by the installer itself.

Configure BI config Volume directory and the BI Cluster Volume directory and Click on Next to continue.

Check the review and click on Install to begin installation.

Installation is on progress ##########.

Installation is on progress ####################.

Installation is on progress ########################################.

Execute Configuration scripts window will be displayed. Make sure the listed script has been executed as user root before Click on OK.

Once the script execution is completed Click on OK on the Execute configuration script window.

Note the information displayed on the summary screen. Click on Close to complete the installation.

Now you may open the Enterprise Manager Cloud Control 13c using the url below:

Set Accessibility Preference and Click on the Save and Continue.

Click on I Accept on the EULA window.