Oracle databases can be managed and maintained with the help of a number of administrative commands that are available from Oracle. Various tools, such as SQL*Plus, SQL Developer, or Enterprise Manager, can be used to execute these commands.
Oracle's most frequently used administrative commands are as follows:
1. ADD A USER: The database's new user account is created with this command. Database object access and manipulation privileges can be granted to the user account.
2. CHANGE USER: Changing a user's password or altering their privileges can be done with this command on an existing user account.
3. QUIT USER: A user account can be removed from the database using this command.
4. MAKE A TABLE: A new database table is created with this command. The command specifies the table structure and column definitions.
5.ALTER TABLE: A table can be changed with this command by adding or removing columns, changing the data types of those columns, or adding constraints.
6.DROP TABLE: To remove a table and all of its data from the database, use this command.
7. MAKE AN INDEX: An index on one or more columns of a table can be created with this command, which can improve query performance.
8. CHANGE INDEX: Changing the storage properties of an existing index or rebuilding it can be done with this command.
9. OPTION INDEX: An index can be removed from the database using this command.
10. SECONDARY DATABASE: The entire database is backed up using this command, which can be used to restore the database in the event of a disaster or data loss.
11. DATABASE RECOVERY: A database backup can be restored using this command to a specific point in time.
12. ACCESS THE DATABASE: To bring a restored database up to date, this command is used to apply archived redo logs.
Oracle offers a wide range of administrative commands, some of which can be seen here. Depending on the requirements of the database environment, there are numerous additional commands and options for managing and maintaining Oracle databases.
# Implementation:
Which files need to be backed up
•REPORT NEED BACKUP;
List of permanent datafiles
- REPORT SCHEMA;
The following 2 commands allow you to view what backups are obsolete and delete them.
- REPORT OBSOLETE;
- DELETE OBSOLETE;
The validate command can be used to check for corruption or verify that database files are in the correct location.
- BACKUP VALIDATE DATABASE ARCHIVELOG ALL;
If you run any batch jobs with the nologging option, the following command will report any files that have not been backed up since the last nolog operation.
- REPORT UNRECOVERABLE;
To free up disk space of archive logs that you know have already been backed up
- DELETE ARCHIVELOG UNTIL TIME 'SYSDATE-1';
The crosscheck command is used to verify that backups of the database are in the location known by the repository and to verify that archive logs are still on disk and have not been deleted outside of RMAN.
- CROSSCHECK BACKUP OF DATABASE;
The LIST commands allow you to view the backup information of the database, datafile, tablespaces, and controlfiles.
- LIST BACKUP OF DATAFILE 4;
- LIST BACKUP OF CONTROLFILE;
- list backup of archivelog all;
- RMAN>crosscheck backup;
- RMAN>delete expired backup;
- RMAN>crosscheck archivelog all;
- RMAN>delete expired archivelog all;
The recovery catalog should be resyncronized on a regular basis so that changes to the database structure and presence of new archive logs is recorded. Some commands perform partial and full resyncs implicitly, but if you are in doubt you can perform a full resync using the follwoing command:
- RMAN> resync catalog;
- List all archivelog backups for the past 24 hours:
- RMAN> LIST BACKUP OF ARCHIVELOG FROM TIME 'sysdate-1';
